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控制窗口函数处理行的 Sequences。 (窗口ORDER BY甚至不必匹配行的输出 Sequences.)这是一个示例:

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子句定义的 Sequences,为当前行分区中每个不同的ORDER BY值生成一个数值等级。 rank不需要显式参数,因为它的行为完全由OVER子句确定。

窗口函数考虑的行是查询的FROM子句所生成的“虚拟表”的行,并按其WHEREGROUP BYHAVING子句(如有)进行了过滤。例如,任何窗口函数都不会看到由于不满足WHERE条件而删除的行。一个查询可以包含多个窗口函数,这些窗口函数使用不同的OVER子句以不同的方式对数据进行切片,但是它们都作用于该虚拟表定义的同一行集合上。

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

与窗口函数相关的另一个重要概念是:对于每一行,在其分区中都有一组称为“窗口框架”的行。某些窗口函数仅作用于窗口框架的行,而不作用于整个分区的行。默认情况下,如果提供了ORDER BY,则该帧由分区开始到当前行的所有行组成,再加上根据ORDER BY子句等于当前行的所有后续行。省略ORDER BY时,默认框架由分区中的所有行组成。 [4]这是使用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 BYHAVINGWHERE子句。这是因为它们在处理这些子句之后在逻辑上执行。同样,窗口函数在非窗口聚合函数之后执行。这意味着在窗口函数的参数中包括聚合函数调用是有效的,反之亦然。

如果在执行窗口计算之后需要对行进行过滤或分组,则可以使用子选择。例如:

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.8Section 9.21Section 7.2.5SELECT参考页。


[4]可以使用其他方法来定义窗口框架,但是本教程不介绍它们。有关详情,请参见Section 4.2.8