sql窗口函数（SQL窗口函数sum求近三天的和）



1. 窗口函数有什么用？

在日常工作的场景中，经常会遇到需要在每组内排名的情况，比如：

a. 排名问题：每个部门按业绩来排名

b. topN问题：找出每个部门排名前N的员工进行奖励

面对这类需求，就需要使用sql的高级功能窗口函数了。

2. 什么是窗口函数？

窗口函数，也叫OLAP函数（Online Analytical Processing，联机分析处理），可以对数据库数据进行实时分析处理

窗口函数的基本语法如下：

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

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

a. 专用窗口函数，例如rank,dense rank,row_number等专用窗口函数。

b. 聚合函数，如sum,avg,count,max,min等

因为窗口函数是对where或者group by子句处理后的结果进行操作，所以窗口函数原则上只能写在select子句中。

讲完了什么是窗口函数，接下来就结合实例练习一下窗口函数的几种用法。

1. 专用窗口函数rank

例如下图，是班级表中的内容。如果想在每个班级内按成绩排名，得到下面框内所示的结果。

能够呈现上面结果的sql语句代码如下：

select *,

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

上面的语句中，rank是排序的函数。题目要求“每个班级内按成绩排名”，可以把这句话分为两部分：

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

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

2) 按成绩排名

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

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

窗口函数同时具备了之前学过group by子句分组的功能和order by子句排序的功能。之所以还要使用窗口函数的原因，是因为group by分组汇总后改变了表的行数，一行只有一个类别。而partition by和rank函数不会减少原表中的行数。例如下面统计每个班级的人数：

通过这个例子，理解窗口函数的使用：

为什么被叫做“窗口”函数的原因，是因为partition by分组后的结果称为“窗口”，这里的“窗口”表示“范围”的意思。

总结一下，窗口函数有以下功能：

1. 同时具有分组和排序的功能
2. 不减少原表的行数
3. 语法如下

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

专用窗口函数rank，dense_rank，row_number之间的区别：

举个例子，

得到结果：

从上面的结果可以看出：

1. rank函数：这个例子中标框线的地方分别是5，5，5，5。这意味着如果有并列名词的行，会占用下一名词的位置。比如正常排名是1，2，3，4，但是现在前三名是并列的名词，那么结果会是：1，1，1，4。

2. dense_rank函数：这个例子中标框线的地方分别是5，5，5，6。这意味着如果有并列名词的行，不会占用下一名词的位置。比如正常排名是1，2，3，4，但是现在前三名是并列的名词，那么结果会是：1，1，1，2。

3. row_number函数：这个例子中标框线的地方分别是5，6，7，8。这意味着如果有并列名词的行，不会考虑并列。比如正常排名是1，2，3，4，但是现在前三名是并列的名词，那么结果会是：1，2，3，4。

这三个函数的区别如下：

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

【题目】

下图是'班级'表中的内容，记录了每个学生所在班级，和对应的成绩

现在需要按成绩来排名，如果两个分数相同，那么排名要是并列的。正常排名是1，2，3，4，但是现在前3名是并列的名次，排名结果是：1，1，1，2。

【解题思路】

1. 此题涉及到排名问题，可以使用窗口函数
2. 在专用窗口函数rank，dense_rank，row_number中选择哪一个，可以参照前面【三、其他专用窗口函数】中这三个函数的比较。根据题目要求的排名规则，选择使用dense_rank函数。sql语句为:

【本题考点】

1. 考察如何使用窗口函数
2. 专用窗口函数排名的区别：rank，dense_rank，row_number

【举一反三】

涉及到排名的问题。都可以使用窗口函数来解决。记住rank，dense_rank，row_number之间的区别。

下面的例子为编写一个SQL查询来实现分数排名。如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名词之间不应该有“间隔”。

例如，根据上述给定的Scores表，查询应该返回如下的结果（按分数从高到低排列）：

参考答案：

select score,

dense_rank() over (order by Score desc) as Rank from Scores;

【面试题类型】topN问题

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

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

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

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

面对该类问题，通过下面成绩表的例子来给出答案。

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

• 分组取每组最大值

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

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

要想看到成绩最大值所在行的所有数据，可以使用关联子查询来实现：

上面查询结果课程号“0001”有2行数据，是该课程号下面最大成绩80有2个

• 分组取每组最小值所在行的数据

与分组取每组最大值所在行的数据一样，同样的使用关联子查询来实现

• 每组最大的N条记录

案例：查询各科成绩前两名的记录

【解题思路】

1. 看到问题中要查“每个”学生最高的成绩，记得之前课程里讲过，当有“每个”出现的时候，就要想到是要分组了。

这里是“每个学生”，结合表的结构，是按学生“姓名”来分组。

2. 将表按学生姓名分组后，把成绩按降序排列，排在最前面的2个就是我们要找的“成绩最高的2个科目”

