窗口函数 — 不折起来也能往旁边看

一句话总结

窗口函数无需折叠行，就能让当前行引用它周围的其他行。排名、累计、与前一值比较等仅靠聚合难以回答的问题，可以在一次扫描中解决。但必须准确知道窗口（分区）和帧（frame）是什么再使用，因为有些默认值与直觉不同。

为什么需要它

如果只用 GROUP BY 求“每个类别价格最高的 3 件商品”，就需要自连接或相关子查询。做法是对每件商品统计“同一类别中比我贵的商品有几件”，代码变长，表也要读好几遍。“环比销售额增减”也类似：要把月度汇总与自身错开一个月做连接，还得另外处理第一个月和空缺的月份。

这两个问题的共同点是要在保留每一行的同时看到旁边的行。聚合会把行折叠掉，做不到这一点。如果每一行都能知道自己在组内排第几、前一行的值是什么，问题就简单了。窗口函数正好填补了这个位置。

它如何工作

窗口函数采用 func() OVER (PARTITION BY ... ORDER BY ... frame) 的形式。

PARTITION BY 是划分窗口的标准。它与 GROUP BY 相似，但不会折叠行。省略时，整个结果是一个窗口。
ORDER BY 是窗口内的顺序，是排名与累计的依据。
帧决定计算当前行时要看到窗口中的哪些行。sum、avg、last_value 这类会查看多行的函数都受它影响。

计算时机也要了解。窗口函数在 WHERE、GROUP BY、HAVING 全部完成之后才计算，由此带来两点。第一，窗口函数的结果不能在同一查询的 WHERE 中使用。要按排名过滤，就得用子查询或 CTE 包一层，在外层过滤。第二，聚合先完成，所以可以把聚合放进窗口函数的参数里。sum(sum(total_amount)) OVER () 先得出各组合计，再把这些合计的总和附到每一行上。这就是计算占比时不必执行两次查询的原因。

SELECT category, id, price
FROM (
  SELECT category, id, price,
         row_number() OVER (PARTITION BY category ORDER BY price DESC, id) AS rn
  FROM products
) t
WHERE rn <= 3;

常用函数可以这样区分。

函数	作用	并列与边界
`row_number()`	从 1 开始编号	并列也给不同编号，谁在前由排序依据决定
`rank()`	排名	并列排名相同，后续名次跳号（1,1,3）
`dense_rank()`	排名	并列排名相同，后续名次连续（1,1,2）
`sum() OVER (ORDER BY ...)`	累计和	默认帧会包含并列的行
`lag()` / `lead()`	前一行／后一行的值	不存在时返回默认值，省略则为 NULL
`ntile(n)`	1~n 的分组编号	尽可能均匀地划分，各组大小最多相差 1

最需要小心的是默认帧。有 ORDER BY 时，默认帧是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，它的意思不是“从分区开头到当前行”，而是“从开头到与当前行排序值相同的最后一行”。没有 ORDER BY 时，所有行彼此并列，帧就成了整个分区。

实际项目中会出什么问题

累计和像台阶一样跳。 按下单时间计算累计销售额，同一时刻的两笔订单却得到完全相同的累计值。因为默认帧会把并列的行一并包含。要一行一行地累加，就要明确写出 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，并在排序依据中加入 id 之类的决胜列。

last_value 返回的是当前行。 想要分区的最后一个值，但默认帧止于当前行，于是得到的是当前行（或与之并列的行）的值。PostgreSQL 文档也提醒这种组合容易得出无用的结果。可以用 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 扩大帧，或者把排序反过来改用 first_value。

首单每次运行都不一样。 用 row_number() ... ORDER BY ordered_at 选出每位客户的首单，如果同一时刻有两笔订单，哪一笔排第 1 是不确定的。今天对，明天可能就是另一个答案。要不断补充列，直到排序依据唯一为止。

lag() 跳过了空缺的月份。 没有销售额的月份在聚合结果中根本没有这一行，所以 3 月的 lag() 返回的是 1 月的值，而结果上写的却是“环比”。如果空缺月份也要比较，就先用 generate_series 生成日历，再做 LEFT JOIN。

WHERE 改变了分母。 窗口函数在 WHERE 之后计算，所以先用 WHERE 只保留某些状态再求占比，得到的是“剩余行中的占比”。需要相对全体的占比时，应在过滤之前计算，再在外层过滤。

取整后的占比加起来不是 100。 把各渠道占比分别四舍五入到小数点后两位，合计可能是 99.99 或 100.01。这不是计算错误，而是舍入误差。如果报表要同时给出合计，就把出现差异的原因写清楚。

如何确认

在相信结果之前，用查询检查不变量。

-- every partition has exactly one row numbered 1
SELECT count(*) FILTER (WHERE rn = 1) = count(DISTINCT category) FROM v_ranked_products;

-- the last running total equals the plain total
SELECT max(cum_revenue) = sum(revenue) FROM v_running_revenue;

-- the first month has no previous value, the others do
SELECT count(*) FILTER (WHERE prev_revenue IS NULL) FROM v_mom;

-- bucket sizes differ by at most one
SELECT quartile, count(*) FROM v_price_quartile GROUP BY quartile ORDER BY 1;

也可以通过执行计划来确认。加上 EXPLAIN，窗口函数会显示为 WindowAgg 节点，下面通常有按 PARTITION BY 和 ORDER BY 排序的 Sort。如果使用了多个排序依据互不相同的 OVER 子句，还能看到 Sort 和 WindowAgg 相应地层层叠加。大表上的窗口查询很慢时，先看这些排序。

下一个实验要做什么

先在前一篇理论课对应的聚合实验中完成 GROUP BY、HAVING、FILTER 和月度汇总，然后在窗口函数实验中依次实现各类别编号、两种排名的比较、累计销售额、环比、各渠道占比、每组前 3 名、价格四分位和每位客户的首单。把上面的确认查询直接运行在实验中创建的视图上，就能在评分之前自行验证答案。