聚合与分组

一、聚合函数

函数	说明
COUNT(*)	行数，不忽略任何行
COUNT(col)	col 非 NULL 的行数
COUNT(DISTINCT col)	去重后的非 NULL 值个数
SUM / AVG	忽略 NULL；全为 NULL 或无行时返回 NULL，不是 0
MAX / MIN	忽略 NULL；可用于字符串和日期
GROUP_CONCAT	组内拼接成字符串（MySQL 专有，标准是 STRING_AGG / LISTAGG）
STD / VARIANCE	总体标准差 / 方差；STDDEV_SAMP / VAR_SAMP 为样本版本
BIT_OR / BIT_AND	按位聚合，做标志位合并时很好用

COUNT(*) 和 COUNT(1) 谁快

一样快。 MySQL 8.0 的优化器对 COUNT(*) 有专门处理，COUNT(1) 不会额外求值常量 1，两者执行计划完全相同。InnoDB 会挑选最小的可用二级索引来扫描（因为二级索引叶子节点比聚簇索引小得多），实在没有二级索引才扫主键。

真正需要区分的是 COUNT(col)：它要判断每一行的 col 是否为 NULL，语义就不同。

空结果集上的聚合

SELECT SUM(amount) FROM orders WHERE 1 = 0;   -- 返回一行，值为 NULL
SELECT COUNT(*)   FROM orders WHERE 1 = 0;    -- 返回一行，值为 0

在程序里读 SUM 结果前记得 COALESCE(SUM(amount), 0)，否则很容易吃到 NULL 引发的空指针。

二、GROUP BY 与 ONLY_FULL_GROUP_BY

SELECT department_id, COUNT(*) AS cnt, AVG(salary) AS avg_salary
FROM employee
GROUP BY department_id
HAVING COUNT(*) >= 3          -- 过滤"组"
ORDER BY avg_salary DESC;

核心规则：SELECT 列表里的每一列，要么出现在 GROUP BY 中，要么被聚合函数包裹，要么函数依赖于 GROUP BY 的列。

-- ❌ name 既不在 GROUP BY 中，也没被聚合：这一组里有多个 name，取哪个？
SELECT department_id, name, MAX(salary) FROM employee GROUP BY department_id;

MySQL 5.7 起 sql_mode 默认包含 ONLY_FULL_GROUP_BY，上面这条语句会直接报错。5.6 及以前会随机返回组内某一行的 name，得到的往往不是 MAX(salary) 对应的那一行——这是一个流传极广的错误写法。

千万不要靠关闭 ONLY_FULL_GROUP_BY 来"修好" SQL

它不是限制，是保护。要取"每组最大值所在的整行"，请用窗口函数或关联子查询，而不是让数据库随便挑一行。

MySQL 支持函数依赖检测：如果 GROUP BY 的是主键或唯一非空键，同表其他列可以直接出现在 SELECT 中，这是合法的：

-- 合法：id 是主键，name 函数依赖于 id
SELECT e.id, e.name, COUNT(o.id) FROM employee e
LEFT JOIN orders o ON o.user_id = e.id GROUP BY e.id;

GROUP BY 与排序

MySQL 5.7 及以前，GROUP BY 会隐式按分组列排序；8.0 起移除了这个隐式排序（GROUP BY ... ASC/DESC 语法也被废弃）。需要有序输出必须显式写 ORDER BY——这是从 5.7 升级到 8.0 时非常常见的线上问题。

三、条件聚合（行转列）⭐⭐

聚合函数(CASE WHEN ...) 是最有用的一个 SQL 技巧，能把多次扫描合并成一次。

-- 一次扫描统计各状态订单数与金额
SELECT
    user_id,
    COUNT(*)                                          AS total_cnt,
    SUM(CASE WHEN status = 1 THEN 1 ELSE 0 END)       AS paid_cnt,
    SUM(CASE WHEN status = 1 THEN amount ELSE 0 END)  AS paid_amount,
    -- 用 AVG 直接算占比
    AVG(status = 1)                                   AS paid_rate,
    -- COUNT 里用 CASE 时 ELSE 必须落到 NULL（COUNT 跳过 NULL）
    COUNT(CASE WHEN status = 2 THEN 1 END)            AS cancelled_cnt
FROM orders
GROUP BY user_id;

两个细节

• SUM(CASE WHEN cond THEN 1 ELSE 0 END) 与 COUNT(CASE WHEN cond THEN 1 END) 等价，但后者若写成 COUNT(CASE WHEN cond THEN 1 ELSE 0 END) 就永远等于总行数（0 也是非 NULL 值）。
• MySQL 中布尔表达式的值就是 0/1，所以 SUM(status = 1) 是计数、AVG(status = 1) 是占比。这是 MySQL 扩展，PostgreSQL 需要写 COUNT(*) FILTER (WHERE status = 1)（标准 SQL 的 FILTER 子句）。

标准的行转列模板

-- 把"每人每科一行"转成"每人一行、每科一列"
SELECT
    student_id,
    MAX(CASE WHEN subject = 'math'    THEN score END) AS math,
    MAX(CASE WHEN subject = 'english' THEN score END) AS english,
    MAX(CASE WHEN subject = 'physics' THEN score END) AS physics
FROM score
GROUP BY student_id;

用 MAX 而不是 SUM 的原因：语义上是"取该科目那一行的值"，MAX 会忽略其余行产生的 NULL，同时在有重复数据时不会把值累加错。

列转行

