绑定完请刷新页面
取消
刷新

分享好友

×
取消 复制
SQL高级功能-窗口函数
2020-07-06 00:31:25

一、窗口函数用来干嘛?

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

排名问题:每个部门按业绩来排名
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(成绩)使用窗口函数,这里实际上就考验面试者对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,使用关联子查询

分享好友

分享这个小栈给你的朋友们,一起进步吧。

SQL Server专区
创建时间:2020-05-14 14:17:02
SQL Server 是Microsoft 公司推出的关系型数据库管理系统
展开
订阅须知

• 所有用户可根据关注领域订阅专区或所有专区

• 付费订阅:虚拟交易,一经交易不退款;若特殊情况,可3日内客服咨询

• 专区发布评论属默认订阅所评论专区(除付费小栈外)

技术专家

查看更多
  • 小雨滴
    专家
戳我,来吐槽~