目录

• 一、什么是窗口函数
• 二、窗口函数的语法2.1 能够作为窗口函数使用的函数
• 三、语法的基本使用方法——使用 RANK 函数
• 四、无需指定 PARTITION BY
• 五、专用窗口函数的种类
• 六、窗口函数的适用范围
• 七、作为窗口函数使用的聚合函数
• 八、计算移动平均8.1 指定框架（汇总范围）8.2 将当前记录的前后行作为汇总对象
• 九、两个 ORDER BY

本文介绍什么是 SQL 窗口函数，窗口函数可以进行排序、生成序列号等一般的 聚合函数 无法实现的高级操作。

本文重点

窗口函数可以进行排序、生成序列号等一般的聚合函数无法实现的高级操作。

理解 PARTITION BY 和 ORDER BY 这两个关键字的含义十分重要。

一、什么是窗口函数

窗口函数也称为 OLAP 函数 [1]。为了让大家快速形成直观印象，才起了这样一个容易理解的名称（“窗口”的含义我们将在随后进行说明）。

OLAP 是 OnLine Analytical Processing 的简称，意思是对数据库数据进行实时分析处理。例如，市场分析、创建财务报表、创建计划等日常性商务工作。

窗口函数就是为了实现 OLAP 而添加的标准 SQL 功能 [2]。

专栏

窗口函数的支持情况

很多数据库相关工作者过去都会有这样的想法：“好不容易将业务数据插入到了数据库中，如果能够使用 SQL 对其进行实时分析的话，一定会很方便吧。”但是关系数据库提供支持 OLAP 用途的功能仅仅只有 10 年左右的时间。

其中的理由有很多，这里我们就不一一介绍了。大家需要注意的是，还有一部分 DBMS 并不支持这样的新功能。

本文将要介绍的窗口函数也是其中之一，截至 2016 年 5 月，Oracle、SQL Server、DB2、PostgreSQL 的最新版本都已经支持了该功能，但是 MySQL 的最新版本 5.7 还是不支持该功能。

通过前面的学习，我们已经知道各个 DBMS 都有自己支持的特定语法和不支持的语法。标准 SQL 添加新功能的时候也会遇到同样的问题（随着时间推移，标准 SQL 终将能够在所有的 DBMS 中进行使用）。

二、窗口函数的语法

接下来，就让我们通过示例来学习窗口函数吧。窗口函数的语法有些复杂。

语法 1 窗口函数

<窗口函数> OVER ([PARTITION BY <列清单>]                         ORDER BY <排序用列清单>)

※ [] 中的内容可以省略。

其中重要的关键字是 PARTITION BY 和 ORDER BY，理解这两个关键字的作用是帮助我们理解窗口函数的关键。

2.1 能够作为窗口函数使用的函数

在学习 PARTITION BY 和 ORDER BY 之前，我们先来列举一下能够作为窗口函数使用的函数。窗口函数大体可以分为以下两种。

① 能够作为窗口函数的聚合函数（SUM、AVG、COUNT、MAX、MIN）

② RANK、DENSE_RANK、ROW_NUMBER 等专用窗口函数

② 中的函数是标准 SQL 定义的 OLAP 专用函数，本文将其统称为“专用窗口函数”。从这些函数的名称可以很容易看出其 OLAP 的用途。

其中 ① 的部分是我们在 SQL 如何对表进行聚合和分组查询并对查询结果进行排序 中学过的聚合函数。将聚合函数书写在“语法 1”的“<窗口函数>”中，就能够当作窗口函数来使用了。

总之，聚合函数根据使用语法的不同，可以在聚合函数和窗口函数之间进行转换。

三、语法的基本使用方法——使用 RANK 函数

首先让我们通过专用窗口函数 RANK 来理解一下窗口函数的语法吧。正如其名称所示，RANK 是用来计算记录排序的函数。

例如，对于之前使用过的 Product 表中的 8 件商品，让我们根据不同的商品种类（product_type），按照销售单价（sale_price）从低到高的顺序排序，结果如下所示：

