9.2 聚合查询：聚合函数、GROUP BY 分组、HAVING 过滤

聚合查询是 SQL 中最常用的分析手段之一。当你需要“统计每个部门的平均工资”“按月份汇总订单量”“找出重复注册的手机号”时，本质上都是在做聚合操作。它把多行数据压缩为一行或少数几行，让你从细节中跳出来，看到数据的整体特征。

9.2.1 聚合函数：从多行中提取一个值

聚合函数接收一组值，返回一个单一的计算结果。MySQL 提供的内置聚合函数足以覆盖绝大多数分析需求，最常用的有以下几种：

COUNT()

COUNT() 用于统计行数，但它有几种容易踩坑的写法：

-- 统计全表行数（包含所有列、包含 NULL 行）
SELECT COUNT(*) FROM orders;

-- 统计某列非 NULL 值的行数
SELECT COUNT(user_id) FROM orders;

-- 统计去重后的用户数
SELECT COUNT(DISTINCT user_id) FROM orders;

关键区别：COUNT() 会统计所有行，包括全为 NULL 的行，而 COUNT(列名) 只统计该列不为 NULL 的行。在 InnoDB 中，COUNT() 会扫描整个索引，通常选择最小的非空索引来优化性能。如果你只是想判断“有没有数据”，用 EXISTS 比 COUNT(*) 更高效，因为后者需要统计完所有行。

另外，COUNT(1) 和 COUNT() 在 MySQL 中性能上没有差别，优化器会将它们等同处理。团队内部统一用 COUNT() 即可，语义清晰。

SUM() 与 AVG()

SUM() 计算数值列的总和，AVG() 计算平均值。它们都自动忽略 NULL 值（就像那些不存在的数据被跳过，不影响分母）。

SELECT SUM(amount) AS total_amount,
       AVG(amount) AS avg_amount
FROM orders
WHERE status = 'completed';

需要注意：如果列中所有值都是 NULL，SUM() 返回 NULL 而不是 0，AVG() 同样返回 NULL。如果业务上需要显示 0，可以用 COALESCE(SUM(amount), 0) 包装。

另一个常见陷阱：不要直接用 AVG() 计算“平均单价”，因为如果某些行的数量或权重不同，算术平均值会失真。正确做法是用 SUM(total_amount) / SUM(quantity)。

MAX() 与 MIN()

这两个函数用于取最大值和最小值。它们不仅适用于数值，也适用于字符串和日期类型，遵循对应的比较规则。

SELECT MAX(create_time) AS last_order_time,
       MIN(score) AS lowest_score
FROM user_activity;

配合 GROUP BY 使用时，MAX() 和 MIN() 取的是每个分组内的极值，这个在使用时需要明确。

GROUP_CONCAT()

这是 MySQL 独有的一个强大函数，可以将分组中的多个行的某个列值拼接成一个字符串，默认用逗号分隔。

-- 查询每个订单中的所有商品名称列表
SELECT order_id,
       GROUP_CONCAT(product_name) AS products
FROM order_items
GROUP BY order_id;

你可以自定义分隔符、排序、去重：

SELECT order_id,
       GROUP_CONCAT(DISTINCT product_name ORDER BY product_name ASC SEPARATOR '; ')
FROM order_items
GROUP BY order_id;

需要注意 GROUP_CONCAT() 的默认最大返回长度是 1024 字节，由 group_concat_max_len 参数控制。如果拼接的内容很长，需要调大这个值，否则会被静默截断，这在排查数据不全问题时很常见。

9.2.2 GROUP BY：把数据分成多个逻辑组

GROUP BY 是聚合的“分界线”，它把表中的行按某一列或多列的值分成若干组，然后聚合函数在每组内独立计算。没有 GROUP BY 时，聚合函数作用于整个表，只返回一行结果；有了 GROUP BY，每个分组返回一行。

基础用法

-- 按状态统计订单数量和总金额
SELECT status,
       COUNT(*) AS order_count,
       SUM(amount) AS total_amount
FROM orders
GROUP BY status;

这条 SQL 的执行逻辑是：先把 orders 表中的行按 status 的值分成若干组（比如 'pending'、'completed'、'cancelled'），然后分别对每组进行 COUNT 和 SUM 计算。

多列分组

你可以按多个列分组，分组的粒度会更细：

-- 按日期和状态统计订单量
SELECT DATE(create_time) AS order_date,
       status,
       COUNT(*) AS cnt
FROM orders
GROUP BY order_date, status;

这会生成每天每个状态一行，相当于 Excel 中的二维透视表行式排列。

GROUP BY 与 SELECT 列的规则

一个非常重要的规则：在开启了 ONLY_FULL_GROUP_BY 模式（MySQL 5.7+ 默认开启）时，SELECT 列表中出现的非聚合列，必须出现在 GROUP BY 子句中。 否则会报错。

这是为了防止“随机取值”问题。例如：

-- 报错！name 不在 GROUP BY 中
SELECT dept_id, name, MAX(salary)
FROM employees
GROUP BY dept_id;

