sql补0函数_SQL从0到1：窗口函数

本次内容包括：

• 什么是窗口函数？
• 如何使用窗口函数？
• 其他专用窗口函数
• 案例：面试经典问题
• 案例：面试经典topN问题

一、什么是窗口函数

1、定义

窗口函数，也称为OLAP函数（Online Analytical Processing，联机分析处理），可以对数据库的数据进行实时分析处理。

2、窗口函数有什么用？

在日常工作中，经常会遇到需要在每组内排名，比如以下业务需求：

排名问题：每个部门按业绩来排名 topN问题：找出每个部门排名前N的员工进行奖励

面对这样的需求，就需要使用窗口函数

3、基本语法

<窗口函数> over 
(partition by <用于分组的列名>
order by <用于排序的列名>)

<窗口函数>的位置，可以放以下两种函数： 1. 专用窗口函数，包括rank，dense_rank，row_number等专用窗口函数 2. 聚合函数，如sum，avg，count，max，min等

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

二、如何使用窗口函数？

1、专用窗口函数rank

以此表为例

如果需要按每个班级内成绩排名，得到下面的结果

以班级“1”为例，这个班级的成绩“95”排在第1位，这个班级的“83”排在第4位，对应的SQL语句如下

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

分析过程： 问题“每个班级内按成绩排名”可以拆分成两个部分：

1. 每个班级内：按班级分组

partition by用于对表分组。这个例子中用于分组的字段为“班级”

1. 按成绩排名

order by用于对分组后的结果进行排序。默认是升序（asc），加入关键字desc可指定为降序排列。

下图说明了partition by和order by的作用

2、为什么要用窗口函数？

问：group by和order by子句同样具有分组和排序的功能， 为什么还要用窗口函数？

答：因为group by分组汇总改变了表的行数，一行只有一个类别；而partition by和rank函数不会减少原表中的行数。

3、“窗口函数”这个名字的由来

partition by分组后产生的结果称为“窗口”，表示“范围”的意思。

4、窗口函数的功能

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

三、其他专用窗口函数

包括了rank，dense_rank，row_number函数

举例说明：

select *,rank()over(
order by 成绩 desc) as ranking,
dense_rank()over(
order by 成绩 desc) as dense_rank,
row_number()over(
order by 成绩 desc) as row_num  #注意别名不可与窗口函数同名，否则报错
from 成绩表

得到的结果如下：

从结果可以看出：

rank函数：例子中显示5、5、5、8。即 当出现有并列名次的行，会占用下一个名次的位置。比如正常排名是1，2，3，4，但如果前三名是并列的话，结果会是1，1，1，4。
dense_rank函数：例子中显示5、5、5、6。即 当出现有并列名次的行，不占用下一名次的位置。比如正常排名是1，2，3，4，如果前3名是并列的话，结果会是1，1，1，2。 row_number函数：这个例子中是5、6、7、8。也就是 不考虑并列名次的情况。如果前3名是并列的话，排名是正常的1，2，3，4。

总结：这三个函数的区别是：

需要强调的一点是，在这三个专用窗口函数中，函数后面的括号不需要任何参数，保持（）空着就可以了。

案例：经典排名问题

给出一个成绩表，里面记录了每个学生学号、所在班级和对应的成绩。

问：按成绩的高低进行排名（如果两个分数相同，排名是并列的）

解题思路：

1. 涉及排名问题，使用窗口函数
2. 分清楚窗口函数ran(),dense_rank(),row_number之间的区别
3. 正确选择所需的窗口函数

SELECT *,dense_rank() over(
ORDER BY 成绩 DESC)as 按成绩排名
from 成绩表 

得到的结果是：

Tips：

涉及分数排名问题时，需选择dense_rank函数，因为相同的分数并列之后，下一个名次应该是下一个连续的整数值。

案例：经典TopN问题

问题举例：

1. 如何找到每个类别下用户最喜欢的产品是哪个？
2. 如何找到每个类别下用户点击最多的5个商品是什么？

这类问题可以归类为：分组取每组最大值、最小值，每组最大的N条（Top N）记录。

以下面这个成绩表为例

1、分组取每组最大值

案例：按课程号分组取成绩最大值所在行的数据

我们可以使用分组（group by）和汇总函数max得到每个组中的最大值，但是无法得到最大值所在行的数据。

SELECT 课程号,max(成绩)
from Score
GROUP BY 课程号

得到的结果如下：

在这种情况下，我们可以用关联子查询来实现查找最大值所在行数据的目的

SELECT * 
from Score as a
where 成绩=(
SELECT max(成绩)
from Score as b
where b.课程号=a.课程号
)

得到的结果如下：

2、分组取每组最小值

案例：按课程号分组取成绩最小值所在行的数据

SELECT * 
from Score as a
where 成绩=(
SELECT min(成绩)
from Score as b
where b.课程号=a.课程号
)