MySQL 没有 UNPIVOT，用 UNION ALL 展开：

SELECT student_id, 'math' AS subject, math AS score FROM wide_score
UNION ALL
SELECT student_id, 'english', english FROM wide_score
UNION ALL
SELECT student_id, 'physics', physics FROM wide_score;

四、GROUP BY 扩展：ROLLUP

WITH ROLLUP 在结果中追加小计与总计行：

SELECT
    COALESCE(department_id, 'ALL') AS dept,
    SUM(salary)
FROM employee
GROUP BY department_id WITH ROLLUP;

小计行中，被"上卷"掉的分组列取值为 NULL。要区分"真正的 NULL 分组"和"小计行"，用 GROUPING() 函数（MySQL 8.0 起支持）：

SELECT
    IF(GROUPING(department_id) = 1, '合计', department_id) AS dept,
    SUM(salary)
FROM employee GROUP BY department_id WITH ROLLUP;

注意 MySQL 不支持标准 SQL 的 CUBE 和 GROUPING SETS。

五、DISTINCT vs GROUP BY

SELECT DISTINCT department_id FROM employee;
SELECT department_id FROM employee GROUP BY department_id;

两者在 MySQL 8.0 中执行计划基本一致（都可能走松散索引扫描 Using index for group-by）。语义上：要去重用 DISTINCT，要聚合用 GROUP BY，不要为了去重写 GROUP BY。

另外记住 DISTINCT 作用于整个选择列表，不是紧跟它的那一列：

SELECT DISTINCT a, b FROM t;    -- 对 (a, b) 组合去重，不是只对 a 去重

---

实战

超过 5 名学生的课

👉 Leetcode 链接-596

Courses(student, class)，查询至少有 5 个学生的所有班级。

考点

HAVING 过滤组。题目保证 (student, class) 不重复，否则要写 COUNT(DISTINCT student)。

select
    class
from
    Courses
group by
    class
having
    count(*) >= 5

查找重复的电子邮箱

👉 Leetcode 链接-182

select
    email as Email
from
    Person
group by
    email
having
    count(*) > 1

各赛事的用户注册率

👉 Leetcode 链接-1633

Users(user_id, user_name)、Register(contest_id, user_id)。求每场比赛的参赛用户占总用户数的百分比，保留 2 位小数，按百分比降序、比赛编号升序排列。

考点

标量子查询做分母。COUNT(*) 是整数除法风险点——MySQL 中整数相除会自动转为 DECIMAL，但仍建议显式乘 100.0 保证精度。

select
    contest_id,
    round(count(distinct user_id) * 100.0 / (select count(*) from Users), 2) as percentage
from
    Register
group by
    contest_id
order by
    percentage desc, contest_id asc

查询结果的质量和占比

👉 Leetcode 链接-1211

Queries(query_name, result, position, rating)。求每个 query_name 的 quality = AVG(rating / position) 和劣质查询占比 poor_query_percentage（rating < 3 的比例 × 100），均保留 2 位小数。

考点

条件聚合算占比的标准模板。注意是 AVG(rating/position) 而不是 AVG(rating)/AVG(position)——先算比值再平均。

select
    query_name,
    round(avg(rating / position), 2) as quality,
    round(avg(rating < 3) * 100, 2)  as poor_query_percentage
from
    Queries
where
    query_name is not null
group by
    query_name

avg(rating < 3) 利用了布尔值即 0/1 的特性，等价于：

round(sum(case when rating < 3 then 1 else 0 end) * 100.0 / count(*), 2)

每月交易 I（行转列）

👉 Leetcode 链接-1193

Transactions(id, country, state, amount, trans_date)，state 取 approved / declined。 查询每个月、每个国家的交易数、已批准交易数、交易总额、已批准交易总额。

考点

条件聚合 + 日期格式化分组。

select
    date_format(trans_date, '%Y-%m')                        as month,
    country,
    count(*)                                                as trans_count,
    sum(state = 'approved')                                 as approved_count,
    sum(amount)                                             as trans_total_amount,
    sum(case when state = 'approved' then amount else 0 end) as approved_total_amount
from
    Transactions
group by
    month, country

生产环境不要这样分组

DATE_FORMAT(trans_date, ...) 让 trans_date 上的索引失效。数据量大时应改为按半开区间过滤 + 预先冗余一个 month 列（或建函数索引 ALTER TABLE t ADD INDEX ((DATE_FORMAT(trans_date,'%Y-%m')))，MySQL 8.0.13+ 支持）。

部门工资前三高的所有员工 ⭐⭐⭐

👉 Leetcode 链接-185

考点

"分组内 Top N"。经典的关联子查询解法（不依赖窗口函数）：统计比自己高的不同薪水个数。

select
    d.name as Department,
    e1.name as Employee,
    e1.salary as Salary
from
    Employee e1
join
    Department d on e1.departmentId = d.id
where
    3 > (
        select count(distinct e2.salary)
        from Employee e2
        where e2.salary > e1.salary
          and e2.departmentId = e1.departmentId
    )

这条子查询对外层每一行都要执行一次，复杂度接近 O(n²)。窗口函数写法只需一次扫描：

select Department, Employee, Salary from (
    select d.name as Department, e.name as Employee, e.salary as Salary,
           dense_rank() over (partition by e.departmentId order by e.salary desc) rk
    from Employee e join Department d on e.departmentId = d.id
) t where rk <= 3

---

下一步 👉 子查询与 CTE