MySql数据库高级函数—窗口函数（开窗函数）

一、有问必答

1.mysql数据库的窗口函数使用和版本有关系吗？

	有关系，在8.0版本之后，才支持使用窗口函数

2.窗口函数和分组（group by）有什么区别？

	窗口函数和group by都是对分组进行操作，不同点是窗口函数保持原来的记录数，group by 分组统计聚合后的记录数

3.窗口函数有哪些功能？

	1.同时具有分组和排序的功能；
    2.不减少原表的行数；

二、认识mysql的窗口函数

1.窗口函数的语法

<窗口函数> over (partition by <用于分组的列名> order by<用于排序的列名>)

一、窗口函数是对where或者group by子句处理后的结果进行操作，所以窗口函数原则上只能写在select子句中作为一个字段
二、<窗口函数> 能使用聚合函数和一些特点的函数，也能使用排序相关的函数
三、partition by 后跟字段，字段为需要分组的字段，可以是多个字段，字段之间用逗号分隔
四、order by 后跟字段，字段为需要排序的字段，可以为多格字段，字段之间用逗号分隔
五、partition by 和order by 两个关键字，可以根据需要选择使用

2.窗口函数

分类

排名函数：row_number()，rank()，dense_rank()
聚合函数：max()，min()，count()，sum()，avg()，median()
向前向后取值：lag()，lead()
百分位：percent_rank()
取值函数：first_value()，last_value()，nth_value()
分箱函数：ntile()

排名函数

row_number()   相等于序号，如成绩100，100，98，97 排名为1，2，3，4
rank() 相同成绩排名一样，序号有缺失， 如成绩100，100，98，97 排名为1，1，3，4
dense_rank() 项目成绩排名一样，序号没有缺失， 如成绩100，100，98，97 排名为1，1，2，3

示例：以row_number()作为示例，其他两个一样的用法

源数据如下

id  userid subject score
1	 001	语文	90
2	 001	数学	92
3	 001	英语	80
4	 002	语文	88
5	 002	数学	90
6	 002	英语	75.5
7	 003	语文	70
8	 003	数学	85
9	 003	英语	90
10	 003	政治	82

需求：需要对各科成绩排名

select id,userid,subject,score,row_number() over (PARTITION BY subject order by score desc) as rank_num from tb_score

结果

id  userid subject score rank_num
10	 003	政治	82	    1
2	 001	数学	92	    1
5	 002	数学	90	    2
8	 003	数学	85	    3
9	 003	英语	90	    1
3	 001	英语	80	    2
6	 002	英语	75.5	3
1	 001	语文	90	    1
4	 002	语文	88	    2
7	 003	语文	70	    3

聚合函数

max()   获得组内最大值
min()    获得组内最小值
count()    统计组内的个数
sum()    组内求和
avg()   统计组内平均值
median()   统计组内中位数

示例：以sum()作为示例，其他一样的用法

需求：对每个同学的各科成绩求和

求每个学生的成绩，只需要按照学生分组求和即可，不需要排序，如需要组内累计求和，此时就需要通过排序来累计了

select id,userid,subject,score,sum(score) over (PARTITION BY userid ) as sum_num from tb_score

结果：

id  userid  subject  score  sum_num
1	 001	 语文	  90	  262
2	 001	 数学	  92	  262
3	 001	 英语	  80	  262
4	 002	 语文	  88	  253.5
5	 002	 数学	  90	  253.5
6	 002	 英语	  75.5	  253.5
7	 003	 语文	  70	  327
8	 003	 数学	  85	  327
9	 003	 英语	  90	  327
10	 003	 政治	  82	  327

向前向后取值

lag()	 前N行，查询的记录向下移动N行，
lead()	后N行，查询的记录向上移动N行

示例：以lag() 作为示例，其他一样的用法

源数据

year   month   num
2022	01	   13
2022	02	   15
2022	03	   16
2022	04	   17
2022	05	   15
2022	06	   18
2022	07	   22
2022	08	   10
2022	09	   18

需求：2022年的环比

with a as (
select year,month,num,lag(num,1) over() fz from t1 )

select *,(num-fz)/fz tb from a

用（num-fz字段）/fz字段 即可求得环比

结果

year    month   num    fz    tb
2022	 01	    13		
2022	 02	    15	   13	0.1538
2022	 03	    16	   15	0.0667
2022	 04	    17	   16	0.0625
2022	 05	    15	   17	-0.1176
2022	 06	    18	   15	0.2000
2022	 07	    22	   18	0.2222
2022	 08	    10	   22	-0.5455
2022	 09	    18	   10	0.8000

向前向后取值：lag()，lead()
百分位：percent_rank()
取值函数：first_value()，last_value()，nth_value()
分箱函数：ntile()

百分位

percent_rank()  计算分组内每一行所在的百分比排名

取值函数

first_value()  分组内的第一行
last_value()  分组内的最后一行
nth_value(expr, n)  组内的第N行

分箱函数

 ntile(n)	将分区中已排序的行划分为大小尽可能相等的n个已排名组