SQL 窗口函数

引入

概念

窗口函数：窗口函数也称为OLAP函数（Online Anallytical Processing，联机分析处理），意思是对数据库数据进行实时分析处理。窗口函数就是为了实现OLAP而添加的标准SQL功能。

基本语法

<窗口函数> over ([partition by <列清单>]
                        order by <排序用列清单>)

FIRST_VALUE: 取分组内排序后，截止到当前行，第一个值
LAST_VALUE： 取分组内排序后，截止到当前行，最后一个值
LEAD(col,n,DEFAULT) ： 用于统计窗口内往下第n行值。第一个参数为列名，第二个参数为往下第n行（可选，默认为1），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）
LAG(col,n,DEFAULT) ： 与lead相反，用于统计窗口内往上第n行值。第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）
CUME_DIST()：返回（小于等于当前行值的行数）/（当前分组内的总行数）应用场景：比如，统计小于等于当前薪水的人数，所占总人数的比例
PERCENT_RANK()：返回（组内当前行的rank值-1）/（分组内做总行数-1）

窗口函数注意事项

（1）因为窗口函数是对where或者group by子句处理后的结果进行操作，所以窗口函数原则上只能写在select子句中。

（2）partition by 可以省略

（3）order by可以省略

（4）（2）和（3）不能同时发生。

（5）窗口函数直接带有空括号就可以

理解窗口函数

特点

1）同时具有分组和排序的功能
2）不减少原表的行数

使用场景

（1）用于分区排序
（2）动态Group By
（3）Top N
（4）累计计算
（5）层次查询

开窗函数和聚合函数的区别

（1）SQL 标准允许将所有聚合函数用作开窗函数，用OVER 关键字区分开窗函数和聚合函数。
（2）聚合函数每组只返回一个值，开窗函数每组可返回多个值。

窗口函数和group by子句的区别

窗口函数具备了我们之前学过的group by子句分组的功能和order by子句排序的功能。那么，为什么还要用窗口函数呢？

这是因为，group by分组汇总后改变了表的行数，一行只有一个类别。而partiition by和rank函数不会减少原表中的行数。例如下面统计每个班级的人数。

排序窗口函数

partition by vs order by

专用窗口函数区别

专用窗口函数rank, dense_rank, row_number有什么区别呢？
row_number() over()、rank() over()、dense_rank() over()、ntile() over()的区别
几个排序函数row_number() over()、rank() over()、dense_rank() over()、ntile() over()的区别
（1） row_number() over()：对相等的值不进行区分，相等的值对应的排名相同，序号从1到n连续。（1、2、3、4、5、6）

（2） rank() over()：计算排序时，如果存在相同位次的记录，则会跳过之后的位次。（1、2、2、4、5、6）

（3） dense_rank() over()：对相等的值排名相同，但序号从1到n连续。如果有两个人都排在第一名，则排在第2名（假设仅有1个第二名）的人是第3个人。（1、2、2、3、4、5）

（4） ntile( n ) over()：可以看作是把有序的数据集合平均分配到指定的数量n的桶中,将桶号分配给每一行，排序对应的数字为桶号。如果不能平均分配，则较小桶号的桶分配额外的行，并且各个桶中能放的数据条数最多相差1。

聚合窗口函数

聚和窗口函数和上面提到的专用窗口函数用法完全相同，只需要把聚合函数写在窗口函数的位置即可，但是函数后面括号里面不能为空，需要指定聚合的列名。


聚合函数sum在窗口函数中，是对自身记录、及位于自身记录以上的数据进行求和的结果。比如0004号，在使用sum窗口函数后的结果，是对0001，0002，0003，0004号的成绩求和，若是0005号，则结果是0001号~0005号成绩的求和，以此类推。

不仅是sum求和，平均、计数、最大最小值，也是同理，都是针对自身记录、以及自身记录之上的所有数据进行计算。

如果想要知道所有人成绩的总和、平均等聚合结果，看最后一行即可。

这样使用窗口函数有什么用呢？

聚合函数作为窗口函数，可以在每一行的数据里直观的看到，截止到本行数据，统计数据是多少（最大值、最小值等）。同时可以看出每一行数据，对整体统计数据的影响。

【特别注意】

