聚合与分组

本章目标：从"查行"升级到"算指标"，用 COUNT、SUM、AVG、GROUP BY、HAVING 算出状态分布、品类销售额、月度趋势和客单价。 前置知识：SQL 基础。 预计用时：50 分钟。

1. 为什么需要它

上一章的结果是一行行明细。但业务方不会问"第 8,321 行是什么"，他们会问：订单状态各有多少？哪个品类卖得最好？每个月的销售趋势怎样？平均每单花多少钱？

这些问题都要把很多行压成一个数，这就是聚合。只会 SELECT ... WHERE 的人能取数，会聚合的人才能做指标。

聚合也是口径问题的起点。同一个"客单价"，除以订单数还是除以明细行数，结果能差不少。本章不只讲语法，还会讲每个数字是怎么来的。

本章继续用上一章的玩具库 project/sql_demo.duckdb（9 张 demo_* 小表），不涉及电商项目数据。

2. 核心概念

2.1 聚合函数把多行压成一个数

SELECT COUNT(*) AS 明细行数,
       SUM(quantity * unit_price) AS 商品金额,
       AVG(unit_price) AS 平均单价,
       MAX(unit_price) AS 最贵单价
FROM demo_order_items;

AVG 的分母是"非空行数"，不是总行数：成绩 90、80、NULL，AVG 得 85，而不是 56.67；想让 NULL 按 0 算，必须写 AVG(COALESCE(x, 0))。做指标前先问清楚：NULL 该排除，还是该按 0 处理？

2.2 COUNT 的三种写法

demo_customers.referrer_id 允许为空，正好能看出差别：

SELECT COUNT(*) AS 总行数,
       COUNT(referrer_id) AS 有推荐人的行数
FROM demo_customers;

结果是 7 和 4。差的 3 位客户（1、5、7）没有推荐人，COUNT(列) 会跳过它们。

COUNT(1) 和 COUNT(*) 在 DuckDB、Hive、Spark 里结果相同，都是数行；COUNT(列) 才会跳过 NULL。面试问三者区别时，重点答"是否跳过 NULL"。

2.3 GROUP BY 的两条规则

GROUP BY 按某列的值把行分成若干组，每组算一次聚合。

SELECT status, COUNT(*) AS 订单数
FROM demo_orders
GROUP BY status;

规则只有两条：SELECT 里的非聚合列必须出现在 GROUP BY 里；GROUP BY 里可以有 SELECT 没用到的列，但通常不要这么写。

-- 错误：order_id 既没有聚合，也没有分组
SELECT status, order_id, COUNT(*)
FROM demo_orders
GROUP BY status;

会报 column "order_id" must appear in the GROUP BY clause or be used in an aggregate function。因为一个状态对应多条订单，数据库不知道该显示哪一个 order_id。这个代码块故意写错，不要直接执行。

多列分组：

SELECT strftime(created_at, '%Y-%m') AS 月份,
       status,
       COUNT(*) AS 订单数
FROM demo_orders
GROUP BY strftime(created_at, '%Y-%m'), status
ORDER BY 月份, status;

先按月分，再在每个月里按状态分。结果有 4 行：3 月 paid 6、refunded 1、canceled 1；4 月 paid 4。

注意：DuckDB 和 Spark 允许 GROUP BY 里写 SELECT 的别名，Hive 不支持。为了可移植，分组时重复写表达式，不要依赖别名。

2.4 HAVING：分组之后再筛选

SELECT category AS 品类, COUNT(*) AS 商品数
FROM demo_products
GROUP BY category
HAVING COUNT(*) >= 2
ORDER BY 商品数 DESC;

只有 电子 一类商品数达到 2 以上（4 个）。WHERE 在分组前筛行，不能用聚合；HAVING 在分组后筛组，可以用聚合。WHERE COUNT(*) > 5 会报错，正确写法是 HAVING。

完整执行顺序：

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

2.5 先定口径，再写 SQL

指标对不上，通常不是 SQL 写错，而是口径不同。本章统一如下：

清洗结果可以先固化成一个视图，后面反复用：

CREATE OR REPLACE VIEW demo_valid_detail AS
SELECT o.order_id, o.customer_id, o.created_at,
       i.product_id, i.quantity, i.unit_price
FROM demo_orders o
JOIN demo_order_items i ON i.order_id = o.order_id
WHERE o.status = 'paid';

SELECT COUNT(*) AS 明细行数,
       COUNT(DISTINCT order_id) AS 有明细订单数,
       ROUND(SUM(quantity * unit_price), 2) AS 销售额
FROM demo_valid_detail;

结果：13 行、10 个订单、4,419.20。

提示：如果销售额只算有明细的订单，订单数却算全部订单，AOV 就会偏小。分子和分母必须来自同一批数据。

3. 动手示例

3.1 订单状态分布

SELECT status AS 状态,
       COUNT(*) AS 订单数,
       ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM demo_orders), 2) AS 占比
FROM demo_orders
GROUP BY status
ORDER BY 订单数 DESC;

这就是所有"分布类"报表的原型：分组、计数、算占比。注意状态是当前快照，不是漏斗，不能直接把相邻状态的差值当转化率。

3.2 品类销售额

商品品类在 demo_products 里，需要先关联明细：

SELECT p.category AS 品类,
       ROUND(SUM(i.quantity * i.unit_price), 2) AS 销售额
FROM demo_order_items i
JOIN demo_orders o ON o.order_id = i.order_id
JOIN demo_products p ON p.product_id = i.product_id
WHERE o.status = 'paid'
GROUP BY p.category
ORDER BY 销售额 DESC;

