一、开窗函数(OLAP函数)
1、定义
用于为行定义一个窗口(这里的窗口是指运算将要操作的行的集合、一组记录,而不是普通意义上的窗户),它对一组值进行操作,不需要使用GROUP BY子句对数据进行分组,能够在同一行中同时返回基础行的列和聚合列。
开窗函数分为排序开窗函数和聚合开窗函数。
2、基本语法
<排序函数/聚合函数> over (partition by <用于分组的列名> order by <用于排序的列名>)
排序函数:rank(),dense_rank(),row_number()
聚合函数:sum(),avg(),count(),max(),min()
3、功能
(1)进行排序、生成序号
(2)返回多列值。
二、排序开窗函数
1、三种排序开窗函数的区别
注意:使用排序函数时,括号里没有参数
2、解决topN问题:子查询+排序开窗函数
select * from
(select *, dense_rank() over(partition by 分组列 order by 排名列 desc)as 排名
from 各科成绩表) as a
where 排名<=N;
注意:这里子查询产生的新表必须命名,否则报错。
三、聚合开窗函数
1、聚合开窗函数和聚合函数的区别
可以说,聚合开窗函数是加强版的聚合函数。
下面举个例子,要求在各科成绩表中增加一列,按学生对成绩求累和(自身和前几行的求和)
这里可以看到,猴子的成绩累和列出现错误,这是为什么呢?
问题出现在“order by 成绩”处。
因为order by将成绩相等的项归为了一类,那么在排序时就无所谓谁前谁后,它们都是同一行的,所以猴子的累和列,前三项都是90+90+90=270。
解决方法是添加排序条件,使每个成绩都可以被唯一识别,不再归为同一类。
改为“order by 科目,成绩“
2、解决组内比较(平均值、最大值、最小值)
3、解决移动平均问题(观察相邻名次的平均成绩)
小结
本章学习内容解决的三个业务问题
topN问题:排序开窗函数+子查询
组内比较问题:聚合开窗函数+子查询,也可以用关联子查询做
移动平均问题:聚合开窗函数