窗口函数知识框架

为何学窗口函数

首先了解相关概念窗口函数也叫OLAP函数（Online Anallytical Processing），可以对数据库数据进行实时分析处理。窗口函数基本语法：

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

其中partition by用来对表分组（分组）;order by子句的功能是对分组后的结果进行排序（组内排序）。窗口函数具备了我们之前学过的group by子句分组的功能和order by子句排序的功能。那么，为什么还要用窗口函数呢？这是因为，group by分组汇总后改变了表的行数，一行只有一个类别。而partiition by和rank函数不会减少原表中的行数。

2. 窗口函数

2.1 分类

1）专用窗口函数，包括后面要讲到的rank, dense_rank, row_number等专用窗口函数。

rank, dense_rank, row_number三个专用函数后面的括号不需要任何参数，保持()空着。

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 class;

rank函数：并列排序，跳过重复序号

dense_rank函数：并列排序，不跳过重复序号

row_number ：不考虑并列名次的情况，顺序排序

2）聚合函数，如sum. avg, count, max, min等

select *,
sum(成绩) over (partition by 科目 order by 成绩) as current_sum,
from class;

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

聚合函数当做窗口函数用时：省略partiition by，保留order by，则是总体数据的相应函数统计；保留partition by，省略order by，则是每组的相应函数统计。

2.2 功能

基于2.1的分析可知窗口函数功能：1) 同时具有分组和排序的功能； 2 )不减少原表的行数。

2.3 应用场景：

组内排名问题 1）排名问题：每部门按业绩排名；2）topN问题：找出每个部门排名前N的员工。

2.4 注意事项

因为窗口函数是对where或者group by子句处理后的结果进行操作，所以窗口函数原则上只能写在select子句中。 partition by/order by 不可以同时省略。

3. 专用窗口函数应用

3.1 排名问题

现在需要按成绩来排名，如果两个分数相同，那么排名要是并列的，平分后的下一个名次应该是下一个连续的整数值（备注：mysql版本8以上才支持窗口函数功能）

select *,
dense_rank() over (partition by 班级 order by 成绩 desc) as ranking
from class;

3.2 topN问题

1)组内最大值

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

#方法1 关联子查询
select *  from score as a
where 成绩 =
(select max(成绩) from score as bwhere b.科目 = a.科目);

#方法2 窗口函数
select * from 
(select*,rank() over (partition by科目 order by 成绩 desc) as ranking from 成绩表) as a
where ranking=1;

2)每组最大的N条记录

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

#方法1：查询各科成绩前两名的记录   每组最大的N条记录-- 来源上节topN问题
(Select* from score where 姓名= '猴子' order by 成绩 desc limit 2)
Union all
(Select* from score where 姓名= '王思聪' order by 成绩 desc limit 2)
Union all
(Select* from score where 姓名= '马云' order by 成绩 desc limit 2);
Union all
(Select* from score where 姓名= '马化腾' order by 成绩 desc limit 2)

#方法2 使用窗口函数 /*完美解决分组条件数量过多的topN问题*/

-- 错误写法 
select *,
row_number() over (partition by 姓名 order by 成绩 desc) as ranking
from 成绩表
where ranking <=2;
/*sql运行顺序：运行到”where ranking › 2”的时候，因为select字句还没有被执行，
因此select中的“ranking”列还没有出现*/

-- 正确写法/*子查询：将应用窗口函数查询出的结果作为一个新表*/
select * from
(select *,row_number() over (partition by 姓名 order by 成绩 desc) as ranking
from 成绩表) as a
where ranking<=2;

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

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）问题类型：每个组内比较

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

#方法1 关联子查询
select * from 成绩表 as s1
where 成绩 > 
(select avg(成绩) from 成绩表 as s2
where s1.科目=s2.科目 group by 课程号)

#方法2 窗口函数
select * from 
(select *,avg(成绩) over (partition by 科目) as avg_score  from 成绩表) as b 
where 成绩 › avg_score;

5. 窗口函数的移动平均

1）案例理解

select *,
   avg(成绩) over (order by 学号 rows 2 preceding) as current_avg
from class

指定框架（汇总范围）：这里使用的rows（行）和preceding（之前）两个关键字，将框架指定为截止到之前rows（行）。

例如：学号0004学生的current_avg，是自己和前2位同学的平均，即0002，0003，0004三位同学成绩的平均，得到的结果是自身记录及前2行的平均。每一行得到的结果，都是当前行和前面2行的平均（共3行）。

用关键字following（之后）替换preceding，就可以将框架改为截止到之后2行,也就是找 004 005 006 的平均值。

2）应用场景：

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

ITPub博客

SQL高级查询

最新文章