MySQL窗口函数详讲

窗口函数是MySQL8提供的超好用统计分析工具，可以基于当前行向前后划分范围作为窗口进行快速统计分析

一、窗口函数语法

函数名(参数) over([partition by 分区列][order by 排序列 [asc/desc]][限制窗口范围]
)

二、排序函数

函数：row_number()、rank()、dense_rank()、ntile(n)

说明：

① row_number()

获取当前行在窗口中的行号（即使排序列值相同，序号也不一样）

案例1

selectname,age,address,row_number() over () as '窗口行号'
fromtb_user u

执行结果：

案例2

先按照address分组，在获取窗口内的行号

selectname,age,address,row_number() over (partition by address) as '窗口行号'
fromtb_user u

执行结果：

② rank()

按照列值排序，相同列值的行序号相同，但是会跳过后续序号

（如a、b都是90分，c是80分，那么a、b并列第1，c为第3名）

案例：

先按照age升序排序，再获取行的排名

selectname,age,address,rank() over(order by age asc)
fromtb_user u

执行结果：

 另外如果不指定rank的排序字段，那么获取的结果都是1

③ dense_rank()

按值列排序，相同值有相同排名，但不会跳过后续序号

（如a、b都是90分，c是80分，那么a、b并列第1，c为第2名）

案例：

先按照age升序排序，再获取行的排名

selectname,age,address,dense_rank() over(order by age asc) as age_asc_dense_rank
fromtb_user u;

执行结果：

同样如果不指定dense_rank的排序字段，那么获取的结果都是1

④ ntile(n)

将数据按照排序字段分为n组，返回组号

案例

按照address进行排序，分为3组，获取组号

selectname,age,address,ntile(3) over(order by address) as address_ntile
fromtb_user u;

执行结果：

三、聚合函数

函数：sum()、avg()、count()、max()、min()

说明：

① sum() over()

计算窗口内列的总和

案例

先按照address进行分组，再计算组内age总和

selectname,age,address,sum(age) over(partition by address) as sum_ntile
fromtb_user u;

执行结果（address_ntile列名当时测试忘记改了）：

②avg() over()

计算窗口内的平均值

如：

selectname,age,address,avg(age) over(partition by address) as avg_ntile
fromtb_user u;

执行结果：

③ count() over()

窗口内的计数

selectname,age,address,count(age) over(partition by address) as cnt_ntile
fromtb_user u;

④ max() over()

计算窗口内最大值

selectname,age,address,max(age) over(partition by address) as max_ntile
fromtb_user u;

⑤ min() over()

计算窗口内的最小值

selectname,age,address,min(age) over(partition by address) as min_ntile
fromtb_user u;

四、偏移函数

函数：lag()、lead()、first_value()、last_value()

说明：

① lag(列，offset)

基于当前行向前偏移offset个单位的行，获取对应列值

案例

获取 (当前行-2) 那一行的name

selectname,age,address,lag(name, 2) over() as lag2_name
fromtb_user u;

执行结果：

② lead(列，offset)

基于当前行``向后``偏移offset个单位的行，获取对应列值

案例

获取 (当前行+2)  那一行的name

selectname,age,address,lead(name, 2) over() as lead2_name
fromtb_user u;

③ first_value(列)

获取窗口内第一行的列值

案例

先按照address进行分组，再获取组内第一个name

selectname,age,address,first_value(name) over(partition by address) as first_name
fromtb_user u;

执行结果：

④ last_value(列)

获取窗口内最后一行的列值

案例

先按照address进行分组，再获取组内最后一个name

selectname,age,address,last_value(name) over(partition by address) as last_name
fromtb_user u;

五、分布函数

① percent_rank()

计算行的相对排名(0-1之间，百分比排名)

案例

按照年龄排序，计算排名

selectname,age,address,percent_rank() over(order by age) as age_percent_rank
fromtb_user u;

执行结果：

② cume_dist()

计算行的累积分布（窗口内小于等于当前行字段值的行数 / 窗口内总行数）

案例

按照年龄升序排序，获取对应的累积分布

selectname,age,address,cume_dist() over(order by age) as age_cume_dist
fromtb_user u;

执行结果：

六、自定义窗口范围

关键字

preceding：之前的

following：之后的

unbounded：无限制的

current row： 当前行

使用：

① rows between

基于物理行位置偏移，直接计算当前行前后指定数量的行范围

案例

针对每一行，计算前一行到后一行的age最大值

selectname,age,address,max(age) over(rows between 1 preceding and 1 following) as rows3_max
fromtb_user u;

执行结果

② range between

基于列值的逻辑范围，包含指定值范围的所有行

案例

基于当前行age，限制[age-1, age+2]为窗口的范围，求出age的平均值

selectname,age,address,avg(age) over(order by age range between 1 preceding and 2 following) as range_avg
fromtb_user u;

执行结果：

关键区别对比