1)什么是窗口函数

1.1 什么是窗口函数

窗口函数，也叫OLAP函数（Online Anallytical Processing，联机分析处理），窗口函数与分组聚合函数类似，但是每一行数据都生成一个结果。配合聚合窗口函数一起使用，如SUM/AVG/COUNT/MAX/MIN等等。可以对数据库数据进行实时分析处理。

1.2 基本语法

窗口函数的基本语法如下：

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

那么语法中的窗口函数可以分以下两种函数：专用窗口函数，包括rank, dense_rank, row_number等专用窗口函数。 聚合函数，如sum. avg, count, max, min等.

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

2）如何使用窗口函数

2.1 专用窗口函数rank

首先，在MYSQL中新建下图中的成绩表。

如果我们需要在每个班级内按成绩降序排名，需要使用语句代码如下：

select *,
rank() over (partition by 班级 -- partition by子句用来对表分组,此例以班级为分组
order by 成绩 desc) as ranking -- order by子句是对分许结果排序，默认升序(ASC)
from class
;

2.2 关于parttion by子句

parttion by子句用于给结果集进行分区。它和聚合函数group by不同的地方在于它只是将原始数据进行名次排列，能够返回一个分组中的多条记录（记录数不变），而group by是对原始数据进行聚合统计，一般只有一条反映统计值的结果（每组返回一条）。

3）其他专用窗口函数

3.1 专用窗口函数rank, dense_rank, row_number有什么区别呢

使用代码来展示专用窗口函数之间的区别。

select *,
rank() over (order by 成绩 desc) as ranking,
dense_rank() over (order by 成绩 desc) as dense__rank, -- as 后面作为新列名，名字不要和原子句相同，会报错
row_number() over (order by 成绩 desc) as row_num
from class
;

从以上结果可以看出：

• rank: 跳跃排序,相同数据排名相同,比如图中结果并列第5,则并列行数据均为5，下一位是第8名，中间的6和7被直接跳过了。排名存在重复值。
• dense_rank: 连续排序,比如图中结果并列第5,则并列行数据均为5,下一个排名是第6名。排名存在重复值。
• row_num: 相同数据，先查出的排名在前,没有重复值。

需要注意的是，专用窗口函数后面紧跟的括号里面不需要任何参数，保持空白即可。

4）专用窗口函数应用案例

4.1 排名问题

根据前面使用的班级表，按成绩来排名，如果两个分数相同，那么排名要是并列的。

正常排名是1，2，3，4，但是现在前3名是并列的名次，排名结果是：1，1，1，2。

/*

分析思路

要求使用连续排序，且下一个排名不跳过，使用dense_rank函数。

*/

select *, rank() over (order by 成绩 desc)
from class
;

4.2 topN问题

topN问题主要为分组取每组最大值、最小值，每组最大的N条（top N）记录。

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

/*

分析思路

使用group by分组和汇总函数得到每个组里的一个值，当表里面出现多行满足条件时，实际只会输出一行数据。所以需要使用前面学到的关联子查询。

*/

select *
from score as a
where 成绩 = (
select max(成绩)
from score as b
where b.课程号 = a.课程号
group by 课程号
)
;

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

/*

分析思路

与分组取每组最大值一样。

*/

select *
from score as a
where 成绩 = (
select min(成绩)
from score as b
where b.课程号 = a.课程号
group by 课程号
)
;

• 每组最大的N条记录

现有“成绩表”，记录了每个学生各科的成绩。表内容如下。问题：查找每个学生成绩最高的2个科目。

/*

分析思路

此题有两个要求，第一，按学生为分类对成绩排序，第二，选取学生成绩最高的2个科目。

对成绩分组排序，使用专用窗口函数进行排序，此题没有特别排序要求，使用row_number，对于选取成绩最高的两个科目，我们可以通过对row_number输出列通过where子句进行条件查询来限制行数。需要注意的是，此题涉及到使用子查询，因为涉及使用到where子句，根据运行顺序，

*/

