Postgresql 中文操作指南

3.5. Window Functions #

window function 对一组以某种方式与当前行相关联的表行执行计算。这与可以使用聚合函数执行的计算类型类似。但是，窗口函数不会导致行像非窗口聚合调用那样分组到一个单独的输出行中。相反，这些行保持自己的独立性。在幕后，窗口函数能够访问的不仅仅是查询结果的当前行。

这里有一个示例，演示如何将每个员工的工资与他或她所在部门的平均工资进行比较：

SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;

  depname  | empno | salary |          avg
-----------+-------+--------+-----------------------
 develop   |    11 |   5200 | 5020.0000000000000000
 develop   |     7 |   4200 | 5020.0000000000000000
 develop   |     9 |   4500 | 5020.0000000000000000
 develop   |     8 |   6000 | 5020.0000000000000000
 develop   |    10 |   5200 | 5020.0000000000000000
 personnel |     5 |   3500 | 3700.0000000000000000
 personnel |     2 |   3900 | 3700.0000000000000000
 sales     |     3 |   4800 | 4866.6666666666666667
 sales     |     1 |   5000 | 4866.6666666666666667
 sales     |     4 |   4800 | 4866.6666666666666667
(10 rows)

前三列输出直接来自表 empsalary，表中每一行对应一个输出行。第四列表示对所有具有与当前行相同的 depname 值的表行取平均值。（实际上，这与非窗口 avg 聚合是相同的函数，但 OVER 子句导致它被视为窗口函数，并在窗口框架中进行计算。）

窗口函数调用总是包含一个 OVER 子句，紧跟在窗口函数的名称和参数之后。这在语法上将其与普通函数或非窗口聚合区分开来。OVER 子句精确地确定查询的行如何分割，以便由窗口函数进行处理。OVER 中的 PARTITION BY 子句将行按共享 PARTITION BY 表达式的相同值划分为组或分区。对于每行，窗口函数在与当前行属于同一分区的行中进行计算。

您还可以在 OVER 中使用 ORDER BY 控制窗口函数处理行的方式。（窗口 ORDER BY 甚至不必与行的输出顺序相匹配。）以下是一个示例：

SELECT depname, empno, salary,
       rank() OVER (PARTITION BY depname ORDER BY salary DESC)
FROM empsalary;

  depname  | empno | salary | rank
-----------+-------+--------+------
 develop   |     8 |   6000 |    1
 develop   |    10 |   5200 |    2
 develop   |    11 |   5200 |    2
 develop   |     9 |   4500 |    4
 develop   |     7 |   4200 |    5
 personnel |     2 |   3900 |    1
 personnel |     5 |   3500 |    2
 sales     |     1 |   5000 |    1
 sales     |     4 |   4800 |    2
 sales     |     3 |   4800 |    2
(10 rows)

如下所示，rank 函数使用 ORDER BY 子句定义的顺序，为当前行的每个不同的 ORDER BY 值生成数字排名。rank 无需显式参数，因为它的行为完全由 OVER 子句决定。

窗口函数考虑的行是查询的 FROM 子句生产的“虚拟表”的行，如果存在它的 WHERE、GROUP BY 和 HAVING 子句，则由这些子句过滤。例如，由于不满足 WHERE 条件而删除的行不会被任何窗口函数看到。一个查询可以包含多个窗口函数，它们使用不同的 OVER 子句以不同的方式对数据进行切分，但它们都在同一个由该虚拟表定义的行集合上执行。

我们已经看到，如果行的顺序不重要，可以省略 ORDER BY。也可以省略 PARTITION BY，在这种情况下，只有一个包含所有行的分区。

还有另一个与窗口函数相关的重要概念：对于每行，其分区中有一组行，称为其 window frame 。某些窗口函数只针对窗口范围的行操作，而不是针对整个分区操作。默认情况下，如果提供了 ORDER BY ，那么该范围将包含从分区开始到当前行的所有行，外加根据 ORDER BY 子句与当前行相等的所有后续行。当省略 ORDER BY 时，默认范围将包含分区中的所有行。 [5 ] 这里是一个使用 sum 的示例：

SELECT salary, sum(salary) OVER () FROM empsalary;

 salary |  sum
--------+-------
   5200 | 47100
   5000 | 47100
   3500 | 47100
   4800 | 47100
   3900 | 47100
   4200 | 47100
   4500 | 47100
   4800 | 47100
   6000 | 47100
   5200 | 47100
(10 rows)

上面，由于 OVER 子句中没有 ORDER BY，所以窗口框架与分区相同，由于没有 PARTITION BY，因此是整个表；换句话说，每次求和都对整个表进行，因此我们对每一行输出获得相同的结果。但如果我们添加一个 ORDER BY 子句，我们得到的结果非常不同：

SELECT salary, sum(salary) OVER (ORDER BY salary) FROM empsalary;

 salary |  sum
--------+-------
   3500 |  3500
   3900 |  7400
   4200 | 11600
   4500 | 16100
   4800 | 25700
   4800 | 25700
   5000 | 30700
   5200 | 41100
   5200 | 41100
   6000 | 47100
(10 rows)

在这里，对从最小的第一个薪水到当前薪水（包括当前薪水的任何重复项）进行了求和（注意重复薪水的结果）。

窗口函数只允许在查询的 SELECT 列表和 ORDER BY 子句中。它们在其他地方是不允许的，例如在 GROUP BY、HAVING 和 WHERE 子句中。这是因为它们在逻辑上在这些子句处理后执行。此外，窗口函数在非窗口聚合函数之后执行。这意味着将一个聚合函数调用包含在窗口函数的参数中是有效的，但反之则不行。

如果需要在窗口计算执行后过滤或分组行，可以使用子选择。例如：

SELECT depname, empno, salary, enroll_date
FROM
  (SELECT depname, empno, salary, enroll_date,
          rank() OVER (PARTITION BY depname ORDER BY salary DESC, empno) AS pos
     FROM empsalary
  ) AS ss
WHERE pos < 3;

上面的查询只显示内部查询中 rank 小于 3 的行。

当一个查询涉及多个窗口函数时，可以用一个单独的 OVER 子句写出每一个函数，但如果需要对几个函数使用相同的窗口行为，则这是重复且容易出错的。相反，可以在 WINDOW 子句中为每个窗口行为命名，然后在 OVER 中引用。例如：

SELECT sum(salary) OVER w, avg(salary) OVER w
  FROM empsalary
  WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);

有关窗口函数的更多详细信息，请参阅 Section 4.2.8 、 Section 9.22 、 Section 7.2.5 和 SELECT 参考页面。

[5 ] 还有其他方式定义窗口范围的选项，但本教程不包含这些内容。有关详细信息，请参阅 Section 4.2.8 。