SQL窗口函数的使用

· 定义：窗口函数，又叫OLAP(Online Anallytical Processing)函数，可对数据库数据进行实时分析处理。

· 功能：同时分组和排序；不减少原表的行数（区别于聚合函数，每行数据都生成一个结果）

· 使用场景：排名问题，topN问题

· 基本语法：

<窗口函数> OVER (PARTITION BY <用于分组的列名> ORDER BY <用于排序的列名>);
-- over关键字用于指定函数的窗口范围，
-- partition by 用于对表分组，
-- order by子句用于对分组后的结果进行排序。

· 窗口函数的位置可以放置以下两种函数：

1>专用窗口函数，包括rownumber();rank();denserank()等（后文以这3个为例介绍）。

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

· 几种窗口函数的用法：

这部分引用学生成绩排名的班级表举例。

①rank()
将每个班按成绩排名

SELECT *,rank() 
OVER 
(PARTITION BY 班级 ORDER BY 成绩 DESC) 
AS ranking 
FROM 班级表;

②dense_rank(),row_number()的用法及与rank()的区别

使用与①中类似的句式，看一下三者之间的区别：

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

rank() 如果有并列名次的行，占用下一名次的位置；

dense_rank() 如果有并列名次的行，顺次排列，不占用下一名次的位置；

row_number() 顺次排序，不考虑并列名次问题。

③聚合窗口函数

可以明确、直观地看到截止到某行数据，统计数据是多少，同时可以看出每行数据对整体统计数据的影响。用法与专用窗口函数相同，但括号中需要指定聚合的列名。

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

另外，还有三种常用方式（以平均数为例）：

①计算当前行与前n行（共n+1行）的聚合窗口函数

SELECT *,avg(成绩) 
OVER 
(ORDER BY 学号 ROWS n PRECEDING) 
AS current_avg 
FROM 班级表; 

②计算当前行与之后n行的聚合窗口函数

SELECT *,avg(成绩) 
OVER 
(ORDER BY 学号 ROWS n FOLLOWING) 
AS current_avg 
FROM 班级表;

③计算当前行与前n1行、后n2行的聚合窗口函数

SELECT *,avg(成绩) 
OVER 
(ORDER BY 学号 ROWS BETWEEN n1 PRECEDING AND n2 FOLLOWING) 
AS current_avg 
FROM 班级表; 

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

注意事项：

①partition子句可以省略，省略时默认不指定分组（开窗列），但会因此失去窗口函数的功能，所有一般不这样使用；

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

③窗口函数中不能嵌套使用窗口函数和聚合函数；

④专用窗口函数（）为空，聚合窗口函数（）中会写对应聚合列。