一、什么是窗口函数

窗口函数也叫OLAP函数（Online Anllytical Processing,联机分析处理）可以对数据进行实时分析处理。

二、窗口函数的作用

窗口函数用于解决需要“在每组内排名”的业务需求问题，比如：

1）排名问题：每个部门按业绩排名

2）TopN问题：找出每个部门排名前N的员工进行奖励

三、窗口函数语法

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

<窗口函数>可以放如下函数：

专用窗口函数：包括rank、dense_rank、row_number等专用窗口函数；
聚合函数：如sum、avg、count、max、min等；

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

四、窗口函数的功能

窗口函数具有以下功能：

1）同时具有分组和排序的功能；

2）不减少原表的行数

3）语法如下：

<窗口函数> over （
Partition by<用于分组的列名>
Order by<用于排序的列名>
）

练习1：将图1班级表按照成绩排名，得到图2结果

sql实现语句如下：

SELECT*,
rank() over (PARTITION BY 班级
order BY 成绩 desc) AS ranking from 班级表;

其中：

partition by 用来对表进行分组，这里按照班级分组；
order by 子句的功能是对分组后的结果进行排序，默认是升序，这里用order by 成绩 desc 表示按照成绩降序排列；

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

五、专用窗口函数

其他专用窗口函数包括：rank，dense_rank，row_number，这几个函数的区别如下：

1） rank函数：如果有并列名次的行，会占用下一名次的位置；

2） dense_rank函数：如果有并列名次的行，不会占用下一名次的位置；

3） 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 班级表;

注意：rank，dense_rank，row_number三个专用窗口函数后面的括号中不需要任何参数，保持（）空着就可以。

六、经典面试题

1、对班级表按照成绩来排名，如果两个分数相同，那么排名是需要并列

解题思路：涉及排名问题，可以使用窗口函数，比如rank，dense_rank，row_number；

同时，需要考虑名次并列问题，因此使用dense_rank;

SELECT *,
dense_rank() over (
ORDER BY 成绩 DESC
) AS dese_rank
from 班级表;

2、TopN问题

成绩表中记录了每个学生的各科成绩，表内容如图3所示，问题：查找每个学生成绩最高的2个科目

方法一：用关联子查询

(SELECT * 
from 各科成绩表
WHERE 姓名="猴子"
ORDER BY 成绩 DESC limit 2)
union ALL
(SELECT * 
from 各科成绩表
WHERE 姓名="王思聪"
ORDER BY 成绩 DESC limit 2)
union ALL
(SELECT * 
from 各科成绩表
WHERE 姓名="马云"
ORDER BY 成绩 DESC limit 2)
union ALL
(SELECT * 
from 各科成绩表
WHERE 姓名="马化腾"
ORDER BY 成绩 DESC limit 2);

方法二：用窗口函数

SELECT * FROM(
SELECT *,
 row_number() over(
partition by 姓名 ORDER BY 成绩 desc 
) as ranking 
from 各科成绩表
) as a WHERE ranking<=2;

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

聚合窗口函数和专用窗口函数的区别是：用法完全相同，但是聚合窗口函数后面的括号中不能为空，需要制定聚合的列名；

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

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 班级表;

练习：现有各科成绩表，记录了每个学生各科的成绩，问题：查找单科成绩高于该科目平均成绩的学生名单

SELECT * from(
SELECT *, 
avg(成绩) over(
partition by 科目
) AS avg_score from 各科成绩表
) as a WHERE 成绩>avg_score;

八、窗口函数的移动平均

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

Select *, avg(成绩) over (
     Order by 学号
      Rows 2 preceding
) as current_avg  from 班级表;

Rows 和preceding关键词：表示“之前~~行”，也就是得到的结果是自身记录及前几行的平均；

九、总结

1）注意事项

Partition子句可以省略，但是省略后就是不指定分组，相当于对整个表进行排序了；

2）总结

（1）窗口函数的语法

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

Order by <用于排序的列名>

)

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

专用窗口函数：比如rank，dense_rank，row_number等；
聚合函数：比如sum，avg，count，max，min等；

（2）窗口函数的功能

同时具有分组（partition by）和排序（order by）的功能；
不减少原表的行数，所以经常用来在组内排名；

（3）注意事项

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

（4）窗口函数使用场景

经典TopN问题：找出每个部门排名前N的员工进行奖励；
经典排名问题：业务需求“在每组内排名”，比如：每个部门按业绩来排名；
在每个组内 比较的问题：比如查找每个组内大于平均值的数据，可以有两种方法：

方法一：使用窗口函数来实现；

方法二：使用关联子查询；

ITPub博客

数据分析之SQL高级功能