LeetCode Sql 178.分数排名 排名函数

LeetCode sql题目

编写一个 SQL 查询来实现分数排名。
如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。

例如，根据上述给定的 Scores 表，你的查询应该返回（按分数从高到低排列）：

重要提示：对于 MySQL 解决方案，如果要转义用作列名的保留字，可以在关键字之前和之后使用撇号。例如 Rank

解答：

第一种方案是将问题分成两步来完成：

一是将分数按照降序排列

select Score
from Scores
order by Score desc 

二是对分数进行rank排名。
rank排名的计算难住了我，如何做到呢？
网上为我们提供的思路是：先提取出大于等于X的所有分数集合H，将H去重后的元素个数就是X的排名。
如：100 100 99 99 99 98
≥100的有2个100，去重后H里的元素只有一个，那么100的rank为1。
同理≥99的有2个100，3个99，去重后H里的元素有100和99两个，那么99的rank为1。

原理 相同的分数属于同一个rank，而不同的分数属于不同的rank，所以rank值相当于≥的分数值种类有多少个。

提取集合H：

select Score from Scores
where Score >= X 

再对集合X去重：

select count(distinct Score)
from Scores
where Score >= X as Rank

结合起来为：

select Score,
(select count(distinct Score)
from Scores
where Score >= X) as Rank
from Scores
order by Score desc 

第二种方案是通过排名函数直接生成rank

值得注意的三个窗口函数。现在给定五个成绩：99，99，85，80，75。
ROW_NUMBER()：将select查询到的数据进行排序，每一条数据加一个序号，他不能用做于学生成绩的排名，一般多用于分页查询。 排名结果：1，2，3，4，5。
后两者都是排名函数，DENSE_RANK()密集的排名他和RANK()区别在于，排名的连续性，DENSE_RANK()排名是连续的，RANK()是跳跃的排名，所以一般情况下用的排名函数就是RANK()。
DENSE_RANK()。排名结果：1，1，2，3，4。
RANK(）。排名结果：1，1，3，4，5。

使用DENSE_RANK()排名函数的方案：

select score, DENSE_RANK()
OVER (ORDER BY Score DESC) as 'Rank'
from Scores

窗口函数

窗口函数的基本语法：

<窗口函数>over (partition by <用于分组的列名>
                            order by<用于排序的列名>)

窗口函数可以是以下的两种函数：
1）专用窗口函数，就是前面提到的rank, dense_rank, row_number
2) 聚合函数，如sum, avg, count, max, min等
因为窗口函数是对where或者group by子句处理后的结果进行操作，所以窗口函数原则上只能写在select子句中。