SQL sort

Time:2020-11-26

Sort 1, 2, 2, 2, 3, 4 with the following function:

select row_ Number() over (order by field) from table name;

1、 row_number()

Values: 1, 2, 2, 2, 3, 4

Sequence: 1, 2, 3, 4, 5, 6

2、rank()

Values: 1, 2, 2, 2, 3, 4

Sequence: 1, 2, 2, 2, 5, 6

3、dense_rank()

Values: 1, 2, 2, 2, 3, 4

Sequence: 1, 2, 2, 2, 3, 4

4. Ntile (n) divides the data in the table into n groups and returns the group number

If it is exactly divided by N, it will be evenly divided into n groups. If the division is not possible, the first group will add one more item to divide the remaining data into n-1 groups. If the division can not be done, the remaining data will be divided into n-1 groups, and the second group will be added with one more line until it can be evenly divided.

Tile (4) divided the data into four groups

Value ntile (4)

1        1

2        1

2        2

2        2

3       3

4       4

5、PERCENT_ Rank() the rank value of the current line in the group – 1 / the total number of rows in the group – 1

Value percentage_ RANK()

1        0

2        0.2

2        0.2

2        0.2

3       0.8

4       1

6、cume_ Dist() less than or equal to the current number of rows / total rows in the group

Value percentage_ RANK()

1        0.1666

2        0.6666

2        0.6666

2        0.6666

3       0.8333

4       1

 

collect_ set、collect_ List group by group turn each group of columns into rows and columns_ Set is used to de duplicate the content of the line_ List all of them without repeating them.

select  depno ,  collect_list(name)  group by  depno; 

 

Recommended Today

Redis design and implementation 4: Dictionary Dict

In redis, the dictionary is the infrastructure. Redis database data, expiration time and hash type all take the dictionary as the underlying structure. Structure of dictionary Hashtable The implementation code of hash table is as follows:dict.h/dictht The dictionary of redis is implemented in the form of hash table. typedef struct dictht { //Hash table array, […]