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;