MySQL窗口函数实现榜单排名
作者:octobershen 时间:2024-01-16 20:22:22
相信大家在日常的开发中经常会碰到榜单类的活动需求,通常在榜单中都会要求返回排名,今天我们就用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,这样子就可以得到我们想要的序号
来源:https://juejin.cn/post/7220434734965866556
标签:MySQL,榜单排名
0
投稿
猜你喜欢
Seaborn数据分析NBA球员信息数据集
2021-06-27 03:36:04
仿jQuery的siblings效果的js代码
2024-04-28 09:36:34
jsp学习之scriptlet的使用方法详解
2023-06-27 11:06:37
sql2008 还原数据库解决方案
2024-01-26 07:16:59
Python pygorithm模块用法示例【常见算法测试】
2023-05-19 00:23:59
PHP操作MySQL中BLOB字段的方法示例【存储文本与图片】
2023-11-23 23:45:27
由不同的索引更新解决MySQL死锁套路
2024-01-18 02:03:53
Python+matplotlib实现华丽的文本框演示代码
2021-07-19 08:19:51
Python 设计模式中的创建型建造者模式
2021-05-23 02:41:50
python3通过udp实现组播数据的发送和接收操作
2023-01-14 02:27:42
django站点管理详解
2022-03-31 00:16:43
opencv导入头文件时报错#include的解决方法
2023-03-28 04:34:34
用Dreamweaver实现Real与网页结合
2010-07-13 12:11:00
python实现线性回归算法
2021-04-11 12:36:48
Django完整增删改查系统实例代码
2022-11-13 03:07:27
JavaScript之Getters和Setters 平台支持等详细介绍
2024-04-19 09:45:18
[hta]一个定时重启或关闭计算机的小工具
2007-10-17 12:02:00
MySQL查看和修改时区的方法
2024-01-15 05:42:33
matplotlib之Font family [‘sans-serif‘] not found的问题解决
2021-03-31 09:55:42
深入理解python中sort()与sorted()的区别
2021-08-17 11:46:49