MySQL进阶之窗口函数

天行健，君子以自强不息；地势坤，君子以厚德载物。

每个人都有惰性，但不断学习是好好生活的根本，共勉！

文章均为学习整理笔记，分享记录为主，如有错误请指正，共同学习进步。

xxxx，xxxx     《》

MySQL学习专栏

7. 窗口函数

https://blog.csdn.net/weixin_45433031/article/details/129406509
https://www.csdn.net/blogstar2024

窗口函数主要有如下
专用窗口函数：
rank
count
max
min
sum
avg
lag
lead
row_number
dense_rank
first_value
last_value

聚合函数：如sum.avg,count,max,min等

语法

窗口函数 over(partition by 分组字段 order by 排序字段)

如使用窗口函数rank()进行分组排序查询

select *,rank() over (partition by 字段名 order by 排序字段 desc) as 组内排序别名 from 表名;

窗口函数是对where或group by子句处理后的结果进行处理，所有窗口函数原则上只能写上select子句中

7.1 序号函数（rank()、dense_rank()、row_number()）

序号函数包含rank()、dense_rank()和row_number()

rank() 返回数据集中的每个值的排名，并列排序，会跳过重复的序号，相同成绩会使用相同排序编号，且会占用名额，并列第2有两个，那么下一个名词为4
dense_rank() 返回数据集中的每个值的排名，并列排序，不会跳过重复的序号，相同成绩会使用相同编号，不会占用名额，并列第2有两个，那么下一个名次为3
row_number() 返回数据集中的每个值的排名，不会并列，相同成绩会使用顺序编号，即成绩相同的有两个第2名，但顺序排名，两个成绩排名为2和3，下一个名词为4
具体实例如下，更为直观的在实际排序中展示

7.1.1 创建表数据

创建测试用的表并填充数据

create table class_grade(id int auto_increment, student_id varchar(50), class_id int, grade int, primary key(id));

insert into class_grade (student_id, class_id, grade) values ("081413301", 3, 80),("081413203", 2, 88),("081413207", 2, 60),("081413111", 1, 78),("081413401", 4, 90),("081413216", 2, 50),("081413421", 4, 89),("081413308", 3, 92),("081413302", 3, 80),("081413303", 3, 80),("081413217", 2, 80);

7.1.2 使用窗口函数查询（包含分组）

按照班级分组，在每个班内按照成绩降序排序，排序名称为ranks，（使用分组后，排序会在每个分组内进行排序，即每个分组内都会从1开始排序）

select *, rank() over (partition by class_id order by grade desc) as ranks from class_grade;

这里可以明显看出班级分组后的成绩排名

7.1.3 rank,dense_rank,row_number的区别

按成绩排序，分别使用三个窗口函数进行查询

select *, rank() over (order by grade desc) as ranks, dense_rank() over (order by grade desc) as dense_ranks, row_number() over (order by grade desc) as row_numbers from class_grade;

rank,dense_rank,row_number三者的区别在于
rank排序会将分数相同的排名用相同数字表示，但下一个排名的计算会包含这几个相同的人数在内，如第三名有三个人，那么三个人都是第三名，但是不会有第四第五名，接下来就是第六名的排名
dense_rank排序不会将相同成绩排名的人数算进去，如第三名有三个人，三个人的排名都是3，但是下一个排名，也就是第六的成绩排名在这里会显示为4
row_number排序则不管成绩是否相同，排序都会依次用数字表示，不会有重复排名，如第三名有三个人，三人排名分别是3、4、5，下一个排名是6

7.2 分布函数（cume_dist()）

分布函数cume_dist() 、percent_rank()
cume_dist() 表示当前行及小于当前行在窗口分区总行数中的占比， 用于查询小于或等于某个值的比例
percent_rank() 计算分区或结果集中行的百分位数

select *,cume_dist() over (partition by class_id order by garade desc) as cume from class_grade;

7.3 前后函数

前后函数lag(expr,n)、lead(expr,n)
lag(expr,n) 向上偏移
lead(expr,n) 向下偏移

7.4 头尾函数

首尾函数first_value(expr)、last_value(expr)
first_value(expr) 用于返回第一个expr的值
last_value(expr) 用于返回最后一个expr的值

8. 拓展

8.1 group by 和 partition by的区别

partition by会将行数全部显示
group by会改变行数
根据班级id分组并根据班级id排序，统计每个班级中有成绩的人数

select class_id, count(班级人数统计) from class_grade group by class_id order by class_id;

根据班级id分组和排序，查看班级id对应的学生人数

select class_id, count(class_id) over (partition by class_id order by class_id) as 班级人数 from class_grade;

按照班级分组，在每个班内按照成绩降序排序，排序名称为ranks

select *, rank() over (partition by class_id order by grade desc) as ranks from class_grade;

根据班级id分组并根据班级id排序，统计每个班级中有成绩的人数

select class_id, count(班级人数统计) from class_grade group by class_id order by class_id;

感谢阅读，祝君暴富！

版权声明：

• 作者：寒山李白
• 博客地址：https://hanshan.blog.csdn.net/
• 版权：本作品采用《创作共享许可证》进行许可，根据该许可授权的内容可在符合本许可证条款的前提下自由使用、、修改和创作衍生作品。

版权许可介绍：
本文采用CC BY-NC-SA许可证
此许可允许在使用者仅出于非商业目的以任何媒体或格式分发、重新混合、改编和构建材料，并且前提是注明创作者。如果您重新混合、改编或基于该材料进行构建，则必须按照相同的条款对修改后的材料进行许可。

更多信息请访问以下网址查看：
版权官网 https://creativecommons.org/licenses/by-nc-sa/4.0/
中文翻译 https://creativecommons.org/licenses/by-nc-sa/4.0/deed.zh-hans