SQL高级功能-窗口函数

一、窗口函数用来干嘛？

在日常工作中，经常会遇到需要在每组内排名，比如下面的业务需求：

排名问题：每个部门按业绩来排名
topN问题：找出每个部门排名前N的员工进行奖励

二、怎么用窗口函数

窗口函数，也叫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子句中。

三、具体案例

1.专用窗口函数rank

select *,
   rank() over (partition by 班级
                 order by 成绩 desc) as ranking
from 班级表

得到如下结果

1）每个班级内：按班级分组
partition by用来对表分组。
2）按成绩排名
order by子句的功能是对分组后的结果进行排序，
通过下图，我们就可以理解partiition by（分组）和order by（在组内排序）的作用了。

窗口函数具备了我们之前学过的group by子句分组的功能和order by子句排序的功能。那么，为什么还要用窗口函数呢？

简单来说，窗口函数有以下功能：

1）同时具有分组和排序的功能

2）不减少原表的行数

3）语法如下：

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

2.其他专业窗口函数
专用窗口函数rank, dense_rank, row_number有什么区别呢？
它们的区别我举个例子，你们一下就能看懂：

select *,   
rank() over (order by 成绩 desc) as ranking,   
dense_rank() over (order by 成绩 desc) as dese_rank,   
row_number() over (order by 成绩 desc) as row_num
from 班级表

得到结果：

这都是对表格进行排序的函数

区别如下：

 

最后，需要强调的一点是：在上述的这三个专用窗口函数中，函数后面的括号不需要任何参数，保持()空着就可以。

3.聚合窗口函数

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 班级表

如果排序字段学号有重复时，则对当前的重复值进行聚合函数，但同时也是计算针对自身记录、以及自身记录之上的所有数据进行计算，现在再结合刚才得到的结果（下图）

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 score

4.经典TOP N 问题

select 学号 ,avg(成绩),
row_number () over( order by avg(成绩) desc)
from score
group by 学号  ;

每次提到sql的执行顺序，想想不是很简单嘛，这些都需要深入理解，对计算的AVG（成绩）使用窗口函数，这里二手QQ出售地图实际上就考验面试者对sql的执行顺序的理解

SELECT a.课程号,a.学号 ,b.姓名,a.成绩 ,a.排名
from
(select 课程号,学号,成绩,
   row_number() over (PARTITION by 课程号 order by 成绩 desc) as 排名
from score) as a
INNER JOIN
student as b
on a.学号=B.学号
WHERE a.排名<3                                                                                                                                                   

 

5.在每个组里进行比较

问题：查找单科成绩高于该科目平均成绩的学生名单

用窗口函数方法

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.课程号
)

6、窗口函数的移动做法

用了rows和preceding这两个关键字是之前-行的意思，也就是自身结果的之前两行的平均，一共三行做聚合函数。我感觉这是窗口函数的一种骚操作

select *,avg(成绩) over (order by 学号 rows 2 preceding) as current_avg 
from score

select *,sum(成绩) over (order by 学号 rows 2 preceding) as current_sum 
from score

四.总结

1.窗口函数语法

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

<窗口函数>的位置，可以放以下两种函数：

1） 专用窗口函数，比如rank, dense_rank, row_number等
2）聚合函数，如sum. avg, count, max, min等

2.窗口函数有以下功能：
1）同时具有分组（partition by）和排序（order by）的功能
2）不减少原表的行数，所以经常用来在每组内排名

3.注意事项
窗口函数原则上只能写在select子句中

4.窗口函数使用场景

1）经典top N问题

找出每个部门排名前N的员工进行奖励

2）经典排名问题

业务需求“在每组内排名”，比如：每个部门按业绩来排名

3）在每个组里比较的问题

比如查找每个组里大于平均值的数据，可以有两种方法：

方法1，使用前面窗口函数案例来实现

方法2，使用关联子查询