教你用SQL實現統計排名頭條網

2021-03-31 11:28:21 佚名

前言：

在某些應用場景中，我們經常會遇到一些排名的問題，比如按成績或年齡排名。排名也有多種排名方式，如直接排名、分組排名，排名有間隔或排名無間隔等等，這篇文章將總結幾種MySQL中常見的排名問題。

創建測試表

<code>create table scores_tb (
    id int  auto_increment primary key,
    xuehao int not null, 
    score int not null
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into scores_tb (xuehao,score) values (1001
,89
),(1002
,99
),(1003
,96
),(1004
,96
),(1005
,92
),(1006
,90
),(1007
,90
),(1008
,94 
);

 
mysql> select * from scores_tb;
+----+--------+-------+
| id |
 xuehao | score |
+----+--------+-------+
|  1 |
   1001
 |    89 |
|  2 |
   1002
 |    99 |
|  3 |
   1003
 |    96 |
|  4 |
   1004
 |    96 |
|  5 |
   1005
 |    92 |
|  6 |
   1006
 |    90 |
|  7 |
   1007
 |    90 |
|  8 |
   1008 
 |    94 |
+----+--------+-------+/<code>

1.普通排名

按分數高低直接排名，從1開始，往下排，類似於row number。下面我們給出查詢語句及排名結果。

<code> 
SELECT xuehao, score, @curRank :
= @curRank + 1 AS rank
FROM scores_tb, (
SELECT @curRank :
= 0
) r
ORDER BY score desc;

 
+--------+-------+------+
| xuehao |
 score | rank |
+--------+-------+------+
|   1002 |
    99
 |    1 |
|   1003 |
    96
 |    2 |
|   1004 |
    96
 |    3 |
|   1008 |
    94
 |    4 |
|   1005 | 
    92
 |    5 |
|   1006 |
    90
 |    6 |
|   1007 |
    90
 |    7 |
|   1001 |
    89
 |    8 |
+--------+-------+------+/<code>

上述查詢語句中，我們申明瞭一個變量 @curRank ,並將此變量初始化為0，查得一行將此變量加一，並以此作為排名。我們看到這類排名是沒間隔的並且有些分數相同但排名不同。

2.分數相同，名次相同，排名無間隔

<code># 查詢語句
SELECT
 xuehao
, score, 
CASE 
WHEN
 @prevRank
 = score THEN  
@curRank 
WHEN
 @prevRank
 := score THEN @curRank
 := @curRank
 + 1
END AS rank
FROM scores_tb, 
(SELECT @curRank
 :=0
, @prevRank := NULL) r
ORDER BY score desc;

# 排名結果
+--------
+-------
+------+
| xuehao
 | score
 | rank |
+--------
+-------
+------+
|   1002
 |    99
 | 1    |
|   1003
 |    96
 | 2    |
|   1004
 |     
96
 | 2    |
|   1008
 |    94
 | 3    |
|   1005
 |    92
 | 4    |
|   1006
 |    90
 | 5    |
|   1007
 |    90
 | 5    |
|   1001
 |    89
 | 6    |
+--------
+-------
+------
+/<code>

3.並列排名，排名有間隔

另外一種排名方式是相同的值排名相同，相同值的下一個名次應該是跳躍整數值，即排名有間隔。

<code># 查詢語句
SELECT
 xuehao
, score
, rank
 FROM
(SELECT xuehao, score,
@curRank
 := IF(@prevRank
 = score, @curRank
, @incRank) AS rank, 
@incRank
 := @incRank
 + 1, 
@prevRank := score
FROM scores_tb, (
SELECT @curRank
 :=0
, @prevRank
 := NULL, @incRank
 := 1
) r 
ORDER BY score desc) s;

# 排名結果
+--------
+-------
+------+
| xuehao
 | score
 | rank 
 |
+--------
+-------
+------+
|   1002
 |    99
 | 1    |
|   1003
 |    96
 | 2    |
|   1004
 |    96
 | 2    |
|   1008
 |    94
 | 4    |
|   1005
 |    92
 | 5    |
|   1006
 |    90
 | 6    |
|   1007
 |    90
 | 6    |
|   1001 
 |    89
 | 8    |
+--------
+-------
+------
+/<code>

上面介紹了三種排名方式，實現起來還是比較複雜的。好在MySQL8.0增加了窗口函數，使用內置函數可以輕鬆實現上述排名。

MySQL8.0 利用窗口函數實現排名

MySQL8.0中可以利用 ROW_NUMBER()，DENSE_RANK()，RANK() 三個窗口函數實現上述三種排名，需要注意的一點是as後的別名，千萬不要與前面的函數名重名，否則會報錯，下面給出這三種函數實現排名的案例：

<code> 
select
 xuehao,score, ROW_NUMBER() OVER
(order
 by
 score desc
) as
 row_r from scores_tb;
select
 xuehao,score, DENSE_RANK
()  
OVER
(order
 by
 score desc
) as
 dense_r from scores_tb;
select
 xuehao,score, RANK
() over
(order
 by
 score desc
) as
 r from scores_tb;

 
SELECT xuehao,score,
    ROW_NUMBER() OVER
 w AS
 'row_r',
    DENSE_RANK
() OVER
 w AS
 'dense_r',
    RANK
()       OVER
 w AS
  
'r'
FROM
 `scores_tb` 
WINDOW
 w AS
 (ORDER
 BY
 `score`
 desc);

 
+ 
| xuehao | score | row_r | dense_r | r |
+ 
|   1002 |    99 |     1 |       1 | 1 |
|   1003 |    96 |     2 |       2 | 2 |
|   1004 |    96 |     3 |       2 | 2 |
|   1008 |    94 |     4 |       3 | 4 |
|   1005 |    92 |     5 |       4 | 5 |
|   1006 |    90 |     6 |       5 | 6 |
|   1007 |    90 |     7 |       5 | 6 |
|   1001 |    89 |     8 |       6 | 8 |
+/<code>