3.5. 窗口函数 · PostgreSQL 中文文档 9.3

# 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); ``` 有关窗口函数的更多详细信息请查阅[Section 4.2.8](#calibre_link-1331), [Section 9.21](#calibre_link-1804),[Section 7.2.4](#calibre_link-1332),和 [SELECT](#calibre_link-104)的参考页。 ### Notes \[1\] 当然，还有其他定义window frame的方法，但本教程并不包括它们。详情请参阅[Section 4.2.8](#calibre_link-1331)。