6、SQL中的窗口函数

Lee1639
417 阅读5分钟

1、什么是窗口函数

1）定义

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

partition by分组后产生的结果称为“窗口”，表示“范围”的意思；因此称为“窗口函数”

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子句中。

4）窗口函数的作用

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

2、如何使用窗口函数

1）rank 专用窗口函数

实例：下面左表需要按每个班级内成绩进行排名，处理成右表的样子，如何处理？

image.png image.png

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

分析过程：
问题“每个班级内按成绩排名”可以拆分成两个部分：
1.每个班级内：按班级分组
partition by用于对表分组。这个例子中用于分组的字段为“班级”
2.按成绩排名
order by用于对分组后的结果进行排序。默认是升序（asc），加入关键字desc可指定为降序排列。
下图说明了partition by和order by的作用
image.png

2）group by/order by 与partition by/rank的区别

group by和order by子句同样具有分组和排序的功能；
但group by分组汇总改变了表的行数，一行只有一个类别；
而partition by和rank函数不会减少原表中的行数。

3、其他专用窗口函数

1）rank、dense_rank、row_number

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

总结如下图，需要注意的是，在这三个专用窗口函数中，函数后面的括号不需要任何参数，保持（）空着就可以了。
image.png

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

image.png

2） 案例：经典排名问题

给出一个成绩表，里面记录了每个学生学号、所在班级和对应的成绩。
问：按成绩的高低进行排名（如果两个分数相同，排名是并列的）

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

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

3）案例：经典topN问题

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

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

实例：以下面这个成绩表为例

image.png

1.分组取每组最大值

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

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

image.png

2.分组取每组最小值

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

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

image.png

3.每组最大的N条记录

topN问题涉及到“即要分组，又要排序”的情况，要想到用窗口函数来实现。问题万能模板如下：

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

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

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

image.png

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

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 成绩表

2）使用场景

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

3）应用案例

题目：现有一个成绩表（包括姓名、科目、成绩） 问：查找单科成绩高于该科目平均成绩的学生名单

解题思路： 查找每个组里大于平均值的数据，可以使用两种方法
1.使用以上窗口函数来实现
2.使用复杂查询中的关联子查询

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

image.png

5、移动平均 rows...preceding

1）示例

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

image.png
代码中的 rows ...preceding关键字是“之前...行”的意思，如rows 2 preceding就是指自身以及前两行的数据。
即：想要计算当前行与前n行（共n+1行）的平均时，只需要调整rows...preceding中间的数字即可。

image.png

2）移动平均的使用场景

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

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

6、总结

1）窗口函数总结

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

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

  • 专用窗口函数，比如rank，dense_rank，row_number等，此时函数后面的括号不用填
  • 聚合函数，如sum，avg，count，max,min等，此时函数后面的括号里面要指定列名

2）窗口的功能

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

3）注意事项

1.窗口函数原则上只能写在select子句中 2.partition子句可以省略，省略就是不指定分组。

4）窗口函数使用场景

1.经典topN问题：找出每个部门排名前N的员工
2.经典排名问题：“每组内排名”问题，如每个部门按业绩来排名
3.在每个组里比较的问题：如查找每个组里大于平均值的数据，可采用窗口函数或关联子查询进行。

avatar
文章
阅读
粉丝