product_name | product_type | sale_price | ranking-------------+--------------+------------+-------- 叉子        | 厨房用具      |        500 |       1 擦菜板      | 厨房用具      |        880 |       2 菜刀        | 厨房用具      |       3000 |       3 高压锅      | 厨房用具      |       6800 |       4 T恤衫       | 衣服          |       1000 |       1 运动T恤     | 衣服          |       4000 |       2 圆珠笔      | 办公用品      |        100 |       1 打孔器      | 办公用品      |        500 |       2

以厨房用具为例，销售单价最便宜的“叉子”排在第 1 位，最贵的“高压锅”排在第 4 位，确实按照我们的要求进行了排序。

能够得到上述结果的 SELECT 语句请参考代码清单 1。

代码清单 1 根据不同的商品种类，按照销售单价从低到高的顺序创建排序表

Oracle SQL Server DB2 PostgreSQL

SELECT product_name, product_type, sale_price,       RANK () OVER (PARTITION BY product_type                          ORDER BY sale_price) AS ranking  FROM Product;

PARTITION BY 能够设定排序的对象范围。本例中，为了按照商品种类进行排序，我们指定了 product_type。

ORDER BY 能够指定按照哪一列、何种顺序进行排序。为了按照销售单价的升序进行排列，我们指定了 sale_price。

此外，窗口函数中的 ORDER BY 与 SELECT 语句末尾的 ORDER BY 一样，可以通过关键字 ASC/DESC 来指定升序和降序。

省略该关键字时会默认按照 ASC，也就是升序进行排序。本例中就省略了上述关键字 [3]。

通过图 1，我们就很容易理解 PARTITION BY 和 ORDER BY 的作用了。如图所示，PARTITION BY 在横向上对表进行分组，而 ORDER BY 决定了纵向排序的规则。

图 1　PARTITION BY 和 ORDER BY 的作用

窗口函数兼具之前我们学过的 GROUP BY 子句的分组功能以及 ORDER BY 子句的排序功能。但是，PARTITION BY 子句并不具备 GROUP BY 子句的汇总功能。

因此，使用 RANK 函数并不会减少原表中记录的行数，结果中仍然包含 8 行数据。

法则 1

窗口函数兼具分组和排序两种功能。

通过 PARTITION BY 分组后的记录集合称为窗口。此处的窗口并非“窗户”的意思，而是代表范围。这也是“窗口函数”名称的由来。[4]

法则 2

通过 PARTITION BY 分组后的记录集合称为“窗口”。

此外，各个窗口在定义上绝对不会包含共通的部分。就像刀切蛋糕一样，干净利落。这与通过 GROUP BY 子句分割后的集合具有相同的特征。

四、无需指定 PARTITION BY

使用窗口函数时起到关键作用的是 PARTITION BY 和 GROUP BY。其中，PARTITION BY 并不是必需的，即使不指定也可以正常使用窗口函数。

那么就让我们来确认一下不指定 PARTITION BY 会得到什么样的结果吧。这和使用没有 GROUP BY 的聚合函数时的效果一样，也就是将整个表作为一个大的窗口来使用。

事实胜于雄辩，下面就让我们删除代码清单 1 中 SELECT 语句的 PARTITION BY 试试看吧（代码清单 2）。

代码清单 2 不指定 PARTITION BY

Oracle SQL Server DB2 PostgreSQL

SELECT product_name, product_type, sale_price,       RANK () OVER (ORDER BY sale_price) AS ranking  FROM Product;

上述 SELECT 语句的结果如下所示：

product_name | product_type | sale_price | ranking-------------+--------------+------------+-------- 圆珠笔      | 办公用品     |         100 |       1 叉子        | 厨房用具     |         500 |       2 打孔器      | 办公用品     |         500 |       2 擦菜板      | 厨房用具     |         880 |       4 T恤衫       | 衣服         |        1000 |       5 菜刀        | 厨房用具     |        3000 |       6 运动T恤     | 衣服         |        4000 |       7 高压锅      | 厨房用具     |        6800 |       8

