sql基于聚合结果集取最大值_SQL的高级功能

一、窗口函数

什么是窗口函数：OLAP函数（Online Anallytical Processing，联机分析处理），可以对数据库数据进行实时分析处理

1.专用窗口函数，包括后面要讲到的rank, dense_rank, row_number等专用窗口函数

2.聚合函数，如sum. avg, count, max, min等

使用窗口函数的情况：需要每组内排名时，兼具分组和排序的功能，例如topN

如何书写窗口函数：‹窗口函数› over (partition by ‹用于分组的列名› order by ‹用于排序的列名›)

窗口函数在SQL中的位置：是对where或者group by子句处理后的结果进行操作，所以窗口函数原则上只能写在select子句中

二、窗口函数的应用

1.专用窗口函数rank，排序函数，不同于 group by分组，rank 不改变分组之后的数据行数，group by汇总后改变了表的行数，一行只有一个类别

在school中创建以下班级表

①.对班级表，按每个班级内按成绩排名，先按班级排序，再按成绩排序（使用窗口函数）

分析：partition by （这里是窗口函数的窗口，限定了范围）对班级表进行分组，order by对分组之后的班内成绩进行排序

2.其他专用窗口函数

rank：rank() over (order by 成绩 desc) as ranking，考虑数据中存在相同的情况，占用下一排名的数据实际排名位置

dense_rank：dense_rank() over (order by 成绩 desc) as dese_rank，考虑数据中存在相同的情况，不占用下一排名的数据实际排名位置

row_number： row_number() over (order by 成绩 desc) as row_num，不考虑数据中存在相同的情况，进行的排名

排名受影响数据行为：最后一行数据,分别是8、6、8

3.解决topN问题

group by可以组内查询到：最大值、最小值和平均值，但是取不到topN的数据，是因为group by排序后的数据行不等于原表中的数据行，已经进行了筛选

关联子查询：group by求最大值

select * from score as a where 成绩 = ( select max(成绩) from score as b where b.课程号 = a.课程号);

关联子查询：group by求最小值

select * from score as a where 成绩 = (select min(成绩) from score as b where b.课程号 = a.课程号);

在school中创建以下各科成绩表

①.查找每个学生成绩最高的2个科目

分析：返回每个学生，需要对学生分组；成绩最高，需要对成绩排序；返回两个最高科目，不改变原有数据使用窗口函数

row_number()排除成绩并列情况，避免出现2行以上数据

由于最后一步才进行返回分组排序后的TOP2数据，根据SQL语句执行顺序，可采用子查询找到TOP2

SQL语句执行顺序如下

4.聚合函数作为窗口函数

应用：

1.截止到本行数据，统计数据是多少

2.每一行数据，对整体统计数据的影响

聚合函数(列名)，书写位置与专用窗口函数相同，只对自身记录、及位于自身记录以上的数据进行运算的结果

例：(成绩)表示对成绩进行运算，第N行返回的数据表示对第N行和（N-1）行的所有数据进行运算，且首先将数据按学号进行排序

三、练习

1.根据各科成绩表，查找单科成绩高于该科目平均成绩的学生名单

分析：首先应该对科目进行分组，求出科目的平均成绩，返回大于平均成绩的所有学生

内层运行结果

外层运行结果

2.子查询查找每个组里大于平均值的数据

四、窗口函数的移动平均：选中某范围做平均值的运算

关键字：rows和preceding，rows n preceding

应用：业绩名单排名中，可以通过移动平均，直观地查看到与相邻名次业绩的平均、求和等统计数据

例：rows 2 preceding意味着：本行数据及其以上两行数据的运算，共3行

五、总结

1.窗口函数的应用场景：进行比较或组内排名，以上的分析结果都是在不改变原表行数的基础上实现的，TOPN与组内排名分析思路基本相同，再多一层比较运算

2.根据想要得到的结果选择窗口函数：rank, dense_rank, row_number，基于是否占用下一排名的位置来选择

3.窗口函数中的 partition by 可对数据进行分组，也可以省略，order by 可对数据进行排序

4.聚合函数中只对当前行及以上行数据进行运算，rows n preceding 可对局部区域进行运算

5.重点注意：SQL语句的运行顺序，窗口函数只应用在select层