分组聚合与统计口径
🎯 引言
学完这篇文章,你能统计人数和平均值,并区分筛选记录与筛选统计结果。
🔢 COUNT 统计的是行还是已填写的值
本篇使用的 query_user 原始数据如下。小安的年龄为 NULL,表示未填写;后面的计数和平均值都根据这五行计算。
| id | username | age | city | created_at |
|---|---|---|---|---|
| 1 | 小明 | 25 | 北京 | 2026-10-01 09:00:00 |
| 2 | 小红 | 30 | 上海 | 2026-10-02 09:00:00 |
| 3 | 小刚 | 22 | 北京 | 2026-10-03 09:00:00 |
| 4 | 小林 | 30 | 上海 | 2026-10-04 09:00:00 |
| 5 | 小安 | NULL | 杭州 | 2026-10-05 09:00:00 |
需要创建这张表时,使用 查询练习数据 中的建表与插入语句。先比较两种计数方式:
SELECT COUNT(*) AS total, COUNT(age) AS age_count FROM query_user;
查询结果:
| total | age_count |
|---|---|
| 5 | 4 |
COUNT(*) 统计记录数;COUNT(age) 只统计年龄不是 NULL 的记录。统计“有多少用户”和“多少用户填了年龄”时,不能混用。
➗ AVG 忽略未填写的数据
SELECT AVG(age) AS average_age FROM query_user;
查询结果:
| average_age |
|---|
| 26.75 |
小数显示位数可能不同。计算使用四个已填写年龄:(25 + 30 + 22 + 30) ÷ 4。未知年龄不会被当成 0。
SUM(age) 同样忽略 NULL,表示已知年龄之和;本例为 107。没有匹配行时,SUM 和 AVG 返回 NULL,而 COUNT 返回 0,应用需要按统计含义处理。
🏙 GROUP BY 按城市分别统计
SELECT city, COUNT(*) AS user_count
FROM query_user GROUP BY city ORDER BY user_count DESC, city;
查询结果:
| city | user_count |
|---|---|
| 上海 | 2 |
| 北京 | 2 |
| 杭州 | 1 |
每个城市变成一行,user_count 是这一组的记录数;相同人数时按城市排序,具体中文比较次序由排序规则决定。
GROUP BY 会把多行合为一组,所以不能随意把 username 也放进结果:同一城市有多个名字,数据库无法替你决定该显示谁。需要用户明细时就不要用分组把它们合并。
🔍 WHERE 先选参与统计的记录
只统计已满 25 岁的用户:
SELECT city, COUNT(*) AS user_count FROM query_user
WHERE age >= 25 GROUP BY city ORDER BY city;
查询结果:
| city | user_count |
|---|---|
| 上海 | 2 |
| 北京 | 1 |
杭州没有符合条件的记录,因此不出现。WHERE 在分组前筛选原始记录。
🧮 HAVING 再筛选统计结果
找出至少两位用户的城市:
SELECT city, COUNT(*) AS user_count FROM query_user
GROUP BY city HAVING COUNT(*) >= 2 ORDER BY city;
查询结果:
| city | user_count |
|---|---|
| 上海 | 2 |
| 北京 | 2 |
HAVING 检查分组后的结果;不能把 COUNT(*) >= 2 放到 WHERE,因为那时还没有算出每组数量。
✅ 用条件统计两个数量
一个结果里同时显示总人数与成年人数:
SELECT COUNT(*) AS total,
SUM(CASE WHEN age >= 18 THEN 1 ELSE 0 END) AS adult_count
FROM query_user;
查询结果:
| total | adult_count |
|---|---|
| 5 | 4 |
CASE 为满足年龄条件的行计 1,其余计 0,SUM 再相加。这里把未知年龄排除在成年人数外,并不代表认定它未成年。
❓ 知识问答
Q1:COUNT(1) 比 COUNT(*) 更准确吗?
两者在这里都统计行数,COUNT(*) 更直接表达“记录数量”。
Q2:先关联文章再 COUNT(*),得到的是用户数吗?
不一定。一位用户的多篇文章会产生多行,要按用户编号去重或先统计文章。关联篇会用实际结果说明。
Q3:可以关闭分组检查来显示 username 吗?
不应靠关闭检查掩盖查询含义不明确的问题。先决定每组到底要返回哪一条记录。
🧪 小练习
按城市统计平均年龄,再仅保留已填写年龄人数至少为 2 的城市。预期保留北京与上海。
-- 请在这里编写 SQL 或记录验证结果
