一.定义:
窗口函数也就叫OLAP函数,可以对数据库数据进行实时分析处理
二.基本语法
‹窗口函数› over (partition by ‹用于分组的列名› order by ‹用于排序的列名›)
三.分类
1.专用窗口函数
如rank, dense_rank, row_number等专用窗口函数
rank, dense_rank, 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 班级表;
2.聚合函数
如sum. avg, count, max, min等,聚合函数在窗口函数中,是对自身记录、及位于自身记录以上的数据进行计算得到结果
如果想要知道所有人成绩的总和、平均等聚合结果,看最后一行即可
例如:
select *, sum(成绩) over (order by 学号) as current_sum
from 班级表;
3.窗口函数的移动平移
用rows和preceding这两个关键字,是“之前几行”的意思,也就是得到的结果是自身记录及前几行的统计计算
想要计算当前行与前n行(共n+1行)的平均时,只要调整rows…preceding中间的数字即可
例如:
select *, avg(成绩) over (order by 学号 rows 2 preceding) as current_avg
from 班级表;
四.作用
1.功能
具有分组(partition by)和排序(order by)功能,不减少原表行数(group by分组后改变表的行数)
2.应用
排名问题,topN问题,每个组中比较问题,累计求和等
五.注意事项
1. partition by可以省略,当不需要分组时
2.窗口函数是对where或者group by子句处理后的结果进行操作,
原则上只能写在select子句中
3.上述的3个专用函数后面的括号中不需要任何参数,空着即可
4.聚合函数作为窗口函数,函数后的括号需要填写相应列名
六.案例应用
1. 面试经典排名问题
下图是"班级"表中的内容,记录了每个学生所在班级,和对应的成绩
现在需要按成绩来排名,如果两个分数相同,那么排名要是并列的。正常排名是1,2,3,4,但是现在前3名是并列的名次,排名结果是:1,1,1,2。
【本题考点】
1.考察如何使用窗口函数
2.专用窗口函数排名的区别:rank, dense_rank, row_number
select *,dense_rank() over (order by 成绩 desc) as dese_rank
from 班级表;
2.面试经典topN问题
这类问题就是常见的:分组取每组最大值、最小值,每组最大的N条(top N)记录。
现有“成绩表”,记录了每个学生各科的成绩。表内容如下。问题:查找每个学生成绩最高的2个科目
【本题考点】
1.主要考查对窗口函数的灵活使用。
2.在筛选过程中,非常容易因为子查询问题报错,本题也考察了对子查询的熟练运用。
3.本题间接考察了对sql语句执行顺序的熟悉程度。
select *
from (select *, row_number() over (partition by 姓名 order by 成绩 desc)
as ranking from 成绩表) as a
where ranking ‹= 2;
3.累计求和:聚合函数作窗口函数的应用
下表为确诊人数表,包含日期和该日期对应的新增确诊人数,按照日期进行升序排列,查找日期、确诊人数以及对应的累计确诊人数。
select 日期,确诊人数, sum(确诊人数) over (order by 日期)
as 累计确诊人数
from 确诊人数表;
【本题考点】
对于“累计”问题,要想到用聚合函数作为窗口函数。比如累计求和,用sum。
sum(列名) over (order by ‹用于排序的列名›)
累计求平均值,用avg
avg(列名) over (order by ‹用于排序的列名›)
4.如何在每组里比较?
可以有两种方法:
1)使用窗口函数来实现
2)使用关联子查询
问题:查找单科成绩高于该科目平均成绩的学生名单
select *
from (select *, avg(成绩) over (partition by 科目) as avg_score from 成绩表) as b
where 成绩 › avg_score;
select *
from 成绩表 as a
where 成绩>(select avg(成绩) from 成绩表 as b where b.科目=a.科目 group by 科目);