聚合函数当做窗口函数用时，只要有order by，聚合函数求得结果都是上述情况。

如果没有order by，只有分组partition by，那么这个聚合函数求的是每组的和、计数、max、min、平均。就是和正常聚合函数的用法式一致的。

窗口函数计算移动平均

框架：

• 之前n行的记录（包括自身n+1行）作为汇总对象

<窗口函数> over ([partition by <列清单>]
                        order by <排序用列清单>
                 		rows n preceding)

• 之后n行的记录（包括自身n+1行）作为汇总对象

<窗口函数> over ([partition by <列清单>]
                        order by <排序用列清单>
                 		rows n following)

• 当前记录的前m行，后n行作为汇总对象

<窗口函数> over ([partition by <列清单>]
                        order by <排序用列清单>
                 		rows between m preceding and n following)

移动平均使用场景

用了rows和preceding这两个关键字，是“之前~行”的意思，上面的句子中，是之前2行。也就是得到的结果是自身记录及前2行的平均。
这样使用窗口函数有什么用呢？

由于这里可以通过preceding关键字调整作用范围，在以下场景中非常适用：在公司业绩名单排名中，可以通过移动平均，直观地查看到与相邻名次业绩的平均、求和等统计数据。

面试经典题

1、面试经典排名问题

下图是course_new表，id是学生学号，class是班级，score是分数
现在需要按score来排名，如果两个分数相同，那么排名要是并列的。
【解题思路】

1.涉及到排名问题，可以使用窗口函数
2.排名是并列的可以用rank()或者dense_rank()
3.如果要求排名并列，且名次是连续的，那么用dense_rank(),如果要求排名并列，名次是根据上面排名的人数往下排，那么用rank()
(当然，如果要求没有并列学生，要用row_number())

• rank()

select id,class,score,
rank() over(order by score desc) as rank
from course_new;

• dense_rank()

select id,class,score,
densse_rank() over(order by score desc) as rank
from course_new;

2、面试经典topN问题

工作中会经常遇到这样的业务问题：
如何找到每个类别下用户最喜欢的产品是哪个？
如果找到每个类别下用户点击最多的5个商品是什么？
这类问题其实就是常见的：分组取每组最大值、最小值，每组最大的N条（top N）记录。

面对该类问题，如何解决呢？

• 分组+关联子查询

我们可以使用分组（group by）和汇总函数得到每个组里的一个值（最大值，最小值，平均值等）。但是无法得到成绩最大值所在行的数据。
我们可以使用关联子查询来实现：

select s.id,s.class,c.max_score
from course_new s
inner join 
(	select max(t.score) as max_score 
	from course_new t
	group by id 
) c on s.id = c.id

• 窗口函数

select t.id, t.class, t.score from
(	select s.id, 
	s.class, 
	s.score, 
	row_number()over(partition by s.id order by s.score desc) as rank 
	from course_new s
) t
where t.rank = 1

每组最大的N条记录

• 窗口函数

select t.id, t.class, t.score from
(	select s.id, 
	s.class, 
	s.score, 
	row_number()over(partition by s.id order by s.score desc) as rank 
	from course_new s
) t
where t.rank  <= N

经典topN问题模板：每组最大的N条记录。这类问题涉及到“既要分组，又要排序”的情况，要能想到用窗口函数来实现。

select * from 
(select *, row_number() over (partition by 要分组的列名 order by 要排序的列名 desc) as ranking 
from 表名)  as a  where ranking<=N;

3.如何在组里比较

问题：course_new表，查找单科成绩高于该科目平均成绩的学生名单

• 关联子查询

select s.id,s.class,s.core
from course_new s
inner join 
(	select avg(t.score) as avg_score 
	from course_new t
	group by id 
) c on s.id = c.id and s.corce > c.avg_score

• 窗口函数

select t.id, t.class, t.score, t.avg_score from
(	select s.id, 
	s.class, 
	s.score, 
	avg(s.score)over(partition by s.id order by s.score desc) as avg_score
	from course_new s
) t
where t.score > t.avg_score

查找每个组里大于平均值的数据问题可以用关联子查询和聚合函数avg当窗口函数来查询。

参考链接：
https://zhuanlan.zhihu.com/p/128123483