分组与聚合：从订单明细看到经营指标 | 自在学

分组与聚合：从订单明细看到经营指标

上一章里，我们把函数当作“逐行加工工具”：ROUND() 修整一个金额，DATE_FORMAT() 从一个日期中取出月份，CONCAT() 把一行里的几段文字拼起来。它们无论写得多复杂，通常都不会改变结果的行数——输入一行，得到的还是这一行的加工结果。

这一章要做的事不一样。小满商店的负责人不会每天盯着一千行订单问“第 101 号订单多少钱”，他更关心的是：今年完成了多少单、成交金额是多少、哪个品类贡献最高、退款有多少、客单价有没有变化。回答这些问题，必须把许多明细行压缩成一行或几行指标。这就是聚合。

先记住一个贯穿全章的判断：写聚合查询之前，先说清楚结果中的一行代表什么。 一行代表全店、一个城市、一个月份，还是“城市与月份的组合”？这个问题一旦答错，后面的 COUNT()、SUM() 即使语法全对，统计口径也会错。

从订单明细到经营指标

本章继续使用小满商店的固定数据。有效销售口径统一为订单状态是 已支付、已发货 或 已完成；待支付、已取消 和 已退款 不计入销售额。后面所有金额与数量都沿用这条口径，除非查询明确说明要观察全部状态。

聚合改变的是结果粒度

先看一条上一章风格的查询：

sql

SELECT
    order_id,
    total_amount,
    ROUND(total_amount * 0.01, 2) AS point_amount
FROM orders
WHERE status IN ('已支付', '已发货', '已完成');

每一笔成交订单仍然对应结果中的一行。ROUND() 只计算了这一行能获得多少积分，没有把不同订单合并起来。这样的函数常被称为标量函数。

把查询改成下面这样，结果粒度立刻发生变化：

sql

SELECT
    COUNT(*) AS order_count,
    SUM(total_amount) AS revenue,
    ROUND(AVG(total_amount), 2) AS avg_order_amount
FROM orders
WHERE status IN ('已支付', '已发货', '已完成');

text

+-------------+---------+------------------+
| order_count | revenue | avg_order_amount |
+-------------+---------+------------------+
|          10 | 1482.70 |           148.27 |
+-------------+---------+------------------+

十行有效订单被压成了一行。这里没有 GROUP BY，数据库就把所有通过 WHERE 的行视为一个整体。我们可以把这看成一个没有写出来的“全体分组”，也就是隐式分组。

如果负责人想分别看每种状态，结果中的一行就应该代表“一种订单状态”：

sql

SELECT
    status,
    COUNT(*) AS order_count,
    SUM(total_amount) AS amount,
    ROUND(AVG(total_amount), 2) AS avg_amount
FROM orders
GROUP BY status
ORDER BY FIELD(
    status,
    '待支付', '已支付', '已发货', '已完成', '已取消', '已退款'
);

text

+-----------+-------------+--------+------------+
| status    | order_count | amount | avg_amount |
+-----------+-------------+--------+------------+
| 待支付    |           1 |  137.00 |     137.00 |
| 已支付    |           1 |   99.00 |      99.00 |
| 已发货    |           1 |  190.00 |     190.00 |
| 已完成    |           8 | 1193.70 |     149.21 |
| 已取消    |           1 |   87.00 |      87.00 |
| 已退款    |           1 |  159.00 |     159.00 |
+-----------+-------------+--------+------------+

这次 GROUP BY status 明确建立了六个组，聚合函数各自在自己的组内计算。结果从“一张表的十三笔订单”变成了“六种状态的指标”。这叫显式分组。

先写一句“结果中的一行代表什么”

遇到报表需求时，可以先不写 SQL，只写一句自然语言：

全店概况：结果中的一行代表所有成交订单。
城市概况：结果中的一行代表一个客户城市。
月度趋势：结果中的一行代表一个自然月。
城市月报：结果中的一行代表“一个城市与一个月份”的组合。
商品表现：结果中的一行代表一个商品。

这句话会直接决定 GROUP BY 后面写什么。普通列既没有参与聚合，也没有确定一个分组，就不应该随意出现在结果中。

聚合函数并不是“必须与 GROUP BY 一起使用”。没有 GROUP BY 时，所有符合条件的行组成一个隐式分组；有 GROUP BY 时，数据库按分组键建立多个显式分组。两者的差别是结果粒度，不是聚合函数能不能运行。

本章使用的数据关系

后面的示例只使用课程中已经出现的统一字段：

表	本章会用到的字段	一行代表什么
`customers`	`customer_id`, `customer_name`, `city`, `registered_at`, `email`	一位客户
`orders`	`order_id`, `customer_id`, `order_date`, `status`, `total_amount`	一张订单
`order_items`	`order_item_id`, `order_id`, `product_id`, `quantity`, `unit_price`, `discount_rate`	一张订单中的一个商品明细
`products`	`product_id`, `category_id`, `product_name`, `unit_price`	一个商品
`categories`	`category_id`, `category_name`, `parent_id`	一个商品分类
`payments`	`payment_id`, `order_id`, `paid_at`, `amount`, `payment_method`, `status`	一次支付记录

这里最容易被忽略的是粒度不同。orders 的一行是一张订单，order_items 的一行是一个订单明细。一张订单有两个商品明细时，把两张表连接起来就会出现两行。稍后统计订单数时如果写 COUNT(*)，这张订单会被数两次。

为了便于核对结果，固定种子中的订单表核心数据如下。表格为了紧凑只展示 order_date 的日期部分，库内保存的是完整日期时间：

text

+----------+-------------+------------+-----------+--------------+
| order_id | customer_id | order_date | status    | total_amount |
+----------+-------------+------------+-----------+--------------+
|      101 |           1 | 2025-06-18 | 已完成    |       118.90 |
|      102 |           2 | 2025-07-02 | 已完成    |       188.00 |
|      103 |           3 | 2025-08-15 | 已取消    |        87.00 |
|      104 |           4 | 2025-09-09 | 已完成    |       183.90 |
|      105 |           1 | 2025-10-12 | 已退款    |       159.00 |
|      106 |           6 | 2025-11-01 | 已完成    |       140.00 |
|      107 |           5 | 2025-12-20 | 待支付    |       137.00 |
|      108 |           9 | 2026-01-11 | 已发货    |       190.00 |
|      109 |           2 | 2026-02-14 | 已完成    |       150.00 |
|      110 |          10 | 2026-03-08 | 已支付    |        99.00 |
|      111 |           1 | 2026-04-01 | 已完成    |        99.90 |
|      113 |           4 | 2026-06-18 | 已完成    |       227.00 |
|      114 |           8 | 2026-07-07 | 已完成    |        86.00 |
+----------+-------------+------------+-----------+--------------+

你不用背这些数字。保留这张快照，是为了在看到 1482.70 或 10 时能回到明细里对账，而不是把结果当成无法验证的答案。

五个核心聚合函数各自回答什么

COUNT()、SUM()、AVG()、MIN()、MAX() 看起来都在“做统计”，但它们回答的是五类不同问题。

五个聚合函数，各回答一个问题

函数	回答的问题	常见业务口径
`COUNT()`	有多少个输入值或输入行	订单数、客户数、填写邮箱的人数
`SUM()`	数值合计是多少	成交金额、销量、库存变动量
`AVG()`	非空输入值的算术平均是多少	客单价、平均件数、平均支付金额
`MIN()`	最小值是什么	最低订单金额、最早下单日期
`MAX()`	最大值是什么	最高订单金额、最近付款时间

COUNT：先问“数行”还是“数值”

COUNT(*) 统计进入当前分组的行数。星号在这里不是“选择所有列”，也不会把一行里的每个字段分别数一遍；它表示只要这行存在，就计数一次。

sql

SELECT COUNT(*) AS customer_count
FROM customers;

结果是 10，因为 customers 有十行。

COUNT(email) 统计的则是 email 表达式不为 NULL 的行数。十位客户中有一位没有填写邮箱，所以结果是 9：

sql

SELECT
    COUNT(*) AS all_customers,
    COUNT(email) AS customers_with_email
FROM customers;

text

+---------------+----------------------+
| all_customers | customers_with_email |
+---------------+----------------------+
|            10 |                    9 |
+---------------+----------------------+

因此，下面两句不能随意互换：

sql

COUNT(*)          -- 进入分组的所有行
COUNT(email)      -- email 不为 NULL 的行