电子占 92.37%，是典型的"头部集中"。真实业务里还要看长尾品类，不能只盯 TopN。

3.3 月度销售额

SELECT strftime(o.created_at, '%Y-%m') AS 月份,
       ROUND(SUM(i.quantity * i.unit_price), 2) AS 销售额
FROM demo_orders o
JOIN demo_order_items i ON i.order_id = o.order_id
WHERE o.status = 'paid'
GROUP BY strftime(o.created_at, '%Y-%m')
ORDER BY 月份;

注意：月度趋势必须固定时间范围。真实数据里，边界月往往不完整，不设范围会让"最近一个月"突然掉下去。

3.4 客单价 AOV

WITH order_amount AS (
    SELECT o.order_id, SUM(i.quantity * i.unit_price) AS 订单金额
    FROM demo_orders o
    JOIN demo_order_items i ON i.order_id = o.order_id
    WHERE o.status = 'paid'
    GROUP BY o.order_id
)
SELECT COUNT(*) AS 订单数,
       ROUND(SUM(订单金额), 2) AS 销售额,
       ROUND(AVG(订单金额), 2) AS AOV
FROM order_amount;

AOV 是先算每个订单的金额，再对订单求平均。不能直接对明细金额求平均，因为一个订单可能有多条明细，明细多的订单会被重复计算。

3.5 明细平均与订单平均为什么不同

SELECT ROUND(AVG(quantity * unit_price), 2) AS 明细行平均,
       (SELECT ROUND(SUM(quantity * unit_price) / COUNT(DISTINCT order_id), 2)
        FROM demo_order_items) AS 按订单平均
FROM demo_order_items;

明细行平均 333.81，按订单平均 417.27。分母不同，指标含义就不同。报表里必须写清楚 AOV 的分母是订单，不是明细行。

4. 常见坑

1. 在 WHERE 里写聚合函数。WHERE COUNT(*) > 5 会报错，应改成 HAVING。
2. SELECT 的列没进 GROUP BY。数据库不知道显示哪一行；要么分组，要么聚合。
3. 以为 COUNT(列) 等于行数。它跳过 NULL，可能比 COUNT(*) 小。
4. 忽略 AVG 跳过 NULL。分母是非空行数；NULL 若应算 0，必须 COALESCE。
5. 忘了 NULL / 默认值会单独成组。按等级分组时，未知 会单独占一行。
6. 在 GROUP BY 里用别名。DuckDB / Spark 能跑，Hive 不支持。
7. 先 JOIN 再聚合，导致金额翻倍。一对多关联会让明细重复，下一章专门讲。
8. 分母口径不一致。销售额只算有效订单，订单数却算全部订单，AOV 就偏小。

5. 在线练习

自测：

• 能解释 WHERE 和 HAVING 的执行顺序差异。
• 能用 COUNT(DISTINCT ...) 去重计数。
• 能在分组后取每组最大值对应的整行。

6. 面试延伸

面试题：COUNT(*)、COUNT(1)、COUNT(列) 有什么区别？

是什么：COUNT(*) 数行数；COUNT(1) 给每行放一个常量 1 再数，现代优化器会把它和 COUNT(*) 处理成一样；COUNT(列) 只数该列非空的行。

解决什么问题：统计"有多少行"用 COUNT(*)；统计"某字段有多少个有效值"用 COUNT(列)。两者相减，可以直接量出空值数量。

我的实操边界：日常统一写 COUNT(*)，明确要排除空值时写 COUNT(列)；COUNT(1) 见得多，但不会主动使用。

面试题：为什么 SELECT 了非聚合列却没报错 / 为什么报 GROUP BY 错误？

是什么：标准 SQL 要求 SELECT 里的非聚合列必须出现在 GROUP BY 中。MySQL 在 ONLY_FULL_GROUP_BY 关闭时、部分 Hive 版本和配置下可能"随便取一行"；Spark / DuckDB 默认报错。

解决什么问题：理解这条规则，才能判断一个分组查询是否真的可复现。数据库随便取的那一行，下一次执行可能变。

我的实操边界：遇到报错时，先确认这个列是"分组维度"还是"要聚合的度量"；前者放进 GROUP BY，后者套 MAX / SUM 等函数。

面试题：AVG 为什么和你手算的不一样？

是什么：AVG(列) 会跳过 NULL，分母是非空行数，不是总行数。

解决什么问题：如果 NULL 的业务含义是 0（比如缺考按 0 分），要写 AVG(COALESCE(列, 0))；如果 NULL 表示"没有发生"，就应该排除，但要在口径里说明。选择不同，指标含义完全不同。

我的实操边界：写指标前先确认空值含义，再决定 COALESCE 还是直接 AVG；报表里会注明分母是什么。

7. 本章验收

• 能说清 COUNT(*)、COUNT(1)、COUNT(列) 的区别。
• 能解释 AVG 为什么跳过 NULL，并知道何时用 COALESCE。
• 能独立写出 GROUP BY 单列和多列分组，不触发非聚合列报错。
• 能说清 WHERE 和 HAVING 的执行时机与使用场景。
• 能背出 FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT。
• 能独立算出状态分布、品类销售额、月度销售额和 AOV 441.92，并说清分母口径。
• 5 道在线练习全部提交通过。

下一章

多表查询与 JOIN：把订单、明细、商品、客户拼起来，同时避免一对多关联把金额算大。