sql over函数_从零学会SQL：高级功能

---

什么是窗口函数？

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

例如下图，是班级表中的内容；

如果我们想在每个班级内按成绩排名，得到如下的结果；

以班级“1”为例，这个班级的成绩“95”排在第1位，这个班级的“83”排在第4位。上面这个结果确实按我们的要求在每个班级内按成绩排名了。

得到以上结果的SQL语句代码如下：

select *,

rank() over (partition by 班级

order by 成绩 desc) as ranking from class

1）每个班级内：按班级分组

partition by用来对表分组。在这个例子中，所以我们指定了按“班级”分组（partition by 班级）

2）按成绩排名

order by子句的功能是对分组后的结果进行排序，默认是按照升序（asc）排列。在本例中（order by 成绩 desc）是按成绩这一列排序，加了desc关键词表示降序排列。

通过下图，我们就可以理解partition by（分组）和order by（在组内排序）的作用了。

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

这是因为，group by分组汇总后改变了表的行数，一行只有一个类别。而partition by和rank函数不会减少原表中的行数。例如下面统计每个班级的人数。如下图：

相信通过这个例子，你已经明白了这个窗口函数的使用：

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

得到结果如下：

PS：返回输出的表列名不能和专用窗口函数同名，否则会报错；

从上面的结果可以看出：

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。

这三个函数的区别如下：

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

案例：面试经典排名问题

问题：按成绩来排名，如果两个分数相同，那么排名要是并列的；

根据题目要求的排名规则，这里我们使用dense_rank函数。

得到以下SQL语句：

select *,

dense_rank() over (order by 成绩 desc) as dese_rank from class;

【本题考点】

1、考察如何使用窗口函数来解决排名问题；2、专用窗口函数排名的区别：rank, dense_rank, row_number

案例：面试经典topN问题

工作中会经常遇到这样的业务问题：

如何找到每个类别下用户最喜欢的产品是哪个？

如果找到每个类别下用户点击最多的5个商品是什么？

这类问题其实就是常见的：分组取每组最大值、最小值，每组最大的N条（top N）记录。

面对该类问题，如何解决呢？

下面我们通过成绩表的例子来给出答案。

成绩表是学生的成绩，里面有学号，课程号（学生选修课程的课程号），成绩

分组取每组最大值

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

我们可以使用分组（group by）和汇总函数得到每个组里的一个值（最大值，最小值，平均值等）。但是无法得到成绩最大值所在行的数据。

select 课程号,max(成绩) as 最大成绩

from score

group by 课程号;

我们可以使用关联子查询来实现：

select *

from score as a

where 成绩 = ( select max(成绩) from score as b

where b.课程号 = a.课程号);

分组取每组最小值

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

同样的使用关联子查询来实现

select *

from score as a

where 成绩 = (

select min(成绩)

from score as b

where b.课程号 = a.课程号);

每组最大的N条记录

案例：现有“各科成绩表”，记录了每个学生各科的成绩。表内容如下：

问题：查找每个学生成绩最高的2个科目；

解题思路

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 各科成绩表;

运行结果如下：

步骤二：如上表黄色框内的数据，每个同学成绩最好的2个科目，就是要求的解。

想得到这个解，只要提取出“ranking”值小于等于2的数据就可以了。那么，只需要在上一步的SQL语句里加入条件字句where就可以了

select *, row_number() over (partition by 姓名 order by 成绩 desc) as ranking from 各科成绩表 where ranking <=2；

很多同学都会用这样的思路解题，但是这样写，sql会报错，为什么呢？

我们在课程里多次强调过，要牢记sql的书写顺序和运行顺序。在运行顺序中，select字句是最后被运行的。

当明白了运行顺序以后，就知道错误的原因了：运行到”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;

得到结果如下：

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;

得到结果如下：

以上结果有发现什么规律吗？我单独用sum举个例子：

如上图，聚合函数sum在窗口函数中，是对自身记录、及位于自身记录以上的数据进行求和的结果。比如0004号，在使用sum窗口函数后的结果，是对0001，0002，0003，0004号的成绩求和，若是0005号，则结果是0001号~0005号成绩的求和，以此类推。

不仅是sum求和，平均、计数、最大最小值，也是同理，都是针对自身记录、以及自身记录之上的所有数据进行计算，现在再结合刚才得到的结果，是不是理解起来容易多了。

比如0005号后面的聚合窗口函数结果是：学号0001~0005五人成绩的总和、平均、计数及最大最小值。

如果想要知道所有人成绩的总和、平均等聚合结果，看最后一行即可。

那么，这样使用窗口函数有什么用呢？

聚合函数作为窗口函数，可以在每一行的数据里直观的看到，截止到本行数据，统计数据是多少（最大值、最小值等）。同时可以看出每一行数据，对整体统计数据的影响。

案例：累计求和问题

题目：“薪水表”中记录了员工发放的薪水。包含雇员编号，薪水、起始日期、结束日期。

其中，薪水是指该雇员在起始日期到结束日期这段时间内的薪水。当前员工是指结束日期 = '9999-01-01'的员工。

问题：按照雇员编号升序排列，查找薪水的累计和（累计薪水）。其中累计薪水是前N个当前员工( 结束日期 = '9999-01-01')的薪水的累计和，其他以此类推。

解题步骤：

1、先筛选出当前员工( 结束日期 = '9999-01-01')的薪水

select 雇员编号,薪水

from 薪水表 where 结束日期 = '9999-01-01';

查询结果如下图：

2.什么是累计薪水？

