· 定义:窗口函数,又叫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子句中;
③窗口函数中不能嵌套使用窗口函数和聚合函数;
④专用窗口函数()为空,聚合窗口函数()中会写对应聚合列。