01 什么是窗口函数
一 窗口函数的作用
在日常工作中，经常会遇到需要在每组内排名，比如下面的业务需求：
排名问题：每个部门按业绩来排名
topN问题：找出每个部门排名前N的员工进行奖励
面对这类需求，就需要使用sql的高级功能窗口函数了。
二 什么是窗口函数
窗口函数，也叫OLAP(Online Anallytical Processing,联机分析处理）,可以对数据库数据进行实时分析处理。
窗口函数的基本语法如下：
<窗口函数> over (partition by <用于分组的列名> order by <用于排序的列名>)
语法中的窗口函数有：
1）专用窗口函数,如 rank, dense_rank, row_number 等。
2）聚合函数，如sum,avg,count,max,min等
因为窗口函数是对where 或者group by 子句处理后的结果进行操作，所以窗口函数原则上只能写在select子句中。
02 如何使用窗口函数
1 专用窗口函数rank
班级表-每个班级内按成绩排名
select *,rank() over(PARTITION by 班级 order by 成绩) as ranking from 班级表;
这样操作后都是按班级分组，按成绩排序，并且多了一个ranking列。
窗口函数具备了我们之前学过的group by子句分组的功能和order by 子句排序的功能，那么，为什么还要用窗口函数呢，这是因为，group by分组后改变了表的行数，一行只有一个类别，二partition by 和rank函数不会减少表中的行数。例如下面统计每个班级的人数。
group by 分组汇总改变行数
select 班级,count(学号) from 班级表 GROUP BY 班级 order by 班级;
partition by 分组汇总行数不变
select 班级,count(学号) over(partition by 班级 order by 班级) AS CURRENT_count from 班级表;
现在说回来，为什么叫“窗口”函数呢？这是因为partition by 分组后的结果称为“窗口”，
这里的窗口不是我们家的门窗，而是表示“范围”的意思。
简单来说，窗口函数的功能：
1）同时具有分组和排序的功能
2）不减少原表的行数
3）语法是
<窗口函数> over (partition by <用于分组的列名> order by <用于排序的列名>)
03 其他专用窗口函数
专用窗口函数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 班级表;
这三个函数的区别如下：
上述这三个专用窗口函数，函数后面的括号不需要任何参数，保持（）空着就可以。
04 案例：面试经典排名问题
1）班级表按成绩来排名，如果两个分数相同，那么排名要是并列的。正常排名是1,2,3,4，
但是现在前3名是并列的名次，排名结果是：1,1,1,2。
select *,dense_rank() over(order by 成绩 desc )as dese_rank from 班级表;
本题考点：考察如何使用窗口函数 2.专用窗口函数排名的区别：rank,dense_rank,
row_number。
2）涉及到排名的问题，都可以使用窗口函数来解决。记住rank,dense_rank,
row_number排名的区别。
编写一个sql查询来实现分数排名。如果两个分数相同，则两个分数（rank）相同。请注意，
平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有间隔。
select score, dense_rank() over(order by Score desc)as Ranking from Scores;
05 案例：面试经典topN问题
工作中会经常遇到这样的业务问题：如何找到每个类别下用户最喜欢的产品是哪个？
如何找到每个类别下用户点击最多的5个商品。
这类问题其实就是常见的：分组取每组最大值，最小值，每组最大的N条（top N）记录。
1）分组取每组最大值（按课程号分组取成绩最大值所在行的数据）
用关联子查询实现
select * from score as a where 成绩=(select max(成绩) from score as b WHERE b.课程编号=a.课程编号);
2）分组取每组最小值（按课程号分组取成绩最小值所在行的数据）
select * from score as a where 成绩=(select min(成绩) from score as b WHERE b.课程编号=a.课程编号);
3）每组最大的N条记录（以下是成绩表，查找每个学生成绩最高的2个科目）
SELECT * from (SELECT *, row_number() over(partition by 姓名 order by 成绩 desc)as ranking from 成绩表)
as a where ranking<=2;
这里的as a 是不能省略的，要注意。 用子查询的原因是，如果不用子查询，where字句里的
ranking是不能识别的。
经典topN问题：每组最大的N条记录。这类问题涉及到既要分组，又要排序的情况，要能想到用窗口函数来实现。
这类题的万能模板
# topN问题 sql摸版 select * from
(select *,row_number()over(partition by 要分组的别名 order by 要排序的列名 desc)as ranking
from 表名）as a where ranking<=N;
关联子查询的运用仅在最大最小的情况下可以用，应该是这类问题特例的运用，涉及到大于2的记录，就必须用窗口函数了。
04 聚合函数作为窗口函数
聚合函数和专用窗口函数用法相同，但函数后面括号里不能为空，需要指定聚合的列名。
例子
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 班级表;
可以看出，聚合函数，可以在每一行的数据里直观的看到，截止到本行数据，统计数据是多少（最大值，最小值等）。同时可以看出每一行数据，对整体统计数据的影响。
05 案例：如何在每个组里比较（查找单科成绩高于该科目平均成绩的学生名单）
select * from
(SELECT *, avg(成绩) over(partition by 科目) as avg_score from 成绩表)
as b where 成绩>avg_score;
查找每个组里大于平均值得数据，可以有2种方法：
1）使用上面讲到的窗口函数来实现
2） 使用关联子查询。
select * from 成绩表 as a where 成绩>(select avg(成绩) from 成绩表 as b WHERE b.科目=a.科目);
05 窗口函数的移动平均
select *, avg(成绩) over (order by 学号 rows 2 preceding)as current_avg from 班级表;
用了rows和preceding 这两个关键字。
这样使用有什么用呢？
在公司业绩名单排名中，可以通过移动平均，直观的查看到与相邻名次业绩的平均,求和等统计数据。
06 总结
一 注意事项
patition子句可以省略，省略就是不指定分组，结果如下，只是按成绩由高到低进行了排序
select *,rank() over(order by 成绩 desc )as ranking from 班级表
二 总结
窗口函数语法
<窗口函数> over (partition by <用于分组的列名> order by <用于排序的列名>)
语法中的窗口函数有：
1）专用窗口函数,如 rank, dense_rank, row_number 等。
2）聚合函数，如sum,avg,count,max,min等
窗口函数有以下功能：
1）同时具有分组（partiton by）和排序（order by） 的功能
2）不减少原表的行数，所有经常用来在每组内排名
3 注意事项
窗口函数原则上只能写在select 子句中
4 窗口函数使用场景
1）经典top N问题
找出每个部门排名前N的员工进行奖励
2）经典排名问题
业务需求“在每组内排名”，比如：每个部门按业绩来排名
3）在每个组里比较的问题
比如查找每个组里大于平均值得数据，可以有2种方法：
方法1，使用前面窗口函数案例来实现
方法2，使用关联子查询