3. 现在分组后，需要排序，又不减少原表的行数，这种功能自然想到是窗口函数。

4. 使用哪个专用窗口函数？

为了不受并列成绩的影响，使用row_number专用窗口函数：

row_number函数：也就是不考虑并列名次的情况。比如前3名是并列的名次，排名是正常的1，2，3，4。

【解题步骤】

步骤一：按姓名分组（partition by 姓名）、并按成绩降序排列（order by 成绩 desc），套入窗口函数的语法，就是下面的sql语句：

运行结果如下：

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

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

但是会发现，如上图所示，这样写sql会报错，原因就是之前在课程里有强调的，需要牢记sql的书写顺序和运行顺序。在运行顺序中，select子句是最后被运行的。

当理解了书写顺序和运行顺序的差别后，就明白错误的原因了：运行到“where ranking<=2”的时候，因为select子句还没有被执行，因此select中的“ranking”列还没有出现，从而导致报错。

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

得到结果：

【本题考点】

1. 主要考察对于窗口函数的选择使用，包括不同窗口函数之间的区别
2. 在筛选过程中，非常容易因为子查询问题报错（自己练习时就错了两次），因此本题也考察了对子查询的熟练运用
3. 在本题中，sql语句执行顺序和书写顺序之间的区别也是需要注意的点

【举一反三】

经典top问题：每组最大的N条记录。这类问题涉及到“既要分组，又要排序”的情况，需要能想到用窗口函数来实现。

将本题的sql语句修改下（其实就是将where子句里的条件修改成N），就可以成为这类型问题的一个万能模板，遇到同类型的问题往里面套就可以了：

查找每组最大的N条记录通用模板：

select *

from (select *, rank/denserank/row_number()

order by <要排序的列名> [desc/asc] as 列名

from 表) as 表1

where 排名<=N;

聚合窗口函数和上面提到的专用窗口函数用法完全相同，只需要把聚合函数写在窗口函数的位置即可，但是函数后面括号里面不能为空，需要指定聚合的列名。下面展示了当窗口函数是聚合函数时，会出来什么样的结果：

得到结果：

观察上面结果，会有一些发现，单独用sum举个例子：

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

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

比如0005号后面的聚合窗口函数结果是：学号0001~0005五人成绩的总和、平均、计数及最大最小值。而如果想要知道所有人成绩的总和、平均等聚合结果，看最后一行即可。

这样使用窗口函数的作用是：

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

【题目】

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

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

【解题思路】

1. '查找单科成绩高于该科目平均成绩'，也就是在“每个”科目里比较。之前课程里讲过，当有“每个”出现的时候，就要想到是要分组了。能实现“分组”功能的sql有两种，一是group by子句，另一个是窗口函数里的partition by。
2. 使用聚合窗口函数（求平均值avg），将每门课的平均成绩求出来以后，然后找出大于平均成绩的数据。

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

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

所以这里使用窗口函数的partition by。

【解题步骤】

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

运行结果如下：

第2步，如上表，按科目分组后各科目的平均分已经计算出，接下来只要筛选出成绩大于平均分的数据即可。那么，只需要在上一步的sql语句里加入条件子句where就可以了：

按照这样的思路解题，写出对应的sql语句，但是会出现如上图所示的报错。出现报错的原因是，在之前的课程里多次强调过，要牢记sql的书写顺序和运行顺序。在运行顺序中，select子句时最后被运行的。如下图所示：

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

而如果需要解决这种情况，就可以用子查询，也就是把第一步得到的查询结果作为一个新的表，如下：

运行结果如下：

【本题考点】

1. 主要考察对窗口函数的灵活使用。
2. 在筛选过程中，非常容易因为子查询报错，本题也考察了对子查询的熟练运用。
3. 本题见解考察了对sql语句执行顺序的熟悉程度。

【举一反三】

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

1. 使用上面讲到的窗口函数来实现
2. 使用关联子查询，可以回顾前面的课程《复杂查询》里的案例

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

使用avg作为窗口函数，得到上述结果

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

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

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

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

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

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

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

1. 注意事项

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

2. 总结

a. 窗口函数有以下功能：

1) 同时具有分组（partition by）和排序（order by）的功能

2) 不减少原表的行数，所以经常用来在每组内排名

3. 注意事项

窗口函数原则上只能写在select子句中

4. 窗口函数使用场景

1) 经典topN问题

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

2）经典排名问题

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

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

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

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

方法2，使用关联子查询

到此这篇sql窗口函数（SQL窗口函数sum求近三天的和）的文章就介绍到这了,更多相关内容请继续浏览下面的相关推荐文章，希望大家都能在编程的领域有一番成就！

版权声明：

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如若内容造成侵权、违法违规、事实不符，请将相关资料发送至xkadmin@xkablog.com进行投诉反馈，一经查实，立即处理！

转载请注明出处，原文链接：https://www.xkablog.com/sqlbc/52913.html