3.5. 窗口函数#

窗口函数对与当前行在某种程度上相关的表格行集执行计算。这与可使用聚合函数执行的计算类型类似。但是，窗口函数不会像非窗口聚合调用那样导致行分组到单个输出行中。相反，行保留其单独的标识。在后台，窗口函数能够访问的不仅仅是查询结果的当前行。

以下示例演示了如何将每个员工的薪水与他或她所在部门的平均薪水进行比较

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，在这种情况下，只有一个分区包含所有行。

与窗口函数关联的另一个重要概念是：对于每一行，在其分区内有一组行，称为其窗口框架。一些窗口函数仅作用于窗口框架的行，而不是整个分区。默认情况下，如果提供了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);

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

[5]有其他方法可以定义窗口框架，但本教程不介绍这些方法。有关详细信息，请参阅第 4.2.8 节。

3.5. 窗口函数# ​

3.5. 窗口函数#