我要评论在数据库查询与数据分析中，对数据分组并进行排名是一项常见任务。sql 窗口函数为此提供了高效的解决方案。本文旨在阐明三个核心排名函数——rank()、dense_rank() 与 row_number()——的功能、差异及适用场景。
示例数据模型
以下说明将基于一张students表示例，该表记录了学生的班级与分数。其中，'a'班存在分数相同的学生，这有助于展示各函数的关键区别。
-- 表结构与数据
create table students
(
id int auto_increment primary key,
name varchar(50) not null,
class varchar(10) null,
score int null
);
insert into students (name, class, score) values
('甲', 'a', 85),
('乙', 'a', 90),
('丙', 'b', 95),
('丁', 'b', 80),
('张三', 'a', 90);
1.rank(): 标准排名 (非连续)
rank() 函数提供标准的排名功能。当存在数值相等的行时，这些行将获得相同的排名，但后续名次的序号将会跳跃。
查询语句:
select name, class, score, rank() over (partition by class order by score desc) as class_rank from students;
结果:
| name | class | score | class_rank |
|---|---|---|---|
| 乙 | a | 90 | 1 |
| 张三 | a | 90 | 1 |
| 甲 | a | 85 | 3 |
| 丙 | b | 95 | 1 |
| 丁 | b | 80 | 2 |
分析: 在 'a' 班中，'乙' 和 '张三' 因分数相同而并列第 1。随后的 '甲' 的排名为第 3，序列跳过了第 2 名。
2.dense_rank(): 紧凑排名 (连续)
dense_rank() 函数在处理并列排名时，不会在名次序列中产生间隙，确保排名是连续的。
查询语句:
select name, class, score, dense_rank() over (partition by class order by score desc) as class_rank from students;
结果:
| name | class | score | class_rank |
|---|---|---|---|
| 乙 | a | 90 | 1 |
| 张三 | a | 90 | 1 |
| 甲 | a | 85 | 2 |
| 丙 | b | 95 | 1 |
| 丁 | b | 80 | 2 |
分析: 在 'a' 班中，'乙' 和 '张三' 并列第 1 后，'甲' 的排名为第 2，名次序列保持了连续性。
3.row_number(): 行号分配 (唯一)
row_number() 函数不考虑数值的并列情况，它为分区内的每一行分配一个从 1 开始的、唯一的、连续的整数。
查询语句:
select name, class, score, row_number() over (partition by class order by score desc) as class_rank from students;
结果:
| name | class | score | class_rank |
|---|---|---|---|
| 乙 | a | 90 | 1 |
| 张三 | a | 90 | 2 |
| 甲 | a | 85 | 3 |
| 丙 | b | 95 | 1 |
| 丁 | b | 80 | 2 |
分析: 即使 '乙' 和 '张三' 的分数相同，
row_number()依然为它们分配了 1 和 2 两个不同的序号。
功能对比与应用场景
| 函数 | 并列处理 | 排名是否连续 | 主要应用场景 |
|---|---|---|---|
rank() | 排名相同 | 不连续 (跳跃) | 体育赛事等传统排名 |
dense_rank() | 排名相同 | 连续 (无间隙) | 榜单排名，要求名次紧凑 |
row_number() | 无并列排名 | 连续 | 分组获取 top n 记录，数据去重 |
在实际应用中，应根据排名是否需要处理并列以及名次序列是否需要连续来选择合适的函数。
到此这篇关于mysql8 排名窗口函数实战的文章就介绍到这了,更多相关mysql8 排名窗口函数内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！
发表评论