SQL窗口函数

1、什么是窗口函数

窗口限定一个范围，简单理解是满足某些条件的记录集合，所以窗口函数,也就是在窗口范围内执行的函数

1.1 作用

• 解决排名问题
• 解决TopN问题

1.2 语法

窗口函数有over关键字，指定函数执行的范围，可以分为三部分，分组子句（partition by）,排序子句（order by），窗口子句（rows）

【MySQL8版本支持开始支持窗口函数】

<函数名> over(partition by <分组的列> order by <排序的列> rows between <起始行> and <终止行>)

1.3 分类

• 排名函数：row_number（）、rank（）、dese_rank（）
• 聚合函数：max（）、min（）、count（）、sum（）、avg（）、median（）
• 向前向后取值：lag（）、lead（）
• 百分位：precent_rank（）
• 分箱函数：ntile（）

1.4 分组子句 (partition by)

不分组可以写成partition by null 或者直接不写，后面可以跟多个列，如partition by cid,name。

select *,sum(score) over(partition by cid) as '总分' form score;

注意：partition by 和 group by的区别
1）partition by 不会压缩行数（每条记录都会返回一个值），group by 会压缩行数 （所有行只返回一个值）
2）group by只能选取分组的列和聚合的其他列

2、窗口函数

2.1 排名函数

函数	用法
row_number（）	相当于行号
rank（）	根据排序值返回记录的序号，有跳级， 例如：3000、2000、2000、1000 排序后：1、2、2、4
dense_rank（）	同薪同名，没有跳级，例如：3000、2000、2000、1000 排序后：1、2、2、3

row_number（）

对employees按照部门号分区，按照工资排序

select emp_no, salary, dept_no, row_number() over (partition by dept_no order by salary) as t
from employees;

rank（）

返回当前行在分区中的名次，如果存在相同名次的数据，后续的排名会产生跳跃。

select emp_no, dept_no, salary, rank() over (partition by dept_no order by salary) ranks
from employees;

dense_rank（）

返回当前行在分区中排名的名次，即使存在相同的数据，后续的排名也是连续值

select emp_no, dept_no, salary, dense_rank() over (partition by dept_no order by salary) ranks
from employees;

2.2 聚合函数

max（）

1）不对数据进行排序的情况，是返回每一行所有数据的最大值。

select emp_no, dept_no, salary, max(salary) over (partition by dept_no) max
from employees;

2）对数据进行排序的情况，是返回上一行到当前行的最大值。

select emp_no, dept_no, salary, max(salary) over (partition by dept_no order by salary) max
from employees;

注意：min（）、svg（）、sum（）也是和max（）一样，排序和不排序情况一样的。

2.3 取值函数

1）向前向后取值

函数	用法
lag(field,n,default)	在一次查询中取出当前行的同一个字段（field）的前面的第n行的数据
lead(field,n,default)	在一次查询中取出当前行的同一个字段（field）的后面的第n行的数据

这两个函数可以在一次查询中取出同一字段的前N行的数据（lag）和后N行的数据（lead）作为独立的列

LAG()函数：统计与前一天相比温度更高的日期Id

SELECT id,
       date,
       temperature,
       LAG(temperature, 1) OVER ( ORDER BY date ) AS temp
FROM weather;

LEAD()函数：统计与后一天相比温度更高的日期Id

SELECT id,
       date,
       temperature,
       LEAD(temperature, 1) OVER ( ORDER BY date ) AS temp
FROM weather;

参考文章：
1、常用的窗口函数
2、MySQL中LAG()函数和LEAD()函数的使用