PostgreSQL 窗口函数：保留明细行，计算分组统计

窗口函数能够对与当前行相关的一组行进行计算。它与聚合函数有相似之处，但普通聚合往往把一组行合并为一行，窗口函数则保留每一行的独立身份；计算时，它还能访问查询结果中当前行以外的行。

按部门比较员工薪资

下面的查询把每位员工的薪资与所在部门的平均薪资放在同一行中：

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 表。最后一列是窗口函数算出的部门平均薪资，同一部门的所有员工都会得到相同的平均值。

窗口函数调用后总是紧跟 OVER 子句，这一点把它与普通函数和非窗口聚合函数区分开来。OVER 决定窗口函数如何处理查询中的行；其中的 PARTITION BY 按表达式值把行划分为不同的分区，窗口函数分别处理各个分区。对当前行来说，参与这次计算的是与它处于同一分区的行。

窗口内的排序与行号

可以在 OVER 内加入 ORDER BY，控制窗口函数处理行的顺序。这个顺序不一定与最终结果的显示顺序相同。下面按部门划分分区，再按薪资从高到低分配行号：

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

原文示例结果：

  depname  | empno | salary | row_number
-----------+-------+--------+------------
 develop   |     8 |   6000 |          1
 develop   |    10 |   5200 |          2
 develop   |    11 |   5200 |          3
 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 |          3
(10 rows)

row_number() 不需要显式参数，它依据 OVER 子句确定编号方式，在每个分区中从 1 开始依次编号。薪资相同的行之间没有指定进一步的排序规则，因此其行号次序是不确定的。

窗口函数处理的是查询的“虚拟表”：由 FROM 生成，再经过 WHERE、GROUP BY 和 HAVING（如果存在）处理后的行。被 WHERE 过滤掉的行不会参与窗口计算。同一个查询可以包含多个窗口函数，各自使用不同的 OVER，但它们都处理这个虚拟表。

PARTITION BY 可以省略，这时所有行属于同一个分区。如果排序并不重要，ORDER BY 也可以省略。

理解窗口帧

每一行还有一个与它关联的窗口帧：它是当前分区内的一部分行。一些窗口函数只对窗口帧计算，而不是对整个分区计算。

在默认情况下，若 OVER 含有 ORDER BY，窗口帧从分区开头延伸到当前行，并包含其后所有排序值与当前行相同的行。若没有 ORDER BY，默认帧就是整个分区。窗口帧还有其他定义方法，本节暂不展开。

先看没有排序的求和：

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 中没有 PARTITION BY，所有行构成一个分区；又没有 ORDER BY，帧就是全部行。因此每一行的求和结果都是全表薪资总额 47100。

现在在窗口内按薪资排序：

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)

这里的结果是从最低薪资开始累加到当前行，同时包含薪资相同的其他行。因此两个薪资为 4800 的员工都得到 25700，两个薪资为 5200 的员工都得到 41100。默认帧把相同排序值的行一起纳入，这并不是单纯的“只累加到物理上的这一行”。

先计算，再过滤

窗口函数只能出现在 SELECT 的输出列表和 ORDER BY 中，不能直接放入 WHERE、GROUP BY 或 HAVING。原因是窗口计算在这些子句之后进行；它也在非窗口聚合之后执行，所以可以把聚合调用作为窗口函数的参数，反过来则不行。

如果需要依据窗口计算结果进行过滤或分组，可以再套一层子查询。例如：

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

内层为每个部门按薪资降序、员工编号升序分配行号，外层保留 pos < 3 的行，即每个部门的前两名。

用命名窗口复用定义

多个窗口函数分别写相同的 OVER 是允许的，但容易重复。可以用 WINDOW 定义一个名字，再让各个函数引用它：

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

这里 sum 和 avg 共用窗口 w 的分区与排序规则。

更详细的语法与行为可继续查看 窗口函数表达式、窗口函数列表、窗口函数处理以及 SELECT 参考。