之前我们得到的是按照商品种类分组后的排序，而这次变成了全部商品的排序。像这样，当希望先将表中的数据分为多个部分（窗口），再使用窗口函数时，可以使用 PARTITION BY 选项。

五、专用窗口函数的种类

从上述结果中我们可以看到，“打孔器”和“叉子”都排在第 2 位，而之后的“擦菜板”跳过了第 3 位，直接排到了第 4 位，这也是通常的排序方法，但某些情况下可能并不希望跳过某个位次来进行排序。

这时可以使用 RANK 函数之外的函数来实现。下面就让我们来总结一下具有代表性的专用窗口函数吧。

• RANK 函数
• 计算排序时，如果存在相同位次的记录，则会跳过之后的位次。
• 例）有 3 条记录排在第 1 位时：1 位、1 位、1 位、4 位……
• DENSE_RANK 函数
• 同样是计算排序，即使存在相同位次的记录，也不会跳过之后的位次。
• 例）有 3 条记录排在第 1 位时：1 位、1 位、1 位、2 位……
• ROW_NUMBER 函数
• 赋予唯一的连续位次。
• 例）有 3 条记录排在第 1 位时：1 位、2 位、3 位、4 位……

除此之外，各 DBMS 还提供了各自特有的窗口函数。上述 3 个函数（对于支持窗口函数的 DBMS 来说）在所有的 DBMS 中都能够使用。

下面就让我们来比较一下使用这 3 个函数所得到的结果吧（代码清单 3）。

代码清单 3 比较 RANK、DENSE_RANK、ROW_NUMBER 的结果

Oracle SQL Server DB2 PostgreSQL

SELECT product_name, product_type, sale_price,     RANK () OVER (ORDER BY sale_price) AS ranking,     DENSE_RANK () OVER (ORDER BY sale_price) AS dense_ranking,     ROW_NUMBER () OVER (ORDER BY sale_price) AS row_num FROM Product;

执行结果：

将结果中的 ranking 列和 dense_ranking 列进行比较可以发现，dense_ranking 列中有连续 2 个第 2 位，这和 ranking 列的情况相同。

但是接下来的“擦菜板”的位次并不是第 4 而是第 3。这就是使用 DENSE_RANK 函数的效果了。

此外，我们可以看到，在 row_num 列中，不管销售单价（sale_price）是否相同，每件商品都会按照销售单价从低到高的顺序得到一个连续的位次。

销售单价相同时，DBMS 会根据适当的顺序对记录进行排列。想为记录赋予唯一的连续位次时，就可以像这样使用 ROW_NUMBER 来实现。

使用 RANK 或 ROW_NUMBER 时无需任何参数，只需要像 RANK() 或者 ROW_NUMBER() 这样保持括号中为空就可以了。这也是专用窗口函数通常的使用方式，请大家牢记。

这一点与作为窗口函数使用的聚合函数有很大的不同，之后我们将会详细介绍。

法则 3

由于专用窗口函数无需参数，因此通常括号中都是空的。

六、窗口函数的适用范围

目前为止我们学过的函数大部分都没有使用位置的限制，最多也就是在 WHERE 子句中使用聚合函数时会有些注意事项。

但是，使用窗口函数的位置却有非常大的限制。更确切地说，窗口函数只能书写在一个特定的位置。

这个位置就是 SELECT 子句之中。反过来说，就是这类函数不能在 WHERE 子句或者 GROUP BY 子句中使用。[5]

虽然我们可以把它当作一种规则死记硬背下来，但是为什么窗口函数只能在 SELECT 子句中使用呢（也就是不能在 WHERE 子句或者 GROUP BY 子句中使用）？

下面我们就来简单说明一下其中的理由。

其理由就是，在 DBMS 内部，窗口函数是对 WHERE 子句或者 GROUP BY 子句处理后的“结果”进行的操作。