由题意可以看出输出结果需要包含薪水和累计薪水。累计薪水是前N个当前员工的薪水的累计和得出。

举个例子，如下图：

第1行的累计薪水为雇员编号（10001）的薪水，

第2行的累计薪水为雇员编号（10001）、雇员编号（10002）的薪水之和，

第3行的累计薪水为雇员编号（10001）、雇员编号（10002）、雇员编号（10003）的薪水之和

依次类推...

3.如何计算出每行的累计薪水？

用聚合函数作为窗口函数，有累计的功能。因为本题是累计“求和”，所以用聚合函数sum。

select 雇员编号,薪水, sum(薪水) over (order by 雇员编号) as 累计薪水

from 薪水表 where 结束日期 = '9999-01-01';

查询结果如下图：

PS：

对于“累计”问题，要想到用聚合函数作为窗口函数。比如累计求和，用sum函数。

sum(列名) over (order by ‹用于排序的列名›)

累计求平均值，用avg函数。

avg(列名) over (order by ‹用于排序的列名›)

所以，我们可以得出“累计求和”问题的万能模板为：

select 列1,列2, sum(列名) over (order by ‹用于排序的列名›) as 累计值的别名

from 表名;

举一反三

下表为确诊人数表，包含日期和该日期对应的新增确诊人数；

按照日期进行升序排列，查找日期、确诊人数以及对应的累计确诊人数。

参考答案如下：

select 日期,确诊人数, sum(确诊人数) over (order by 日期) as 累计确诊人数

from 确诊人数表;

查询结果如下图：

案例：如何在每个组里比较？

题目：现有“成绩表”，记录了每个学生各科的成绩。表内容如下：

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

解题思路

1."查找单科成绩高于该科目平均成绩"，也就是在“每个”科目里比较。还记得我们之前课程里讲过的吗？当有“每个”出现的时候，就要想到是要分组了。

能实现“分组”功能的sql有两种，一是group by字句，另一个是窗口函数的partition by。

2.使用聚合窗口函数(求平均值avg)，将每门课的平均成绩求出以后，然后找出大于比平均成绩的数据。

这就要求分组后不能减少表的行数。

group by分组汇总后改变了表的行数，一行只有一个类别。而partition by和rank函数不会减少原表中的行数。

例如下面统计每个班级的人数：

所以这里我们使用窗口函数的partition by；

解题步骤

第1步，聚合函数avg()作为窗口函数，将每一科目成绩的平均值求出。

SQL语句如下：

select *,avg(成绩) over (partition by 科目) as avg_score

from 各科成绩表;

运行结果如下图：

第2步，如上表，按科目分组后各科目的平均分已经计算出，接下来只要筛选出成绩大于平均分的数据即可。

那么，只需要在上一步的SQL语句里加入条件字句where就可以了

select *,avg(成绩) over (partition by 科目) as avg_score

from 各科成绩表 where 成绩 › avg_score;

很多同学都会用这样的思路解题，但是这样写，SQL会报错，为什么呢？

我们在课程里多次强调过，要牢记SQL的书写顺序和运行顺序。在运行顺序中，select字句是最后被运行的。

当明白了运行顺序以后，就知道错误的原因了：运行到”where 成绩 > avg_score”的时候，因为select字句还没有被执行，因此select中的“avg_score”列还没有出现，从而导致报错。

解决方法是什么呢？

这种情况就可以用子查询，也就是把第一步得到查询结果作为一个新的表，sql语句如下：

select * from (select *,avg(成绩) over (partition by 科目) as avg_score

from 各科成绩表) as b where 成绩 > avg_score;

运行结果如下图：

PS：

1.主要考查对窗口函数的灵活使用。

2.在筛选过程中，非常容易因为子查询问题报错，本题也考察了对子查询的熟练运用。

3.本题间接考察了对sql语句执行顺序的熟悉程度。

举一反三

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

1）使用上面讲到的窗口函数来实现

2）使用关联子查询，可以回顾《从零学会sql：复杂查询》里的案例

窗口函数的移动平均

这里我们直接用聚合函数avg的窗口函数举例说明：

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

from class;

得到结果如下图：

仔细看上面的窗口函数中，用了rows和preceding这两个关键字，是“之前~行”的意思，上面的句子中，是之前2行。也就是得到的结果是自身记录及与前2行（一共3行）的平均值。

例如：学号0004学生的current_avg，是自己和前2位同学的平均，即0002，0003，0004三位同学成绩的平均，其他数据的情况也一样，下图非常直观的可以看到计算过程：

每一行得到的结果，都是当前行和前面2行的平均（共3行）。想要计算当前行与前n行（共n+1行）的平均时，只要调整rows…preceding中间的数字即可。

这里需要注意：在移动平均中，被选出的数据构成一个“框架”，例如，刚才例子中的0002、0003、0004行数据，就是一个“框架”。

这样使用窗口函数有什么用呢？

由于这里可以通过preceding关键字调整作用范围，在以下场景中非常适用：

在公司业绩名单排名中，可以通过移动平均，直观地查看到与相邻名次业绩的平均、求和等统计数据。

总结

PS：

partition子句可以省略，省略就是不指定分组，结果如下，只是按成绩由高到低进行了排序：

select *,rank() over (order by 成绩 desc) as ranking

from class；

得到结果如下图：

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：使用关联子查询

---

总结

如果此篇文章有帮助到您, 希望打大佬们能关注、点赞、收藏、评论支持一波，非常感谢大家！
如果有不对的地方请指正!!!