整体内容的思维导图:
![1e6675963db524cc906320d3fcfa030e.png](https://i-blog.csdnimg.cn/blog_migrate/f2904aea44a9104b89a919dfe9e141fe.jpeg)
一、定义
窗口函数也称为OLAP函数(OLAP是online analytical processing,联机分析处理的简称),意思是对数据库进行实时分析处理。
Q;为什么叫“窗口”函数?
A:因为partition by分组后的结果称为“窗口”,表示“范围”的意思。
二、基本语法
![341d4eb314260f5dc71d4bdaaa27eafc.png](https://i-blog.csdnimg.cn/blog_migrate/9ab86c68f4829de47fe822ec78de493b.png)
注意:
- 窗口函数对where或者group by子句处理的结果进行分析处理,所以原则上只能写在select子句中。
- partition子句可以省略,省略就是不指定分组。
三、功能
- 同时具有分组(partition by)和排序(order by)的功能
- 不减少原表的行数,所以经常用在组内排名
Q:为什么用窗口函数分组而不用group by?
A:因为group by分组汇总后改变了表的行数,一行只有一个类型;而partition by和rank函数不会减少原表的行数。举个例子方便理解,例如下面统计每个班级的人数:
![6cfd6991aed871cd1674c5801c8dc170.png](https://i-blog.csdnimg.cn/blog_migrate/e8f9bbac34324690cbae3cf0799fdc87.png)
四、类型
<窗口函数>的位置,可以放以下两种函数:
- 专用窗口函数,包括rank,dense_rank,row_number等
- 聚合窗口函数,包括sum,avg,count,max,min等
1.专用窗口函数
- 包括:rank,dense_rank,row_number等
- 注意:a.这三个专用窗口函数后面的括号不需要任何参数,保持()空着就行。b.涉及到排名的问题,都可以使用窗口函数来解决(经典排名问题和经典topN问题)
(1) rank函数
例子:在每个班级内按成绩排名
![75f7e487c5aed3c9678352b543ed492a.png](https://i-blog.csdnimg.cn/blog_migrate/80970db3d9e52dbbe43f47e4ebee3d9b.png)
解题代码:
-- 在每个班级内按成绩排名
分组排序的计算过程:
![85a2376e24ae038e8bfbd70c850b2858.png](https://i-blog.csdnimg.cn/blog_migrate/7f2f3516381452ae4f58bac8e1acda38.png)
(2) 三个函数的区别——经典排序问题
- rank函数 如果有并列名次的行,会占用下一名次的位置
- dense_rank函数 如果有并列名次的行,不占用下一名次的位置
- row_number函数 不考虑并列名次的情况
![b5c9b0bff5feccf5bd4dde11754b430e.png](https://i-blog.csdnimg.cn/blog_migrate/38ff237a57b5004f111d6e0cb9ee3bb0.png)
(3) 经典top N问题
- 释义:每组的最大的N条记录。这类问题设计到“既要分组,又要排序 ”的情况,要能想到用窗口函数来实现。(下面用例子的解题步骤来解释万能模板的结构)
- 万能模板:
![73cf4892cd62e17e27e111162d99e7b3.png](https://i-blog.csdnimg.cn/blog_migrate/0993230863a5053423608e2089cf16f6.png)
- 例子:查找每个学生成绩最高的2个科目
![0ef277954fb05df1df4bd1f7151dcc2c.png](https://i-blog.csdnimg.cn/blog_migrate/3b6f712fe82f3d9a1837867e1999e48a.jpeg)
解题步骤:
- 套入窗口函数的语法,按姓名分组(partition by 姓名)、并按成绩降序排列(order by 成绩 desc),得到每个学生的所有成绩排名情况。
- 用where子句提取出“排名”值小于等于2的数据。但是会报错,因为where子句先于select子句运行,where得不到“排名”的数据。
- 用子查询解决。使用子查询语句,把(1)得到的查询结果作为一个新的表,然后用(2)中的where子句。
注意:别忘了子查询最后要给新表命名
代码:
-- 查找每个学生成绩最高的2个科目
2.聚合函数
- 包括:sum,avg,count,max,min等。
- 用法:和专用窗口函数完全相同,只需把聚合函数写在窗口函数的位置即可。
- 注意:函数后面的()不能为空。需要指定聚合的列名。
- 作用:可以在每一行的数据里直观地看到,截止到本行数据,统计数据是多少(最大值最小 值等)。同时可以看出每一行数据,对整体统计数据的影响。
- 计算过程:求和、平均、计数、最大最小值,都是针对自身记录、以及自身记录之上的所有数据进行计算的结果。 以sum作为窗口函数进行原理分析:
![fb7eb8bf670e7127029bffa593fdac9e.png](https://i-blog.csdnimg.cn/blog_migrate/8551648e9f780b0adafa6abc912d0d32.png)
- 例子:查询每个组里大于平均值的数据,可以有两种方法:窗口函数和关联子查询
代码:
-- 查询每个组里大于平均值的数据
查询结果:
![3476498665f61b24fe74a25f4981e0f8.png](https://i-blog.csdnimg.cn/blog_migrate/f16da0e5d2f2994bc35b878988d961c9.png)
3.窗口函数的移动平均
- 使用:rows ... preceding ...内容是数字
- 作用:调整窗口函数的作用范围,范围是当前行与前...行之间。在以下场景中非常适用:在公司业绩名单排名中,可以通过移动平均。直观地查看与相邻名次业绩的平均、求和等统计数据。
- 例子:计算当前行和上两行,3位同学的平均成绩
代码:
-- 计算当前行和上两行,3位同学的平均成绩
计算过程:上面的句子中rows 2 preceding代表“之前2行”,也就是得到的结果是自身记录及前2行的平均。例如学号0004学生的current_avg,是自己和前两位同学的平均,即0002,0003,0004三位同学成绩的平均,其他数据也一样
![3fe51f77246e87ad899bf193a4dbc4ba.png](https://i-blog.csdnimg.cn/blog_migrate/545b6d227e63928b3078464279d61925.png)
查询结果:
![7202d0710ce254d608d6451659827d73.png](https://i-blog.csdnimg.cn/blog_migrate/c837c2a0e1d49d184008e385403107f3.png)
五、使用场景
- 经典排名问题 业务需求“在组内排名”,比如:每个部门按业绩来排名
- 经典topN问题 找出每个部门排名前N的员工进行奖励(有万能模板)
- 在每个组里比较的问题 比如查找每个组里大于平均值的数据,可以有两种方法:①使用窗口函数 ②使用关联子查询