SQL 窗口函数

业务需求

排名问题：班级成绩排名

topN问题：考试前几名学生

基本语法

<窗口函数> over (partition by <用于分组列名> order by <用于排序列名>)

窗口函数：

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 班级表

学号	班级	成绩	rank	dense_rank	row_number
2	1	98	1	1	1
3	1	96	2	2	2
1	1	95	3	3	3
6	2	92	4	4	4
4	2	92	4	4	5
5	3	78	6	5	6
8	4	77	7	6	7
7	4	60	8	7	8

聚合函数作为窗口函数

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 班级表

学号	班级	成绩	sum	avg	count	max	min
1	1	86	86	86.0000	1	86	86
2	1	95	181	90.5000	2	95	86
3	2	89	270	90.0000	3	95	86
4	1	83	353	88.2500	4	95	83
5	2	86	439	87.8000	5	95	83
6	3	92	531	88.5000	6	95	83
7	3	86	617	88.1429	7	95	83