什么是窗口函数?
1、窗口函数有什么用?
在日常工作中,经常会遇到需要在每组内排名,比如以下业务需求:
排名问题:每个部门按业绩来排名
topN问题:找出每个部门排名前N的员工进行奖励
面对这类需求,就需要使用SQL的高级功能窗口函数了。
二.什么是窗口函数?
窗口函数,也叫OLAP函数(Online Analytical Processing,联机分析处理),可以对数据库数据进行实时分析处理。
窗口函数的基本语法如下:
‹窗口函数› over (partition by ‹用于分组的列名› order by ‹用于排序的列名›)
那么语法中的‹窗口函数›都有哪些呢?
‹窗口函数›的位置,可以放以下两种函数:
1) 专用窗口函数,比如rank、dense_rank、row_number等专用窗口函数。
2) 聚合函数,比如sum、avg、count、max、min等
因为窗口函数是对where或者group by子句处理后的结果进行操作,所以窗口函数原则上只能写在select子句中。
如何使用窗口函数?
1、专用窗口函数rank
例如下图,是班级表中的内容;
![a83749d3e362285050d65baeeddb8943.png](https://i-blog.csdnimg.cn/blog_migrate/0ec3a030171b969db1a3e226b0a8fce0.png)
如果我们想在每个班级内按成绩排名,得到如下的结果;
![614295f4cb974d832dce9fe1130704c4.png](https://i-blog.csdnimg.cn/blog_migrate/8a874807555d5905281357d9631813f3.jpeg)
以班级“1”为例,这个班级的成绩“95”排在第1位,这个班级的“83”排在第4位。上面这个结果确实按我们的要求在每个班级内按成绩排名了。
得到以上结果的SQL语句代码如下:
select *,
rank() over (partition by 班级
order by 成绩 desc) as ranking from class
![e508477e22c483d6b96f6b7e32d7a34d.png](https://i-blog.csdnimg.cn/blog_migrate/2493197df95a047d8a06eb4c31083691.jpeg)
1)每个班级内:按班级分组
partition by用来对表分组。在这个例子中,所以我们指定了按“班级”分组(partition by 班级)
2)按成绩排名
order by子句的功能是对分组后的结果进行排序,默认是按照升序(asc)排列。在本例中(order by 成绩 desc)是按成绩这一列排序,加了desc关键词表示降序排列。
通过下图,我们就可以理解partition by(分组)和order by(在组内排序)的作用了。
![ef58667595174410d1bb7311e4079b65.png](https://i-blog.csdnimg.cn/blog_migrate/285e605f7d52832eca75d9e8245398b5.jpeg)
PS:我们之前学过的group by子句分组的功能和order by子句排序的功能已经具备窗口函数的功能了。那么,为什么还要用窗口函数呢?
这是因为,group by分组汇总后改变了表的行数,一行只有一个类别。而partition by和rank函数不会减少原表中的行数。例如下面统计每个班级的人数。如下图:
![dba708b5c597735caa3359be0d94ea1e.png](https://i-blog.csdnimg.cn/blog_migrate/08075b633be409a777eb9ed539bf95dc.png)
相信通过这个例子,你已经明白了这个窗口函数的使用:
select *,
rank() over (partition by 班级
order by 成绩 desc) as ranking from class
现在我们说回来,为什么叫“窗口”函数呢?
这是因为partition by分组后的结果称为“窗口”,这里的窗口不是我们家里的门窗,而是表示“范围”的意思。
简单来说,窗口函数有以下功能:
1)同时具有分组和排序的功能
2)不减少原表的行数
3)语法如下:
‹窗口函数› over (partition by ‹用于分组的列名› order by ‹用于排序的列名›)
其他专用窗口函数
涉及到排名问题,可以使用窗口函数;
专用窗口函数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 class
得到结果如下:
![071634ac235acf3b6e687fd1111fc5cd.png](https://i-blog.csdnimg.cn/blog_migrate/e6db3b3f1d8e82a76f42ee7ea9ca5915.jpeg)
PS:返回输出的表列名不能和专用窗口函数同名,否则会报错;
![2e50a6b69e6ed1ff494e60d949d936b3.png](https://i-blog.csdnimg.cn/blog_migrate/59cccf71cfdf2ca0861a1ff484722903.jpeg)
从上面的结果可以看出:
rank函数:这个例子中是5位,5位,5位,8位,也就是如果有并列名次的行,会占用下一名次的位置。比如正常排名是1,2,3,4,但是现在前3名是并列的名次,结果是:1,1,1,4。
dense_rank函数:这个例子中是5位,5位,5位,6位,也就是如果有并列名次的行,不占用下一名次的位置。比如正常排名是1,2,3,4,但是现在前3名是并列的名次,结果是:1,1,1,2。
row_number函数:这个例子中是5位,6位,7位,8位,也就是不考虑并列名次的情况。比如前3名是并列的名次,排名是正常的1,2,3,4。
这三个函数的区别如下:
![d4b29f5ee9a32457f8530f215c35ee05.png](https://i-blog.csdnimg.cn/blog_migrate/ada14bc63687e4cb6463b9ac799534bd.png)
最后,需要强调的一点是:在上述的这三个专用窗口函数中,函数后面的括号不需要任何参数,保持()空着就可以了。
案例:面试经典排名问题
问题:按成绩来排名,如果两个分数相同,那么排名要是并列的;
根据题目要求的排名规则,这里我们使用dense_rank函数。
得到以下SQL语句:
select *,
dense_rank() over (order by 成绩 desc) as dese_rank from class;
![ac9e186f7c5d1900973ee30e12d3cc24.png](https://i-blog.csdnimg.cn/blog_migrate/aee09362533517a19728b4449b2621bf.jpeg)
【本题考点】
1、考察如何使用窗口函数来解决排名问题;2、专用窗口函数排名的区别:rank, dense_rank, row_number
案例:面试经典topN问题
工作中会经常遇到这样的业务问题:
如何找到每个类别下用户最喜欢的产品是哪个?
如果找到每个类别下用户点击最多的5个商品是什么?
这类问题其实就是常见的:分组取每组最大值、最小值,每组最大的N条(top N)记录。
面对该类问题,如何解决呢?
下面我们通过成绩表的例子来给出答案。
成绩表是学生的成绩,里面有学号,课程号(学生选修课程的课程号),成绩
![adf22d13f87ff0f2cf9815c674adea99.png](https://i-blog.csdnimg.cn/blog_migrate/9160e12da43cb3364ad03d734ffe1e1e.jpeg)
分组取每组最大值
案例:按课程号分组取成绩最大值所在行的数据
我们可以使用分组(group by)和汇总函数得到每个组里的一个值(最大值,最小值,平均值等)。但是无法得到成绩最大值所在行的数据。
select 课程号,max(成绩) as 最大成绩
from score
group by 课程号;
![a5f096c5ad377d987ff841933cdd2c80.png](https://i-blog.csdnimg.cn/blog_migrate/74875ad6936c76982e7ea6747f8cffc2.jpeg)
我们可以使用关联子查询来实现:
select *
from score as a
where 成绩 = ( select max(成绩) from score as b
where b.课程号 = a.课程号);
![c47a8cb3c18ca541d3a66cce4ae33bb8.png](https://i-blog.csdnimg.cn/blog_migrate/cc8bf5c01e59a20c2213f5f5f9bf9d8d.jpeg)
分组取每组最小值
案例:按课程号分组取成绩最小值所在行的数据
同样的使用关联子查询来实现
select *
from score as a
where 成绩 = (
select min(成绩)
from score as b
where b.课程号 = a.课程号);
![45cdaea491262c9d71efa51a0b867cf0.png](https://i-blog.csdnimg.cn/blog_migrate/6a01e674ea58c2ba8a2cfd4e2d278956.jpeg)
每组最大的N条记录
案例:现有“各科成绩表”,记录了每个学生各科的成绩。表内容如下:
问题:查找每个学生成绩最高的2个科目;
![f02bc05968bbe6dac274bda50835869d.png](https://i-blog.csdnimg.cn/blog_migrate/45b561173f814d24b73912240a4ecbd2.jpeg)
解题思路
1.看到问题中要查“每个”学生最高的成绩。还记得我们之前课程里讲过的吗?当有“每个”出现的时候,就要想到是要分组了。
这里是“每个学生”,结合表的结构,是按学生“姓名”来分组。
2.将表按学生姓名分组后,把成绩按降序排列,排在最前面的2个就是我们要找的“成绩最高的2个科目”。
3.现在分组后,需要排序,又不减少原表的行数,这种功能自然想到是窗口函数。
4.使用哪个专用窗口函数?
为了不受并列成绩的影响,使用row_number专用窗口函数:
row_number函数:也就是不考虑并列名次的情况。比如前3名是并列的名次,排名是正常的1,2,3,4。
解题步骤
步骤一:按姓名分组(partition by 姓名)、并按成绩降序排列(order by 成绩 desc),套入窗口函数的语法,就是下面的SQL语句:
select *, row_number() over (partition by 姓名 order by 成绩 desc) as ranking from 各科成绩表;
![d8df214e15ff855feb981404a42f3df6.png](https://i-blog.csdnimg.cn/blog_migrate/f1a2f029b69471ae2b31eca0b8d53d74.jpeg)
运行结果如下:
![8efa20bd3d5ac48bebe6f9ea0904d0b7.png](https://i-blog.csdnimg.cn/blog_migrate/08900eefea1b655dee6465a9593cb279.jpeg)
步骤二:如上表黄色框内的数据,每个同学成绩最好的2个科目,就是要求的解。
想得到这个解,只要提取出“ranking”值小于等于2的数据就可以了。那么,只需要在上一步的SQL语句里加入条件字句where就可以了
select *, row_number() over (partition by 姓名 order by 成绩 desc) as ranking from 各科成绩表 where ranking <=2;
很多同学都会用这样的思路解题,但是这样写,sql会报错,为什么呢?
我们在课程里多次强调过,要牢记sql的书写顺序和运行顺序。在运行顺序中,select字句是最后被运行的。
![cf3f6fcb28cf9b22603951e41d2cf795.png](https://i-blog.csdnimg.cn/blog_migrate/1759c41061f669666e85a8a203573ac2.jpeg)
当明白了运行顺序以后,就知道错误的原因了:运行到”where ranking › 2”的时候,因为select字句还没有被执行,因此select中的“ranking”列还没有出现,从而导致报错。
那么,解决方法是什么呢?
这种情况就可以用子查询,也就是把第一步得到查询结果作为一个新的表,SQL语句如下:
select * from (select *, row_number() over (partition by 姓名 order by 成绩 desc) as ranking from 各科成绩表) as a where ranking <= 2;
![55b0fc6b7babff95fb998ef653b9a2dd.png](https://i-blog.csdnimg.cn/blog_migrate/5fd868bd8eb22306844a649d9dd96adb.jpeg)
得到结果如下:
![3a108e76e0f89fc429261b7488e75e87.png](https://i-blog.csdnimg.cn/blog_migrate/753cfc834c19a0bebcdfe70048703e44.png)
PS:
1.主要考查对窗口函数的灵活使用。
2.在筛选过程中,非常容易因为子查询问题报错,本题也考察了对子查询的熟练运用。
3.本题间接考察了对sql语句执行顺序的熟悉程度。
topN问题SQL模板:
经典topN问题:每组最大的N条记录。这类问题涉及到“既要分组,又要排序”的情况,要能想到用窗口函数来实现。
本题的SQL语句修改下(将where字句里的条件修改成N),就可以成为这类问题的一个万能模板,遇到这类问题往里面套就可以了;
select * from (select *, row_number() over (partition by 要分组的列名 order by 要排序的列名 desc) as ranking from 表名) as a where ranking <= N;
聚合函数作为窗口函数
聚和窗口函数和上面提到的专用窗口函数用法完全相同,只需要把聚合函数写在窗口函数的位置即可,但是函数后面括号里面不能为空,需要指定聚合的列名。
我们来看一下窗口函数是聚合函数时,会出来什么结果:
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 class;
![70b571282faef6134175cc090db11a6f.png](https://i-blog.csdnimg.cn/blog_migrate/53a42b0575a1e6a8d1cc2ef213d09605.jpeg)
得到结果如下:
![c607e1e9b8d0b9b81120900b469e55fe.png](https://i-blog.csdnimg.cn/blog_migrate/0f74f187ac0e88bc945c744ce1f74beb.png)
以上结果有发现什么规律吗?我单独用sum举个例子:
![5be711985625c4213d95a018e0e18683.png](https://i-blog.csdnimg.cn/blog_migrate/056ca0d66dd777190eb2c61151e79c91.png)
如上图,聚合函数sum在窗口函数中,是对自身记录、及位于自身记录以上的数据进行求和的结果。比如0004号,在使用sum窗口函数后的结果,是对0001,0002,0003,0004号的成绩求和,若是0005号,则结果是0001号~0005号成绩的求和,以此类推。
不仅是sum求和,平均、计数、最大最小值,也是同理,都是针对自身记录、以及自身记录之上的所有数据进行计算,现在再结合刚才得到的结果,是不是理解起来容易多了。
比如0005号后面的聚合窗口函数结果是:学号0001~0005五人成绩的总和、平均、计数及最大最小值。
如果想要知道所有人成绩的总和、平均等聚合结果,看最后一行即可。
那么,这样使用窗口函数有什么用呢?
聚合函数作为窗口函数,可以在每一行的数据里直观的看到,截止到本行数据,统计数据是多少(最大值、最小值等)。同时可以看出每一行数据,对整体统计数据的影响。
案例:累计求和问题
题目:“薪水表”中记录了员工发放的薪水。包含雇员编号,薪水、起始日期、结束日期。
其中,薪水是指该雇员在起始日期到结束日期这段时间内的薪水。当前员工是指结束日期 = '9999-01-01'的员工。
问题:按照雇员编号升序排列,查找薪水的累计和(累计薪水)。其中累计薪水是前N个当前员工( 结束日期 = '9999-01-01')的薪水的累计和,其他以此类推。
![b99528f3bb8c0c6aed0df04787f003e8.png](https://i-blog.csdnimg.cn/blog_migrate/a8c4e1167d3812c57066e908a9de6ffc.jpeg)
解题步骤:
1、先筛选出当前员工( 结束日期 = '9999-01-01')的薪水
select 雇员编号,薪水
from 薪水表 where 结束日期 = '9999-01-01';
![7bd80a5653e9731398c92e3c1db01da5.png](https://i-blog.csdnimg.cn/blog_migrate/36c9ffe4c9e79a11df68b3178893ba65.jpeg)
查询结果如下图:
![6741d3711c38008c52fd4b8a53a48035.png](https://i-blog.csdnimg.cn/blog_migrate/226dce6d7262f8ef75309ff4c66b4bd3.jpeg)
2.什么是累计薪水?
由题意可以看出输出结果需要包含薪水和累计薪水。累计薪水是前N个当前员工的薪水的累计和得出。
举个例子,如下图:
![419bc0ffec55420f0e43673f5b42cb16.png](https://i-blog.csdnimg.cn/blog_migrate/118696f25f0ccf4028ff42bf27ce4a4b.jpeg)
第1行的累计薪水为雇员编号(10001)的薪水,
第2行的累计薪水为雇员编号(10001)、雇员编号(10002)的薪水之和,
第3行的累计薪水为雇员编号(10001)、雇员编号(10002)、雇员编号(10003)的薪水之和
依次类推...
3.如何计算出每行的累计薪水?
用聚合函数作为窗口函数,有累计的功能。因为本题是累计“求和”,所以用聚合函数sum。
select 雇员编号,薪水, sum(薪水) over (order by 雇员编号) as 累计薪水
from 薪水表 where 结束日期 = '9999-01-01';
![7923da0c053157dbecf2ddefe0259098.png](https://i-blog.csdnimg.cn/blog_migrate/753ee3deb1ec08d3d9d4ea0eefd91d59.jpeg)
查询结果如下图:
![6741d3711c38008c52fd4b8a53a48035.png](https://i-blog.csdnimg.cn/blog_migrate/226dce6d7262f8ef75309ff4c66b4bd3.jpeg)
PS:
对于“累计”问题,要想到用聚合函数作为窗口函数。比如累计求和,用sum函数。
sum(列名) over (order by ‹用于排序的列名›)
累计求平均值,用avg函数。
avg(列名) over (order by ‹用于排序的列名›)
所以,我们可以得出“累计求和”问题的万能模板为:
select 列1,列2, sum(列名) over (order by ‹用于排序的列名›) as 累计值的别名
from 表名;
举一反三
下表为确诊人数表,包含日期和该日期对应的新增确诊人数;
![a803ebe18cc5279d7fe364a84d99888c.png](https://i-blog.csdnimg.cn/blog_migrate/5b2ff4481fd39bd76bb7393ad6a21c73.jpeg)
按照日期进行升序排列,查找日期、确诊人数以及对应的累计确诊人数。
参考答案如下:
select 日期,确诊人数, sum(确诊人数) over (order by 日期) as 累计确诊人数
from 确诊人数表;
![ba33d6966e9b0c76d6c9c5545cc2708d.png](https://i-blog.csdnimg.cn/blog_migrate/b930f1dcba2c23cd7a73fc528f4ab5df.jpeg)
查询结果如下图:
![478a78af04c9a5f84b7cc64bf47fed71.png](https://i-blog.csdnimg.cn/blog_migrate/8b223baeac8a04a778d1a8231e2a65d7.jpeg)
案例:如何在每个组里比较?
题目:现有“成绩表”,记录了每个学生各科的成绩。表内容如下:
![445c357d8773e71b94f5ced4c1bd9e3a.png](https://i-blog.csdnimg.cn/blog_migrate/0d3b2dc756b8792c095985a359448a3e.png)
问题:查找单科成绩高于该科目平均成绩的学生名单
解题思路
1."查找单科成绩高于该科目平均成绩",也就是在“每个”科目里比较。还记得我们之前课程里讲过的吗?当有“每个”出现的时候,就要想到是要分组了。
能实现“分组”功能的sql有两种,一是group by字句,另一个是窗口函数的partition by。
2.使用聚合窗口函数(求平均值avg),将每门课的平均成绩求出以后,然后找出大于比平均成绩的数据。
这就要求分组后不能减少表的行数。
group by分组汇总后改变了表的行数,一行只有一个类别。而partition by和rank函数不会减少原表中的行数。
例如下面统计每个班级的人数:
![dba708b5c597735caa3359be0d94ea1e.png](https://i-blog.csdnimg.cn/blog_migrate/08075b633be409a777eb9ed539bf95dc.png)
所以这里我们使用窗口函数的partition by;
解题步骤
第1步,聚合函数avg()作为窗口函数,将每一科目成绩的平均值求出。
SQL语句如下:
select *,avg(成绩) over (partition by 科目) as avg_score
from 各科成绩表;
运行结果如下图:
![d0611640f503f1d0c7f1229fdcb986c6.png](https://i-blog.csdnimg.cn/blog_migrate/69f2f20ae2d748960e9438f0517bb662.jpeg)
第2步,如上表,按科目分组后各科目的平均分已经计算出,接下来只要筛选出成绩大于平均分的数据即可。
那么,只需要在上一步的SQL语句里加入条件字句where就可以了
select *,avg(成绩) over (partition by 科目) as avg_score
from 各科成绩表 where 成绩 › avg_score;
很多同学都会用这样的思路解题,但是这样写,SQL会报错,为什么呢?
我们在课程里多次强调过,要牢记SQL的书写顺序和运行顺序。在运行顺序中,select字句是最后被运行的。
![7a0c160581336c502ce1788b8486944f.png](https://i-blog.csdnimg.cn/blog_migrate/8eac54c6d67e88bb86a2e387f3da00c5.jpeg)
当明白了运行顺序以后,就知道错误的原因了:运行到”where 成绩 > avg_score”的时候,因为select字句还没有被执行,因此select中的“avg_score”列还没有出现,从而导致报错。
解决方法是什么呢?
这种情况就可以用子查询,也就是把第一步得到查询结果作为一个新的表,sql语句如下:
select * from (select *,avg(成绩) over (partition by 科目) as avg_score
from 各科成绩表) as b where 成绩 > avg_score;
![946d139e7ef980a9b4164f18679715f2.png](https://i-blog.csdnimg.cn/blog_migrate/16317e5d5b5fcfba39c2ee6c7f51b0c0.jpeg)
运行结果如下图:
![0013dd4feed5e351398a89ed49cc86aa.png](https://i-blog.csdnimg.cn/blog_migrate/f0b03878c89540fa59e6113cf3102514.jpeg)
PS:
1.主要考查对窗口函数的灵活使用。
2.在筛选过程中,非常容易因为子查询问题报错,本题也考察了对子查询的熟练运用。
3.本题间接考察了对sql语句执行顺序的熟悉程度。
举一反三
查找每个组里大于平均值的数据,可以有两种方法:
1)使用上面讲到的窗口函数来实现
2)使用关联子查询,可以回顾《从零学会sql:复杂查询》里的案例
![e67fea866c242ae90653be5a105da7ea.png](https://i-blog.csdnimg.cn/blog_migrate/0b76bc25de6130993df80001c510e172.jpeg)
窗口函数的移动平均
这里我们直接用聚合函数avg的窗口函数举例说明:
select *,avg(成绩) over (order by 学号 rows 2 preceding) as current_avg
from class;
![6daac1b788bbc70e0025d0e363a0e6a8.png](https://i-blog.csdnimg.cn/blog_migrate/6125e37b5ac9910389dcc5700f445274.jpeg)
得到结果如下图:
![bb361c3266466b5473363bae292744e5.png](https://i-blog.csdnimg.cn/blog_migrate/137c0c9a4806b1dadc6ce9216c171447.png)
仔细看上面的窗口函数中,用了rows和preceding这两个关键字,是“之前~行”的意思,上面的句子中,是之前2行。也就是得到的结果是自身记录及与前2行(一共3行)的平均值。
例如:学号0004学生的current_avg,是自己和前2位同学的平均,即0002,0003,0004三位同学成绩的平均,其他数据的情况也一样,下图非常直观的可以看到计算过程:
![9b3f1e5bcce2989e20d2b74cf88b8e8a.png](https://i-blog.csdnimg.cn/blog_migrate/4a81cfe8756e680e8acb5271944d9f68.jpeg)
每一行得到的结果,都是当前行和前面2行的平均(共3行)。想要计算当前行与前n行(共n+1行)的平均时,只要调整rows…preceding中间的数字即可。
这里需要注意:在移动平均中,被选出的数据构成一个“框架”,例如,刚才例子中的0002、0003、0004行数据,就是一个“框架”。
这样使用窗口函数有什么用呢?
由于这里可以通过preceding关键字调整作用范围,在以下场景中非常适用:
在公司业绩名单排名中,可以通过移动平均,直观地查看到与相邻名次业绩的平均、求和等统计数据。
总结
PS:
partition子句可以省略,省略就是不指定分组,结果如下,只是按成绩由高到低进行了排序:
select *,rank() over (order by 成绩 desc) as ranking
from class;
得到结果如下图:
![f6cf311e4d9226dcfed8a40747162cb4.png](https://i-blog.csdnimg.cn/blog_migrate/21acc9505c017d0a604473df26ba66ac.jpeg)
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:使用关联子查询
![7aeb7c2965d258ee5460516eea1dc0d8.png](https://i-blog.csdnimg.cn/blog_migrate/c9c4fd8f8b5d8644d9425d78e72f2dbb.jpeg)
总结
如果此篇文章有帮助到您, 希望打大佬们能
关注
、点赞
、收藏
、评论
支持一波,非常感谢大家!
如果有不对的地方请指正!!!