窗口函数：不折叠行的聚合

准备中…

窗口函数和 GROUP BY 都做聚合，区别只有一句话：GROUP BY 把多行折叠成一行，窗口函数保留每一行，只是额外算出一个值。

从一个 GROUP BY 做不到的需求开始

「列出用户 42 的每一笔订单，同时显示这笔订单占他总消费的百分比。」

用 GROUP BY 你会卡住——分组之后单笔订单就没了。窗口函数不折叠行：

SELECT id,
     created_at::date,
     amount,
     count(*) OVER (PARTITION BY user_id)                          AS 总单数,
     sum(amount) OVER (PARTITION BY user_id)                       AS 总消费,
     round(amount / sum(amount) OVER (PARTITION BY user_id) * 100, 1) AS 占比
FROM orders
WHERE user_id = 42
ORDER BY created_at;

⌘/Ctrl + Enter

OVER (PARTITION BY user_id) 的意思是：对当前行所属的那个 user_id 分区做聚合，但不要把行合并掉。

三件事：分区、排序、框架

OVER (...) 里最多写三样东西：

OVER (
  PARTITION BY user_id      -- ① 分区：按什么切分
  ORDER BY created_at       -- ② 排序：分区内怎么排
  ROWS UNBOUNDED PRECEDING  -- ③ 框架：当前行能"看到"分区里的哪几行
)

前两个直觉，第三个是绝大多数 bug 的来源。先看排序带来的能力。

排名与取第 N 名

SELECT user_id, created_at::date, amount
FROM (
SELECT user_id, created_at, amount,
       row_number() OVER (PARTITION BY user_id ORDER BY created_at, id) AS rn
FROM orders
) t
WHERE rn = 2
ORDER BY user_id
LIMIT 5;

⌘/Ctrl + Enter

「每个用户第 2 次下单的时间」——阶段目标里的那道题。注意必须套子查询，因为 WHERE 早于窗口函数执行。

排名函数有三个，差别只在并列时：

函数	并列时	序列
`row_number()`	强行分先后	1, 2, 3, 4
`rank()`	并列同名次，之后跳号	1, 2, 2, 4
`dense_rank()`	并列同名次，不跳号	1, 2, 2, 3

WITH t(name, score) AS (
VALUES ('a', 90), ('b', 85), ('c', 85), ('d', 70)
)
SELECT name, score,
     row_number() OVER (ORDER BY score DESC) AS row_number,
     rank()       OVER (ORDER BY score DESC) AS rank,
     dense_rank() OVER (ORDER BY score DESC) AS dense_rank
FROM t;

⌘/Ctrl + Enter

看邻居：lag 与 lead

SELECT id,
     created_at::date,
     created_at::date - lag(created_at::date)
       OVER (PARTITION BY user_id ORDER BY created_at) AS 距上次天数
FROM orders
WHERE user_id = 42
ORDER BY created_at;

⌘/Ctrl + Enter

第一行是 NULL（没有上一行）。这类「相邻行做差」的需求，不用窗口函数就得自连接，代价高一个数量级。

框架：那个人人踩过的坑

框架决定当前行做聚合时能看到分区里的哪些行。当你写了 ORDER BY 却没写框架时，Postgres 用的默认值是：

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

关键在 RANGE 这个词。它按排序键的值划定边界，而不是按行——意味着排序键相同的行会被一起算进来。

WITH t(k, v) AS (
VALUES (1, 10), (1, 20), (2, 30), (2, 40), (3, 50)
)
SELECT k, v,
     sum(v) OVER (ORDER BY k)                          AS 默认_RANGE,
     sum(v) OVER (ORDER BY k ROWS UNBOUNDED PRECEDING) AS 显式_ROWS
FROM t;

⌘/Ctrl + Enter

看第一、二行：k 都是 1。

默认_RANGE 两行都算出 30——因为 k=1 的所有行属于同一个「peer group」，边界一次性推到该组末尾。
显式_ROWS 算出 10 和 30——严格按物理行累加。

如果你想要的是「累计和」，RANGE 给你的结果在有并列值时就是错的。