当列被定义为 NOT NULL 时，两者的数值通常相同，但表达的口径仍然不同。统计订单行时写 COUNT(*) 更直白；统计“已填写邮箱的人数”时写 COUNT(email) 更准确。

SUM：合计的是表达式，不一定是原始列

成交金额可以直接对 total_amount 求和：

sql

SELECT SUM(total_amount) AS revenue
FROM orders
WHERE status IN ('已支付', '已发货', '已完成');

结果是 1482.70。

商品销售额则不能简单使用商品表里的标价。每条订单明细都有成交数量、下单时单价和折扣率，因此应先逐行计算，再求和：

sql

SELECT
    SUM(quantity * unit_price * (1 - discount_rate)) AS item_revenue
FROM order_items i
JOIN orders o ON o.order_id = i.order_id
WHERE o.status IN ('已支付', '已发货', '已完成');

这里的计算顺序可以读成：先为每一条明细算出 数量 × 成交单价 × 折扣率，再把所有明细金额加总。函数括号里放的是表达式，聚合的就是表达式的结果。

AVG：分母由非空输入数量决定

AVG(total_amount) 不是简单地“除以表的行数”。它等价于对非 NULL 的 total_amount 求和，再除以非 NULL 的 total_amount 数量：

sql

SELECT
    ROUND(AVG(total_amount), 2) AS avg_order_amount,
    ROUND(SUM(total_amount) / COUNT(total_amount), 2) AS checked_avg
FROM orders
WHERE status IN ('已支付', '已发货', '已完成');

text

+------------------+-------------+
| avg_order_amount | checked_avg |
+------------------+-------------+
|           148.27 |      148.27 |
+------------------+-------------+

在本章数据里，有效订单的 total_amount 都不为空，所以十笔订单共同构成分母。如果其中一笔金额是 NULL，它不会按零元订单参与平均，而是完全不进入 AVG() 的分子和分母。

MIN 与 MAX：数值、日期、文本都可以比较

MIN() 和 MAX() 不只处理数字。下面一次取出最低订单金额、最高订单金额、最早成交日期和最近成交日期：

sql

SELECT
    MIN(total_amount) AS min_amount,
    MAX(total_amount) AS max_amount,
    MIN(order_date) AS first_order_date,
    MAX(order_date) AS last_order_date
FROM orders
WHERE status IN ('已支付', '已发货', '已完成');

text

+------------+------------+---------------------+---------------------+
| min_amount | max_amount | first_order_date | last_order_date |
+------------+------------+---------------------+---------------------+
|      86.00 |     227.00 | 2025-06-18 10:05:00 | 2026-07-07 07:50:00 |
+------------+------------+---------------------+---------------------+

日期有明确的先后顺序，因此最小日期就是最早日期，最大日期就是最晚日期。文本也可以按排序规则比较，但“名称最大的客户”通常没有业务意义。能运行，不代表指标有解释价值。

一次查询可以产生多个指标

数据库扫描一批成交订单时，可以同时计算多个聚合：

sql

SELECT
    COUNT(*) AS order_count,
    COUNT(DISTINCT customer_id) AS customer_count,
    SUM(total_amount) AS revenue,
    ROUND(AVG(total_amount), 2) AS avg_order_amount,
    MIN(total_amount) AS min_order_amount,
    MAX(total_amount) AS max_order_amount
