窗口函数（OLAP：Online Anallytical Processing，联机分析处理）
- 具有分组和排序的功能
- 不减少原表行数（group by 是分组汇总，改变行数）
窗口函数() over (partition by 分组的列名 order by 排序的列明)
select *,
rank() over (PARTITION BY 班级 order By 成绩 desc) as ranking
FROM course
3种窗口函数：rank, dense_rank, row_number
select *,
rank() over (order by 成绩 desc)as ranking
from course
select *,
dense_rank() over (order by 成绩 desc)as dense_ranking
from course
select *,
row_number() over (order by 成绩 desc)as row_number_ranking
from course
总结：
练习题1：按照成绩排名，如果两个分数相同，排名并列
select 课程号,成绩,
dense_rank() over(order by 成绩 desc) as 排名
from course
练习2：对每个课程号进行成绩排名
select 课程号,成绩,
dense_rank() over(PARTITION by 课程号 order by 成绩 desc) as 排名
from course
练习3：按课程号分组取成绩最大值所在行的数据 (2种做法)
<子查询>
select 课程号,成绩
from course as a
where 成绩 = (select max.成绩
from course as b
where a.课程号=b.课程号)
order by 课程号
<窗口函数·模板>:先开一个窗口（排号顺序的表格），在从筛选出要的名词
select *
from (SELECT *,
rank()over(partition by 课程号 order by 成绩 desc) as ranking
from course ) as a
where a.ranking <=1
聚合函数+窗口函数：将排在自己前面的值sum, avg, max, min
- 可以看出每一行数据对整体的影响
- 最后一行就是整体数据的sum, avg, max, min
select *,
sum(成绩)over (order by 学号) as 自己和排名比自己高的成绩总和,
avg(成绩)over (order by 学号) as 自己和排名比自己高的成绩平均,
max(成绩)over (order by 学号) as 自己和排名比自己高的成绩最大值,
min(成绩)over (order by 学号) as 自己和排名比自己高的成绩最小值
from course1
练习1：查找单科高于该科目平均成绩的学生名单（在每个组里进行比较）
第一步：按照课程号分组，求每个课程的平均成绩
select *,
avg(成绩)over (PARTITION BY 课程号) as 各科平均成绩
from course1
第二步：从↑的表中，挑出学生成绩>各科平均成绩
select *
from(select *,
avg(成绩)over (PARTITION BY 课程号) as 各科平均成绩
from course1)as a
where 成绩 > 各科平均成绩
子查询：
select 学号,课程号,成绩
from course1 as a
where 成绩 > (select avg(成绩)
from course1 as b
where a.课程号=b.课程号
group by 课程号)
窗口函数的移动平均：rows # preceding
- 直观的查看与相邻名次业绩的平均、求和等
select *,
avg(成绩) over (order by 学号 rows 2 preceding) as 自己和前两行的平均成绩
from course1
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/31537575/viewspace-3171420/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
聊聊并发编程的10个坑
请登录后发表评论
登录
全部评论
