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排序,再获取组内最小值和最大值,示例如:

THE END