MySQL窗口函数代码应用示例及解说
1、概念简介
窗口函数,也叫分析函数,也有称开窗函数。
可实现复杂的统计分析,统计数据分为灵活可变,是一种非常重要且常用的分析(OLAP)函数。
窗口函数可以轻易实现在excel中需要多次透视或者很多步骤完成的任务,是省时省力的好帮手。
下表展示窗口函数和聚合函数联系和区别,帮助更好理解两类函数的作用及运用:
(窗口函数是对聚合函数更高级的使用,所有将聚合函数放前面展示了)
2、各窗口函数运用示例
2.1 sum()over()函数(比对聚合函数sum()函数):
(avg()、max()、min()函数同理)
2.2 order by 子句作用
2.3 rank()、dense_rank()、row_number()排序函数
① row_number() over(order by Sales desc) ----按照Sales降序排序号,不重不漏,1 2 3 4 5;
② rank() over(order by Sales desc) ----按照Sales降序排序号,并列第一无第二,1 1 3 4 5;
③ dense_rank() over(order by Sales desc) ----按照Sales降序排序号,并列第一有第二,1 1 2 3 3 4;
④ percent_rank() over(order by Sales desc) ----按照Sales降序排序号,计算公式为(①的排序号)/(N-1)
2.4 偏移 lead()向前移动、lag()向后移动
以下写法较符合使用习惯,上面列的顺序主要是为方便后面解说用,注意前期写代码注意养成好的习惯(代码带说明、下一列逗号放前面醒目等等)
偏移多用于时间向前或向后,计算留存、最近两次消费时间间隔等,本文示例数据中没有日期,所以暂且用销量数据一用,想要理解透彻,还需多动手对比代码运行结果才好。
2.5 ntile()函数
2.6 first_value() 函数和 last_value()函数
顾名思义:第一个值和最后一个值,多数结合 partition by 子句和 order by 子句
先通过partition by 分组,order by排序,再获取组内最小值和最大值,示例如: