sql分组后组内排序_SQL的开窗函数|解决topN、组内排序、移动平均问题

一、开窗函数(OLAP函数)

1、定义

用于为行定义一个窗口（这里的窗口是指运算将要操作的行的集合、一组记录，而不是普通意义上的窗户），它对一组值进行操作，不需要使用GROUP BY子句对数据进行分组，能够在同一行中同时返回基础行的列和聚合列。

开窗函数分为排序开窗函数和聚合开窗函数。

2、基本语法

<排序函数/聚合函数> over (partition by <用于分组的列名> order by <用于排序的列名>)

排序函数：rank(),dense_rank(),row_number()

聚合函数：sum(),avg(),count(),max(),min()

3、功能

（1）进行排序、生成序号

（2）返回多列值。

二、排序开窗函数

1、三种排序开窗函数的区别

让班级表里的各班按成绩进行排名

注意：使用排序函数时，括号里没有参数

2、解决topN问题：子查询+排序开窗函数

从各科成绩表中查询每个学生成绩最高的两科目

select * from 
(select *, dense_rank() over(partition by 分组列 order by 排名列 desc)as 排名
from 各科成绩表) as a
where 排名<=N;

注意：这里子查询产生的新表必须命名，否则报错。

三、聚合开窗函数

1、聚合开窗函数和聚合函数的区别

可以说，聚合开窗函数是加强版的聚合函数。

下面举个例子，要求在各科成绩表中增加一列，按学生对成绩求累和（自身和前几行的求和）

前三项数值错误

这里可以看到，猴子的成绩累和列出现错误，这是为什么呢？

问题出现在“order by 成绩”处。
因为order by将成绩相等的项归为了一类，那么在排序时就无所谓谁前谁后，它们都是同一行的，所以猴子的累和列，前三项都是90+90+90=270。

解决方法是添加排序条件，使每个成绩都可以被唯一识别，不再归为同一类。
改为“order by 科目,成绩“

结果正确

2、解决组内比较(平均值、最大值、最小值)

查询单科成绩高于该科目平均成绩的学生名单

3、解决移动平均问题（观察相邻名次的平均成绩）

按科目求各科移动两项的平均成绩

小结

本章学习内容解决的三个业务问题

topN问题：排序开窗函数+子查询

组内比较问题：聚合开窗函数+子查询，也可以用关联子查询做

移动平均问题：聚合开窗函数