PostgreSQL 窗口函数实战

窗口函数(Window Function)是 SQL 里被严重低估的能力。很多开发者一遇到"每个分类的 Top 3"、"累计销售额"、"同环比对比",第一反应是写 GROUP BY 嵌子查询,或者干脆把数据拉到应用层用 Python/Java 循环算。结果是 SQL 又长又慢,逻辑还容易写错。

其实这些问题,一条窗口函数就能搞定。本文把窗口函数从语法到实战讲透,看完你能在生产 SQL 里直接用起来。

一、先看 GROUP BY 到底哪里不够用

假设有一张订单表:

CREATE TABLE orders (
    id          BIGSERIAL PRIMARY KEY,
    user_id     BIGINT NOT NULL,
    amount      NUMERIC(12,2) NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL
);

产品同学说:"我想看每个用户的总消费,同时保留每笔订单的明细。"

用 GROUP BY 你会写出这种东西:

SELECT user_id, SUM(amount) AS total
FROM orders
GROUP BY user_id;

结果是每个用户只剩一行——明细没了。要保留明细,只能再 JOIN 回去:

SELECT o.*, t.total
FROM orders o
JOIN (
    SELECT user_id, SUM(amount) AS total
    FROM orders
    GROUP BY user_id
) t ON t.user_id = o.user_id;

能跑,但丑。如果再来个"每个用户消费排名前 3 的订单",GROUP BY 就彻底没辙了,得靠自连接或者把数据拉出去算。

窗口函数就是为了解决这个矛盾:既要做跨行计算,又不要把行合并掉。

二、核心语法:OVER 的三要素

窗口函数的通用形态:

函数(参数) OVER (
    PARTITION BY <分组列>
    ORDER BY     <排序列>
    ROWS BETWEEN <起点> AND <终点>
)

理解三个要素就抓住了全部:

• PARTITION BY:像 GROUP BY 一样分组,但不合并行。把表切成若干"窗口",每个窗口独立计算。
• ORDER BY:在组内排序。注意它会改变聚合的语义——没有 ORDER BY 时,SUM 算整组;有了 ORDER BY,默认变成"从组首累加到当前行"。
• ROWS BETWEEN(帧):定义每行计算时"往回看几行、往后看几行"。这是移动平均、滑动求和的关键。

三者都是可选的。最简单的 SUM(amount) OVER () 就是"全局总和,贴在每一行后面"。

三、排名三剑客:ROW_NUMBER / RANK / DENSE_RANK

最常见的诉求是"每组 Top N"。三个函数处理并列的方式不同:

SELECT
    user_id,
    amount,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rn,
    RANK()       OVER (PARTITION BY user_id ORDER BY amount DESC) AS rnk,
    DENSE_RANK() OVER (PARTITION BY user_id ORDER BY amount DESC) AS drnk
FROM orders;

• ROW_NUMBER():无脑 1、2、3、4,并列也给不同号。
• RANK():并列同号,后面跳过(1、2、2、4)。
• DENSE_RANK():并列同号,后面不跳(1、2、2、3)。

做"每组 Top 3"的标准姿势是套一层子查询过滤:

SELECT * FROM (
    SELECT o.*,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
    FROM orders o
) t
WHERE rn <= 3;

这是面试和实战的高频题。记住一句话:窗口函数不能直接写在 WHERE 里,必须包一层子查询——因为窗口函数的执行顺序在 WHERE 之后。

四、累计与移动聚合

加上 ORDER BY 后,SUM / AVG / COUNT 会变成"从组首到当前行"的累计:

SELECT
    created_at::date AS d,
    amount,
    SUM(amount) OVER (ORDER BY created_at) AS running_total
FROM orders;

这就是累计销售额。

要做"3 日移动平均",用帧显式控制范围:

