分组聚合与统计口径

统计人数和平均值,并区分筛选记录与筛选统计结果

🎯 引言

学完这篇文章,你能统计人数和平均值,并区分筛选记录与筛选统计结果。


🔢 COUNT 统计的是行还是已填写的值

本篇使用的 query_user 原始数据如下。小安的年龄为 NULL,表示未填写;后面的计数和平均值都根据这五行计算。

idusernameagecitycreated_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;

查询结果:

totalage_count
54

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;

查询结果:

cityuser_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;

查询结果:

cityuser_count
上海2
北京1

杭州没有符合条件的记录,因此不出现。WHERE 在分组前筛选原始记录。


🧮 HAVING 再筛选统计结果

找出至少两位用户的城市:

SELECT city, COUNT(*) AS user_count FROM query_user
GROUP BY city HAVING COUNT(*) >= 2 ORDER BY city;

查询结果:

cityuser_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;

查询结果:

totaladult_count
54

CASE 为满足年龄条件的行计 1,其余计 0,SUM 再相加。这里把未知年龄排除在成年人数外,并不代表认定它未成年。


❓ 知识问答

Q1:COUNT(1) 比 COUNT(*) 更准确吗?

两者在这里都统计行数,COUNT(*) 更直接表达“记录数量”。

Q2:先关联文章再 COUNT(*),得到的是用户数吗?

不一定。一位用户的多篇文章会产生多行,要按用户编号去重或先统计文章。关联篇会用实际结果说明。

Q3:可以关闭分组检查来显示 username 吗?

不应靠关闭检查掩盖查询含义不明确的问题。先决定每组到底要返回哪一条记录。


🧪 小练习

按城市统计平均年龄,再仅保留已填写年龄人数至少为 2 的城市。预期保留北京与上海。

-- 请在这里编写 SQL 或记录验证结果