MySQL窗口函数实现榜单排名

吾爱主题阅读：147 2023-04-12 15:41:00 评论：0

相信大家在日常的开发中经常会碰到榜单类的活动需求，通常在榜单中都会要求返回排名，今天我们就用MySQL的窗口函数来快速实现一下

首先，先建一个测试表

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

然后让chatGpt给我们生成几条测试数据

1 2 3 4 5 6 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()

1 2 3 4 5 6 7 8 9 10 11 12 ## 注意这里返回的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()

1 2 3 4 5 6 7 8 9 10 11 12 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()

1 2 3 4 5 6 7 8 9 10 11 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()函数的模拟实现

1 2 3 4 5 6 7 8 9 10 11 12 13 14 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()的模拟实现

1 2 3 4 5 6 7 8 9 10 11 12 13 14 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的模拟实现

1 2 3 4 5 6 7 8 9 10 11 12 13 ##使用自定义变量得先初始化 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 | + -------+------------+------------+