玩转SQL窗口函数
说明：MySQL8.0 之后才支持窗口函数
1 提前准备
在MySQL8.0版本数据库下新建数据表，插入数据
CREATE TABLE `student` ( `id` int(10) DEFAULT NULL, `name` varchar(20) DEFAULT NULL, `age` int(10) DEFAULT NULL, `address` varchar(20) DEFAULT NULL ) ENGINE=InnoDB; insert into student values(1,'zs',23,'Beijing'); insert into student values(2,'ls',21,'Tianjing'); insert into student values(3,'ww',13,'Shanghai'); insert into student values(4,'zl',23,'Beijing'); insert into student values(5,'mq',4,'Nanjing'); insert into student values(6,'wb',3,'Beijing'); insert into student values(7,'hj',21,'Hebei'); insert into student values(8,'sw',34,'Henan'); insert into student values(9,'sa',42,'Beijing'); 复制代码
2 MySQL窗口函数及其语法
分类：
- 专用窗口函数，比如rank、dense_rank、row_number等
- 聚合函数，如sum、 avg、count、max、min等
2.1 基本语法
<窗口函数> over (partition by <用于分组的列名> order by <用于排序的列名>) 复制代码
2.2 专用窗口函数示例
2.2.1 rank函数
作用：
RANK()函数，顾名思义排名函数，可以对某一个字段进行排名.
示例：
根据address分组，再根据age排序
select *,rank() over (partition by address order by age desc) as ranking from student 复制代码
网络异常，图片无法展示
|
2.2.2 dense_rank函数
作用：
DENSE_RANK() 函数用来表示排名，与RANK()不同的是，DENSE_RANK() 不会出现空缺数字。比如，如果出现了两个并列的1，DENSE_RANK() 的第三个数仍然是2，而RANK()的第三个数是3。
示例：
根据address分组，再根据age排序
select *,dense_rank() over (partition by address order by age desc) as ranking from student 复制代码
网络异常，图片无法展示
|
2.2.3 row_number函数
作用：
ROW_NUMBER()函数作用就是将select查询到的数据进行排序，每一条数据加一个序号，他不能用做于学生成绩的排名，一般多用于分页查询
示例：
根据address分组，再根据age排序
select *,row_number() over (partition by address order by age desc) as ranking from student 复制代码
网络异常，图片无法展示
|
2.2.4 first_value函数
作用：
FIRST_VALUE() 返回窗口范围内的第一个值。
示例：
根据address分组，返回某组中最大age的数量
select *,FIRST_VALUE(age) over (partition by address order by age desc) as ranking from student 复制代码
网络异常，图片无法展示
|
2.2.5 综合使用
相当于将三个函数的排名情况进行对比
select *, rank() over (order by age desc) as ranking, dense_rank() over (order by age desc) as dese_rank, row_number() over (order by age desc) as row_num from student 复制代码
网络异常，图片无法展示
|
聚合窗口函数，不断向下聚会取结果
select *, sum(age) over (order by age) as current_sum, avg(age) over (order by age) as current_avg, count(age) over (order by age) as current_count, max(age) over (order by age) as current_max, min(age) over (order by age) as current_min from student 复制代码
网络异常，图片无法展示
|
3 总结
窗口函数有以下功能：
- 同时具有分组（partition by）和排序（order by）的功能
- 不减少原表的行数，所以经常用来在每组内排名
注意事项
- 窗口函数原则上只能写在select子句中
- 窗口函数使用场景一般是排名问题和top N问题上
参考:
