SQL 窗口函数

 

1. 窗口函数简介

MySQL 8.0+ 版本支持窗口函数，该函数也称分析函数，对初学者来说，窗口函数特别容易与分组聚合函数混合。两者的不同之处在于，窗口函数使每一行数据都生成一个结果。

 

 

2. 窗口函数分类

排序函数：row_number()、rank()、dense_rank() 对分析对象进行排序

分布函数：percent_rank()、cume_dist()  对分析对象记录进行比较，类似于统计学中的中位数或四分位数

前后函数：lag()、lead()  对分析对象自身前面/后面一定顺序的数据进行分析

头尾函数：first_val()、last_val() 对分析对象第一/最后值进行分析

其他函数：nth_value()、nfile()  用于给分析对象进行分段

 

 

3. 窗口函数详解

1. 排序函数

排序函数，顾名思义就是对数据对象进行排序。

 

1. 排序函数分类及基础语法

rank() over (partition by 分区字段 order by 排序字段 desc/asc);
dense_rank() over (partition by 分区字段 order by 排序字段 desc/asc);
row_number() over (partition by 分区字段 order by 排序字段 desc/asc);

 

例1：先按班级分组，再在组内按成绩排名

select *,
   rank() over (partition by 班级
                 order by 成绩 desc) as ranking
from 班级表

 

 

三种排序函数的差异点：

• rank() ：当指定字段数值相同，则会产生相同序号记录，且产生序号间隙。
• dense_rank() ：当指定字段数值相同，则会产生相同序号记录，且不会产生序号间隙。
• row_number() ：不区分是否记录相同，产生自然序列。

例2：直接按成绩排名

select *,
   rank() over (order by 成绩 desc) as ranking,
   dense_rank() over (order by 成绩 desc) as dese_rank,
   row_number() over (order by 成绩 desc) as row_num
from 班级表

 

2. 排序函数小结

1. 函数、over()是必须存在的，且over()括号里面的内容是可选的。
2. over()用来指定函数执行窗口范围，如果后面括号内无任何内容，则指窗口范围是满足where条件所有行。
3. partition by，指定按照某字段进行分组，窗口函数是在不同分组分别执行。
4. order by，指定按照某字段进行排序。

 

2. 前后函数

未完待续.......

 

来自：https://zhuanlan.zhihu.com/p/92654574