39 窗口函数

对行进行排名，并计算累计值、相邻值和窗口帧。

窗口函数使用一个窗口为每一行计算一个值——窗口由 OVER (...) 子句定义，是一组相关行。GROUP BY 会折叠行，而窗口函数会在原始列旁边为每一行添加一个结果。排名、累计值、移动平均、百分位区间、与“上一行”比较等场景，都适合使用窗口函数；凡是过去需要在应用代码中通过排序和迭代完成的任务，窗口函数都可以胜任。

本章要点

窗口函数的形式：f() OVER (PARTITION BY ... ORDER BY ...)
最有用的函数：row_number、rank、dense_rank、lag、lead、sum、avg、first_value、last_value
OVER 内的分区与排序
使用 WINDOW name AS (...) 复用窗口
窗口帧子句：ROWS BETWEEN ... AND ...

示例

示例 39.1 窗口函数

-- Numbering rows within each partition
SELECT customer_id, id AS order_id, total,
       row_number() OVER (PARTITION BY customer_id ORDER BY total DESC) AS rn,
       rank()       OVER (PARTITION BY customer_id ORDER BY total DESC) AS rnk
FROM orders_example
ORDER BY customer_id, rn;

-- "Most expensive order per customer" — keep rn = 1
WITH ranked AS (
  SELECT *,
         row_number() OVER (PARTITION BY customer_id ORDER BY total DESC) AS rn
  FROM orders_example
)
SELECT customer_id, id, total FROM ranked WHERE rn = 1;

-- lag / lead: look at previous / next row in the partition
SELECT customer_id, id, total,
       lag(total)  OVER (PARTITION BY customer_id ORDER BY id) AS prev_total,
       lead(total) OVER (PARTITION BY customer_id ORDER BY id) AS next_total
FROM orders_example
ORDER BY customer_id, id;

-- Running total (a sum is also a window function)
SELECT customer_id, id, total,
       sum(total) OVER (PARTITION BY customer_id ORDER BY id) AS running_total
FROM orders_example
ORDER BY customer_id, id;

-- Reusable window
SELECT id, total,
       sum(total) OVER w AS running,
       avg(total) OVER w AS running_avg
FROM orders_example
WINDOW w AS (PARTITION BY customer_id ORDER BY id)
ORDER BY customer_id, id;

-- Frame clause: 3-row trailing average
SELECT id, total,
       avg(total) OVER (
         PARTITION BY customer_id
         ORDER BY id
         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) AS trailing_avg
FROM orders_example
ORDER BY customer_id, id;

窗口由三个部分定义：

PARTITION BY ——将行拆分成相互独立的组。与 GROUP BY 类似，但每个组都会保留其中的行。
ORDER BY ——对每个分区内的行排序。它决定 row_number、lag、累计值等结果。
窗口帧子句（可选）——ROWS BETWEEN ... AND ... 或 RANGE BETWEEN ...。它定义当前行周围哪些行属于窗口。不同函数族的默认值不同：排名函数会忽略窗口帧；带有 ORDER BY 时，默认窗口帧包含从分区开头到当前行所在同等行组末尾的所有行；不带 ORDER BY 时，窗口覆盖整个分区。

row_number、rank 和 dense_rank 在存在并列时的差异很重要。row_number 总是分配 1、2、3、……，并列时任意决定先后。rank 分配 1、2、2、4（并列后有间断）。dense_rank 分配 1、2、2、3（没有间断）。

“每组取前 N 行”模式（PARTITION BY group_col ORDER BY rank_col DESC + filter rn = 1）是 row_number 的典型用法。它替代了繁琐的自连接和子查询。

示例数据

第 38 章归档了第 3 个订单。本章输出沿用该章归档后的状态，目前剩下两个订单。重新运行第 29 章会恢复原来的三个订单。

运行示例

运行方式（需要 orders_example）：

$ psql -f source/window-functions.sql postgres
 customer_id | order_id | total | rn | rnk
-------------+----------+-------+----+-----
           1 |        1 | 50.00 |  1 |   1
           1 |        2 | 30.00 |  2 |   2
(2 rows)
 ...

常见陷阱

窗口函数不能出现在 WHERE 或 HAVING 中（它们在这些子句之后计算）。请把查询包在 CTE 或子查询中，再在外层过滤。
sum() OVER (ORDER BY ...) 的默认窗口帧是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW——ORDER BY 值相等的同等行会被计入同一个累计值。若要严格按行累计，请使用 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。
如果 OVER 内没有 ORDER BY，lag / lead 会使用未指定的行顺序，PostgreSQL 也不会发出警告。请指定排序顺序和任何用于打破并列的排序键。
性能：窗口函数会对分区排序。如果在 (partition_col, order_col) 上有匹配的索引，就可以跳过排序。

提示

“显示相对于上一行的变化”可写为 total - lag(total) OVER (ORDER BY ...)。“显示占总数的百分比”可写为 100.0 * total / sum(total) OVER ()。两者都用一个易读的表达式替代自连接或子查询。

动手练习

使用 ntile(4) 把每个订单分到四分位组：ntile(4) OVER (ORDER BY total)。然后在时间序列表中，用 lag(daily_total) OVER (ORDER BY day) 计算“今天的总额与昨天的总额”。最后试试 first_value(total) OVER (PARTITION BY customer_id ORDER BY id)，看看每一行中显示的该客户首笔订单金额。

39 窗口函数

本章要点

示例

运行示例

常见陷阱

下载