PostgreSQL 窗口函数实战：row_number 去重、滑动窗口与执行计划优化

分类：数据库与中间件 · 作者：管理员 · 发布于 2026-10-12 · 阅读 0 · 标签：PostgreSQL / 窗口函数 / SQL优化 / row_number / 数据分析

一、业务痛点：从 MySQL 迁移到 PostgreSQL 后，分组 TOP N 怎么写

很多团队从 MySQL 迁到 PostgreSQL 后，第一批被问倒的 SQL 就是「分组取最新一条」「算环比增长率」「求累计金额」。MySQL 8 之前只能靠子查询 + 自连接硬啃，写出来的 SQL 又长又慢；而 PostgreSQL 原生支持窗口函数，一条 row_number() over() 就能把这类问题写得干净利落。

本文不讲概念定义，直接上三个真实场景：订单去重、销售环比、累计与移动平均，最后讲执行计划与三个高频坑。示例表很简单：

-- 订单表
CREATE TABLE orders (
    id         bigserial PRIMARY KEY,
    user_id    bigint  NOT NULL,
    amount     numeric(12,2) NOT NULL,
    status     varchar(16) NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);

-- 日销售汇总表（用于环比/移动平均演示）
CREATE TABLE daily_sales (
    sale_date date PRIMARY KEY,
    amount    numeric(12,2) NOT NULL
);

二、窗口函数基础：OVER 子句只做三件事

窗口函数 = 聚合/排序函数 + OVER 子句。OVER 里就三件配置：

PARTITION BY：按什么分组，等价于 GROUP BY 的分组键；
ORDER BY：组内按什么排序，决定窗口函数计算的顺序；
窗口帧（frame）：在当前行前后划一个滑动区间，默认是「从分区起点到当前行」。

与 GROUP BY 最大的区别：窗口函数不合并行，每一行明细都会保留，只是多算出一列结果。这决定了它天然适合「既要明细、又要聚合值」的场景。

三、实战一：row_number 分组去重，取每个用户最新订单

需求：查询每个用户最近一笔成功订单。传统写法要自连接 + 子查询，窗口函数一行搞定：

SELECT id, user_id, amount, status, created_at
FROM (
    SELECT o.*,
           row_number() OVER (
               PARTITION BY user_id
               ORDER BY created_at DESC
           ) AS rn
    FROM orders o
    WHERE o.status = 'PAID'
) t
WHERE t.rn = 1;

关键点：row_number() 只负责给组内行编号，真正的筛选发生在外层 WHERE。窗口函数不能直接出现在 WHERE 里（SQL 的 WHERE 在窗口计算之前执行），所以必须套一层子查询——这是新手最容易报错的地方。

如果需求是「每组只要一条，哪条都行」，PostgreSQL 还有更快的 DISTINCT ON：

SELECT DISTINCT ON (user_id) id, user_id, amount, status, created_at
FROM orders
WHERE status = 'PAID'
ORDER BY user_id, created_at DESC;

注意 DISTINCT ON 的排序要求：排序列必须以 DISTINCT ON 的分组列开头，否则结果不符合预期且 PG 会直接报错。

四、实战二：lag/lead 算环比与移动平均

需求：统计每天销售额，并和前一天对比算出环比增长率。用 lag() 取上一行：

SELECT
    sale_date,
    amount,
    lag(amount, 1) OVER (ORDER BY sale_date) AS prev_amount,
    CASE
        WHEN lag(amount, 1) OVER (ORDER BY sale_date) IS NULL THEN NULL
        WHEN lag(amount, 1) OVER (ORDER BY sale_date) = 0 THEN NULL
        ELSE round(
            (amount - lag(amount, 1) OVER (ORDER BY sale_date))
            * 100.0
            / lag(amount, 1) OVER (ORDER BY sale_date), 2
        )
    END AS growth_rate
FROM daily_sales
ORDER BY sale_date;

坑一：首行没有前一行，lag() 返回 NULL，直接拿 NULL 做除法会整列变 NULL，必须用 CASE 或 NULLIF 兜底。

坑二：同一个表达式在 SELECT 里写三遍 lag(...)，很啰嗦。可以先把 lag 结果放子查询再算比率，代码可读性更好：

SELECT sale_date, amount, prev_amount,
       round((amount - prev_amount) * 100.0 / NULLIF(prev_amount, 0), 2) AS growth_rate
