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

GROUP BY 与聚合：把子集塌成一行

JOIN 把行缝到一起，GROUP BY 反过来——把很多行拢成少数几行。它就两步：先按 key 把行装进桶，再让每个桶塌成一行。想清这两步，WHERE 和 HAVING 谁先谁后、为什么 SELECT 里不能乱写列，全都通了。

分组聚合HAVINGGROUP BY 的严格

分组的两步：装桶，塌行

假设有一张销售流水 sales，想知道「每本书卖了多少」。GROUP BY book 做的事，拆成两步看最清楚：

  1. 装桶：把所有行按 book 分门别类，同一本书的流水放进同一个桶。
  2. 塌行：让每个桶塌成一行。桶里可能有好几条流水，但塌完只剩一行——靠聚合函数把一桶值压成一个值（count 数个数、sum 求和、avg 求均、min/max 取极值）。
▶ 动手 · 装桶 → 塌行 → HAVING

下面这台 demo 把这两步分开演给你看：先看 5 条流水按 book 装成 3 个桶，再看每个桶塌成一行、算出订单数 / 销量 / 收入。然后打开 HAVING，看它怎么在「塌成行之后」再筛掉一些组。

结果是 demo 当场算的：Left Hand 有 2 笔订单、共卖 4 本、进账 180；Three-Body 2 笔、3 本、117；Hobbit 1 笔、1 本、25。5 条流水，塌成了 3 行——一本书一行。这就是聚合：信息被压缩了，你看不见单笔明细了，换来的是每组的汇总。（想同时保留明细又要汇总？那是下一章窗口函数的活。）

WHERE 在装桶前，HAVING 在塌行后

这是 GROUP BY 查询里最该记牢的时序。一句完整的分组查询，各子句的执行顺序不是你书写的顺序，而是：

FROM → WHERE → GROUP BY → 聚合 → HAVING → SELECT → ORDER BY → LIMIT

关键是 WHERE 和 HAVING 一前一后卡在 GROUP BY 两侧：

  • WHERE 在装桶之前筛行：它决定哪些原始流水能进桶。这里不能用聚合函数——那时候桶还没形成，sum 无从谈起。
  • HAVING 在塌成行之后筛组：它决定哪些汇总结果能留下。这里可以、且通常要用聚合函数——比如「只要总销量 ≥ 3 的书」。
SELECT book, sum(qty) AS units, sum(qty*price) AS revenue
FROM sales
WHERE price >= 30          -- 先筛行：只算单价 ≥ 30 的流水
GROUP BY book
HAVING sum(qty) >= 3       -- 再筛组：只留总销量 ≥ 3 的书
ORDER BY revenue DESC;

上面 demo 里打开 HAVING sum(qty) >= 3，Hobbit（只卖 1 本）就被灰掉了，剩下 Left Hand（4）和 Three-Body（3）两组。用一个 WHERE sum(qty) >= 3 去写会直接报错——因为 WHERE 阶段桶还不存在。记住这个时序，这类错就不会犯。

PostgreSQL 的严格：SELECT 里不能乱来

这里是本章的「当真」时刻。分组之后，SELECT 列表里只能出现两种东西：

  1. 分组的列本身（GROUP BY 里点了名的，比如 book）；
  2. 聚合函数（sum(...)、count(*) 之类）。

为什么？因为一个桶要塌成一行。book 在桶里是同一个值（正是按它分的组），塌成一行没歧义；sum(qty) 把一桶数压成一个数，也没歧义。但假如你写 SELECT book, price——一个桶里 Left Hand 的流水可能有好几个不同的 price，塌成一行时该显示哪个 price？没有正确答案。

db=# SELECT book, price FROM sales GROUP BY book;
ERROR:  column "sales.price" must appear in the GROUP BY clause
        or be used in an aggregate function
LINE 1: SELECT book, price FROM sales GROUP BY book;
                     ^

PostgreSQL 当场报错：这个问题没有确定答案，我不猜。你要么把 price 也加进 GROUP BY，要么对它用聚合（max(price)、avg(price)）明确说你想要哪个。它逼你把「你到底想要什么」说清楚。

◆ 「歧义就报错」是一种尊重

历史上的 MySQL 在这里不报错：它允许你 SELECT 一个既不分组、也不聚合的列，然后从那个桶里随便挑一行的值还给你。方便，但极其危险——你以为拿到的 price 是「这本书的价格」，其实是那一桶里某条流水的价格，哪一条完全不确定，可能随执行计划、随数据变化而变。这种「悄悄给你一个不确定的值」，正是第 1 章说的「不当真」。PostgreSQL 的报错看起来烦，实则是在替你堵住一个隐蔽的 bug。

两个让聚合更好用的东西

FILTER 子句：想在一次查询里算「总数」和「满足某条件的数」，不用写多个子查询，用 FILTER：

SELECT
  count(*)                         AS 全部,
  count(*) FILTER (WHERE stock > 0) AS 有货,
  count(*) FILTER (WHERE stock = 0) AS 缺货
FROM books;

FILTER 是标准 SQL，PostgreSQL 支持得很好，比老式的 sum(CASE WHEN ... THEN 1 ELSE 0 END) 清爽太多。

GROUPING SETS / ROLLUP / CUBE：一次查询同时按多个维度组合汇总。ROLLUP (a, b) 会给你「按 a,b 分组」「按 a 分组」「总计」三个层级的小计，做报表时省去多次查询和 UNION。

⇄ 对照 MySQL
  • 宽松的 GROUP BY：这是两家最出名的差异之一。历史 MySQL 默认允许「SELECT 非分组非聚合列」，返回不确定值；现代 MySQL（5.7.5+）默认打开了 ONLY_FULL_GROUP_BY，行为向 PostgreSQL 看齐、也会报错了。但这个 sql_mode 能被关掉——关掉后老坑就回来。PostgreSQL 没有这个开关，它永远严格。
  • FILTER：PostgreSQL 支持标准的 FILTER (WHERE ...)；MySQL 不支持，只能退回 SUM(CASE WHEN ...)。
  • ROLLUP：两家都有，但语法不同——MySQL 是 GROUP BY a, b WITH ROLLUP，PostgreSQL 是标准的 GROUP BY ROLLUP (a, b)，还额外支持更灵活的 GROUPING SETS 和 CUBE。

这一章的一句话

GROUP BY = 装桶 + 塌行；WHERE 在装桶前筛行、HAVING 在塌行后筛组；SELECT 里只能放分组列或聚合，否则 PostgreSQL 当场报错——它不肯给你一个「哪条都可能」的值。

聚合的代价是丢掉明细。可有时你既想要每组的汇总、又想留住每一行明细（比如「这笔订单，以及它在当天的累计」）。下一章的窗口函数，正是为这个而生。