SELECT
    created_at::date AS d,
    amount,
    AVG(amount) OVER (
        ORDER BY created_at::date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS ma3
FROM orders;

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW 表示"当前行加上前两行"。帧的边界还有 UNBOUNDED PRECEDING(组首)、UNBOUNDED FOLLOWING(组尾)、CURRENT ROW。带 ORDER BY 时,默认帧就是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW,这也是为什么"加上 ORDER BY 就自动累加"。

顺带提一个易混点:ROWS 按物理行数算,RANGE 按排序值算(同值的行会被一起纳入)。日常 95% 的场景用 ROWS,行为可预测。

五、偏移:LAG / LEAD 做同环比

LAG 取当前行之前的数据,LEAD 取之后,天然适合做环比同比:

SELECT
    created_at::date AS d,
    amount                                  AS today,
    LAG(amount, 1) OVER (ORDER BY created_at) AS yesterday,
    amount - LAG(amount, 1) OVER (ORDER BY created_at) AS diff,
    ROUND(
        (amount - LAG(amount, 1) OVER (ORDER BY created_at))
        / NULLIF(LAG(amount, 1) OVER (ORDER BY created_at), 0) * 100,
        2
    ) AS growth_pct
FROM orders;

第二个参数是偏移行数(默认 1),第三个参数是缺省值(行不存在时填什么)。NULLIF 防除零是必须的工程细节,别省。

对比上一年同月,把 LAG(amount, 1) 换成 LAG(amount, 12) OVER (ORDER BY month) 就行。比起自连接 JOIN orders o2 ON o2.month = o.month - 12,干净太多,也不容易写错。

六、首尾与分位:FIRST_VALUE / NTILE / PERCENT_RANK

-- 每个用户第一笔订单金额
SELECT DISTINCT user_id,
    FIRST_VALUE(amount) OVER (
        PARTITION BY user_id ORDER BY created_at
    ) AS first_amount
FROM orders;

-- 按消费金额分成 4 档(四分位)
SELECT user_id, amount,
    NTILE(4) OVER (ORDER BY amount) AS quartile
FROM orders;

-- 在组内的百分位排名
SELECT user_id, amount,
    PERCENT_RANK() OVER (PARTITION BY user_id ORDER BY amount) AS pct
FROM orders;

LAST_VALUE 有个经典坑——默认帧只到当前行,所以"组内最后值"必须显式写 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING,否则永远返回当前行自己。这个坑踩过一次就记住了。

七、窗口函数 vs GROUP BY:本质区别

一句话:GROUP BY 把 N 行压成 1 行,窗口函数让 N 行都还在、各自带一个结果。

记住一个判断标准:你要的答案如果"每一行都得有一个值",基本就该上窗口函数了。反之只要"每组一个汇总值",GROUP BY 就够。

八、性能:一次扫描的胜利

窗口函数在执行计划里通常只扫一遍数据,然后按 PARTITION BY 列排序后流式计算。对比自连接(JOIN ... ON a.user_id = b.user_id AND a.id >= b.id)那种容易被优化成嵌套循环、近似 O(N²) 的行为,窗口函数常常快一个数量级。

大数据量下的几条实战经验:

1. 给 PARTITION BY + ORDER BY 的组合建复合索引。比如 PARTITION BY user_id ORDER BY created_at DESC 对应 CREATE INDEX ON orders(user_id, created_at DESC),执行计划里能直接走索引有序扫描,省掉 Sort 节点。
2. 别滥用窗口函数。问题本质是纯聚合时(只要每组一个结果),老老实实 GROUP BY 通常更快更省内存——窗口函数还得为每行保留状态。
3. 用 EXPLAIN ANALYZE 验证。看到 WindowAgg 前面挂了高代价的 Sort,基本就是索引没命中。
4. 大窗口注意内存。帧越大、分区越大,内存压力越高。适当调大 work_mem 能避免落盘,否则 sort/write file 会让性能断崖。

九、三个实战案例

案例 1:每个用户最近 3 笔订单

SELECT * FROM (
    SELECT o.*,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
    FROM orders o
    WHERE created_at >= NOW() - INTERVAL '90 days'
) t
WHERE rn <= 3;

注意先 WHERE 再窗口——过滤前置能大幅减少排序的数据量,这是常见的优化手段。

案例 2:每日累计 PV

SELECT
    day,
    pv,
    SUM(pv) OVER (ORDER BY day) AS cumulative_pv
FROM daily_pv;

案例 3:同环比一张表搞定

WITH monthly AS (
    SELECT date_trunc('month', created_at) AS mon,
           SUM(amount) AS total
    FROM orders
    GROUP BY 1
)
SELECT mon,
    total,
    LAG(total, 1)  OVER (ORDER BY mon) AS prev_month,   -- 环比
    LAG(total, 12) OVER (ORDER BY mon) AS prev_year     -- 同比
FROM monthly;

GROUP BY 和窗口函数配合:先用 GROUP BY 算出月度汇总,再用 LAG 做时间偏移。这是报表系统的经典组合拳,两者不是替代关系,而是协作。

小结

• 窗口函数 = 跨行计算 + 不合并行,这是它和 GROUP BY 的本质分界。
• 三要素:PARTITION BY 分组、ORDER BY 排序且会改变聚合语义、ROWS BETWEEN 定义帧。
• 五大场景:排名、累计/移动、偏移同环比、首尾取值、分位分档,每个都有对应函数。
• 不能直接进 WHERE,要过滤窗口结果就包一层子查询。
• 性能通常优于自连接,大数据量给 PARTITION + ORDER BY 列建复合索引。

金句:GROUP BY 看的是"组的脸",窗口函数看的是"行的上下文"——同样是聚合,一个压扁数据,一个放大视野。

掌握窗口函数后,你会发现过去写的很多丑陋子查询、应用层 for 循环、自连接 JOIN,都可以删掉重写成一条 SQL。这不仅是代码量的问题,更是思维方式从"行级"升级到"窗口级"——一旦习惯,你就再也回不去了。