FROM (
    SELECT sale_date, amount,
           lag(amount, 1) OVER (ORDER BY sale_date) AS prev_amount
    FROM daily_sales
) t
ORDER BY sale_date;

移动平均（如 7 日 MA）则用 ROWS 窗口帧显式限定滑动区间：

SELECT sale_date, amount,
       round(avg(amount) OVER (
           ORDER BY sale_date
           ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ), 2) AS ma_7
FROM daily_sales
ORDER BY sale_date;

五、实战三：sum over 累计金额与占比

需求：统计每个用户累计消费金额，并按用户维度算金额占比。窗口聚合不合并行、每行都返回聚合结果，正好同时满足：

SELECT user_id,
       amount,
       sum(amount) OVER (
           PARTITION BY user_id
           ORDER BY created_at
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS cumulative_amount,
       round(amount * 100.0 / sum(amount) OVER (), 2) AS amount_pct
FROM orders
WHERE status = 'PAID'
ORDER BY user_id, created_at;

注意两个细节：

不带任何参数的 OVER () 表示「全表一个分区」，用于算全局占比；
sum() OVER (PARTITION BY ... ORDER BY ...) 默认帧就是「分区起点到当前行」，即累计值；如果你想要「整组总和」而不是累计，就不要写 ORDER BY。

六、执行计划优化：从 Sort 到 Index Scan

窗口函数几乎都要排序，执行计划里必然出现 WindowAgg 节点。以第一个例子为例：

EXPLAIN ANALYZE
SELECT id, user_id, amount, status, created_at
FROM (
    SELECT o.*, row_number() OVER (PARTITION BY user_id ORDER BY created_at DESC) rn
    FROM orders o
    WHERE o.status = 'PAID'
) t
WHERE t.rn = 1;

未建索引时，计划大致是：

WindowAgg  (cost=... rows=...)
  ->  Sort  (cost=...)
        Sort Key: created_at DESC
        ->  Seq Scan on orders  (cost=...)
              Filter: ((status)::text = 'PAID'::text)

优化手段按优先级：

建匹配索引：CREATE INDEX idx_orders_user_time ON orders (user_id, created_at DESC); 当索引顺序与 PARTITION BY + ORDER BY 完全一致时，PG 可以直接从索引读序，Sort 节点消失，WindowAgg 直接从 Index Scan 取数，大表下通常能省下大量 CPU 和内存；
留意 work_mem：Sort 的数据量超过 work_mem 会落盘（临时文件），EXPLAIN ANALYZE 输出里出现 Sort Method: external merge Disk 就是信号，结合实际情况调大 work_mem 或改走索引；
合并窗口节点：多个窗口函数如果 PARTITION BY / ORDER BY 完全相同，PG 会自动合并成一个 WindowAgg 节点；如果定义不同，会出现多个 WindowAgg，每多一个就多一轮排序，能统一就统一。

七、踩坑记录：三个最容易翻车的地方

默认窗口帧是 RANGE 不是 ROWS：默认帧是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。当 ORDER BY 列存在重复值时，RANGE 会把所有相同排序列的行一起纳入帧，导致移动平均、累计值「跳变」。例如按金额排序时两行金额相同，第二行会把第一行也算进「当前帧」，结果与直觉不符。需要精确滑动时，永远显式写 ROWS BETWEEN；
窗口函数不能直接出现在 WHERE / GROUP BY / HAVING：它们的执行顺序都在窗口计算之前，必须套子查询或 CTE；
ORDER BY 与 PARTITION BY 的索引顺序敏感：索引 (user_id, created_at DESC) 能支撑 PARTITION BY user_id ORDER BY created_at DESC，但换成 (created_at DESC, user_id) 就完全用不上，Sort 照旧。判断标准：索引前导列必须匹配分区键，且排序方向一致。

八、最佳实践总结

分组 TOP N 用 row_number() 子查询；只要任意一条用 DISTINCT ON，注意排序列以分组列开头；
环比 / 相邻行比较用 lag / lead，NULL 与 0 分母必须兜底；
累计、移动平均这类滑动计算，显式声明 ROWS BETWEEN ... AND ...，不要依赖默认帧；
上线前对窗口查询跑一次 EXPLAIN ANALYZE，确认没有 external merge Disk、WindowAgg 尽量只有一个；
窗口函数和 GROUP BY 互补：要明细 + 聚合列用窗口，只要聚合行用 GROUP BY，别混着用出隐蔽 Bug。