一、知识点
窗口函数
group by 与 partition by 的区别
窗口函数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 班级表
聚合函数作为窗口函数
聚合函数作为窗口函数，可以在每一行的数据里直观的看到，截止到本行数据，统计数据是多少（最大值、最小值等）。同时可以看出每一行数据，对整体统计数据的影响。
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 班级表;
存储过程
二、练习操作
1、每个班级内按成绩排名
班级表
select *,
rank() over (partition by 班级 order by 成绩 desc )
as ranking
from 班级表;
每个人班级内：按班级分组
partition by 用来对表分组。按班级分组。
按成绩排名
order by子句的功能是对分组后的结果进行排序。
分组取每组最大值、最小值，每组最大的N条（topN）记录
2、按课程号分组取成绩最大值所在行的数据
select 课程号,max(成绩)
as 最大成绩
from score
group by 课程号;
select *
from score as a
where 成绩 = (
select max(成绩)
from score as
b where b.课程号 = a.课程号
);
3、按课程号分组取成绩最小值所在行的数据
select *
from score as a
where 成绩 = (
select max(成绩)
from score as
b where b.课程号 = a.课程号
);
4、每组最大的N条记录，topN问题
查找每个学生成绩最高的2个科目
select * from
(select *,row_number() over ( partition by 姓名 order by 成绩 desc)
as ranking from 成绩表 ) as a
where ranking<=2;
5、累计求和问题
薪水表部分数据
其中，薪水是指该雇员在起始时期到结束日期这段时间内的薪水。当前员工是指结束日期=‘9999-01-01’的员工（在职员工）
按照雇员编号升序排序，查找薪水的累计和（累计薪水）
select 雇员编号,薪水,sum(薪水) over ( order by 雇员编号 )
as 累计薪水
from 薪水表
where 结束时间='9999-01-01';
6、查找单科成绩高于该科目平均成绩的学生名单
select * from
(select *,avg(成绩) over ( partition by 科目)
as avg_score from 成绩表 ) as b
where 成绩 > avg_score;
当前行和前n行(n+1)位同学的平均成绩
select *,
avg(成绩) over ( order by 学号 rows 2 preceding )
as current_avg
from 班级表;
