SQL排名问题，100% leetcode答案大公开！
（首先原谅我最近新番看多了，起了一个中二的名字）
最近在找实习，所以打算系统总结（复习）一下sql中经常遇到问题。不管是刷leetcode还是牛客的sql题，有一个问题总是绕不开的，那就是排名问题。其实对于MySql8.0以上版本来说，排名问题已经很容易解决了。因为MySql8.0之后开始支持三个窗口函数，分别是rank(),dense_rank()以及row_number()。这三个窗口函数对应了排名问题中最常见的三种情况。而对于之前的版本，则需要模拟这几个函数。
网上也有很多相关的文章，但实际上他们给出的代码都无法100%通过leetcode的样例。于是就想在这里重新总结一下sql中的排名问题。我们以分数排名为例，假设有一个数据表scores包括两个字段id和score，需要对score进行排名。
| id | score |
|---|---|
| 1 | 0 |
| 2 | 15 |
| 3 | 15 |
| 4 | 15 |
| 5 | 17 |
| 6 | 18 |
| 7 | 20 |
不同的排名需求适合不同的场景，但为了方便比较，就不分来举例了。对于排名的结果，一般会包括以下几种情况：
1. 同样的分数不同的名次，且排名连续
如果是这样的排名需求，排名的结果应该是：
| id | score | rank |
|---|---|---|
| 7 | 20 | 1 |
| 6 | 18 | 2 |
| 5 | 17 | 3 |
| 2 | 15 | 4 |
| 3 | 15 | 5 |
| 4 | 15 | 6 |
| 1 | 0 | 7 |
窗口函数 row_number()
SELECT id, score, row_number() over (order by score desc) as 'rank'FROM Scores;模拟窗口函数
SET @curRank = 0;SELECT id, Score, (@currank := @currank + 1) As 'rank' From ScoresORDER BY score DESC;自行模拟窗口函数的关键就在于要设置一个变量来保存当前的排名。
2. 同样的分数相同的名次，且排名连续（Leetcode 178）
如果是这样的排名需求，排名的结果应该是：
| id | score | rank |
|---|---|---|
| 7 | 20 | 1 |
| 6 | 18 | 2 |
| 5 | 17 | 3 |
| 2 | 15 | 4 |
| 3 | 15 | 4 |
| 4 | 15 | 4 |
| 1 | 0 | 5 |
使用窗口函数 dense_rank()
SELECT id, score, dense_rank() over (order by score desc) as 'rank'FROM Scores;使用变量模拟窗口函数
对于这种排名，网上给出的代码大多是这样的：
SELECT tmp.score, @ranking := case when @lastscore = tmp.score then @ranking when @lastscore := tmp.score then @ranking +1 end as 'rank'FROM (select * from scores order by score desc) tmp, (select @ranking := 0, @lastscore := null) r;这短代码可能在一般的数据表中都没问题，但在leetcode上是不能完全ac的。主要有两个问题：
- 返回的rank排名是字符串，而不是数字，会导致样例失败
- 如果表中score有值为0，排名结果就会是null。
这种做法的思想是：
- 如果当前的score跟上一行score（@lastscore）相等，则@ranking不增加（@ranking := @ranking）;
- 否则就 @lastscore := tmp.score （这一句在score不为0的时候永远为真），给 @lastscore 赋新值的同时，@ranking增加1（@ranking := @ranking + 1）。
但是当score为0时，两个when中的表达式都是False，所以什么都不执行，导致0值的结果为null。
针对这种情况，可以稍微改进一下：
SELECT tmp.score, @ranking := case when @lastscore = tmp.score then @ranking +0 when @lastscore := tmp.score then @ranking +1 else @ranking := @ranking + 1 end as 'rank'FROM (select * from scores order by score desc) tmp, (select @ranking := 0, @lastscore := null) r;- 第一点是在第一个when处增加了 +0 ，进行类型转换
- 第二点是增加了 else @ranking := @ranking + 1 ，保证出现0值的时候，仍然可以进行排名。
这样的写法是可以通过leetcode的所有样例的。
使用联结模拟窗口函数
SET @currank := 0;Select os.id, r.Score, r.rankFrom Scores os LEFT JOIN (SELECT *, (@currank := @currank + 1) As 'rank' From (Select * From Scores Group BY Score ORDER BY score DESC ) AS ra ) AS rON os.score = r.scoreORDER BY r.score DESC;这种查询其实可以看做两步，第一步是使用Group BY分组，进行了一个无重复值的排名（其实就是第一种情况），之后再把这个排名表Left Join到原始的表中，然后对组合表在进行一次排名。
3. 同样的分数相同的名次，且排名不连续
这种情况应该是最符合现实中分数排名的。排名结果如下：
| id | score | rank |
|---|---|---|
| 7 | 20 | 1 |
| 6 | 18 | 2 |
| 5 | 17 | 3 |
| 2 | 15 | 4 |
| 3 | 15 | 4 |
| 4 | 15 | 4 |
| 1 | 0 | 7 |
使用窗口函数rank（）
SELECT id, score, rank() over (order by score desc) as 'rank'FROM Scores;使用变量模拟窗口函数
SELECT tmp2.id, tmp2.score, tmp2.ranking AS 'rank'FROM (SELECT tmp.*, @rownum := @rownum+1 AS rownum, @ranking := case when @lastscore = tmp.score then @ranking + 0 when @lastscore := tmp.score then @rownum + 0 else @rownum + 0 end as ranking FROM (select * from scores order by score desc) tmp, (select @rownum := 0, @lastscore := null, @ranking := 0) r ) AS tmp2这里的做法其实相当于第一种情况和第二种情况的结合，用两个变量分别存储排名和当前的行数。
- 如果当前的score跟上一行score（@lastscore）相等，则@ranking不增加（@ranking := @ranking +0 ）;
- 否则就 @lastscore := tmp.score ，这里跟第二种情况不同在于@ranking不再是增加1，而是赋值为行数（@rownum）。
以上就是遇到比较多的排名问题的解法啦。关于窗口函数，其实还有更多的用途，比如分组排名。后面可能专门写一篇来介绍这几个排名窗口函数。
最后惯例：
能力有限，如有错漏，多多包涵，欢迎指正！
原文转载：http://www.shaoqun.com/a/647728.html
易佰：https://www.ikjzd.com/w/1482
myyearbook：https://www.ikjzd.com/w/726
（首先原谅我最近新番看多了，起了一个中二的名字）最近在找实习，所以打算系统总结（复习）一下sql中经常遇到问题。不管是刷leetcode还是牛客的sql题，有一个问题总是绕不开的，那就是排名问题。其实对于MySql8.0以上版本来说，排名问题已经很容易解决了。因为MySql8.0之后开始支持三个窗口函数，分别是rank(),dense_rank()以及row_number()。这三个窗口函数对应了
韩国naver：https://www.ikjzd.com/w/1727
wangwei：https://www.ikjzd.com/w/1744
淘粉8：https://www.ikjzd.com/w/1725
Amy聊跨境：英国VAT递延，为什么目前办VAT递延那么难？：https://www.ikjzd.com/home/141299
宠物可伸缩牵引绳类目亚马逊美国站市场调查报告：https://www.ikjzd.com/home/14331
Listing流程化运营系列--CPC广告投放策略：https://www.ikjzd.com/tl/21752
Comments
Post a Comment