得到的结果如下：

3、每组最大的N条记录

案例：现有“成绩表”，记录了每个学生各科的成绩。表内容如下。

问：查找每个学生成绩最高的2个科目

SELECT *,row_number() over(
partition by 姓名 order by 成绩 desc) as ranking
from 各科成绩表

得到的结果如下：

每个学生的排名情况都得出来之后，再取排名前两位的行作为结果输出即可。此时可以用到子查询，把第一步得到的结果作为一个新的表

SELECT *
from(
SELECT *,row_number() over(
partition by 姓名 order by 成绩 desc) as ranking
from 各科成绩表
) as a  #此处需要给表取一个别名，否则会报错
where ranking<=2;

举一反三：

经典的topN问题涉及到“即要分组，又要排序”的情况，要想到用窗口函数来实现。

将本体的where子句的条件修改成为N，就是这类问题的一个万能模板

#topN问题万能模板
SELECT *
from(
SELECT *,row_number() over(
partition by 要分组的列名 order by 要排序的列名 desc) as ranking
from 表名
) as a  #生成的表需要一个别名
where ranking<=N;

四、聚合函数作为窗口函数

1.如何使用？

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

举例说明：

select *,
sum(成绩)over(order by 学号)as current_sum,
avg(成绩)over(order by 学号)as current_avg,
count(成绩)over(order by 学号)as current_count,
max(成绩)over(order by 学号) as current_max,
min(成绩)over(order by 学号) as current_min
from 成绩表

输出结果如下：

从结果中可以看到，聚合函数（求和、平均、计数、最大最小值等）都是针对自身记录、以及自身记录以上的所有数据进行计算。如0005号的sum函数，是对0001，0002，0003，0004，0005号的成绩进行求和，再输出结果。

2.有什么用？

聚合函数作为窗口函数，可以在每行数据里直观地看到，截止到本行数据的统计情况，同时可以看到每行数据对整体统计数据的影响。

案例：如何在每个组里比较？

题目：现有一个成绩表（包括姓名、科目、成绩）

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

解题思路：涉及每个科目的比较，需要用到分组。而能够实现分组功能的函数有两个，一个是group by子句，一个是窗口函数partition by。

由于group by分组汇总后会改变表的行数，我们选择使用窗口函数partition by

解题过程：

第一步：求出各科目的平均成绩

select *,
avg(成绩) over(partition by 科目) as avg_score
from 各科成绩表

得出结果如下：

第二步：将成绩与平均成绩进行比较，筛选出成绩高于平均成绩的行

SELECT *
from (select *,
avg(成绩) over(partition by 科目) as avg_score
from 各科成绩表) as b
where 成绩> avg_score

输出结果如下：

需要注意的是，第二步必须用到子查询，因为涉及到sql语句的运行顺序，存在于第一步select子句中的avg_score列无法直接与成绩列进行比较。

总结：

查找每个组里大于平均值的数据，可以使用两种方法

1. 使用以上窗口函数来实现
2. 使用复杂查询中的关联子查询

五、窗口函数的移动平均

1.移动平均是什么？

举例说明

SELECT *,avg(成绩)over(ORDER BY 学号 rows 2 preceding) as current_avg
from 成绩表

输出结果为：

代码中的 rows ...preceding关键字是“之前...行”的意思，如rows 2 preceding就是指自身以及前两行的数据。

如图所示，current_avg每一行的结果，都是当前行和前面2行的平均值。

想要计算当前行与前n行（共n+1行）的平均时，只需要调整rows...preceding中间的数字即可。

需要注意的是，在移动平均中，被选出的数据构成一个“框架”。如上面的0002、0003、0004的成绩数据，就构成了一个“框架“。

2.移动平均有什么用？

通过preceding关键字调整作用范围，在以下场景中非常适用：

• 在公司业绩名单排名中，可以通过移动平均，直观地看到与相邻名次业绩的平均、求和等统计数据。

六、总结

1.注意事项

partition子句可以省略，省略就是不指定分组。

举例说明

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

输出结果如下：

2.窗口函数总结

1. 窗口函数语法

‹窗口函数› over (
partition by ‹用于分组的列名›                             
order by ‹用于排序的列名›)

<窗口函数>的位置可以放两种函数：

专用窗口函数，比如rank，dense_rank，row_number等，此时函数后面的括号不用填

聚合函数，如sum，avg，count，max,min等，此时函数后面的括号里面要指定列名

2. 窗口的功能

同时具有分组和排序的功能；不减少原表的行数，可以用来给组内排名

3. 注意事项

窗口函数原则上只能写在select子句中

4. 窗口函数使用场景

1）经典topN问题： 找出每个部门排名前N的员工

2） 经典排名问题：“每组内排名”问题，如每个部门按业绩来排名

3） 在每个组里比较的问题：如查找每个组里大于平均值的数据