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。

还有一个与窗口函数相关的重要的概念:对于每一行,有在其分区范围内的行集, 又称为它的window frame。许多(但不是全部)窗口函数,只作用于window frame中的行上, 而不是整个分区。默认情况下,如果使用ORDER BY, 那么这个frame包含从分区开始到当前行的所有行,以及那些当前行后面的,根据ORDER BY 子句等于当前行的所有行,如果省略ORDER BY,那么,frame默认包含分区中的所有行。 [1] 下面是一个使用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,因此, window frame与分区(不使用PARTITION BY时即整个表)相同;换句话说, 每一次sum求和都是使用表中所有的salary,所以我们得到的每个输出行的结果相同。 但是,如果我们添加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.21 节,第 7.2.4 节,和 SELECT的参考页。