Rank排名函数
1、rank()
按照某字段的排序结果添加排名，但是他是跳跃的、间断的排名
partition by子句按照对应字段将结果集分为多个分区，然后order by子句按分数对结果集进行排序
RANK() OVER (
PARTITION BY <expression>[{,<expression>...}]
ORDER BY <expression> [ASC|DESC], [{,<expression>...}]
) 例：按照score进行排名
SELECT score, rank() over(ORDER BY score desc) as 'Rank'
FROM rank;
-->
+------+---------+
| score| Rank |
+------+---------+
| 100 | 1 |
| 100 | 1 |
| 95 | 3 |
| 95 | 3 |
| 95 | 3 |
| 90 | 6 |
| 89 | 7 |
+------+---------+按照name进行分区，根据分数进行排名
select name, score ,rank() over(partition by name, order by score desc) 'Rank'
from rankl;
+------+------+---------+
| name | score| Rank |
+------+------+---------+
| a | 100 | 1 |
| a | 90 | 2 |
| a | 89 | 3 |
| b | 100 | 1 |
| c | 95 | 1 |
| d | 95 | 1 |
| e | 95 | 1 |
+------+------+---------+2、dense_rank()
dense_rank()是的排序数字是连续的、不间断。当有相同的分数时，它们的排名结果是并列的，例如，1,2,2,3。
DENSE_RANK() OVER (
PARTITION BY <expression>[{,<expression>...}]
ORDER BY <expression> [ASC|DESC], [{,<expression>...}]
) 例：leetcode178. 分数排名
select score, dense_rank() over(order by score desc) 'rankd'
from Scores
输入:
Scores 表:
+----+-------+
| id | score |
+----+-------+
| 1 | 3.50 |
| 2 | 3.65 |
| 3 | 4.00 |
| 4 | 3.85 |
| 5 | 4.00 |
| 6 | 3.65 |
+----+-------+
输出:
+-------+------+
| score | rank |
+-------+------+
| 4.00 | 1 |
| 4.00 | 1 |
| 3.85 | 2 |
| 3.65 | 3 |
| 3.65 | 3 |
| 3.50 | 4 |
+-------+------+