select *
from(
select *,row_number() over (
partition by 姓名
order by 成绩 desc
) as row_num -- 使用as 作为窗口函数使用的列名
from score1
) as a -- 此处使用子查询需要使用as 为新的一个表增加表表名，否则会报错
where row_num <= 2
;

如果使用下列代码，就会报错。因为select运行顺序是在from、where、group by、having子句后面，下列代码中where子句执行在select子句之前，MYSQL就会识别不了row_num识别不了报错。

select *,row_number() over (
partition by 姓名
order by 成绩 desc
)as row_num
from 成绩表
where row_num <=2
;

此题可以举一反三，此题题目为每个学生的top2问题，若对于topN问题，将where子句中的限制条件改为<=N即可。

5）聚合函数作为窗口函数

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

聚合函数在窗口函数中，是对自身记录、及位于自身记录以上的数据进行聚合的结果。如果知道所有聚合结果，看最后一行即可。聚合函数作为窗口函数，可以在每一行的数据里直观的看到，截止到本行数据，统计数据是多少（最大值、最小值等）。同时可以看出每一行数据，对整体统计数据的影响。

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

• 查找单科成绩高于该科目平均成绩的学生名单（使用各科成绩表）

/*

分析思路

首先需要对每个分组科目求平均值，再找出大于平均值的数据。所以分组后不能减少表的行数，因此在group by和partition by中选择partition by，结合窗口函数使用，需要注意的是，此次依然需要结合子查询进行使用。先使用子查询使用窗口函数求出每行数据的平均值，再使用where子句查询条件大于平均值。

*/

select *
from(
select *,
avg(成绩) over (partition by 科目) as avg_
from score1
) as avg_form
where 成绩>=avg_
;

需要注意：在聚合窗口函数中，没有partition by, 没有order by，为全部总计；只有partition by, 没有order by，为分组小计；只有order by，没有partition by，为全部累计求和。

本题也可以使用复杂查询中的关联子查询得到结果。关联子查询可以在每个组里比较。

select *
from score1 as s1
where 成绩 >= (select avg(成绩)
from score1 as s2
where s1.科目 = s2.科目
group by 科目
)
;

5.3 窗口函数的移动平均

直接使用聚合函数avg的窗口函数举例。

select *,
avg(成绩) over (order by 学号 rows 2 preceding) as current_avg
from class
;
-- 上面的窗口函数中，用了rows和preceding这两个关键字，是“之前~行”的意思，上面的句子中，是之前2行。也就是得到的结果是自身记录及前2行的平均。若前面不足两行则按实际行数求平均。第一行的移动平均只包括第一行本身，第二行的移动平均只包括第一行和第二行本身。

想要计算当前行与前n行（共n+1行）的平均时，只要调整rows…preceding中间的数字即可。可以通过移动平均，直观地查看到与相邻行数的平均、求和等统计数据。

6）本节总结

6.1 总结窗口函数

• 窗口函数语法

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

• ‹窗口函数›的位置，可以放以下两种函数：

（1）专用窗口函数，比如rank, dense_rank, row_number等。

rank: 跳跃排序,相同数据排名相同,比如图中结果并列第5,则并列行数据均为5，下一位是第8名，中间的6和7被直接跳过了。排名存在重复值。

dense_rank: 连续排序,比如图中结果并列第5,则并列行数据均为5,下一个排名是第6名。排名存在重复值。

row_num: 相同数据，先查出的排名在前,没有重复值。

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

• 窗口函数有以下功能：

（1）同时具有分组（partition by）和排序（order by）的功能

（2）不减少原表的行数，所以经常用来在每组内排名

6.2 注意事项

需要注意：在聚合窗口函数中，没有partition by, 没有order by，为全部聚合不排序；只有partition by, 没有order by，为分组聚合；只有order by，没有partition by，为全部聚合排序。窗口函数同理。

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

6.3 运用场景

• 经典top N问题

找出每个部门排名前N的员工进行奖励。

• 经典排名问题

业务需求“在每组内排名”，比如：每个部门按业绩来排名。

• 在每个组里比较的问题

比如查找每个组里大于平均值的数据，可以有两种方法：

1.使用前面窗口函数案例来实现。

2.使用关联子查询。