[SQL] 经典topN问题：每组最大的N条记录

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

基本语法：

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

‹窗口函数›类型：
1） 专用窗口函数，包括后面要讲到的rank, dense_rank, row_number等专用窗口函数。
- rank： 11346668
- dense_rank：111234555556
- row_number：123456789
2） 聚合函数，如sum. avg, count, max, min等

窗口函数解决哪类问题
排名问题：每个部门按业绩来排名
topN问题：找出每个部门排名前N的员工

窗口函数的作用
1）同时具有分组和排序的功能
2）不减少原表的行数

窗口函数注意事项
1）因为窗口函数是对where或者group by子句处理后的结果进行操作，所以窗口函数原则上只能写在select子句中。
2）partition by 可以省略
3）order by可以省略
4）（2）和（3）不能同时发生。
5）窗口函数直接带有空括号就可以

经典topN问题：
每组最大的N条记录。这类问题涉及到“既要分组，又要排序”的情况，要能想到用窗口函数来实现：

--【TOPN万能模板】
select * from 
(select *, row_number() over (partition by 要分组的列名 order by 要排序的列名 desc) as ranking 
from 表名)  as a  
where ranking<=N;

案例1：按课程号分组取成绩最大值所在行的数据（表格式：学号 课程 成绩）

--方法一：关联子查询
select *
from score t1
where 成绩=(select max(c.成绩)
		from score t2
		where c1.学号=c2.学号);

--方法二：窗口函数
select * 
from
(select *,
		rank() over (partition by 课程号 order by 成绩 desc) as rank
from score) t 
where rank=1;

案例2：按课程号分组取成绩最小值所在行的数据

--方法一：关联子查询
select *
from score t1
where 成绩=(select min(c.成绩)
		from score t2
		where c1.学号=c2.学号);

--方法二：窗口函数
select * 
from
(select *,
		rank() over (partition by 课程号 order by 成绩) as rank
from score) t 
where rank=1;

案例3：查找单科成绩高于该科目平均成绩的学生名单

--关联子查询
select * from score t1
where 成绩>
(select avg(成绩) as avg_score)
from score t2
where t1.课程号=t2.课程号);

--方法二 窗口函数
select *
from
(select *, avg(成绩) over (partition by 课程号) as avg_score from score) t
where 成绩>avg_score;

案例4：查找每个学生成绩最高的2个科目

--窗口函数
select * 
from
(select *,
		row_number() over (partition by 学号 order by 成绩 desc) as rank
from score) t 
where rank<=2;

案例6：最近在统计业务数据的时候，需要实现这么一个功能：统计母商品下对应的子商品的点击次数，并取出每个母商品下对应的点击次数top100的子商品。

select parent_item, item, item_click_rank
from 
(
select parent_item, 
	item, 
	row_number() over (partition by parent_item order by click desc) as item_click_rank
from table
where day = '2020-04-04'
) a
where item_click_rank<=100;

注：partition by可以同时作用于多个字段，比如partition by day, parent_item，表示同时对日期和母商品进行分组。