怎么用MySQL窗口函數實現榜單排名

本篇內容主要講解“怎么用MySQL窗口函數實現榜單排名”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓小編來帶大家學習“怎么用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，這樣子就可以得到我們想要的序號

到此，相信大家對“怎么用MySQL窗口函數實現榜單排名”有了更深的了解，不妨來實際操作一番吧！這里是億速云網站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續學習！