卷 II · 取数CH 09深度 09/24

窗口函数：不塌行的聚合

上一章聚合的代价，是丢掉明细。但「每笔订单，外加它当天的累计」这种需求，要求你既保留明细、又能看到汇总。窗口函数就是干这个的——它聚合，却不塌行。学会它，一大批「本来要写子查询」的难题会瞬间变简单。

OVERPARTITION BY累计排名

关键区别：GROUP BY 塌行，窗口不塌

先把这一句刻进脑子：窗口函数和聚合函数用的是同一批函数（sum、count、avg、rank……），区别只在一个 OVER 关键字。

-- 聚合：塌行。5 行流水塌成 1 行总和
SELECT sum(amount) FROM sales;

-- 窗口：不塌行。5 行还是 5 行，每行旁边多一列「到此为止的累计」
SELECT id, book, amount,
       sum(amount) OVER (ORDER BY id) AS running_total
FROM sales;

加了 OVER (...)，sum 就从「把整张表塌成一个总和」变成了「为每一行，算一个它所在窗口里的和」。结果的行数和原表一模一样——明细一行不少，只是每行多贴了一个汇总值。这就是它的魔力。

▶ 动手 · 四个窗口函数

下面这台真引擎在同一批销售数据上，切换四种最常用的窗口函数。注意无论选哪个，结果永远是 5 行——原来几行，还是几行。

盯着 sum() running 那一档：累计值沿 id 一路加上去，45 → 123 → 148 → 187 → 322（这串数字是 demo 当场算的）。第一行就是它自己 45；第二行是 45+78=123；……最后一行 322，正好等于所有 amount 的总和。但你依然看得见每一笔明细——这是 GROUP BY 给不了的。

OVER 括号里的三件事

OVER (...) 括号里最多有三个部分，共同定义「每一行的窗口是哪些行」：

  • PARTITION BY：把数据分区，窗口不跨区。像 GROUP BY 一样分组，但不塌行——每个分区内部各算各的。比如 PARTITION BY genre 就是「在每种类型内部」算排名 / 累计。
  • ORDER BY：在分区内定义顺序。累计、排名、lag 都依赖顺序——「累计到此为止」的「此」，就是 ORDER BY 排出来的位置。
  • 帧（frame）：更精细地圈定「窗口具体到哪几行」，比如 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW（当前行 + 前两行，做移动平均）。不写帧时有个默认帧，下面就是坑。

两类窗口函数，脾气不同

窗口函数大致分两类，用途和心智模型不一样：

函数算什么典型用途
row_number()行在分区内的序号（1,2,3…，绝不并列）去重取一行、分页
rank()排名，并列同名次，之后跳号（1,1,3）比赛排名
dense_rank()排名，并列同名次，之后不跳号（1,1,2）密集排名
lag(x) / lead(x)取前一行 / 后一行的 x 值同比、环比、差值
sum/avg/count OVER窗口内的聚合（累计、移动平均）累计额、滑动窗口

lag 尤其好用。想算「每笔订单比上一笔多卖了多少」，一个 lag 搞定，不用自连接：

SELECT id, amount,
       amount - lag(amount) OVER (ORDER BY id) AS 环比增量
FROM sales;
-- 第一行没有「上一笔」，lag 返回 NULL，增量也是 NULL
⚠ 带 ORDER BY 的默认帧，可能不是你以为的

一旦 OVER 里写了 ORDER BY，默认帧是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW——「从分区开头到当前行」。这正是累计想要的。但注意是 RANGE 不是 ROWS：RANGE 会把「和当前行 ORDER BY 值相同」的行一并算进当前行的窗口。如果排序列有重复值（比如按天排序、同一天多笔），累计到那天时会把当天所有行都算上、给出同一个值。要「严格逐行累计」，显式写 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。这个 RANGE/ROWS 的坑，是窗口函数里最隐蔽的一个。

它替你消灭的那些子查询

窗口函数最大的价值，是把一类本来要写相关子查询的难题变简单。经典例子「每种类型里最贵的一本书」，没有窗口函数时要写相关子查询或 self join，有了它：

SELECT * FROM (
  SELECT title, genre, price,
         row_number() OVER (PARTITION BY genre ORDER BY price DESC) AS rn
  FROM books
) t
WHERE rn = 1;   -- 每个 genre 分区里排第一的那行

在每种 genre 里按价格降序编号，取 rn = 1——每组最贵的一本。（第 5 章的 DISTINCT ON 是 PostgreSQL 对这个模式的一个更短的特供写法，两者都值得会。）「组内 top-N」「组内排名」「同比环比」这些报表刚需，全靠窗口函数。

⇄ 对照 MySQL
  • MySQL 直到 8.0（2018 年）才有窗口函数。在那之前，上面这些需求要用变量黑魔法（@rank := @rank + 1）或多重子查询硬凑，又难写又容易错。如果你对窗口函数陌生，很可能就是被老 MySQL 耽误了——PostgreSQL 从 8.4（2009 年）就支持了。
  • MySQL 8.0 之后，窗口函数语法与 PostgreSQL 基本一致（都是标准 SQL），OVER / PARTITION BY / rank / lag 都能用，这一处终于拉平了。
  • PostgreSQL 额外支持 WINDOW 子句给窗口起别名复用，以及更完整的帧语法（如 GROUPS 模式、EXCLUDE 选项），比 MySQL 更全。

这一章的一句话

窗口函数 = 聚合但不塌行：OVER 里的 PARTITION BY 分区、ORDER BY 定序、帧圈定范围，让每一行旁边长出一个「它那个窗口里算出的值」——累计、排名、同比全靠它，还消灭了一大批相关子查询。

卷 II 只剩最后一块拼图：当数据本身是层级或图（组织架构、评论楼中楼、物料清单），普通 JOIN 只能查一层。下一章的递归 CTE，能把整棵树一层层展开。