SQL排名問題，100% leetcode答案大公開！

阿閒KOP發表於2021-03-27
SQLLeetCode

（首先原諒我最近新番看多了，起了一箇中二的名字）

最近在找實習，所以打算系統總結（複習）一下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 Scores
ORDER 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的所有樣例的。

    leetcode_ac

  • 使用聯結模擬視窗函式

    SET @currank := 0;
Select os.id, r.Score, r.rank
From Scores os 
LEFT JOIN (SELECT *, (@currank := @currank + 1) As 'rank'
           From (Select *
                 	From Scores
                 	Group BY Score
                 	ORDER BY score DESC
                 ) AS ra
           ) AS r
ON os.score = r.score
ORDER 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）。

以上就是遇到比較多的排名問題的解法啦。關於視窗函式，其實還有更多的用途，比如分組排名。後面可能專門寫一篇來介紹這幾個排名視窗函式。

最後慣例：

能力有限，如有錯漏，多多包涵，歡迎指正！

相關文章