大家仔细想一想就会明白，在得到用户想要的结果之前，即使进行了排序处理，结果也是错误的。

在得到排序结果之后，如果通过 WHERE 子句中的条件除去了某些记录，或者使用 GROUP BY 子句进行了汇总处理，那好不容易得到的排序结果也无法使用了。[6]

正是由于这样的原因，在 SELECT 子句之外“使用窗口函数是没有意义的”，所以在语法上才会有这样的限制。

七、作为窗口函数使用的聚合函数

前面给大家介绍了使用专用窗口函数的示例，下面我们再来看一看把之前学过的 SUM 或者 AVG 等聚合函数作为窗口函数使用的方法。

所有的聚合函数都能用作窗口函数，其语法和专用窗口函数完全相同。但大家可能对所能得到的结果还没有一个直观的印象，所以我们还是通过具体的示例来学习。

下面我们先来看一个将 SUM 函数作为窗口函数使用的例子（代码清单 4）。

代码清单 4 将 SUM 函数作为窗口函数使用

Oracle SQL Server DB2 PostgreSQL

SELECT product_id, product_name, sale_price,    SUM (sale_price) OVER (ORDER BY product_id) AS current_sum  FROM Product;

执行结果：

 product_id | product_name | sale_price | current_sum------------+--------------+------------+------------ 0001       | T恤衫        |       1000 |        1000   ←1000 0002       | 打孔器       |        500 |        1500   ←1000+500 0003       | 运动T恤      |       4000 |        5500   ←1000+500+4000 0004       | 菜刀         |       3000 |        8500   ←1000+500+4000+3000 0005       | 高压锅       |       6800 |       15300              · 0006       | 叉子         |        500 |       15800              · 0007       | 擦菜板       |        880 |       16680              · 0008       | 圆珠笔       |        100 |       16780              ·

使用 SUM 函数时，并不像 RANK 或者 ROW_NUMBER 那样括号中的内容为空，而是和之前我们学过的一样，需要在括号内指定作为汇总对象的列。

本例中我们计算出了销售单价（sale_price）的合计值（current_sum）。

但是我们得到的并不仅仅是合计值，而是按照 ORDER BY 子句指定的 product_id 的升序进行排列，计算出商品编号“小于自己”的商品的销售单价的合计值。

因此，计算该合计值的逻辑就像金字塔堆积那样，一行一行逐渐添加计算对象。在按照时间序列的顺序，计算各个时间的销售额总额等的时候，通常都会使用这种称为累计的统计方法。

使用其他聚合函数时的操作逻辑也和本例相同。例如，使用 AVG 来代替 SELECT 语句中的 SUM（代码清单 5）。

代码清单 5 将 AVG 函数作为窗口函数使用

Oracle SQL Server DB2 PostgreSQL

SELECT product_id, product_name, sale_price,     AVG (sale_price) OVER (ORDER BY product_id) AS current_avg  FROM Product;

执行结果：

product_id | product_name | sale_price |     current_avg-----------+--------------+------------+----------------------- 0001      | T恤衫        |       1000 | 1000.0000000000000000 ←(1000)/1 0002      | 打孔器       |        500 | 750.0000000000000000  ←(1000+500)/2 0003      | 运动T恤      |       4000 | 1833.3333333333333333 ←(1000+500+4000)/3 0004      | 菜刀         |       3000 | 2125.0000000000000000 ←(1000+500+4000+3000)/4 0005      | 高压锅       |       6800 | 3060.0000000000000000 ←(1000+500+4000+3000+6800)/5 0006      | 叉子         |        500 | 2633.3333333333333333               · 0007      | 擦菜板       |        880 | 2382.8571428571428571               · 0008      | 圆珠笔       |        100 | 2097.5000000000000000               ·

从结果中我们可以看到，current_avg 的计算方法确实是计算平均值的方法，但作为统计对象的却只是“排在自己之上”的记录。

