窗口函数
定义
窗口函数,也叫OLAP函数(Online Analytical Processing,联机分析处理),可以对数据库数据进行实时分析处理。
基本语法
‹窗口函数›
over (partition by ‹用于分组的列名›
order by ‹用于排序的列名›)
‹窗口函数›的位置,可以放以下两种函数:
1) 专用窗口函数,包括后面要讲到的rank, dense_rank, row_number等专用窗口函数。
2) 聚合函数,如sum. avg, count, max, min等
因为窗口函数是对where或者group by子句处理后的结果进行操作,所以窗口函数原则上只能写在select子句中。
作用
在日常工作中,经常会遇到需要在每组内排名,比如下面的业务需求:
排名问题:每个部门按业绩来排名
topN问题:找出每个部门排名前N的员工进行奖励
面对这类需求,就需要使用sql的高级功能窗口函数了
应用
1.专用窗口函数rank
rank是排序的函数,要求”每个班级内按成绩排名“,分为两部分:
1.按班级分组
partition by 班级
2.按成绩排名
order by 成绩 desc
先班级分组,在班级内按程序降序排名
group by与窗口函数的区别 : group by分组汇总改变行数,一行只有一个类别,而partition by和rank函数不会减少原表中的行数。
总结
- 同时具有分局和排序的功能
- 不减少原表的行数
- 语法如下
(窗口函数)
over (partition by 分组列名
order by 排序列名)
专用窗口函数
rank(), dense_rank(), row_number() 区别:
rank() 相同并列,序号不连续,dense_rank()相同并列,序号连续,
row_number() 相同不并列,序号连续
经典面试题1
Q:需要按照成绩排名,如果分数相同,则需并列排名(eg. 正常排名1,2,3,4;排名需1,1,1,2)
A:
面试经典题2: 分组取每组最大值,最小值,每组最大的N条(top N)记录
Q:按课程号分组取成绩最大值所在行的数据
A:
Q:按课程号分组取成绩最小值所在行的数据
A:
Q:查找每个学生成绩最高的2各科目
A:
聚合函数作为窗口函数
语法
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 雇员编号,薪水, sum(薪水)
over (order by 雇员编号) as 累计薪水 from 薪水表
where 结束日期 = '9999-01-01';
如何在每个组里比较?
Q:查找单科成绩高于该科目平均成绩的学生名单
select *
from (select *, avg(成绩) over (partition by 科目) as avg_score from 成绩表) as b
where 成绩 › avg_score;
窗口函数的移动平均
这里我们直接用聚合函数avg的窗口函数举例说明:
select *,
avg(成绩) over (order by 学号 rows 2 preceding) as current_avg from 班级表;
得到结果:
仔细看上面的窗口函数中,用了rows和preceding这两个关键字,是“之前~行”的意思,上面的句子中,是之前2行。也就是得到的结果是自身记录及前2行的平均。
例如:学号0004学生的current_avg,是自己和前2位同学的平均,即0002,0003,0004三位同学成绩的平均,其他数据的情况也一样,下图非常直观的可以看到计算过程:
每一行得到的结果,都是当前行和前面2行的平均(共3行)。想要计算当前行与前n行(共n+1行)的平均时,只要调整rows…preceding中间的数字即可。
这里需要注意:在移动平均中,被选出的数据构成一个“框架”,例如,刚才例子中的0002、0003、0004行数据,就是一个“框架”。
这样使用窗口函数有什么用呢?
由于这里可以通过preceding关键字调整作用范围,在以下场景中非常适用:
在公司业绩名单排名中,可以通过移动平均,直观地查看到与相邻名次业绩的平均、求和等统计数据。
总结
二.总结
1.窗口函数语法
‹窗口函数› over (partition by ‹用于分组的列名›
order by ‹用于排序的列名›)
‹窗口函数›的位置,可以放以下两种函数:
1) 专用窗口函数,比如rank, dense_rank, row_number等
2) 聚合函数,如sum. avg, count, max, min等
2.窗口函数有以下功能:
1)同时具有分组(partition by)和排序(order by)的功能
2)不减少原表的行数,所以经常用来在每组内排名
3.注意事项
窗口函数原则上只能写在select子句中
4.窗口函数使用场景
1)经典top N问题
找出每个部门排名前N的员工进行奖励
2)经典排名问题
业务需求“在每组内排名”,比如:每个部门按业绩来排名
3)在每个组里比较的问题
比如查找每个组里大于平均值的数据,可以有两种方法:
方法1,使用前面窗口函数案例来实现
方法2,使用关联子查询
4)累计求和问题