因为一个部门内有多个员工，MAX(salary) 是确定的，但 name 该取哪个人？如果不强制，MySQL 可能返回任意一个 name，这在业务上是危险的。正确写法要么把 name 也加入 GROUP BY（如果确实需要按人分组），要么使用聚合函数取 name（如 MIN(name)），或者使用子查询取最高薪员工的名字。

GROUP BY 的排序与性能

在 MySQL 8.0 之前，GROUP BY 默认会按分组列排序，这一隐式排序可能带来额外的排序开销。MySQL 8.0 后移除了这个默认行为，如果需要排序，明确写 ORDER BY 即可。

对于大表分组，确保 GROUP BY 列上有索引，可以避免“临时表 + 文件排序”，大幅提升性能。EXPLAIN 中出现 Using temporary; Using filesort 往往就是 GROUP BY 导致的，需要重点关注。

9.2.3 HAVING：对分组结果再过滤

WHERE 是在分组前过滤行，HAVING 是在分组后过滤组。这是两者最本质的区别。

基础用法

-- 筛选订单数超过 5 的客户
SELECT customer_id,
       COUNT(*) AS order_cnt
FROM orders
GROUP BY customer_id
HAVING order_cnt > 5;

这里你不能在 WHERE 中写 COUNT(*) > 5，因为在分组还没进行的时候，聚合值还不存在。

同时使用 WHERE 和 HAVING

WHERE 和 HAVING 可以同时存在，此时执行顺序是：WHERE 过滤行 → GROUP BY 分组 → 聚合计算 → HAVING 过滤组。

-- 先筛选已完成的订单，再找订单总额超过 1000 的客户
SELECT customer_id,
       SUM(amount) AS total_spent
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
HAVING total_spent > 1000;

这种写法不仅逻辑清晰，性能也更好：先用 WHERE 缩小数据范围，分组和聚合的数据量就小了，最后 HAVING 再滤掉不符合条件的分组。

常见错误：在 WHERE 中使用聚合函数

-- 错误！WHERE 中不能使用聚合函数
SELECT department, AVG(salary) 
FROM employees
WHERE AVG(salary) > 5000
GROUP BY department;

这是很多新手会犯的错误。必须改写成 HAVING：

SELECT department, AVG(salary) AS avg_sal
FROM employees
GROUP BY department
HAVING avg_sal > 5000;

HAVING 的别名使用

在 MySQL 中，HAVING 子句可以使用 SELECT 中定义的别名（如 avg_sal），但 WHERE 不能。这也是 HAVING 的一个便利之处。

9.2.4 聚合查询的完整执行顺序

理解一条完整聚合 SQL 的执行步骤，有助于你定位问题：

FROM / JOIN：确定数据源，执行表连接。
WHERE：对原始行进行过滤。
GROUP BY：将数据分组。
聚合函数计算：在每个组内计算聚合值。
HAVING：对分组结果进行过滤。
SELECT：选出需要返回的列。
ORDER BY：对最终结果排序。
LIMIT：限制返回行数。

注意，SELECT 中的别名在 WHERE 中不能使用，因为 WHERE 执行时别名还未定义；但在 ORDER BY 中可以，在 HAVING 中也可以（MySQL 扩展，标准 SQL 要求重复表达式）。遵循这个顺序，你就能有条理地构造和调试聚合查询。

9.2.5 实用场景与调优提示

场景一：统计每日新增用户

SELECT DATE(register_time) AS day,
       COUNT(*) AS new_users
FROM users
GROUP BY day
ORDER BY day;

确保 register_time 上有索引，避免全表扫描和文件排序。

场景二：找出重复记录

SELECT email, COUNT(*) AS cnt
FROM users
GROUP BY email
HAVING cnt > 1;

结合去重或后续清理操作，可以先定位再修正。

场景三：按区间分组统计

有时需要自定义区间，比如按消费金额分档：

SELECT 
  CASE 
    WHEN amount < 100 THEN '0-99'
    WHEN amount < 500 THEN '100-499'
    WHEN amount < 1000 THEN '500-999'
    ELSE '1000+'
  END AS level,
  COUNT(*) AS cnt
FROM orders
GROUP BY level;

调优提示

给 GROUP BY 列建索引，最好能成覆盖索引，这样可以利用索引的有序性直接分组，避免创建临时表。
避免对 GROUP BY 列使用函数（如 DATE(create_time)），这会导致索引失效。可以考虑创建函数索引（MySQL 8.0.13+ 支持）或者添加冗余列存储转换后的值。
对于超大表的分组统计，如果时效性要求不高，可考虑使用汇总表定时预计算，或者借助物化视图（MySQL 无物化视图，可手动创建汇总表并定时更新）。

聚合查询是数据分析的基础，写法看似简单，但其中关于执行顺序、索引利用、NULL 处理等细节，直接决定了查询结果的正确性和性能。把这一节练熟练透，你就能从容应对绝大多数统计类需求。