像这样以“自身记录（当前记录）”作为基准进行统计，就是将聚合函数当作窗口函数使用时的最大特征。

八、计算移动平均

窗口函数就是将表以窗口为单位进行分割，并在其中进行排序的函数。其实其中还包含在窗口中指定更加详细的汇总范围的备选功能，该备选功能中的汇总范围称为框架。

其语法如代码清单 6 所示，需要在 ORDER BY 子句之后使用指定范围的关键字。

代码清单 6 指定“最靠近的 3 行”作为汇总对象

Oracle SQL Server DB2 PostgreSQL

SELECT product_id, product_name, sale_price,       AVG (sale_price) OVER (ORDER BY product_id                               ROWS 2 PRECEDING) AS moving_avg  FROM Product;

执行结果（在 DB2 中执行）：

product_id    product_name    sale_price     moving_avg-----------   -------------  -------------   ------------ 0001         T恤衫                 1000           1000 ←(1000)/1 0002         打孔器                 500            750 ←(1000+500)/2 0003         运动T恤               4000           1833 ←(1000+500+4000)/3 0004         菜刀                  3000           2500 ←(500+4000+3000)/3 0005         高压锅                6800           4600 ←(4000+3000+6800)/3 0006         叉子                   500           3433          · 0007         擦菜板                 880           2726          · 0008         圆珠笔                 100            493          ·

8.1 指定框架（汇总范围）

我们将上述结果与之前的结果进行比较，可以发现商品编号为“0004”的“菜刀”以下的记录和窗口函数的计算结果并不相同。

这是因为我们指定了框架，将汇总对象限定为了“最靠近的 3 行”。

这里我们使用了 ROWS（“行”）和 PRECEDING（“之前”）两个关键字，将框架指定为“截止到之前 ~ 行”，因此“ROWS 2 PRECEDING”就是将框架指定为“截止到之前 2 行”，也就是将作为汇总对象的记录限定为如下的“最靠近的 3 行”。

• 自身（当前记录）
• 之前 1 行的记录
• 之前 2 行的记录

也就是说，由于框架是根据当前记录来确定的，因此和固定的窗口不同，其范围会随着当前记录的变化而变化。

图 2　将框架指定为截止到当前记录之前 2 行（最靠近的 3 行）

如果将条件中的数字变为“ROWS 5 PRECEDING”，就是“截止到之前 5 行”（最靠近的 6 行）的意思。

这样的统计方法称为移动平均（moving average）。由于这种方法在希望实时把握“最近状态”时非常方便，因此常常会应用在对股市趋势的实时跟踪当中。

使用关键字 FOLLOWING（“之后”）替换 PRECEDING，就可以指定“截止到之后~ 行”作为框架了（图 3）。

图 3　将框架指定为截止到当前记录之后 2 行（最靠近的 3 行）

8.2 将当前记录的前后行作为汇总对象

如果希望将当前记录的前后行作为汇总对象时，就可以像代码清单 7 那样，同时使用 PRECEDING（“之前”）和 FOLLOWING（“之后”）关键字来实现。

代码清单 7 将当前记录的前后行作为汇总对象

Oracle SQL Server DB2 PostgreSQL

