通过常见的业务掌握SQL高级功能
前言：
本文使用的窗口函数需要Mysql8
阅读需要10分钟，题目有一定难度
1、窗口函数
基本语法：
<窗口函数> over (partition by <用户分组的列名> order by <用户排序的列名>)<窗口函数>的位置可以放一下两种函数：
专用窗口函数：rank,dense_rank,low_number
聚合函数：sum,avg,count,max,min
窗口函数是对where或者group by 子句处理后的结果进行操作，所以窗口函数原则上只能写在select 子句中。
2、专用窗口函数rank
转成
select *,rank() over(partition by 班级 order by 成绩 desc) as ranking from 班级表3、专用函数rank,dense_rank,row_number有什么区别呢？
select *,rank() over(order by 成绩 desc) as ranking,
dense_rank() over (order by 成绩 desc)as desc_rank,
row_number() over (order by 成绩 desc)as row_num from 班级表
4、题目
下图是"班级"表中的内容，记录了每个学生所在班级，和对应的成绩。
正常排名是1，2，3，4，但是现在前3名是并列的名次，排名结果是：1，1，1，2。
所以用dense_rank
5、【面试题类型】topN问题
- 分组取每组最大值
案例：按课程号分组取成绩最大值所在行的数据
select 课程号,max(成绩) as 最大成绩
from score
group by 课程号;分组取每组最小值
案例：按课程号分组取成绩最小值所在行的数据（意思是每个课程最小值的学生信息都要出来）
关联子查询
select *
from score a
where 成绩=(
select min(成绩)
from score b
where b.课程号=a.课程号
)案例：查询各科成绩前两名的记录
select *,
row_number() over (partition by 姓名
order by 成绩 desc) as ranking from 成绩表
where ranking <=2很容易写成这样的错误写法，是因为where先执行，但是where就用了select里面的东西所以会报错
select *
from (
select *,row_number() over
(partition by 学号
order by 成绩 desc) as ranking from score
) as a
where ranking <=2所以我们要把内容转移到from里面，然后select * 因为from和select是一起运行的
select *
from (
select *,row_number() over
(partition by 学号
order by 成绩 desc) as ranking from score
) as a
where ranking <=2经典topN问题：每组最大的N条记录。这类问题涉及到“既要分组，又要排序”的情况，要能想到用窗口函数来实现。
select *
from
(select *,row_number() over
(partition by 要分组的列
order by 要排序的列 desc) as ranking from 表名) as a
where ranking<= n;6、聚和窗口函数
select *,sum(成绩) over(order by 学号) as current_sum,
avg(成绩) over(order by 学号) as current_avg,
count(成绩) over (order by 学号) as current_count
min(成绩) over (order by 学号) as current_min
from 班级表得到
这样使用窗口函数的作用就是，可以在每一行的数据可以直观的看到，截止到本行数据，统计数据是多少行，同时可以看到每一行数据，对整体统计数据的影响。
7、如何在每个组里面比较
问题：查找单科成绩高于该科目平均成绩的学生名单
窗口函数写法
select *
from(
select *,
avg(成绩) over(PARTITION by 课程号) as 平均成绩
from score
)as a
where 成绩>平均成绩
关联子查询：
select *
from score a
where 成绩=(
select avg(成绩)
from score b
where b.课程号=a.课程号
)
输出结果还是有所不同的，要注意！
8、窗口函数的移动平均
select *,avg(成绩) over (order by 学号 rows 2 preceding) as current_avg
from 班级表用了rows和preceding这两个关键字是之前-行的意思，也就是自身结果的之前两行的平均，一共三行平均。
由于这里可以通过preceding关键字调整作用范围，在以下场景中非常适用：
在公司业绩名单排名中，可以通过移动平均，直观地查看到与相邻名次业绩的平均、求和等统计数据。
9、总结
partition是可以省略的，省略就是不指定分组。
order by 加上去如果是用avg,sum这样的函数的话就是计算相邻的数据，所以如果遇到要每组数据大于平均数据的业务问题的话就不能加order by了，不然出来的平均数就不对了
窗口函数使用场景 1）经典top N问题
找出每个部门排名前N的员工进行奖励
2）经典排名问题
业务需求“在每组内排名”，比如：每个部门按业绩来排名
3）在每个组里比较的问题
比如查找每个组里大于平均值的数据，可以有两种方法：
方法1，使用前面窗口函数案例来实现
方法2，使用关联子查询