MySQL窗口函數實現榜單排名

Posted on by

相信大傢在日常的開發中經常會碰到榜單類的活動需求，通常在榜單中都會要求返回排名，今天我們就用MySQL的窗口函數來快速實現一下

首先，先建一個測試表

create table praise_record(
    id bigint primary key auto_increment,
    name varchar(10),
    praise_num int
) ENGINE=InnoDB;

然後讓chatGpt給我們生成幾條測試數據

INSERT INTO praise_record (name, praise_num) VALUES ('John', 5);
INSERT INTO praise_record (name, praise_num) VALUES ('Jane', 3);
INSERT INTO praise_record (name, praise_num) VALUES ('Bob', 10);
INSERT INTO praise_record (name, praise_num) VALUES ('Alice', 3);
INSERT INTO praise_record (name, praise_num) VALUES ('David', 7);
INSERT INTO praise_record (name, praise_num) VALUES ('oct', 7);

然後就可以開始實現我們的需求：返回點贊的榜單，並返回排名

rank()

使用rank()函數返回點贊的榜單， rank() over()

## 註意這裡返回的rank字段要用反引號包起來
select name, praise_num, rank() over (order by praise_num desc) as `rank` from praise_record;
+-------+------------+------+
| name  | praise_num | rank |
+-------+------------+------+
| Bob   |         10 |    1 |
| David |          7 |    2 |
| oct   |          7 |    2 |
| John  |          5 |    4 |
| Jane  |          3 |    5 |
| Alice |          3 |    5 |
+-------+------------+------+

可以看到使用rank()函數的時候相同的點贊數會返回相同的排名，排名會產生跳躍，最終的排名不是連續的

dense_rank()

使用dense_rank()函數返回點贊的榜單， dense_rank() over()

 select name, praise_num, dense_rank() over (order by praise_num desc) as `rank` from praise_record;
 
 +-------+------------+------+
| name  | praise_num | rank |
+-------+------------+------+
| Bob   |         10 |    1 |
| David |          7 |    2 |
| oct   |          7 |    2 |
| John  |          5 |    3 |
| Jane  |          3 |    4 |
| Alice |          3 |    4 |
+-------+------------+------+

與rank()函數相同的是，相同點贊數會返回相同的排名，但是dense_rank()返回的最終排名是連續的排名

row_number()

row_number()函數返回點贊的榜單，row_number() over()

 select name, praise_num, row_number() over (order by praise_num desc) as `rank` from praise_record;
 +-------+------------+------+
| name  | praise_num | rank |
+-------+------------+------+
| Bob   |         10 |    1 |
| David |          7 |    2 |
| oct   |          7 |    3 |
| John  |          5 |    4 |
| Jane  |          3 |    5 |
| Alice |          3 |    6 |
+-------+------------+------+

row_number()函數適合當返回的列表隻需要序號時使用

以上三個函數都是MySQL8.0新加入的，所以在MySQL5.7這些老版本上我們可以模擬實現一下，順便學習一下這三個窗口函數的實現原理

rank()函數的模擬實現

select p1.name, p1.praise_num, count(p2.praise_num) + 1 as `rank` from praise_record p1
left join praise_record p2 on p1.praise_num < p2.praise_num
group by p1.name, p1.praise_num
order by `rank`;
+-------+------------+------+
| name  | praise_num | rank |
+-------+------------+------+
| Bob   |         10 |    1 |
| David |          7 |    2 |
| oct   |          7 |    2 |
| John  |          5 |    4 |
| Jane  |          3 |    5 |
| Alice |          3 |    5 |
+-------+------------+------+

我們可以使用自聯接的方式將每個分數低於當前行分數的記錄計數，最後將計數值加1作為當前行的排名，來模擬實現rank()

dense_rank()的模擬實現

select p1.name, p1.praise_num, count(distinct p2.praise_num) + 1 as `dense_rank` from praise_record p1
left join praise_record p2 on p1.praise_num < p2.praise_num
group by p1.name, p1.praise_num
order by `dense_rank`;
+-------+------------+------------+
| name  | praise_num | dense_rank |
+-------+------------+------------+
| Bob   |         10 |          1 |
| oct   |          7 |          2 |
| David |          7 |          2 |
| John  |          5 |          3 |
| Jane  |          3 |          4 |
| Alice |          3 |          4 |
+-------+------------+------------+

dense_rank的實現與rank差不多，唯一的區別是增加瞭distinct對點贊數做瞭去重，這樣子對不同的點贊數返回的排名就是連續的

row_number的模擬實現

##使用自定義變量得先初始化
set @rowNum = 0;
select name, praise_num, @rowNum := @rowNum +1 as `row_number`  from praise_record order by praise_num desc ;
+-------+------------+------------+
| name  | praise_num | row_number |
+-------+------------+------------+
| Bob   |         10 |          1 |
| David |          7 |          2 |
| oct   |          7 |          3 |
| John  |          5 |          4 |
| Jane  |          3 |          5 |
| Alice |          3 |          6 |
+-------+------------+------------+

我們可以使用一個rowNum變量來記錄行號，每一行的數據rowNUm都+1，這樣子就可以得到我們想要的序號

總結

1.rank()函數返回的排名會產生跳躍

2.dense_rank()函數返回的排名是連續的

3.row_number()函數返回的排名類似序號

4.窗口函數是MySQL8.0新增的特性，如果在低版本的MySQL要自己模擬實現一下

到此這篇關於MySQL窗口函數實現榜單排名的文章就介紹到這瞭,更多相關MySQL 榜單排名內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：