怎麼用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視窗函數實現榜單排名的詳細內容。更多資訊請關注PHP中文網其他相關文章！