PostgreSQL中窗口函数如何替代复杂GROUP BY

窗口函数的存在，说白了就是为了弥补 GROUP BY 的先天不足。GROUP BY 一出手就把多行揉成一行，但你很多时候想要的是：行数据完整保留，同时还能算个分组内的排名、累计值什么的。比如，用 ROW_NUMBER() 取每组最新记录，或者用 SUM() OVER() 高效地做累计统计。这些活儿要是硬让 GROUP BY 来干，不仅逻辑容易出岔子（比如那个烦人的 Expression of SELECT list is not in GROUP BY clause 报错），性能上还可能陷入子查询的泥潭，怎么调都别扭。

首先得明确一个概念：ROW_NUMBER()、RANK()、SUM() OVER() 这些窗口函数，与其说是为了“替代” GROUP BY，不如说是在解决 GROUP BY 根本搞不定的事。核心矛盾在于，你既想保留每条原始记录的完整性，又想在同一份数据上计算分组内的排名、累计值或者前后行的差值。

非要用 GROUP BY 去硬套这些场景，结果往往很尴尬。轻则语法报错，重则查询效率低下，逻辑也绕。一旦需要加个新条件，可能得把之前写的三层嵌套子查询全部重来一遍，维护成本极高。

为什么 GROUP BY 搞不定“每组取最新一条”

这个问题很经典。就拿 MySQL 8.0 和 PostgreSQL 来说，它们默认都会拒绝下面这种看似聪明的写法：

SELECT user_id, order_id, MAX(created_at) FROM orders GROUP BY user_id;

真正可控的做法，是用窗口函数先打好标记，再在外层精确过滤：

• 用 ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) 给每个用户按时间倒序排个号。
• 然后在外层的 WHERE 条件里加一句 WHERE rn = 1，就能精准地拿到每个用户最新那条完整的订单记录，一劳永逸。
• 如果业务场景允许并列（比如两个订单恰好在同一秒创建），那就别用 ROW_NUMBER() 了，改用 RANK() 或 DENSE_RANK()，免得漏掉并列项。

用 SUM() OVER() 替代关联子查询做累计统计

需要查每个订单对应的“用户历史总消费”，很多人的第一反应是写关联子查询：

SELECT o1.*,
    (SELECT SUM(amount) FROM orders o2 WHERE o2.user_id = o1.user_id AND o2.created_at <= o1.created_at) AS cum_amount
FROM orders o1;

窗口函数写起来清爽，执行起来也高效：

SELECT *,
       SUM(amount) OVER (PARTITION BY user_id ORDER BY created_at ROWS UNBOUNDED PRECEDING) AS cum_amount
FROM orders;

• ROWS UNBOUNDED PRECEDING 的意思很明确：从分组的第一行开始，一直累加到当前行。
• 如果在 (user_id, created_at) 上建了合适的索引，这个查询甚至可以直接走索引扫描，省掉一次显式的排序，性能表现很亮眼。
• 有一点要注意：如果字段里含有 NULL，SUM() 会直接忽略它们。如果你需要保留 NULL 行的位置（比如计数），用 COUNT(*) OVER(...) 会更稳妥。

用 LAG() 和 LEAD() 替代自连接计算时间差或环比

要查询“每个用户两次登录之间的时间间隔”，传统思路是用自连接：写个 JOIN，配上 ON 条件，再来个 WHERE 过滤，逻辑绕，性能差，还容易漏掉边界情况。

用窗口函数，一行就搞定：

SELECT user_id, login_time,
       login_time - LAG(login_time) OVER (PARTITION BY user_id ORDER BY login_time) AS gap
FROM logins;

• LAG(login_time, 1) 默认就是取前一行。你可以显式地写成 LAG(login_time, 1, '1970-01-01'::timestamp) 来设定一个默认值，这样第一行就不会是 NULL 了。
• 如果需要跨多行对比（比如对比两周前的数据），把第二个参数改成 7 或 14 就行，比写一串复杂的 LEFT JOIN 要清晰得多。
• 还得提醒一下：ORDER BY 子句必须明确且稳定，否则 LAG/LEAD 的结果根本不可控。对于时间字段，最好加上 NULLS LAST，防止空值打乱顺序。

窗口帧（frame clause）选 ROWS 还是 RANGE？

这是个很容易踩坑的地方。选错了，结果可能就偏了。举个例子，算移动平均：

SUM(amount) OVER (ORDER BY created_at ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

而 RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW 的含义则完全不同，它取的是时间跨度在 7 天之内的所有行。就算某一天没有任何数据，它也不会用空行去凑，而是逻辑上忽略它。

• 在做时间序列分析时，RANGE 更符合业务直觉。但有个前提：ORDER BY 的字段类型必须支持区间运算，比如 timestamp 或 integer。
• ROWS 的计算更直接，性能也稍好一些，适合那些固定行数的滑动窗口场景，比如算 Top N、滚动计数。
• 需要特别注意的是，在 PostgreSQL 里，如果你对 text 或 jsonb 字段用了 RANGE，它并不会报错，而是会静默地退化为 ROWS 的行为。这个细节很容易被忽略，导致结果跟预期不符。

说一千道一万，实际用的时候，别光盯着那几个函数名。背后真正的关键，是 PARTITION BY 是否跟索引匹配，ORDER BY 有没有处理好 NULLS，帧子句的定义是否精确。把这些细节拿捏住了，性能和结果的正确性才能稳稳落地。