本文包括以下几部分

• 窗口函数
• 专用窗口函数
• 聚合窗口函数
• 窗口函数移动平均
• 总结

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

2、窗口数有什么用？

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

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

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

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

3、窗口函数的基本语法：

4、窗口函数的种类?

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

1 专用窗口函数，包括后面要讲到的rank, dense_rank, row_number等专用窗口函数。

2 聚合窗口函数，如sum. avg, count, max, min等

5、注意事项

因为窗口函数是对where或者group by子句处理后的结果进行操作，所以窗口函数原则上只能写在select子句中。partition子句是可以省略，省略就是不指定分组，结果如下，只是按成绩由高到低进行了排序

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

2） 按成绩排名

order by子句的功能是对分组后的结果进行排序，默认是按照升序（asc）排列。在本例中（order by 成绩 desc）是按成绩这一列排序，加了desc关键词表示降序排列。通过下图，我们就可以理解partiition by（分组）和order by（在组内排序）的作用了。

窗口函数具备了我们之前学过的group by子句分组的功能和order by子句排序的功能。那么，为什么还要用窗口函数呢？这是因为，group by分组汇总后改变了表的行数，一行只有一个类别。而partiition by和rank函数不会减少原表中的行数。

这是因为partition by分组后的结果称为“窗口”，这里的窗口不是我们家里的门窗，而是表示“范围”的意思。

4、窗口函数的功能 ？

简单来说，窗口函数有以下功能：

1）同时具有分组和排序的功能

2）不减少原表的行数

3）语法如下：

5、3种专用窗口函数的区别？

rank函数：如果有并列名次的行，会占用下一名次的位置。比如正常排名是1，2，3，4，但是现在前3名是并列的名次，结果是：1，1，1，4。

dense_rank函数：如果有并列名次的行，不占用下一名次的位置。比如正常排名是1，2，3，4，但是现在前3名是并列的名次，结果是：1，1，1，2。

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

1）经典排名问题

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

解题思路：涉及排名，用窗口函数，再看用什么窗口函数？

注解：这里因为没有要求，不需要对班级进行分组，所以没有使用paritition by 班级。

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

2）案例：经典top N问题

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

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

子查询方法：

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

子查询后面要加 as a

举一反三 遇到类似问题 可以使用模板

聚和窗口函数和上面提到的专用窗口函数用法完全相同，只需要把聚合函数写在窗口函数的位置即可，但是函数后面括号里面不能为空，需要指定聚合的列名。

2、聚合函数的作用？

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

3、案例

查找单科成绩高于该科目平均成绩（两种解法）

解法1：窗口 函数

解题思路：

第1步，聚合函数avg()作为窗口函数，将每一课程号成绩的平均值求出。sql语句如下：

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

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

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

由于这里可以通过preceding关键字调整作用范围，在以下场景中非常适用：在公司业绩名单排名中，可以通过移动平均，直观地查看到与相邻名次业绩的平均、求和等统计数据。

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子句中

partition子句是可以省略，省略就是不指定分组

4.窗口函数使用场景

1）经典top N问题

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

2）经典排名问题

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

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

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

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