Oracle  SQL Server  DB2  PostgreSQLSELECT product_id, product_name, sale_price,       AVG (sale_price) OVER (ORDER BY product_id                               ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_avg  FROM Product;

执行结果（在 DB2 中执行）：

product_id    product_name    sale_price    moving_avg-----------   -------------   -----------   ----------- 0001         T恤衫                 1000           750   ←(1000+500)/2 0002         打孔器                 500          1833   ←(1000+500+4000)/3 0003         运动T恤               4000          2500   ←(500+4000+3000)/3 0004         菜刀                  3000          4600   ←(4000+3000+6800)/3 0005         高压锅                6800          3433             · 0006         叉子                   500          2726             · 0007         擦菜板                 880           493             · 0008         圆珠笔                 100           490             ·

在上述代码中，我们通过指定框架，将“1 PRECEDING”（之前 1 行）和“1 FOLLOWING”（之后 1 行）的区间作为汇总对象。

具体来说，就是将如下 3 行作为汇总对象来进行计算（图 4）。

• 之前 1 行的记录
• 自身（当前记录）
• 之后 1 行的记录

如果能够熟练掌握框架功能，就可以称为窗口函数高手了。

图 4　将框架指定为当前记录及其前后 1 行

九、两个 ORDER BY

最后我们来介绍一下使用窗口函数时与结果形式相关的注意事项，那就是记录的排列顺序。

因为使用窗口函数时必须要在 OVER 子句中使用 ORDER BY，所以可能有读者乍一看会觉得结果中的记录会按照该 ORDER BY 指定的顺序进行排序。

但其实这只是一种错觉。OVER 子句中的 ORDER BY 只是用来决定窗口函数按照什么样的顺序进行计算的，对结果的排列顺序并没有影响。

因此也有可能像代码清单 8 那样，得到一个记录的排列顺序比较混乱的结果。有些 DBMS 也可以按照窗口函数的 ORDER BY 子句所指定的顺序对结果进行排序，但那也仅仅是个例而已。

代码清单 8 无法保证如下 SELECT 语句的结果的排列顺序

Oracle SQL Server DB2 PostgreSQL

SELECT product_name, product_type, sale_price,       RANK () OVER (ORDER BY sale_price) AS ranking  FROM Product;

有可能会得到下面这样的结果：

 product_name | product_type | sale_price | ranking--------------+--------------+------------+-------- 菜刀         | 厨房用具      |       3000 |       6 打孔器       | 办公用品      |        500 |       2 运动T恤      | 衣服          |       4000 |       7 T恤衫        | 衣服          |       1000 |       5 高压锅       | 厨房用具      |       6800 |       8 叉子         | 厨房用具      |        500 |       2 擦菜板       | 厨房用具      |        880 |       4 圆珠笔       | 办公用品      |        100 |       1

那么，如何才能让记录切实按照 ranking 列的升序进行排列呢？

答案非常简单。那就是在 SELECT 语句的最后，使用 ORDER BY 子句进行指定（代码清单 9）。

这样就能保证 SELECT 语句的结果中记录的排列顺序了，除此之外也没有其他办法了。

代码清单 9 在语句末尾使用 ORDER BY 子句对结果进行排序

Oracle SQL Server DB2 PostgreSQL

SELECT product_name, product_type, sale_price,       RANK () OVER (ORDER BY sale_price) AS ranking  FROM Product ORDER BY ranking;

也许大家会觉得在一条 SELECT 语句中使用两次 ORDER BY 会有点别扭，但是尽管这两个 ORDER BY 看上去是相同的，但其实它们的功能却完全不同。

法则 5

将聚合函数作为窗口函数使用时，会以当前记录为基准来决定汇总对象的记录。

1. 在 Oracle 和 SQL Server 中称为分析函数。 ??
2. 目前 MySQL 还不支持窗口函数。详细信息请参考专栏“窗口函数的支持情况”。 ??
3. 其所要遵循的规则与 SELECT 语句末尾的 ORDER BY 子句完全相同。 ??
4. 从词语意思的角度考虑，可能“组”比“窗口”更合适一些，但是在 SQL 中，“组”更多的是用来特指使用 GROUP BY 分割后的记录集合，因此，为了避免混淆，使用 PARTITION BY 时称为窗口。 ??
5. 语法上，除了 SELECT 子句，ORDER BY 子句或者 UPDATE 语句的 SET 子句中也可以使用。但因为几乎没有实际的业务示例，所以开始的时候大家只要记得“只能在 SELECT 子句中使用”就可以了。 ??
6. 反之，之所以在 ORDER BY 子句中能够使用窗口函数，是因为 ORDER BY 子句会在 SELECT 子句之后执行，并且记录保证不会减少。 ??