上一章的单表查询,返回的是一行行明细。可业务方从来不要明细:运营问“每个渠道卖了多少”,
老板问“哪个月最好”,财务问“客单价为什么降了”。这些问题都要求你先把行变成组,再对每组算一个数。
本章就干这件事。12.1 讲透五条规则:COUNT 三兄弟、GROUP BY 的分组逻辑、WHERE 与 HAVING 的分工、
指标的可加性、除零处理。12.2 专门讨论一个最容易错、也最容易被追问的问题:分母到底该是谁。
12.3 用青柠优选的 20,000 笔订单做七步实验,最后你会得到四张能直接进月报的汇总表。
聚合的第一个动作不是写 SUM,而是决定“按什么分组”。分组键选错,后面算得再准也是另一个问题的答案。 这一节的五条规则,对应后面实验里的五个坑。
COUNT 是 SQL 里最容易被低估的函数。很多人以为它只有一种写法,实际上它有三个完全不同的口径。 先看青柠优选的订单表,一条 SQL 就能把三兄弟的差别摆出来:
SELECT COUNT(*) AS 行数,
COUNT(user_id) AS 有用户ID的行,
COUNT(DISTINCT user_id) AS 下单用户数,
COUNT(DISTINCT city) AS 城市数
FROM orders;
行数 有用户ID的行 下单用户数 城市数 20000 19435 2000 8
COUNT(*) 数的是行;COUNT(user_id) 只数非 NULL,少了 565 行游客订单;
COUNT(DISTINCT user_id) 去重后是 2,000 人。三个数,三种口径。
只要列里有 NULL,COUNT(列) 就一定小于 COUNT(*);只有加了 DISTINCT,才是在“数人”。
如果不这样会怎样:把 COUNT(user_id) 当成“下单人数”写进月报,你会得到 19,435 人,
而真实人数是 2,000 人,多算了 17,435 人。老板按人头算获客成本,会把预算砍掉一个数量级。
| 写法 | 数什么 | 本项目结果 | 回答的问题 |
|---|---|---|---|
COUNT(*) | 行数,NULL 也算一行 | 20,000 | 有多少笔订单 |
COUNT(user_id) | 非 NULL 的 user_id | 19,435 | 有多少笔订单带账号 |
COUNT(DISTINCT user_id) | 去重后的 user_id | 2,000 | 有多少人下过单 |
COUNT(DISTINCT city) | 去重后的城市 | 8 | 覆盖了几个城市 |
很多人把 GROUP BY 理解成“把相同的行合并”。 更准确的说法是: 数据库先按分组键把行装进不同的桶,桶的个数等于分组键的不同取值个数,然后对每个桶独立执行聚合函数。 桶与桶之间互不干扰,最后每个桶输出一行。
SELECT channel AS 渠道,
COUNT(*) AS 订单数,
ROUND(SUM(pay_amount),2) AS GMV
FROM orders
WHERE status = '已支付'
GROUP BY channel
ORDER BY GMV DESC;
渠道 订单数 GMV 自然搜索 6281 63215624.36 信息流 4678 47042514.15 朋友推荐 3154 32164193.80 线下 1502 14941047.83
20,000 行订单先被 WHERE 筛成 15,615 行,再被 GROUP BY 装进 4 个桶,
每个桶算一次 COUNT 和 SUM,最后输出 4 行。
分组之后,一个桶里有成百上千行,而 SELECT 的每一行只能放一个值。所以除了分组键,
其他列必须被聚合函数“压”成一个值。你写 SELECT channel, city, SUM(...) ... GROUP BY channel,
city 就是一个裸列——它不属于分组键,数据库不知道该取桶里的哪一行。
如果不这样会怎样:SQLite 会“宽容”地返回桶里任意一行的 city。你得到的结果长这样: 信息流 / 上海、朋友推荐 / 杭州、线下 / 深圳、自然搜索 / 武汉。四个城市看起来都很正常, 但它们只是每个渠道里碰巧被选中的一行,没有任何业务含义。换一台机器、换一个版本,结果可能就变了。
| 订单号 | 渠道 | 实付 | |
|---|---|---|---|
| 1 | O23010116593 | 自然搜索 | 5,988.49 |
| 2 | O23010103782 | 线下 | 2,015.47 |
| 3 | O23010104230 | 朋友推荐 | 22,966.56 |
| 4 | O23010105785 | 朋友推荐 | 15,102.29 |
| 5 | O23010117757 | 线下 | 13,313.22 |
| 6 | O23010110389 | 自然搜索 | 3,215.88 |
| GROUP BY 渠道:6 行装进 3 个桶 | |||
| 渠道 | 订单数 | GMV | |
| 1 | 自然搜索 | 2 | 9,204.37 |
| 2 | 线下 | 2 | 15,328.69 |
| 3 | 朋友推荐 | 2 | 38,068.85 |
ONLY_FULL_GROUP_BY,同样的写法会直接报错;
你能在 SQLite 里跑通,不代表这条 SQL 是对的。
WHERE 和 HAVING 都叫“过滤”,但执行的时机完全不同。WHERE 在分组之前执行,手里只有一行数据, 所以它只能引用列,不能引用聚合函数;HAVING 在分组之后执行,手里已经有一个组,所以它可以引用 COUNT、SUM 这些聚合结果。
实验里我们会跑一句典型的错 SQL:
SELECT city FROM orders WHERE status = '已支付' AND SUM(pay_amount) > 10000000 GROUP BY city;
Result: misuse of aggregate: SUM()
报错信息已经把原因说得很清楚:误用了聚合函数。WHERE 执行时还没有“组”,SUM 无从算起。 把条件挪到 HAVING,立刻就能跑:
SELECT city,
ROUND(SUM(pay_amount),2) AS GMV
FROM orders
WHERE status = '已支付'
GROUP BY city
HAVING SUM(pay_amount) > 10000000
ORDER BY GMV DESC;
判断标准只有一句话:这个条件是在挑“行”,还是在挑“组”? “只要已支付的订单”是在挑行,放 WHERE;“只要 GMV 超过一千万的城市”是在挑组,放 HAVING。 顺序也不能反:先把行缩小,再分组,计算量最小。
如果不这样会怎样:把 status = '已支付' 放进 HAVING,SQLite 可能不报错,
但它会先把所有状态的行都分好组、算完 SUM,再丢掉不要的组。数据量一大,速度差几倍。
这是月报里最隐蔽的错误:把分组结果顺手相加,金额通常没事,比率和人数十有八九是错的。
金额可加。四个渠道的 GMV 分别是 63,215,624.36、47,042,514.15、32,164,193.80、14,941,047.83, 加起来正好是 157,363,380.14,与订单表的总 GMV 一分不差。分组只是换了装法,总数不变。
比率不可加。四个渠道的客单价分别是 10,064.58、10,056.12、10,197.91、9,947.44。 如果你把它们求平均,得到 10,066.51;而全站真实客单价是 157,363,380.14 ÷ 15,615 = 10,077.71。 差了 11.20 元。原因很简单:四个渠道的订单数不同(6,281、4,678、3,154、1,502), 简单平均给每个渠道相同的权重,而正确的算法是“总 GMV ÷ 总订单数”,让订单多的渠道自然占更大权重。
去重人数不可加。四个渠道的付费用户数分别是 1,914、1,786、1,575、1,043,相加得 6,318。
但全站付费用户只有 2,000 人。为什么?因为 2,000 人里有 1,970 人在两个以上渠道下过单,
同一个人在多个渠道各算一次,就被重复计了多次。人数要去重,就必须回到明细重新
COUNT(DISTINCT user_id),不能拿四个分组的数相加。
分组结果不是“更小的总数”,而是“另一个口径”。金额、订单数可加;客单价、退款率、去重人数不可加。
如果不这样会怎样:把四个渠道的付费人数相加写成“全站付费用户 6,318 人”, 真实只有 2,000 人,是真实值的 3.16 倍。按这个数去算获客成本,会把 200 元的成本算成 63 元, 投放预算直接翻三倍。
| 指标类型 | 本项目的例子 | 能否直接相加 | 正确做法 |
|---|---|---|---|
| 金额 | 渠道 GMV 63,215,624.36 + 47,042,514.15 + … | 可以 | 直接相加,结果等于总 GMV |
| 次数 | 渠道订单数 6,281 + 4,678 + 3,154 + 1,502 | 可以 | 直接相加,结果等于 15,615 |
| 比率 | 渠道客单价 10,064.58 / 10,056.12 / … | 不可以 | 总 GMV ÷ 总订单数 = 10,077.71 |
| 去重人数 | 渠道付费用户 1,914 + 1,786 + 1,575 + 1,043 | 不可以 | 回到明细 COUNT(DISTINCT user_id) = 2,000 |
所有除法都隐含一个假设:分母不为 0。可数据从来不保证这一点。某天没有支付订单、某个城市没有新用户, 分母就是 0。这时你希望 SQL 返回什么?
在 SQLite 和 MySQL 里,1 / 0 返回 NULL,不报错;在 PostgreSQL 和 SQL Server 里,
它会直接抛出“division by zero”,让整条查询失败。更危险的是第一种情况:BI 工具把 NULL 显示成 0.00,
老板看到“今日客单价 0 元”,以为业务崩了。
SELECT COUNT(*) AS 订单数,
ROUND(SUM(pay_amount) / NULLIF(COUNT(*), 0), 2) AS 客单价
FROM orders
WHERE status = '已支付'
AND order_date LIKE '2025%';
订单数 客单价 0 NULL
NULLIF(COUNT(*), 0) 的意思是:如果 COUNT(*) 等于 0,就把它换成 NULL;
否则原样返回。任何数除以 NULL 都得到 NULL,于是“没有数据”被诚实地表达成 NULL,而不是一个会被误读的 0。
标准写法是 分子 / NULLIF(分母, 0)。它同时解决两个问题:在严格模式下不报错,
在宽松模式下不产生一个假的 0。展示时把 NULL 渲染成“—”。
如果不这样会怎样:日报里有一句“城市退款率”,某天新开了一个城市,订单数为 0。 PostgreSQL 直接报错,整张日报刷不出来;换成 SQLite 则返回 NULL,前端格式化后显示 0.00%, 运营以为“这个城市很健康”,实际上它一单都没有。
COALESCE(分子 / 分母, 0) 掩盖问题。它把“没有数据”和“比率为 0”混成一件事,
除非业务明确要求,否则保留 NULL。
同样一个 40,除以 280 是 14.29%,除以 2,000 是 2.00%。分子没变,结论从“留存不错”变成“留存灾难”。 分母不是数学问题,是业务定义问题。
先看留存。青柠优选 2024 年 6 月 1 日有 280 个活跃用户,其中 40 人在 6 月 2 日又来了。 次日留存率的正确算法是 40 ÷ 280 = 14.29%,分母是“6 月 1 日的活跃用户”, 因为这 280 人才是“有机会留存”的人。如果你用全量注册用户 2,000 做分母,得到 40 ÷ 2,000 = 2.00%, 这个数把 1,720 个当天根本没来的用户也塞进了分母,等于问了一个不同的问题: “全站用户里,有多少人在 6 月 2 日回访?”
再看退款率。全站 1,203 笔退款。用全部订单 20,000 做分母,得到约 6.0%;用已支付订单 15,615 做分母, 得到 7.70%。哪个对?退款的前提是“付过款”,那 3,182 笔已取消和待支付的订单从来没有付过钱, 根本没有退款资格,不应该进入分母。用全量分母,等于把风险基数人为放大,把 7.70% 稀释成 6.0%, 整整差 1.7 个百分点。
客单价也是同一个道理。GMV 157,363,380.14 除以已支付订单 15,615,得到 10,077.71; 除以全部订单 20,000,得到 7,868.17。后者把没付钱的订单也当成了“客”,客单价被拉低 2,209.54 元,低了 22%。
退款率 = 1,203 ÷ 20,000 = 6.0%
客单价 = 157,363,380.14 ÷ 20,000 = 7,868.17
把没有支付资格的订单也算进分母,指标被系统性稀释。
退款率 = 1,203 ÷ 15,615 = 7.70%
客单价 = 157,363,380.14 ÷ 15,615 = 10,077.71
只有付过款的订单才可能退款,才配做分母。
| 指标 | 分子 | 错误分母 | 错误结果 | 正确分母 | 正确结果 |
|---|---|---|---|---|---|
| 次日留存率 | 40 | 全量用户 2,000 | 2.00% | 6/1 活跃 280 | 14.29% |
| 退款率 | 1,203 | 全部订单 20,000 | 6.0% | 已支付 15,615 | 7.70% |
| 客单价 | 157,363,380.14 | 全部订单 20,000 | 7,868.17 | 已支付 15,615 | 10,077.71 |
| 人均下单次数 | 20,000 | 全量注册 2,000 | 10.0 | 下单用户 2,000 | 10.0(巧合) |
写 SQL 之前,先用一句话把分母说清楚:“这个比率的分母是 6 月 1 日的活跃用户”“这个比率的分母是已支付订单”。 说不清,就说明业务定义还没对齐,先别写 SQL。三个自检问题:
打开 DB Browser for SQLite,加载 qingning.db,切到“执行 SQL”页签。
下面七步按顺序做,每一步都先写 SQL,再看结果,最后和“看到什么”核对。
SELECT COUNT(*) AS 行数,
COUNT(user_id) AS 有用户ID的行,
COUNT(DISTINCT user_id) AS 下单用户数,
COUNT(DISTINCT city) AS 城市数
FROM orders;
如果第二个数是 20,000,说明你写成了 COUNT(*);如果第三个数是 19,435,说明漏了 DISTINCT。
SELECT channel AS 渠道,
COUNT(*) AS 订单数,
ROUND(SUM(pay_amount),2) AS GMV,
ROUND(AVG(pay_amount),2) AS 客单价
FROM orders
WHERE status = '已支付'
GROUP BY channel
ORDER BY GMV DESC;
渠道 订单数 GMV 客单价 自然搜索 6281 63215624.36 10064.58 信息流 4678 47042514.15 10056.12 朋友推荐 3154 32164193.80 10197.91 线下 1502 14941047.83 9947.44
如果 GMV 是 202,298,404.35,说明没筛已支付;如果只有 1 行,说明 GROUP BY 写错了列。
SELECT city AS 城市,
COUNT(*) AS 订单数,
ROUND(SUM(pay_amount),2) AS GMV,
ROUND(AVG(pay_amount),2) AS 客单价
FROM orders
WHERE status = '已支付'
GROUP BY city
ORDER BY 客单价 DESC;
城市 订单数 GMV 客单价 上海 1949 20009620.63 10266.61 广州 1937 19707537.02 10174.26 武汉 2009 20318374.79 10113.68 深圳 1951 19691841.98 10093.20 杭州 2050 20508489.75 10004.14 西安 1929 19296448.47 10003.34 成都 1919 19191271.10 10000.66 北京 1871 18639796.40 9962.48
8 个城市的订单数都在 1,871–2,050 之间,权重几乎相同;换成订单数从 1,502 到 6,281 的渠道分组,差距会扩大到 11.20 元。
substr(order_date,1,7) 截出“年-月”,才是正确的分组键。SELECT substr(order_date,1,7) AS 月份,
COUNT(*) AS 订单数,
ROUND(SUM(pay_amount),2) AS GMV
FROM orders
WHERE status = '已支付'
GROUP BY substr(order_date,1,7)
ORDER BY 月份;
月份 订单数 GMV 2023-01 875 9175215.05 2023-02 801 8199651.91 2023-03 886 8826221.22 2023-04 871 8794929.05 2023-05 841 8131421.05 2023-06 876 8875566.96 2023-07 899 8707978.05 2023-08 892 8853384.32 2023-09 884 8961127.51 2023-10 867 8917350.17 2023-11 834 8121168.51 2023-12 841 8222355.38 2024-01 876 9085918.82 2024-02 810 8345370.02 2024-03 893 9033174.82 2024-04 924 9331840.17 2024-05 864 8765340.49 2024-06 881 9015366.64
如果得到 20,000 行,说明 GROUP BY 用了 order_date 原文;时分秒不同,每笔都成了独立一组。
misuse of aggregate。SELECT city,
COUNT(*) AS 订单数,
ROUND(SUM(pay_amount),2) AS GMV
FROM orders
WHERE status = '已支付'
GROUP BY city
HAVING SUM(pay_amount) > 10000000
ORDER BY GMV DESC;
然后把最后一行改成 HAVING SUM(pay_amount) > 20000000 再跑一次。
-- 门槛 1000 万:8 行(全部城市) 杭州 2050 20508489.75 武汉 2009 20318374.79 上海 1949 20009620.63 广州 1937 19707537.02 深圳 1951 19691841.98 西安 1929 19296448.47 成都 1919 19191271.10 北京 1871 18639796.40 -- 门槛 2000 万:3 行 杭州 2050 20508489.75 武汉 2009 20318374.79 上海 1949 20009620.63
把 HAVING 改成 WHERE,会看到 Result: misuse of aggregate: SUM()。这个报错几乎总是“聚合条件写错位置”。
SELECT COUNT(*) AS 全部订单,
SUM(CASE WHEN status = '已支付' THEN 1 ELSE 0 END) AS 已支付订单,
SUM(CASE WHEN status = '已退款' THEN 1 ELSE 0 END) AS 退款订单,
ROUND(100.0 * SUM(CASE WHEN status = '已退款' THEN 1 ELSE 0 END)
/ COUNT(*), 2) AS 退款率_全量分母,
ROUND(100.0 * SUM(CASE WHEN status = '已退款' THEN 1 ELSE 0 END)
/ SUM(CASE WHEN status = '已支付' THEN 1 ELSE 0 END), 2) AS 退款率_支付分母
FROM orders;
SELECT ROUND(SUM(CASE WHEN status = '已支付' THEN pay_amount ELSE 0 END)
/ COUNT(*), 2) AS 客单价_全量分母,
ROUND(SUM(CASE WHEN status = '已支付' THEN pay_amount ELSE 0 END)
/ SUM(CASE WHEN status = '已支付' THEN 1 ELSE 0 END), 2) AS 客单价_支付分母
FROM orders;
CASE WHEN 是条件表达式:成立记 1,否则记 0;套上 SUM 就是条件计数或条件求和。
全部订单 已支付订单 退款订单 退款率_全量分母 退款率_支付分母 20000 15615 1203 6.01 7.70 客单价_全量分母 客单价_支付分母 7868.17 10077.71
1,203 ÷ 20,000 = 6.015%,两位小数应是 6.02%;SQLite 的浮点表示让 ROUND 得到 6.01。报告里统一写“约 6.0%”即可。
NULLIF,让“没有数据”保持 NULL。对账则是确认分组没有漏行、没有重复。SELECT COUNT(*) AS 订单数,
ROUND(SUM(pay_amount) / NULLIF(COUNT(*), 0), 2) AS 客单价
FROM orders
WHERE status = '已支付'
AND order_date LIKE '2025%';
再分别把第 2、3、4 步结果表的 GMV 列相加,与下面这句的总数比较:
SELECT COUNT(*) AS 已支付订单,
ROUND(SUM(pay_amount),2) AS 总GMV
FROM orders
WHERE status = '已支付';
订单数 客单价 0 NULL 已支付订单 总GMV 15615 157363380.14
如果空月份报 “division by zero”,说明你用的是 PostgreSQL,NULLIF 正是为它准备的。
三个维度对不上,先检查每个查询的 WHERE 是否完全一致。
先填两个关键数字,再做五道选择题:数字对,说明聚合逻辑通了;选择题对,说明口径也通了。
status = '已支付' 过滤后 COUNT(*)。SELECT COUNT(*), COUNT(user_id), COUNT(DISTINCT user_id) FROM orders,结果应该是?misuse of aggregate。行级条件 status='已支付' 放 WHERE,组级条件放 HAVING。按“现象 → 原因 → 解决”整理,遇到问题先在这里找。
| 现象 | 原因 | 解决 |
|---|---|---|
报错 misuse of aggregate |
把 SUM / COUNT 写进了 WHERE(规则 3) | 把聚合条件移到 HAVING,行级条件留在 WHERE |
| COUNT(列) 比 COUNT(*) 少 | 这一列有 NULL,比如 565 笔游客订单(规则 1) | 数行用 COUNT(*),数人用 COUNT(DISTINCT 列) |
| 分组结果里出现了莫名其妙的城市 | SELECT 了非分组列的裸列(规则 2) | 只保留分组键和聚合函数;要城市就把城市加进 GROUP BY |
| 各组 SUM 相加 ≠ 总表 | 各查询的 WHERE 不一致,或统计范围不同 | 统一过滤条件;用 GROUP BY 后单独查一次总数对账 |
| 比率求平均后和总比率对不上 | 比率不可加,各组权重不同(规则 4) | 回到分子分母重算:总分子 ÷ 总分母 |
| 渠道人数相加远大于全站人数 | 同一用户跨渠道下单,人数被重复计数(规则 4) | 重新 COUNT(DISTINCT user_id),不要加分组结果 |
| 除零报错,或空数据显示 0.00 | 分母为 0,且没有做 NULLIF(规则 5) | 写成 分子 / NULLIF(分母, 0),展示为“—” |
1. 第一行是自然搜索:6,281 单、63,215,624.36、10,064.58。SQL 就是把第 2 步的查询按 GMV 降序,四列都在。
2. 北京最高,8.02%(150 ÷ 1,871);杭州最低,7.07%(145 ÷ 2,050)。相差 0.95 个百分点。注意分母用已支付单,不是全部订单。
3. 157,363,380.14 ÷ 20,000 = 7,868.17;正确值 10,077.71;差 2,209.54 元,低了约 22%。
4. 2 个:杭州 2,050 单、武汉 2,009 单。SQL 是 GROUP BY city HAVING COUNT(*) > 2000,别忘了先 WHERE status='已支付'。
SUM 出来的 GMV 可能是真实值的 2.32 倍。聚合学得再熟,粒度错了照样翻车。