用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数据库中，窗口函数是一种非常强大的功能，它可以在分组的结果集上进行聚合计算，同时返回中间结果，并在最后将结果集合并成完整的结果。作为一个数据库开发者，窗口函数是一项必须掌握的技能。本文着重介绍窗口函数实现榜单排名的三种方法。
一、使用RANK()函数
RANK函数可以对给定的分组，在根据指定的列排序后，给每个分组的每一行一个排名。它会跳过相同值，即如果列值是相同的，排名将跳过这些行数。例如：
SELECT student_name, grade, RANK() OVER (PARTITION BY grade ORDER BY score DESC) AS rank
FROM student_score;
在这个例子中，我们给每个学生按年级等级为分组，并按分数从大到小排序，并为每个等级分配排名。
二、使用DENSE_RANK()函数
DENSE_RANK()函数与RANK()几乎相同，但是对于相同的值，它不跳过它们。相反，它为每个值分配一个唯一的排名。例如：
SELECT student_name, grade, DENSE_RANK() OVER (PARTITION BY grade ORDER BY score DESC) AS rank
FROM student_score;
在这个例子中，我们使用DENSE_RANK()函数进行排名，结果与使用RANK()函数几乎相同，只是对于相同的分数，它不会跳过它们。
三、使用ROW_NUMBER()函数
ROW_NUMBER()函数是一种不太常用的函数，但是它也可以用来进行排名操作。它类似于DENSE_RANK()，但不会跳过相等的值，而是分配一个唯一的排名。例如：
SELECT student_name, grade, ROW_NUMBER() OVER (PARTITION BY grade ORDER BY score DESC) AS rank
FROM student_score;
在这个例子中，我们使用ROW_NUMBER()函数进行排名。结果与DENSE_RANK()相同，只是分配了不同的排名。
综上所述，以上就是三种使用MySQL窗口函数实现榜单排名的方法，它们都有各自的优缺点。选择哪种方法，取决于具体的需求和数据集。了解这些方法可以帮助您更好地掌握窗口函数的用法，从而提高MySQL的应用水平。