聚合 — GROUP BY 实际在做什么

一句话总结

GROUP BY 把行折叠成组，折叠之后只剩下代表该组的值。因此 SELECT 列表中只能出现分组键、依附于键的值和聚合函数。聚合是一种不报错却会算错的运算，所以在相信结果之前，要先确认折叠前后的合计是否一致。

为什么需要它

有人来电说，月度报表里按渠道统计的销售额与财务部的数字差了 3%。查询没有报错，数字看上去也合情合理。拆开一看，原因有三个：退货单以负数数量混在里面；按月截断时用了服务器时区（UTC），导致每月 1 日 0 点到 9 点的韩国订单被算进了上个月；订单表与订单明细表连接后再对订单金额求和，含多个商品的订单金额被重复累加了好几次。

“各渠道销售额”“各状态订单数”这类问题要的不是单独的行，而是汇总。聚合是把多行折叠为一行的运算，折叠的依据就是 GROUP BY。折叠之后原始行不再可见，所以像上面那样被错误折叠的结果，从表面上无法分辨。因此要把工作原理和确认方法一起掌握。

它如何工作

记住处理顺序，大多数困惑都会迎刃而解。

FROM -> WHERE -> GROUP BY -> aggregate -> HAVING -> SELECT -> ORDER BY -> LIMIT

WHERE 在折叠之前、HAVING 在折叠之后生效。所以不能写 WHERE count(*) > 5，因为那一刻还没有可数的对象。行级条件最好放在 WHERE 中，要折叠的行变少，工作量也随之减少。
在 PostgreSQL 中，SELECT 里起的别名可以在 ORDER BY 和 GROUP BY 中使用，但不能在 WHERE 和 HAVING 中使用，那里必须把表达式重新写一遍。
SELECT 列表中的列必须出现在 GROUP BY 中，或者位于聚合函数内部。有一个例外：如果按某张表的主键分组，该表的其他列在函数上依赖于这个键，每组只有一个值，所以即使不写进 GROUP BY，PostgreSQL 也会接受。

聚合函数会忽略 NULL。avg(price) 会把 NULL 行从分母中也去掉。count(*) 统计所有行，而 count(price) 只统计 price 不为 NULL 的行。另外，一行都没有时，除 count 外的聚合函数返回的不是 0 而是 NULL，sum 也一样。需要 0 时要明确写成 coalesce(sum(x), 0)。

FILTER 子句用于在同一组内按不同条件做不同的聚合，只有条件为真的行才会进入该聚合函数。

SELECT channel,
       count(*) FILTER (WHERE status = 'paid')      AS paid_count,
       count(*) FILTER (WHERE status = 'cancelled') AS cancelled_count
FROM orders
GROUP BY channel;

同样的事若用 WHERE 实现，就得执行两次查询再连接，还要另外处理某一边缺失的渠道从结果中消失的问题。

按时间单位聚合时，一定要把时区定死。对 timestamptz 列使用 date_trunc('month', ordered_at)，会以会话的 TimeZone 设置为准进行截断。同一条查询因连接工具不同而给出不同答案，原因就在这里。ordered_at AT TIME ZONE 'Asia/Seoul' 会把该时刻在首尔是几点，作为不带时区的时间戳返回，对它截断，在哪里运行都得到同样的答案。PostgreSQL 还提供以第三个参数接收时区的 date_trunc。

实际项目中会出什么问题

连接使行数膨胀。 一张订单含 3 件商品时，订单与订单明细连接后，这张订单会出现 3 行。此时对订单金额求和，就会加三次。症状是销售额按平均商品数被放大。订单级金额应先在订单表中聚合，或在连接之前折叠到所需的粒度。

LEFT JOIN 悄悄变成 INNER JOIN。 为了把没有订单的客户也计入分母，以客户表为基准做了 LEFT JOIN，却在 WHERE 中加上订单表列的条件（o.status = 'paid'），没有订单的客户行在这个条件上为 NULL，就被过滤掉了。条件必须移到 ON 子句中。同一条查询里如果用 count(*)，没有订单的客户也会被计为 1，所以统计订单数时要用 count(o.id)。

不了解负数和缺失值就求和。 退货单以负数数量存在时，sum(quantity) 会悄悄减少销量，热销商品排名也会颠倒。聚合之前先看符号和 NULL。

月份边界错开 9 小时。 韩国时间比 UTC 快 9 小时。按 UTC 截断月份，每月 1 日上午 9 点前的韩国订单就会被算进上个月的销售额。各月合计加起来仍等于总额，所以用合计校验发现不了，只是每个月都差一点。

对平均值再求平均。 把各门店的平均客单价再平均一次，订单 10 笔的门店和 1 万笔的门店就有了同样的权重。需要整体平均时，应当用合计除以合计。

整数除法与取整类型。 在 PostgreSQL 中，整数相除会舍去小数部分。如果 100 * a / b 得出 0 或 99，就要怀疑这一点，并像 100.0 * a / b 那样把其中一边变成 numeric。另外，接收小数位数的 round(x, 2) 是为 numeric 定义的，用在 double precision 值上会报“函数不存在”的错误，要改成 round(x::numeric, 2)。

如何确认

在给出聚合结果之前，核对四件事。

-- 1. fan-out: rows vs distinct keys after the join
SELECT count(*), count(DISTINCT o.id)
FROM orders o JOIN order_items i ON i.order_id = o.id;

-- 2. sign and nulls before summing
SELECT min(quantity), max(quantity), count(*) - count(quantity) AS null_qty
FROM order_items;

-- 3. which time zone this session uses
SHOW TimeZone;

-- 4. the grouped totals must add up to the ungrouped total
SELECT (SELECT sum(total_amount) FROM orders) AS all_rows,
       (SELECT sum(revenue) FROM (SELECT channel, sum(total_amount) AS revenue
                                  FROM orders GROUP BY channel) g) AS by_group;

第一条查询中两个数字不同，说明连接正在让行数膨胀。第二条查询的最小值为负数，或 NULL 个数不为 0，就要先决定如何处理这些行。第四条查询的两个值不同，说明在分组折叠的过程中有行丢失或重叠。另外，计算平均值或人均值时，要把分母中放了什么和结果一起写下来。是否计入没有订单的客户，数字会差很多，而两种都可能是正确答案。

接下来的理论课要看什么

紧接着的阅读会学习不折叠行、计算排名与累计的窗口函数。之后的实验会依次做出各状态数量、各渠道销售额、HAVING 与 FILTER、以首尔时间为准的月度汇总、排除负数数量的热销商品，以及把没有订单的客户也计入分母的各等级人均销售额，亲身踩一遍本节讲到的每一个陷阱。