FROM orders
WHERE status IN ('已支付',

text

+-------------+----------------+---------+------------------+------------------+------------------+
| order_count | customer_count | revenue | avg_order_amount | min_order_amount | max_order_amount |
+-------------+----------------+---------+------------------+------------------+------------------+
|          10 |              7 | 1482.70 |           148.27 |            86.00 |           227.00 |
+-------------+----------------+---------+------------------+------------------+------------------+

这六列不是六次独立报表，而是同一批输入行上的六种观察方式。它们共享完全相同的 FROM 与 WHERE 口径，所以适合放在一起。

空输入集不等于“一行都不返回”

聚合查询还有一个容易误判的边界。下面的条件找不到任何订单：

sql

SELECT
    COUNT(*) AS order_count,
    SUM(total_amount) AS revenue,
    AVG(total_amount) AS avg_amount,
    MIN(total_amount) AS min_amount,
    MAX(total_amount) AS max_amount
FROM orders
WHERE order_date >= '2099-01-01';

没有 GROUP BY 时，查询仍然返回一行：

text

+-------------+---------+------------+------------+------------+
| order_count | revenue | avg_amount | min_amount | max_amount |
+-------------+---------+------------+------------+------------+
|           0 | NULL    | NULL       | NULL       | NULL       |
+-------------+---------+------------+------------+------------+

COUNT(*) 能明确回答“有零行”，所以返回 0。SUM()、AVG()、MIN()、MAX() 没有输入值可计算，返回 NULL。如果报表必须显示零元，可以在输出层写：

sql

SELECT COALESCE(SUM(total_amount), 0) AS revenue
FROM orders
WHERE order_date >= '2099-01-01';

但不要因为界面想显示 0，就误以为“没有成交”和“成交金额确实为零”在数据语义上完全相同。是否转换要由报表口径决定。

NULL 如何改变统计口径

NULL 表示未知或缺失。大多数聚合函数会忽略 NULL 输入，COUNT(*) 则统计行本身。这条规则很短，却会直接改变分母、合计和去重结果。

NULL 会改变统计口径

用 COUNT 的差值找缺失数据

客户表中有十位客户，其中九位填写了邮箱：

sql

SELECT
    COUNT(*) AS all_customers,
    COUNT(email) AS customers_with_email,
    COUNT(*) - COUNT(email) AS customers_without_email
FROM customers;

text

+---------------+----------------------+-------------------------+
| all_customers | customers_with_email | customers_without_email |
+---------------+----------------------+-------------------------+
|            10 |                    9 |                       1 |
+---------------+----------------------+-------------------------+

这个写法不需要单独扫描 email IS NULL，同一组内就能同时得到总人数、已填写人数和未填写人数。

AVG 忽略 NULL，不会把它当成零

假设一组支付金额是 100、200、NULL：

sql

SELECT AVG(amount)
FROM payments;

对于这三行，平均值的思路是 (100 + 200) / 2 = 150，不是 (100 + 200 + 0) / 3 = 100。如果业务明确规定“缺失金额按零元处理”，才应该写：

sql

SELECT AVG(COALESCE(amount, 0))
FROM payments;

这两条语句的分母不同。第一条问“已知支付金额的平均值”，第二条问“每条支付记录按缺失为零处理后的平均值”。COALESCE() 不是纯粹的防报错技巧，它改变了统计定义。

表达式中任一项为 NULL，整项可能被忽略

再看订单明细金额：

sql

SUM(quantity * unit_price * (1 - discount_rate))

共享结构把 discount_rate 定义为非空，并约定 0 表示不打折。若你在别的系统里遇到可空折扣率，乘法中的 NULL 会让整条表达式变成 NULL，SUM() 随后跳过它。只有业务规则确认缺失折扣率表示不打折时，才可以明确补零：

sql

SUM(quantity * unit_price * (1 - COALESCE(discount_rate, 0)))

先确认数据含义，再使用默认值。数据库无法替你判断 NULL 是“未打折”“尚未录入”，还是“该商品不适用折扣”。

NULL 也会形成一个分组

如果按一列分组，该列中的多个 NULL 会被归到同一组。比如客户城市为空时：

sql

SELECT
    city,
    COUNT(*) AS customer_count
FROM customers
GROUP BY city;

所有城市未知的客户会共同出现在 city = NULL 的那一行结果里。不要用 WHERE city = NULL 过滤它；判断缺失仍然要写 city IS NULL。

ROLLUP 产生的小计行也可能在分组列上显示 NULL，而原始数据本身也可能有 NULL。稍后我们会用 GROUPING() 区分“业务数据未知”和“这一行是汇总层级”，不能看到 NULL 就直接写成“合计”。

DISTINCT 去掉的是重复输入

DISTINCT 放在聚合函数内部时，会先去掉重复值，再把剩余值交给聚合函数。最常见的是统计不重复客户数：

sql

SELECT
    COUNT(*) AS order_count,
    COUNT(customer_id) AS customer_value_count,
    COUNT(DISTINCT customer_id) AS customer_count
FROM orders
WHERE status IN ('已支付', '已发货', '已完成');

text

+-------------+----------------------+----------------+
| order_count | customer_value_count | customer_count |
+-------------+----------------------+----------------+
|          10 |                   10 |              7 |
+-------------+----------------------+----------------+

十笔有效订单都有客户编号，所以前两个计数都是十。但同一客户可以下多笔订单，去重后只有七位客户。

SELECT DISTINCT 与 COUNT(DISTINCT ...) 不在同一层

下面的查询返回客户编号列表：

sql

SELECT DISTINCT customer_id
FROM orders
WHERE status IN ('已支付', '已发货', '已完成');

它会返回七行。下面的查询返回的是数量，因此只有一行：

sql

SELECT COUNT(DISTINCT customer_id) AS customer_count
FROM orders
WHERE status IN ('已支付', '已发货', '已完成');

第一个 DISTINCT 作用于整个结果行，第二个 DISTINCT 只作用于聚合函数的输入。

DISTINCT 也能用于 SUM 和 AVG，但要小心业务含义

这条语句语法成立：

sql

SELECT SUM(DISTINCT total_amount)
FROM orders;

它会把金额相同的订单只累加一次。两张不同订单恰好都是 99.00 时，第二张会消失。除非你的需求真的是“不同金额档位的合计”，否则这通常不是成交金额。

同理，AVG(DISTINCT total_amount) 算的是不同金额值的平均，而不是订单平均金额。DISTINCT 解决重复值问题，不解决连接造成的重复行，更不会自动理解“同额但不同订单”仍然是两笔业务。

多列唯一组合存在方言差异

如果想统计“客户与下单日期”的唯一组合，MySQL 支持：

sql

SELECT COUNT(DISTINCT customer_id, order_date) AS customer_day_count
FROM orders;

PostgreSQL 通常把多个字段组成一个行值：

sql

SELECT COUNT(DISTINCT (customer_id, order_date)) AS customer_day_count
FROM orders;

不要为了兼容而草率地写 COUNT(DISTINCT CONCAT(customer_id, order_date))。如果没有可靠分隔符或类型规范，(1, 23) 与 (12, 3) 之类的组合可能拼成同一个字符串。跨数据库项目应在所用数据库上明确写法，并用边界数据验证。

聚合表达式先逐行计算再汇总

聚合函数的参数可以是列，也可以是算术表达式、CASE 表达式或函数结果。理解计算层次后，很多报表不需要先新增物理字段。

销售数量与销售额是两个指标

统计商品表现时，数量和金额来自不同表达式：

sql

SELECT
    SUM(i.quantity) AS units_sold,
    ROUND(
        SUM(i.quantity * i.unit_price * (1 - i.discount_rate)),
        2
    ) AS item_revenue
FROM order_items i
JOIN orders o ON o.order_id = i.

text

+------------+--------------+
| units_sold | item_revenue |
+------------+--------------+
|         22 |      1492.49 |
+------------+--------------+

units_sold 数的是商品件数，不是订单数，也不是明细行数。三者分别对应 SUM(quantity)、COUNT(DISTINCT order_id) 与 COUNT(*)，必须按问题选择。

ROUND 放在聚合外还是聚合内

下面两种写法看上去只差一对括号：

sql

-- 先精确合计，最后展示时保留两位小数
ROUND(SUM(quantity * unit_price * (1 - discount_rate)), 2)
 
-- 每条明细先保留两位小数，再合计
SUM(ROUND(quantity * unit_price * (1 - discount_rate), 2))

它们可能得到不同结果。财务结算到底是“每行先结算再合计”，还是“保留完整精度后统一结算”，应由业务规则决定。若只是报表展示，通常在聚合外 ROUND()；若每条明细在业务上已经独立结算，就按每条结算规则处理。

比率不要平均比率，先看分子分母

假设每条明细都有折扣率，AVG(discount_rate) 回答的是“明细折扣率的简单平均”。它没有考虑昂贵商品和便宜商品的金额权重。如果要计算整体成交折扣率，更接近业务问题的写法是：

sql

SELECT
    ROUND(
        1 - SUM(quantity * unit_price * (1 - discount_rate))
            / NULLIF(SUM(quantity * unit_price), 0),
        4
    ) AS weighted_discount_rate
FROM order_items i
JOIN orders o ON o.order_id = i.order_id
WHERE o.

NULLIF(分母, 0) 在分母为零时返回 NULL，避免除零错误。这里不是为了追求复杂写法，而是因为“折扣率的平均”与“总成交金额除以总原价”回答的是不同问题。

GROUP BY 定义结果中的分组键

GROUP BY 的本质不是排序，也不只是“去重”。它按一个或多个表达式的计算结果把输入行分桶，然后让聚合函数在每个桶里分别工作。

单列分组：每种状态一行

sql

SELECT
    status,
    COUNT(*) AS order_count,
    SUM(total_amount) AS amount
FROM orders
GROUP BY status
ORDER BY status;

当两行的 status 相同，它们就进入同一个组。每组最终只产生一行，所以 status 看起来也有“去重”效果。不过此处真正的目标是为每个状态计算指标；如果只想得到状态列表，SELECT DISTINCT status 更直接。

多列分组：每种组合一行

负责人要看每个城市、每种订单状态的数量。城市在 customers，订单状态在 orders，需要先连接再按两个字段分组：

sql

SELECT
    c.city,
    o.status,
    COUNT(*) AS order_count,
    SUM(o.total_amount) AS amount
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
GROUP BY c.city, o.status

结果中的一行代表“城市与状态的一个组合”，不是单独的城市，也不是单独的状态。例如杭州的 已完成 与杭州的 已退款 是两个组；上海的 已完成 又是另一个组。

text

+------+-----------+-------------+--------+
| city | status    | order_count | amount |
+------+-----------+-------------+--------+
| 上海 | 已完成    |           2 | 338.00 |
| 北京 | 待支付    |           1 | 137.00 |
| 南京 | 已发货    |           1 | 190.00 |
| 广州 | 已完成    |           1 | 140.00 |
| 成都 | 已取消    |           1 |  87.00 |
| 杭州 | 已完成    |           2 | 218.80 |
| 杭州 | 已退款    |           1 | 159.00 |
| 武汉 | 已完成    |           1 |  86.00 |
| 苏州 | 已完成    |           2 | 410.90 |
| 西安 | 已支付    |           1 |  99.00 |
+------+-----------+-------------+--------+

多列分组不是先按城市算一遍、再按状态算一遍，而是按列值组合建立最细的一层分组。GROUP BY c.city, o.status 和 GROUP BY o.status, c.city 产生的明细组合相同；列顺序会在 ROLLUP 中决定汇总层级，那时就不能随意交换。

按表达式分组：把日期加工成年份

订单表存的是具体日期时间，但年度概览的一行应代表一个年份。先用上一章学过的日期函数取出年份，再按同一表达式分组：

sql

SELECT
    YEAR(order_date) AS order_year,
    COUNT(*) AS order_count,
    SUM(total_amount) AS revenue,
    ROUND(AVG(total_amount), 2) AS avg_order_amount
FROM orders
WHERE status IN ('已支付', '已发货', '已完成')
GROUP BY YEAR(order_date)
ORDER BY order_year;

text

+-------------+-------------+---------+------------------+
| order_year | order_count | revenue | avg_order_amount |
+-------------+-------------+---------+------------------+
|       2025 |           4 |  630.80 |           157.70 |
|       2026 |           6 |  851.90 |           141.98 |
+-------------+-------------+---------+------------------+

这里先把 2025-06-18 10:05:00、2025-07-02 21:10:00 等日期时间都计算成 2025，同一年的行自然进入同一组。

在 PostgreSQL 中，可以用 EXTRACT(YEAR FROM order_date) 取出年份：

sql

SELECT
    EXTRACT(YEAR FROM order_date) AS order_year,
    COUNT(*) AS order_count,
    SUM(total_amount) AS revenue
FROM orders
WHERE status IN ('已支付', '已发货', '已完成')
GROUP BY EXTRACT(YEAR FROM order_date)
ORDER BY order_year;

如果项目跨数据库，不要把日期函数当作完全通用的 SQL。先确定数据库，再选择对应写法。

时间分组先确认“自然月”和“最近 30 天”

按 2025-06、2025-07 分组是在比较自然月。最近 30 天是一个滚动区间，通常只需要 WHERE order_date >= ...，不等于按月分组。两者常被口头上都叫“月度数据”，实际口径不同。

按日、周、月分组时还要考虑字段类型与时区。如果 paid_at 存的是 UTC 时间戳，而报表按北京时间自然日统计，应先转换到业务时区，再取日期。否则北京时间零点后的订单可能落到前一天。

别依赖 GROUP BY 的输出顺序

分组只决定哪些行属于同一组，不保证最终排序。需要按金额从高到低展示，就明确写：

sql

SELECT
    status,
    SUM(total_amount) AS amount
FROM orders
GROUP BY status
ORDER BY amount DESC;

不要因为一次运行恰好按状态或日期排列，就把这种顺序当成数据库承诺。

别用列位置隐藏真实含义

MySQL 允许某些场景写 GROUP BY 1，表示按 SELECT 列表中的第一项分组：

sql

SELECT DATE_FORMAT(order_date, '%Y-%m') AS order_month, COUNT(*)
FROM orders
GROUP BY 1;

它很短，却会让查询在调整列顺序后悄悄改变含义。教学、报表和长期维护的 SQL 更适合写出完整表达式，或者在所用数据库明确支持时使用清楚的别名。

WHERE 与 HAVING 分别过滤明细和分组

很多聚合错误并不是函数写错，而是条件放错了位置。可以把处理过程记成一条流水线：

WHERE 和 HAVING 不在同一层

text

FROM / JOIN
    ↓
WHERE          过滤明细行
    ↓
GROUP BY       建立分组
    ↓
聚合计算
    ↓
HAVING         过滤分组结果
    ↓
SELECT         形成输出列
    ↓
ORDER BY       排列最终结果

这不是要求你按这个顺序书写 SQL，而是帮助理解每个子句能看见什么。

WHERE：不让某些明细进入统计

我们只把状态为 已支付、已发货 和 已完成 的订单计入有效成交：

sql

SELECT
    c.city,
    COUNT(*) AS order_count,
    SUM(o.total_amount) AS revenue
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.status IN ('已支付', '已发货', '已完成')

待支付、已取消 和 已退款 行在分组之前就被排除。它们不进入订单数，也不进入金额合计。

HAVING：分组算完后再决定保留哪些组

在成交口径基础上，只保留成交金额不低于 300 元的城市：

sql

SELECT
    c.city,
    COUNT(*) AS order_count,
    SUM(o.total_amount) AS revenue,
    ROUND(AVG(o.total_amount), 2) AS avg_order_amount
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id

text

+------+-------------+---------+------------------+
| city | order_count | revenue | avg_order_amount |
+------+-------------+---------+------------------+
| 苏州 |           2 |  410.90 |           205.45 |
| 上海 |           2 |  338.00 |           169.00 |
+------+-------------+---------+------------------+

这里有两次过滤，但对象不同：

WHERE 从订单明细中排除取消和退款订单。
HAVING 从七个有有效订单的城市分组中排除金额不足 300 元的杭州、南京、广州、西安和武汉。

为什么聚合条件不能写在 WHERE

下面的写法会报错：

sql

SELECT
    c.city,
    SUM(o.total_amount) AS revenue
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE SUM(o.total_amount) >= 300
GROUP BY c.city;

数据库处理 WHERE 时，城市分组还没有建立，SUM(o.total_amount) 也没有算出来。因此 WHERE 无法判断“这个组的合计是否达到 300”。聚合后的条件应写进 HAVING。

能放 WHERE 的条件，不要习惯性塞进 HAVING

下面在 MySQL 中可能可以运行：

sql

SELECT status, COUNT(*)
FROM orders
GROUP BY status
HAVING status IN ('已支付', '已发货', '已完成');

但 status 是每条明细原本就有的值，没有理由等分组结束再过滤。更清楚的写法是：

sql

SELECT status, COUNT(*)
FROM orders
WHERE status IN ('已支付', '已发货', '已完成')
GROUP BY status;

尽早过滤通常也能减少后续分组需要处理的数据。只有条件依赖聚合结果，或者明确需要在分组层判断时，才使用 HAVING。

HAVING 中使用别名的兼容性

MySQL 支持在 HAVING 中引用选择列表别名：

sql

SELECT
    c.city,
    SUM(o.total_amount) AS revenue
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.status IN ('已支付', '已发货', '已完成')
GROUP BY c.city
HAVING revenue

为了让计算逻辑更显式，也为了减少数据库方言差异，本章主要在 HAVING 中重复聚合表达式：

sql

HAVING SUM(o.total_amount) >= 300

重复这一小段比依赖不通用的别名解析规则更稳妥。

同时约束组大小和组内平均值

HAVING 可以组合多个聚合条件。例如，找出至少有两笔成交订单且客单价达到 130 元的城市：

sql

SELECT
    c.city,
    COUNT(*) AS order_count,
    ROUND(AVG(o.total_amount), 2) AS avg_order_amount
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.status IN ('已支付',

苏州和上海满足条件。杭州也有两笔有效订单，但平均订单金额是 109.40，会被过滤。

ONLY_FULL_GROUP_BY 在阻止不确定结果

初学 GROUP BY 时，经常会写出这样一条查询：

sql

SELECT
    c.city,
    o.order_id,
    SUM(o.total_amount) AS revenue
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
GROUP BY c.city;

意图是“按城市汇总”，但一个城市可能有多张订单。结果中的杭州只有一行，那么 order_id 应该显示 101、105 还是 111？SQL 没有依据选出唯一答案。

MySQL 8.4 默认启用的 ONLY_FULL_GROUP_BY 会拒绝这类不确定查询，常见错误信息包含：

text

Expression ... of SELECT list contains nonaggregated column ...
this is incompatible with sql_mode=only_full_group_by

合法选择列表的三类内容

在分组查询中，选择列表通常应由以下内容组成：

分组键，例如 c.city。
聚合表达式，例如 SUM(o.total_amount)。
能由分组键唯一确定的列。数据库对函数依赖的识别有具体规则，不能凭感觉假设。

对于刚才的错误，最直接的修复是删除没有意义的 order_id：

sql

SELECT
    c.city,
    SUM(o.total_amount) AS revenue
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
GROUP BY c.city;

如果需求其实是“每个城市、每张订单一行”，就把粒度写完整：

sql

SELECT
    c.city,
    o.order_id,
    SUM(o.total_amount) AS revenue
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
GROUP BY c.city, o.order_id;

不过在 orders 粒度上，一张订单本来只有一行，第二条查询通常不需要聚合。语法修好以后，还要重新问一次：这真是你想要的报表吗？

不要关闭严格模式掩盖问题

关闭 ONLY_FULL_GROUP_BY 后，MySQL 可能从每个组里随意挑一个 order_id。即使当前数据碰巧显示了你期待的编号，换一套执行计划、索引或数据后也可能变化。ORDER BY 不能控制“组内挑哪一个普通值”，因为挑值发生在最终排序之前。

ANY_VALUE 只表示“不关心取哪一个”

MySQL 提供 ANY_VALUE()，可以明确告诉数据库：这一列任取组内一个值即可。

sql

SELECT
    c.city,
    ANY_VALUE(o.order_id) AS sample_order_id,
    SUM(o.total_amount) AS revenue
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
GROUP BY c.city;

只有列真的只是样例、且任意值都不影响业务判断时才适合这样写。它不会把任意值变成“第一笔订单”“最大金额订单”或“最近订单”。这些需求都需要明确的排序和取行逻辑，后面的子查询与窗口函数会处理。

主键分组与函数依赖

如果按客户主键分组，同一 customer_id 只能对应一个 customer_name。某些数据库能识别这种函数依赖，允许选择客户姓名：

sql

SELECT
    c.customer_id,
    c.customer_name,
    COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id;

不过函数依赖的识别范围、约束条件和跨表行为有数据库差异。为了让查询意图一眼可见，课程示例会把显示字段也写进 GROUP BY：

sql

GROUP BY c.customer_id, c.customer_name

这不是机械地把所有列都塞进去，而是因为“每位客户一行”确实由客户编号和对应名称共同说明。

ROLLUP 在一份结果中加入小计与总计

普通 GROUP BY city, status 只给出最细的“城市 × 状态”组合。报表还可能需要每个城市的小计和全店总计。逐条写多次查询再手工拼接既重复，也容易让各段筛选条件不一致。ROLLUP 可以沿分组列的层级向上生成汇总行。

ROLLUP 一次生成多层汇总

MySQL 的 WITH ROLLUP

sql

SELECT
    c.city,
    o.status,
    COUNT(*) AS order_count,
    SUM(o.total_amount) AS amount
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
GROUP BY c.city, o.status

对于 GROUP BY c.city, o.status WITH ROLLUP，结果包含三层：

text

城市 + 状态       最细分组，例如“杭州 + 已完成”
城市              城市小计，例如“杭州 + 所有状态”
全部              全店总计

ROLLUP 沿着分组列从右向左收起。先收起 status 得到城市小计，再收起 city 得到全店总计。因此，列顺序表达了层级关系。

如果写成：

sql

GROUP BY o.status, c.city WITH ROLLUP

中间层就会变成“每种状态的小计”，而不是“每个城市的小计”。最细组合相同，汇总层级不同。

不要仅凭 NULL 判断汇总行

ROLLUP 会在被收起的分组列位置显示 NULL。如果原始数据本身也允许城市为空，仅看 city IS NULL 无法知道这是“城市未知”还是“全店合计”。应该使用 GROUPING()：

sql

SELECT
    CASE
        WHEN GROUPING(c.city) = 1 THEN '全部城市'
        WHEN c.city IS NULL THEN '城市未知'
        ELSE c.city
    END AS city_label,
    CASE
        WHEN GROUPING(o.status) = 1 THEN '全部状态'
        ELSE o

GROUPING(表达式) 在该表达式被当前汇总层收起时返回 1，在普通分组中返回 0。它判断的是这一行怎么生成，而不是原始值是否为空。

读懂一段 ROLLUP 结果

为了突出层级，下面只列出杭州、苏州的小计与最后总计：

text

+------------+--------------+-------------+---------+
| city_label | status_label | order_count | amount  |
+------------+--------------+-------------+---------+
| 杭州       | 已完成       |           2 |  218.80 |
| 杭州       | 已退款       |           1 |  159.00 |
| 杭州       | 全部状态     |           3 |  377.80 |
| 苏州       | 已完成       |           2 |  410.90 |
| 苏州       | 全部状态     |           2 |  410.90 |
| 全部城市   | 全部状态     |          13 | 1865.70 |
+------------+--------------+-------------+---------+

这里的全店总计包含取消和退款订单，因为查询没有 WHERE 排除它们。ROLLUP 只增加汇总层级，不会替你决定成交口径。如果只要成交数据，应在分组前加：

sql

WHERE o.status IN ('已支付', '已发货', '已完成')

PostgreSQL 的 ROLLUP 写法

PostgreSQL 常用的标准形式是：

sql

GROUP BY ROLLUP (c.city, o.status)

MySQL 8.4 既支持 WITH ROLLUP，也支持 GROUP BY ROLLUP (...) 形式。团队应选定一种项目风格，不要在同一份代码里反复切换。

用 HAVING 选择汇总层级

GROUPING() 也可以放进 HAVING。例如，只保留城市小计和全店总计，不显示状态明细：

sql

SELECT
    CASE
        WHEN GROUPING(c.city) = 1 THEN '全部城市'
        ELSE c.city
    END AS city_label,
    SUM(o.total_amount) AS amount
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
GROUP BY c

这里的 HAVING 不是按金额筛选，而是按汇总行的层级筛选。WHERE 无法完成这件事，因为 ROLLUP 行在分组阶段之后才产生。

条件聚合把一组数据横向展开成指标

普通分组会把状态放到多行：已完成 一行、已退款 一行。经营看板往往希望把这些状态放到同一行的不同列。条件聚合的做法是：先用 CASE 判断每一行是否符合条件，再用 SUM()、COUNT() 或 AVG() 聚合判断结果。

一次扫描，生成多项指标

用 SUM(CASE ...) 统计数量

sql

SELECT
    COUNT(*) AS all_orders,
    SUM(CASE WHEN status = '已支付' THEN 1 ELSE 0 END) AS paid_orders,
    SUM(CASE WHEN status = '已发货' THEN 1 ELSE 0 END) AS shipped_orders,
    SUM(CASE WHEN status = '已完成'

text

+------------+-------------+----------------+------------------+-----------------+------------------+----------------+
| all_orders | paid_orders | shipped_orders | completed_orders | refunded_orders | cancelled_orders | pending_orders |
+------------+-------------+----------------+------------------+-----------------+------------------+----------------+
|         13 |           1 |              1 |                8 |               1 |                1 |              1 |
+------------+-------------+----------------+------------------+-----------------+------------------+----------------+

以 paid_orders 为例，每条 已支付 行先变成 1，其他行变成 0，最后求和就是处在已支付状态的订单数。

COUNT(CASE ...) 与 SUM(CASE ...) 的差别

同一个数量也可以写成：

sql

COUNT(CASE WHEN status = '已支付' THEN 1 END)

条件成立时返回 1，条件不成立时因为没有 ELSE 而返回 NULL，COUNT(表达式) 只数非空值。

下面的写法却是错误口径：

sql

COUNT(CASE WHEN status = '已支付' THEN 1 ELSE 0 END)

0 不是 NULL，所以每一行都会被 COUNT() 统计，结果会等于全部订单数 13。这是条件聚合中最常见的陷阱之一。

可以选一种团队容易读懂的风格：

sql

SUM(CASE WHEN 条件 THEN 1 ELSE 0 END)
 
COUNT(CASE WHEN 条件 THEN 1 END)

二者都成立，但不要把第二种的隐式 NULL 逻辑忘掉。

用 SUM(CASE ...) 统计不同金额口径

sql

SELECT
    SUM(CASE
            WHEN status IN ('已支付', '已发货', '已完成') THEN total_amount
            ELSE 0
        END) AS revenue,
    SUM(CASE
            WHEN status = '已退款' THEN total_amount
            ELSE 0
        END) AS refunded_amount,
    SUM(CASE

text

+---------+-----------------+------------------+
| revenue | refunded_amount | cancelled_amount |
+---------+-----------------+------------------+
| 1482.70 |          159.00 |            87.00 |
+---------+-----------------+------------------+

同一批十三行订单被扫描后，得到三套互斥口径。相比为每个状态分别写一条查询，这种写法更容易保证日期范围和连接条件一致。

条件聚合与 GROUP BY 可以叠加

把状态指标放到每个城市的一行：

sql

SELECT
    c.city,
    COUNT(*) AS all_orders,
    SUM(CASE WHEN o.status = '已支付' THEN 1 ELSE 0 END) AS paid_orders,
    SUM(CASE WHEN o.status = '已发货' THEN 1 ELSE 0 END) AS

text

+------+------------+------+---------+-----------+----------+-----------+---------+---------+
| city | all_orders | paid | shipped | completed | refunded | cancelled | pending | revenue |
+------+------------+------+---------+-----------+----------+-----------+---------+---------+
| 苏州 |          2 |    0 |       0 |         2 |        0 |         0 |       0 |  410.90 |
| 上海 |          2 |    0 |       0 |         2 |        0 |         0 |       0 |  338.00 |
| 杭州 |          3 |    0 |       0 |         2 |        1 |         0 |       0 |  218.80 |
| 南京 |          1 |    0 |       1 |         0 |        0 |         0 |       0 |  190.00 |
| 广州 |          1 |    0 |       0 |         1 |        0 |         0 |       0 |  140.00 |
| 西安 |          1 |    1 |       0 |         0 |        0 |         0 |       0 |   99.00 |
| 武汉 |          1 |    0 |       0 |         1 |        0 |         0 |       0 |   86.00 |
| 成都 |          1 |    0 |       0 |         0 |        0 |         1 |       0 |    0.00 |
| 北京 |          1 |    0 |       0 |         0 |        0 |         0 |       1 |    0.00 |
+------+------------+------+---------+-----------+----------+-----------+---------+---------+

结果的一行仍然代表一个城市，只是这一行里同时放了多个条件指标。GROUP BY 决定纵向粒度，条件聚合决定横向指标。

条件平均值只让目标行进入 AVG

比较已付款和已发货订单的平均金额：

sql

SELECT
    ROUND(AVG(CASE
                  WHEN status = '已支付' THEN total_amount
              END), 2) AS paid_avg_amount,
    ROUND(AVG(CASE
                  WHEN status = '已发货' THEN total_amount
              END), 2) AS shipped_avg_amount
FROM orders;

text

+-----------------+--------------------+
| paid_avg_amount | shipped_avg_amount |
+-----------------+--------------------+
|           99.00 |             190.00 |
+-----------------+--------------------+

条件不成立时返回 NULL，AVG() 会忽略这些行。如果写 ELSE 0，其他状态会按零进入分母，平均值就变成另一个问题。

计算占比时保护分母

退款订单率可以写成：

sql

SELECT
    ROUND(
        100.0 * SUM(CASE WHEN status = '已退款' THEN 1 ELSE 0 END)
        / NULLIF(COUNT(*), 0),
        2
    ) AS refund_order_rate_pct
FROM orders;

结果是 7.69。100.0 让表达式按小数计算，NULLIF(COUNT(*), 0) 避免空数据时除以零。某些数据库会对整数除法截断，因此比率计算最好显式引入小数。

MySQL 的布尔求和简写要克制使用

在 MySQL 中，真假条件可参与数值运算，因此下面写法成立：

sql

SUM(status = '已支付')

它很短，但不是所有数据库都把布尔值当作 1 和 0。跨数据库课程和团队代码使用完整 CASE 更清楚：

sql

SUM(CASE WHEN status = '已支付' THEN 1 ELSE 0 END)

小满商店的连贯经营分析

现在把前面的语法收拢成一组分析。我们不追求堆很多彼此无关的查询，而是从同一份固定数据逐步回答更具体的问题。每一步开始前，先写清结果粒度和有效销售口径。

全店概况：先建立对账基线

问题是“固定数据里一共完成多少笔有效订单，涉及多少位客户，销售额和平均订单金额是多少”。结果中的一行代表全店：

sql

SELECT
    COUNT(*) AS order_count,
    COUNT(DISTINCT customer_id) AS customer_count,
    SUM(total_amount) AS revenue,
    ROUND(AVG(total_amount), 2) AS avg_order_amount,
    MIN(total_amount) AS min_order_amount,
    MAX(total_amount) AS max_order_amount
FROM orders
WHERE status IN ('已支付',

text

+-------------+----------------+---------+------------------+------------------+------------------+
| order_count | customer_count | revenue | avg_order_amount | min_order_amount | max_order_amount |
+-------------+----------------+---------+------------------+------------------+------------------+
|          10 |              7 | 1482.70 |           148.27 |            86.00 |           227.00 |
+-------------+----------------+---------+------------------+------------------+------------------+

这条查询是后面对账的订单基线：按城市或客户拆分的订单销售额重新加总后，应回到 1482.70。商品明细折后金额不一定等于这个数，因为 orders.total_amount 可以包含没有拆成明细字段的订单级优惠或运费。

如果只统计一个时间区间，日期时间字段建议用左闭右开边界：

sql

WHERE order_date >= '2025-01-01'
  AND order_date <  '2026-01-01'

比起 <= '2025-12-31'，这种写法不会遗漏 12 月 31 日零点之后的记录。

年度趋势：先看规模，再看平均值

结果中的一行代表一个自然年：

sql

SELECT
    YEAR(order_date) AS order_year,
    COUNT(*) AS order_count,
    COUNT(DISTINCT customer_id) AS customer_count,
    SUM(total_amount) AS revenue,
    ROUND(AVG(total_amount), 2) AS avg_order_amount
FROM orders
WHERE status IN ('已支付', '已发货', '已完成')

text

+------------+-------------+----------------+---------+------------------+
| order_year | order_count | customer_count | revenue | avg_order_amount |
+------------+-------------+----------------+---------+------------------+
|       2025 |           4 |              4 |  630.80 |           157.70 |
|       2026 |           6 |              6 |  851.90 |           141.98 |
+------------+-------------+----------------+---------+------------------+

2026 年的有效订单数和销售额都高于 2025 年，但平均订单金额从 157.70 降到 141.98。因此“销售额增长”不能自动解释为“每笔订单更大”。它也可能由订单数增加带来。

仅靠这一层分组，我们能比较两个已知年份，却还不能很自然地把“上一年销售额”放到当前行计算增长率。那需要让一层查询读取另一层聚合结果，下一章的子查询会接手。

城市表现：把 WHERE 与 HAVING 放回业务问题

现在只看销售额至少 300 元的城市。结果中的一行代表一个客户城市：

sql

SELECT
    c.city,
    COUNT(*) AS order_count,
    COUNT(DISTINCT o.customer_id) AS customer_count,
    SUM(o.total_amount) AS revenue,
    ROUND(AVG(o.total_amount), 2) AS avg_order_amount
FROM orders o
JOIN

text

+------+-------------+----------------+---------+------------------+
| city | order_count | customer_count | revenue | avg_order_amount |
+------+-------------+----------------+---------+------------------+
| 苏州 |           2 |              1 |  410.90 |           205.45 |
| 上海 |           2 |              1 |  338.00 |           169.00 |
+------+-------------+----------------+---------+------------------+

从 orders 与 customers 的匹配行开始，因为城市属于客户，不属于订单。

WHERE 先限定有效订单状态，因此待支付、取消和退款订单都不会进入分组。

GROUP BY c.city 把剩余订单按客户城市分成七组。

聚合函数分别计算每组的订单数、客户数、销售额和平均订单金额。

如果把 HAVING SUM(o.total_amount) >= 300 误写成 WHERE o.total_amount >= 300，问题会被改成“只统计单笔金额至少 300 元的订单”。固定数据中没有任何一笔订单达到 300 元，结果会变成空集。

客户贡献：同一客户的多笔订单汇成一行

找出累计有效消费至少 200 元的客户。结果中的一行代表一位客户：

sql

SELECT
    c.customer_id,
    c.customer_name,
    COUNT(*) AS order_count,
    SUM(o.total_amount) AS revenue,
    ROUND(AVG(o.total_amount), 2) AS avg_order_amount
FROM customers c
JOIN orders o ON o.customer_id

text

+-------------+---------------+-------------+---------+------------------+
| customer_id | customer_name | order_count | revenue | avg_order_amount |
+-------------+---------------+-------------+---------+------------------+
|           4 | 江行          |           2 |  410.90 |           205.45 |
|           2 | 顾言          |           2 |  338.00 |           169.00 |
|           1 | 苏小满        |           2 |  218.80 |           109.40 |
+-------------+---------------+-------------+---------+------------------+

江行的累计有效消费最高，平均订单金额也最高。固定数据里这两个排名恰好一致，但查询仍应保留明确指标；换一批数据，累计金额最高的人未必拥有最高平均订单金额。

商品表现：连接明细表后重新确认粒度

商品销量存在 order_items。如果结果中的一行代表一个商品，应从订单明细出发，连接订单筛选状态，再连接商品取得名称：

sql

SELECT
    p.product_id,
    p.product_name,
    SUM(i.quantity) AS units_sold,
    COUNT(DISTINCT i.order_id) AS order_count,
    ROUND(
        SUM(i.quantity * i.unit_price * (1 - i.

text

+------------+--------------------+------------+-------------+--------------+
| product_id | product_name       | units_sold | order_count | item_revenue |
+------------+--------------------+------------+-------------+--------------+
|          5 | 65W 氮化镓充电器  |          3 |           3 |       463.69 |
|          9 | 轻量折叠伞         |          2 |           2 |       193.00 |
|          6 | 编织数据线         |          5 |           4 |       184.30 |
|          7 | 亚麻抱枕           |          2 |           1 |       150.01 |
|          3 | 四季手账本         |          3 |           2 |       135.00 |
|          8 | 暖光阅读灯         |          1 |           1 |       120.00 |
|          4 | 雾蓝中性笔套装     |          3 |           3 |        87.70 |
|          1 | 白瓷马克杯         |          2 |           2 |        79.80 |
|          2 | 原木托盘           |          1 |           1 |        79.00 |
+------------+--------------------+------------+-------------+--------------+

编织数据线卖出五件，件数最多；65W 氮化镓充电器的明细折后金额最高。销量榜必须说明按件数还是按金额排序。

COUNT(DISTINCT i.order_id) 统计包含该商品的订单数。明细表的一行不等于一件商品，因为 quantity 可能大于一；也不一定等于一张订单，因为一张订单有多条明细。

分类表现：订单数不能用明细行数代替

再向上连接分类表，结果中的一行代表一个商品分类：

sql

SELECT
    c.category_id,
    c.category_name,
    COUNT(DISTINCT o.order_id) AS order_count,
    SUM(i.quantity) AS units_sold,
    ROUND(
        SUM(i.quantity * i.unit_price * (1 - i.

text

+-------------+---------------+-------------+------------+--------------+
| category_id | category_name | order_count | units_sold | item_revenue |
+-------------+---------------+-------------+------------+--------------+
|           4 | 数码配件      |           5 |          8 |       647.98 |
|           1 | 居家生活      |           2 |          3 |       270.00 |
|           3 | 文具手账      |           4 |          6 |       222.70 |
|           5 | 户外出行      |           2 |          2 |       193.00 |
|           2 | 厨房用品      |           2 |          3 |       158.80 |
+-------------+---------------+-------------+------------+--------------+

分类订单数相加会大于十，因为同一张订单可以包含不同分类的商品。分类内的 DISTINCT 只保证“一张订单在同一个分类里数一次”，不会让分类之间互斥。

各分类的明细折后金额由于分别保留两位小数，相加时还可能出现一分钱的展示差异。更关键的是，它本来就不必等于订单销售额 1482.70：订单最终金额可以包含订单级优惠或运费，明细折后金额则严格按 quantity * unit_price * (1 - discount_rate) 计算。

支付方式：使用支付表自己的状态

结果中的一行代表一种支付方式，并且只统计成功支付记录：

sql

SELECT
    payment_method,
    COUNT(*) AS payment_count,
    SUM(amount) AS paid_amount,
    ROUND(AVG(amount), 2) AS avg_payment_amount,
    MAX(amount) AS max_payment_amount
FROM payments
WHERE status = '成功'
GROUP BY payment_method
ORDER BY paid_amount DESC;

text

+----------------+---------------+-------------+--------------------+--------------------+
| payment_method | payment_count | paid_amount | avg_payment_amount | max_payment_amount |
+----------------+---------------+-------------+--------------------+--------------------+
| 支付宝         |             4 |      705.00 |             176.25 |             227.00 |
| 微信           |             4 |      494.80 |             123.70 |             190.00 |
| 银行卡         |             2 |      282.90 |             141.45 |             183.90 |
+----------------+---------------+-------------+--------------------+--------------------+

当前数据中，十笔有效订单恰好各有一条成功支付，因此成功支付金额合计与订单销售额都为 1482.70。真实系统可能有分次支付、失败重试或部分退款；那时订单数、支付次数与支付金额必须分别定义。

注册后 30 天内的成交：条件按订单发生

把下单时间落在注册后 30 天内的有效订单视为“注册后 30 天内成交”：

sql

SELECT
    SUM(CASE
            WHEN DATEDIFF(o.order_date, c.registered_at) <= 30
            THEN o.total_amount
            ELSE 0
        END) AS first_30_day_revenue,
    SUM(CASE
            WHEN DATEDIFF(o.order_date, c.registered_at) >

text

+----------------------+---------------+
| first_30_day_revenue | later_revenue |
+----------------------+---------------+
|               289.00 |       1193.70 |
+----------------------+---------------+

这里分类的是订单，不是给客户贴永久标签。程一的订单 108 和安禾的订单 110 都发生在注册当天，因此进入第一列。若业务要按“客户首次有效下单月份”固定分群，就需要先求每位客户的首次有效订单，再把结果交给外层查询。这个多阶段问题会自然带到下一章。

聚合查询的常见错法与排查顺序

报表数字异常时，最有效的排查方式不是反复换函数，而是从输入行开始逐层核对。

错误：COUNT(1)、COUNT(*) 和 COUNT(列)混为一谈

在常见数据库中，COUNT(*) 与 COUNT(1) 都是在统计输入行，优化器通常能有效处理。COUNT(email) 才会忽略邮箱为 NULL 的行。

如果需求是订单数，优先写能表达行口径的 COUNT(*)；连接一对多表后，可能需要 COUNT(DISTINCT o.order_id)。不要靠“COUNT(1) 一定更快”之类的旧经验替代执行计划和真实测试。

错误：连接 order_items 后直接 SUM(orders.total_amount)

下面的查询会重复订单总额：

sql

SELECT SUM(o.total_amount) AS wrong_revenue
FROM orders o
JOIN order_items i ON i.order_id = o.order_id
WHERE o.status IN ('已支付', '已发货', '已完成');

订单 101 有两条明细，118.90 会在连接结果中出现两次。SUM(DISTINCT o.total_amount) 也不是可靠修复，因为两张不同订单可能金额相同。

正确做法取决于问题：

统计订单成交金额时，只从 orders 聚合。
统计商品明细金额时，从 order_items 聚合 quantity * unit_price * (1 - discount_rate)。
同时需要两种粒度时，先分别汇总，再连接汇总结果。这个“先汇总再连接”会在子查询章节展开。

错误：用 WHERE 过滤聚合结果

症状通常是“Invalid use of group function”或类似错误：

sql

WHERE SUM(total_amount) > 300

排查时问：这个条件能否只看一条原始订单就判断？如果不能，必须等分组算完，放到 HAVING。

错误：SELECT 中出现不属于当前粒度的普通列

sql

SELECT city, order_id, SUM(total_amount)
...
GROUP BY city;

如果结果一行代表城市，就无法确定一个唯一 order_id。看到 ONLY_FULL_GROUP_BY 错误时，不要先关模式，先用自然语言重述结果粒度，再删除、聚合或补充分组键。

错误：把 HAVING 当作所有条件的统一入口

日期范围、订单状态等明细条件可以在分组前判断，应该放 WHERE。这样既符合语义，也让数据库更早缩小输入集。

sql

WHERE order_date >= '2025-01-01'
  AND order_date < '2026-01-01'
  AND status IN ('已支付', '已发货', '已完成')
GROUP BY ...
HAVING SUM(total_amount) >= 300

错误：以为 SUM 没有行时返回 0

空输入下，COUNT(*) 返回 0，而 SUM() 通常返回 NULL。报表确实要展示零时，在最外层输出处使用 COALESCE(SUM(...), 0)，并确认“无数据按零显示”符合业务解释。

错误：条件平均值写 ELSE 0

sql

AVG(CASE WHEN status = '已支付' THEN total_amount ELSE 0 END)

这会把所有非 已支付 订单都按零元纳入分母。若问题是“处在已支付状态订单的平均金额”，应让不符合条件的行返回 NULL：

sql

AVG(CASE WHEN status = '已支付' THEN total_amount END)

错误：用格式化字符串排序月份却省略年份

sql

DATE_FORMAT(order_date, '%m')

只取月份会把不同年份的一月放进同一组。如果报告跨年，应至少包含年和月：

sql

DATE_FORMAT(order_date, '%Y-%m')

若还要进行日期运算，保留日期类型的月份起点通常比只保留展示字符串更稳。

错误：ROLLUP 的 NULL 全部替换成“合计”

原始分组键本身可能为空。使用 COALESCE(city, '合计') 会把“城市未知”和“全部城市”混成一个标签。应组合 GROUPING(city) 与原始 city IS NULL 分别判断。

练习：把问题先翻译成结果粒度

以下练习都基于小满商店的固定种子数据。先独立写出“结果中的一行代表什么”，再展开答案。

练习一：按状态核对订单

统计每种订单状态的订单数、金额合计、最低金额与最高金额，并按订单数降序、状态升序排列。

结果中的一行代表一种订单状态。

sql

SELECT
    status,
    COUNT(*) AS order_count,
    SUM(total_amount) AS amount,
    MIN(total_amount) AS min_amount,
    MAX(total_amount) AS max_amount
FROM orders
GROUP BY status
ORDER BY order_count DESC, status ASC;

text

+--------+-------------+---------+------------+------------+
| status | order_count | amount  | min_amount | max_amount |
+--------+-------------+---------+------------+------------+

练习二：统计联系方式缺失

用一条不分组的查询返回客户总数、有邮箱客户数、无邮箱客户数、有手机号客户数和城市数量。

结果中的一行代表整个客户表。

sql

SELECT
    COUNT(*) AS customer_count,
    COUNT(email) AS customers_with_email,
    COUNT(*) - COUNT(email) AS customers_without_email,
    COUNT(phone) AS customers_with_phone,
    COUNT(DISTINCT city) AS city_count
FROM customers;

text

+----------------+----------------------+-------------------------+----------------------+------------+
| customer_count | customers_with_email | customers_without_email | customers_with_phone | city_count |
+----------------+----------------------+-------------------------+----------------------+------------+

练习三：按年份观察有效销售

按年份统计有效订单数与销售额，只保留至少四笔有效订单的年份。

结果中的一行代表一个自然年。状态条件在明细层，订单数条件在分组层：

sql

SELECT
    YEAR(order_date) AS order_year,
    COUNT(*) AS order_count,
    SUM(total_amount) AS revenue
FROM orders
WHERE status IN ('已支付', '已发货', '已完成')
GROUP BY YEAR(order_date)
HAVING COUNT(*) >= 4
ORDER BY order_year;

text

练习四：做一张城市状态横表

每个城市一行，分别显示已完成、已退款、已取消和待支付订单数，以及有效销售额。按有效销售额从高到低排列。

sql

SELECT
    c.city,
    SUM(CASE WHEN o.status = '已完成' THEN 1 ELSE 0 END) AS completed_orders,
    SUM(CASE WHEN o.status = '已退款' THEN 1 ELSE 0 END) AS refunded_orders,
    SUM(CASE WHEN o

练习五：按年度比较支付方式

每个支付年份、每种支付方式一行，只统计成功支付，显示支付次数、支付金额与平均支付金额。

结果中的一行代表“一个年份与一种支付方式”的组合：

sql

SELECT
    YEAR(paid_at) AS paid_year,
    payment_method,
    COUNT(*) AS payment_count,
    SUM(amount) AS paid_amount,
    ROUND(AVG(amount), 2) AS avg_payment_amount
FROM payments
WHERE status = '成功'
GROUP BY YEAR(paid_at), payment_method
ORDER BY paid_year, paid_amount DESC;

text

练习六：找出至少覆盖三张订单的商品

统计每个商品的有效销售件数、涉及订单数与明细折后金额，只保留至少出现在三张有效订单中的商品。

练习七：生成城市小计与全店总计

用 ROLLUP 输出每个城市、每种状态的金额，附带城市小计和全店总计。要正确区分原始 NULL 与汇总行。

sql

SELECT
    CASE
        WHEN GROUPING(c.city) = 1 THEN '全部城市'
        WHEN c.city IS NULL THEN '城市未知'
        ELSE c.city
    END AS city_label,
    CASE
        WHEN GROUPING(o.status) = 1 THEN '全部状态'
        ELSE o

练习八：识别并修复重复销售额

下面查询想按分类统计订单数与销售额，但结果偏大。说明原因并修复：

sql

SELECT
    c.category_name,
    COUNT(*) AS order_count,
    SUM(o.total_amount) AS revenue
FROM orders o
JOIN order_items i ON i.order_id = o.order_id
JOIN products p ON p.product_id = i.product_id
JOIN categories c

连接后的一行是订单商品明细，不是一张订单。COUNT(*) 数的是明细行；SUM(o.total_amount) 会让有多条明细的订单总额重复出现。

分类涉及订单数可以写成 COUNT(DISTINCT o.order_id)。分类明细折后金额则按固定公式求和：

sql

SELECT
    c.category_id,
    c.category_name,
    COUNT(DISTINCT o.order_id) AS order_count,
    ROUND(
        SUM(i.quantity * i.unit_price * (1 - i.discount_rate)),
        2
    )

从分组结果继续追问

到这里，我们已经能把订单明细压缩成全店、年份、城市、客户、商品和分类指标。写任何聚合查询时，可以用下面这份清单收尾：

结果中的一行代表什么？
输入来自哪张表，连接后的一行又代表什么？
哪些明细应在 WHERE 阶段排除？
COUNT(*) 数的是行，还是应该对业务主键 DISTINCT？
NULL 被忽略后，分子和分母是否仍符合口径？
选择列表中的普通列是否都属于分组粒度？
组级条件是否放在 HAVING？
ROLLUP 的列顺序是否对应想要的小计层级？
条件聚合中，不符合条件的行应该贡献 0 还是 NULL？
拆分后的金额与数量能否重新对回全店基线？

还有一类问题，本章故意没有强行塞进单层聚合里：哪些客户的累计消费高于所有客户的平均累计消费？哪个商品的销售额高于本分类平均值？怎样把 2026 年金额和 2025 年金额放在同一行计算增长率？

这些问题都要求“先得到一份聚合结果，再把这份结果交给另一层查询”。下一章的子查询会让一条查询使用另一条查询的结果。你会发现，子查询并不是突然增加的新语法负担，它只是把我们已经会写的聚合查询包成下一步的数据来源。

+------------+-------------+---------+
| order_year | order_count | revenue |
+------------+-------------+---------+
|       2025 |           4 |  630.80 |
|       2026 |           6 |  851.90 |
+------------+-------------+---------+

text

+------------+--------------------+------------+-------------+--------------+
| product_id | product_name       | units_sold | order_count | item_revenue |
+------------+--------------------+------------+-------------+--------------+
|          5 | 65W 氮化镓充电器  |          3 |           3 |       463.69 |
|          6 | 编织数据线         |          5 |           4 |       184.30 |
|          4 | 雾蓝中性笔套装     |          3 |           3 |        87.70 |
+------------+--------------------+------------+-------------+--------------+

分组与聚合：从订单明细看到经营指标

聚合改变的是结果粒度

先写一句“结果中的一行代表什么”

本章使用的数据关系

五个核心聚合函数各自回答什么

COUNT：先问“数行”还是“数值”

SUM：合计的是表达式，不一定是原始列

AVG：分母由非空输入数量决定

MIN 与 MAX：数值、日期、文本都可以比较

一次查询可以产生多个指标

空输入集不等于“一行都不返回”

NULL 如何改变统计口径

用 COUNT 的差值找缺失数据

AVG 忽略 NULL，不会把它当成零

表达式中任一项为 NULL，整项可能被忽略

NULL 也会形成一个分组

DISTINCT 去掉的是重复输入

SELECT DISTINCT 与 COUNT(DISTINCT ...) 不在同一层

DISTINCT 也能用于 SUM 和 AVG，但要小心业务含义

多列唯一组合存在方言差异

聚合表达式先逐行计算再汇总

销售数量与销售额是两个指标

ROUND 放在聚合外还是聚合内

比率不要平均比率，先看分子分母

GROUP BY 定义结果中的分组键

单列分组：每种状态一行

多列分组：每种组合一行

按表达式分组：把日期加工成年份

时间分组先确认“自然月”和“最近 30 天”

别依赖 GROUP BY 的输出顺序

别用列位置隐藏真实含义

WHERE 与 HAVING 分别过滤明细和分组

WHERE：不让某些明细进入统计

HAVING：分组算完后再决定保留哪些组

为什么聚合条件不能写在 WHERE

能放 WHERE 的条件，不要习惯性塞进 HAVING

HAVING 中使用别名的兼容性

同时约束组大小和组内平均值

ONLY_FULL_GROUP_BY 在阻止不确定结果

合法选择列表的三类内容

不要关闭严格模式掩盖问题

ANY_VALUE 只表示“不关心取哪一个”

主键分组与函数依赖

ROLLUP 在一份结果中加入小计与总计

MySQL 的 WITH ROLLUP

不要仅凭 NULL 判断汇总行

读懂一段 ROLLUP 结果

PostgreSQL 的 ROLLUP 写法

用 HAVING 选择汇总层级

条件聚合把一组数据横向展开成指标

用 SUM(CASE ...) 统计数量

COUNT(CASE ...) 与 SUM(CASE ...) 的差别

用 SUM(CASE ...) 统计不同金额口径

条件聚合与 GROUP BY 可以叠加

条件平均值只让目标行进入 AVG

计算占比时保护分母

MySQL 的布尔求和简写要克制使用

小满商店的连贯经营分析

全店概况：先建立对账基线

年度趋势：先看规模，再看平均值

城市表现：把 WHERE 与 HAVING 放回业务问题

客户贡献：同一客户的多笔订单汇成一行

商品表现：连接明细表后重新确认粒度

分类表现：订单数不能用明细行数代替

支付方式：使用支付表自己的状态

注册后 30 天内的成交：条件按订单发生

聚合查询的常见错法与排查顺序

错误：COUNT(1)、COUNT(*) 和 COUNT(列)混为一谈

错误：连接 order_items 后直接 SUM(orders.total_amount)

错误：用 WHERE 过滤聚合结果

错误：SELECT 中出现不属于当前粒度的普通列

错误：把 HAVING 当作所有条件的统一入口

错误：以为 SUM 没有行时返回 0

错误：条件平均值写 ELSE 0

错误：用格式化字符串排序月份却省略年份

错误：ROLLUP 的 NULL 全部替换成“合计”

推荐的六步对账法

练习：把问题先翻译成结果粒度

练习一：按状态核对订单

练习二：统计联系方式缺失