SQL开窗函数

SQL开窗函数

开窗函数介绍详见https://zhuanlan.zhihu.com/p/450298503

开窗函数含义

开窗函数的语法形式为：函数 + over(partition by <分组用列> order by <排序用列>)，表示对数据集按照分组用列进行分区，并且对每个分区按照函数聚合计算，最终将计算结果按照排序用列排序后返回到该行。括号中的两个关键词partition by 和order by 可以只出现一个。同时同一个查询语句中可以同时使用多个开窗函数。适用版本为mysql 8.0版本。

常用开窗函数（具体实现效果见链接）

1.排序类开窗函数
row_number() – 相同值排名顺延，返回结果1、2、3、4
rank() – 相同结果排名相同，后续排名不连续，返回结果为 1、2、2、4
dense_rank() – 相同结果排名相同，后续排名顺延，返回结果为 1、2、2、3
ntile(n) – 分组排名，将数据分为n组并返回对应组号1、2…n
其中1，2较为常用
2.聚合开窗函数
sum() – 分组求和
count() – 分组求总数
min() – 分组求最小值
max() – 分组求最大值
avg() --分组求均值

举个例子区分加不加order by的差异：
select grades
,subjects
,results
,sum(results) over(partition by grades,subjects order by results desc) as sum聚合1
,sum(results) over(partition by grades,subjects) as sum聚合2

其中注意语句中可以加order by或者不加order by，最后的结果表明：若加order by代表对results进行聚合且操作的行数为到当前行；若不加order by代表对results进行聚合且results为所有的相同grades,subjects的记录。

开窗函数使用场景（需要有组的概念）

1. 求某个分组下的最大/最小值/TOPn值对应信息，如年级中每个班级的第一名，大区中销售额最高城市，此处用排序函数row_number()，需用order by 排序
2. 对某个分组求和/个数/均值，如城市历史截至昨天累积销售额/营业天数/平均销售额，此处用sum()/count()/avg()，需用order by 默认的定位框架
3. 相邻时间求时间差，如用户复购时间周期，此处用lag()，需用order by 排序（使用lag可以在本次知道上次的记录，继而可以计算两次的时间差）