正确的累计消费：

SELECT id,
     created_at::date,
     amount,
     sum(amount) OVER (
       PARTITION BY user_id
       ORDER BY created_at, id
       ROWS UNBOUNDED PRECEDING
     ) AS 累计消费
FROM orders
WHERE user_id = 42
ORDER BY created_at, id;

⌘/Ctrl + Enter

框架还能开窗口，比如「前后各一行的移动平均」：

SELECT id,
     amount,
     round(avg(amount) OVER (
       PARTITION BY user_id
       ORDER BY created_at, id
       ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
     ), 2) AS 三点移动平均
FROM orders
WHERE user_id = 42
ORDER BY created_at, id;

⌘/Ctrl + Enter

顺带一提：FILTER 比 CASE WHEN 好

聚合的条件统计，很多人写 sum(CASE WHEN ... THEN 1 ELSE 0 END)。Postgres 有专门语法：

SELECT country,
     count(*)                                            AS 用户数,
     count(*) FILTER (WHERE created_at >= '2024-06-01')  AS 六月后注册,
     count(*) FILTER (WHERE created_at <  '2024-06-01')  AS 六月前注册
FROM users
GROUP BY country
ORDER BY country;

⌘/Ctrl + Enter

FILTER 可读性更好，且能用在任何聚合函数上（包括窗口函数的 OVER 之前）。

本节验收

先自己回答，再点开对照。

用一句话说清窗口函数和 GROUP BY 的区别。

GROUP BY 把多行折叠成一行，窗口函数保留每一行、只是额外算出一个值。

所以凡是「既要明细又要聚合值」的需求，都是窗口函数的场景——本节开头那个「每笔订单占用户总消费的百分比」就是典型。

常见错误：以为窗口函数只是 GROUP BY 的另一种写法，两者可以互相替代。它们的输出行数就不同——十笔订单经过 GROUP BY user_id 只剩一行，经过窗口函数还是十行。真要说替代关系，是「窗口函数能做 GROUP BY 做不到的事」，反过来不成立。

为什么「取每个分区的第一名」必须套一层子查询？

因为 窗口函数在 WHERE 之后执行。SQL 的逻辑执行顺序是 FROM → WHERE → GROUP BY → 窗口函数 → SELECT → ORDER BY，写 WHERE row_number() OVER (...) = 1 时，row_number() 还没算出来。

所以只能先在子查询里把 rn 算出来，外层再过滤。

常见错误：以为报错是语法不支持，于是改成 HAVING。HAVING 同样早于窗口函数执行，一样不行。唯一的出路是分两层——或者干脆换成 DISTINCT ON / LATERAL（详见下一节）。

rank() 和 dense_rank() 的区别是什么？

都在并列时给相同名次，区别在之后跳不跳号：

rank() → 1, 2, 2, 4（跳过 3）
dense_rank() → 1, 2, 2, 3（不跳）

再加上 row_number() → 1, 2, 3, 4（强行分先后，并列时谁在前不确定）。

常见错误：用 row_number() 做排名展示。数据里一旦出现并列，两个同分的人会被分出先后，而且每次执行的先后可能不同——因为没有 tiebreaker 时排序是不稳定的。排名用 rank()/dense_rank()，row_number() 只用于「每组取一个」这类需要唯一编号的场景。

写累计和时为什么必须显式写 ROWS？不写会在什么数据上出错？

因为写了 ORDER BY 却不写框架时，默认是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。RANGE 按排序键的值划边界，排序键相同的行会被当成一个整体一次性算完。

所以在排序键有并列值的数据上，RANGE 会让并列的几行都得到相同的、已经包含了彼此的累计值，而不是逐行递增。

常见错误：在测试数据上验证通过就认为没问题。这个坑最阴险的地方是——如果测试数据恰好没有并列值，RANGE 和 ROWS 结果完全一致。它只在真实数据出现同一时间戳、同一分数时才暴露。判断标准很简单：要「逐行累加」就写 ROWS，要「按值分组结算」才用 RANGE。