数据分析师训练营/ 第三篇 SQL/ 第十二章
第十二章 · 聚合与分组

把 20,000 行压成 4 行

上一章的单表查询,返回的是一行行明细。可业务方从来不要明细:运营问“每个渠道卖了多少”, 老板问“哪个月最好”,财务问“客单价为什么降了”。这些问题都要求你先把行变成组,再对每组算一个数。

本章就干这件事。12.1 讲透五条规则:COUNT 三兄弟、GROUP BY 的分组逻辑、WHERE 与 HAVING 的分工、 指标的可加性、除零处理。12.2 专门讨论一个最容易错、也最容易被追问的问题:分母到底该是谁。 12.3 用青柠优选的 20,000 笔订单做七步实验,最后你会得到四张能直接进月报的汇总表。

理论 : 实操 = 5 : 5 约 6 学时 前置 第 11 章

12.1原理:从行到组的五条规则

聚合的第一个动作不是写 SUM,而是决定“按什么分组”。分组键选错,后面算得再准也是另一个问题的答案。 这一节的五条规则,对应后面实验里的五个坑。

12.1.1COUNT 三兄弟:数行、数非空、数唯一

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 人。三个数,三种口径。

1

COUNT(*) 数行,COUNT(列) 数非空,COUNT(DISTINCT 列) 数唯一值

只要列里有 NULL,COUNT(列) 就一定小于 COUNT(*);只有加了 DISTINCT,才是在“数人”。

如果不这样会怎样:把 COUNT(user_id) 当成“下单人数”写进月报,你会得到 19,435 人, 而真实人数是 2,000 人,多算了 17,435 人。老板按人头算获客成本,会把预算砍掉一个数量级。

写法数什么本项目结果回答的问题
COUNT(*)行数,NULL 也算一行20,000有多少笔订单
COUNT(user_id)非 NULL 的 user_id19,435有多少笔订单带账号
COUNT(DISTINCT user_id)去重后的 user_id2,000有多少人下过单
COUNT(DISTINCT city)去重后的城市8覆盖了几个城市
图 12.1.1 同一个 COUNT,三种口径:20,000 行、19,435 个非空值、2,000 个人

12.1.2GROUP BY 的本质:先分桶,再对每个桶算数

很多人把 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 行。

2

SELECT 里只能出现“分组键”和“聚合结果”,不能出现裸列

分组之后,一个桶里有成百上千行,而 SELECT 的每一行只能放一个值。所以除了分组键, 其他列必须被聚合函数“压”成一个值。你写 SELECT channel, city, SUM(...) ... GROUP BY channel, city 就是一个裸列——它不属于分组键,数据库不知道该取桶里的哪一行。

如果不这样会怎样:SQLite 会“宽容”地返回桶里任意一行的 city。你得到的结果长这样: 信息流 / 上海、朋友推荐 / 杭州、线下 / 深圳、自然搜索 / 武汉。四个城市看起来都很正常, 但它们只是每个渠道里碰巧被选中的一行,没有任何业务含义。换一台机器、换一个版本,结果可能就变了。

订单号渠道实付
1O23010116593自然搜索5,988.49
2O23010103782线下2,015.47
3O23010104230朋友推荐22,966.56
4O23010105785朋友推荐15,102.29
5O23010117757线下13,313.22
6O23010110389自然搜索3,215.88
GROUP BY 渠道:6 行装进 3 个桶
渠道订单数GMV
1自然搜索29,204.37
2线下215,328.69
3朋友推荐238,068.85
图 12.1.2 分组就是分桶:桶内聚合,桶间独立,一个桶只输出一行(仅取 6 行示意)
SQLite 的宽容是陷阱。MySQL 8.0 默认开启 ONLY_FULL_GROUP_BY,同样的写法会直接报错; 你能在 SQLite 里跑通,不代表这条 SQL 是对的。

12.1.3WHERE 与 HAVING:一个管行,一个管组

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

行级条件放 WHERE,组级条件放 HAVING,能放 WHERE 就别放 HAVING

判断标准只有一句话:这个条件是在挑“行”,还是在挑“组”? “只要已支付的订单”是在挑行,放 WHERE;“只要 GMV 超过一千万的城市”是在挑组,放 HAVING。 顺序也不能反:先把行缩小,再分组,计算量最小。

如果不这样会怎样:把 status = '已支付' 放进 HAVING,SQLite 可能不报错, 但它会先把所有状态的行都分好组、算完 SUM,再丢掉不要的组。数据量一大,速度差几倍。

FROM
20,000 行
→
WHERE
15,615 行
→
GROUP BY
4 个组
→
HAVING
3 个组
→
SELECT
3 行
图 12.1.3 SQL 的逻辑执行顺序(以渠道为例):WHERE 在分组前,HAVING 在分组后,中间隔着 GROUP BY

12.1.4指标的可加性:金额能加,比率和人数不能加

这是月报里最隐蔽的错误:把分组结果顺手相加,金额通常没事,比率和人数十有八九是错的。

金额可加。四个渠道的 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),不能拿四个分组的数相加。

4

金额可以加,比率要重算分子分母,人数要重新去重

分组结果不是“更小的总数”,而是“另一个口径”。金额、订单数可加;客单价、退款率、去重人数不可加。

如果不这样会怎样:把四个渠道的付费人数相加写成“全站付费用户 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
图 12.1.4 可加性判断表:金额和次数能加,比率和去重人数不能加

12.1.5除零处理:NULLIF 不是可选技巧,是底线

所有除法都隐含一个假设:分母不为 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。

5

每个除法都要问一句:分母为 0 时怎么办?

标准写法是 分子 / NULLIF(分母, 0)。它同时解决两个问题:在严格模式下不报错, 在宽松模式下不产生一个假的 0。展示时把 NULL 渲染成“—”。

如果不这样会怎样:日报里有一句“城市退款率”,某天新开了一个城市,订单数为 0。 PostgreSQL 直接报错,整张日报刷不出来;换成 SQLite 则返回 NULL,前端格式化后显示 0.00%, 运营以为“这个城市很健康”,实际上它一单都没有。

不要用 COALESCE(分子 / 分母, 0) 掩盖问题。它把“没有数据”和“比率为 0”混成一件事, 除非业务明确要求,否则保留 NULL。

12.2分析:分母到底该是谁

同样一个 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,0002.00%6/1 活跃 28014.29%
退款率1,203全部订单 20,0006.0%已支付 15,6157.70%
客单价157,363,380.14全部订单 20,0007,868.17已支付 15,61510,077.71
人均下单次数20,000全量注册 2,00010.0下单用户 2,00010.0(巧合)
图 12.2.1 同一个分子,换一个分母,结论可能差一个量级

写 SQL 之前,先用一句话把分母说清楚:“这个比率的分母是 6 月 1 日的活跃用户”“这个比率的分母是已支付订单”。 说不清,就说明业务定义还没对齐,先别写 SQL。三个自检问题:

  1. 谁有资格进入分母?只有支付过的订单才可能退款,只有当天活跃的人才可能次日留存。
  2. 分子和分母的时间窗口一致吗?分子是 6 月 2 日回访,分母就只能是 6 月 1 日活跃,不能是 6 月全月活跃。
  3. 分母是“人数”还是“次数”?“人均下单次数”的分母是人,“客单价”的分母是订单,两者不能混用。

12.3实验:七步做出四张汇总表

打开 DB Browser for SQLite,加载 qingning.db,切到“执行 SQL”页签。 下面七步按顺序做,每一步都先写 SQL,再看结果,最后和“看到什么”核对。

已完成 0 / 7 预计 40 分钟
01

先用三个 COUNT 认表

3 分钟 · 对应规则 1
做什么
确认订单表有多少行、多少个带账号、多少个人、多少个城市。
为什么
规则 1:COUNT 的三种写法回答三个不同问题。不先认清楚,后面算“人均”一定用错分母。
怎么做
SELECT COUNT(*)                  AS 行数,
       COUNT(user_id)            AS 有用户ID的行,
       COUNT(DISTINCT user_id)   AS 下单用户数,
       COUNT(DISTINCT city)      AS 城市数
FROM orders;
看到什么
一行四列:20000 / 19435 / 2000 / 8。20,000 − 19,435 = 565,正好是游客订单数。
卡住了 / 出错了

如果第二个数是 20,000,说明你写成了 COUNT(*);如果第三个数是 19,435,说明漏了 DISTINCT。

02

按渠道统计 GMV 与订单数

5 分钟 · 对应规则 2
做什么
把 15,615 笔已支付订单按渠道压成 4 行。
为什么
规则 2:先 WHERE 筛行,再 GROUP BY 分桶,最后对每个桶 SUM。顺序错了,口径就错了。
怎么做
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 相加 = 157,363,380.14,订单数相加 = 15,615,与订单表总额一致。占比:40.17% / 29.89% / 20.44% / 9.49%。
卡住了 / 出错了

如果 GMV 是 202,298,404.35,说明没筛已支付;如果只有 1 行,说明 GROUP BY 写错了列。

03

按城市算客单价,并检查“能不能平均”

6 分钟 · 对应规则 4
做什么
算 8 个城市的客单价,再判断这 8 个比率能不能直接求平均。
为什么
规则 4:比率不可加。城市订单数不同,简单平均会给每个城市相同权重,结果与全站真实客单价有偏差。
怎么做
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 个客单价的简单平均是 10,077.30,全站真实客单价是 10,077.71。差 0.41 元,方向已经错了。
为什么差得这么小

8 个城市的订单数都在 1,871–2,050 之间,权重几乎相同;换成订单数从 1,502 到 6,281 的渠道分组,差距会扩大到 11.20 元。

04

按月统计 GMV

6 分钟 · 对应规则 2
做什么
把 18 个月的已支付 GMV 按月汇总,找出最高月和最低月。
为什么
日期字段带时分秒,不能直接 GROUP BY。用 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
最高月 2024-04(924 单 / 9,331,840.17),最低月 2023-11(834 单 / 8,121,168.51)。18 个月 GMV 相加仍是 157,363,380.14。
卡住了 / 出错了

如果得到 20,000 行,说明 GROUP BY 用了 order_date 原文;时分秒不同,每笔都成了独立一组。

05

用 HAVING 找出千万级城市

5 分钟 · 对应规则 3
做什么
先筛出 GMV 超过 1,000 万的城市,再把门槛提到 2,000 万,观察结果变化。
为什么
规则 3:GMV 是组级聚合结果,只能用 HAVING 过滤。把它写进 WHERE,SQLite 会报 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
1,000 万门槛太低,8 个城市全部过线;提到 2,000 万,只剩杭州、武汉、上海 3 个。
故意犯一次错

把 HAVING 改成 WHERE,会看到 Result: misuse of aggregate: SUM()。这个报错几乎总是“聚合条件写错位置”。

06

换分母:退款率与客单价各算两遍

8 分钟 · 对应规则 4、12.2
做什么
用“全部订单”和“已支付订单”两个分母,各算一次退款率和客单价,把差异量化。
为什么
12.2 的结论:分母必须是“有资格”的行。只有付过款的订单才可能退款,才配当退款率和客单价的分母。
怎么做
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
退款率从 6.01% 变成 7.70%;客单价从 7,868.17 变成 10,077.71。分子没动,只是换了个分母。
为什么退款率全量分母是 6.01 而不是 6.02

1,203 ÷ 20,000 = 6.015%,两位小数应是 6.02%;SQLite 的浮点表示让 ROUND 得到 6.01。报告里统一写“约 6.0%”即可。

07

空结果与三维对账

7 分钟 · 对应规则 5
做什么
先看一个没有订单的月份返回什么,再把渠道、城市、月份三个维度的 GMV 各自加总,和总表对账。
为什么
规则 5:分母为 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
空月份返回 0 行 / NULL,不是 0.00。三个维度的 GMV 相加都等于 157,363,380.14,对账通过。
卡住了 / 出错了

如果空月份报 “division by zero”,说明你用的是 PostgreSQL,NULLIF 正是为它准备的。

三个维度对不上,先检查每个查询的 WHERE 是否完全一致。

12.4结果验证

先填两个关键数字,再做五道选择题:数字对,说明聚合逻辑通了;选择题对,说明口径也通了。

用 status = '已支付' 过滤后 COUNT(*)。
SUM(pay_amount),保留两位小数。

12.4.1五道选择题

Q1对 orders 表执行 SELECT COUNT(*), COUNT(user_id), COUNT(DISTINCT user_id) FROM orders,结果应该是?
解析:565 笔游客订单的 user_id 是 NULL。COUNT(*) 数行得 20,000;COUNT(user_id) 跳过 NULL 得 19,435;COUNT(DISTINCT user_id) 去重得 2,000 人。
Q2要统计每个渠道的已支付 GMV,并且只要 GMV 超过 5,000 万的渠道。条件应该写在哪里?
解析:GMV 是分组后的聚合结果,WHERE 执行时还没有组,写进去会报 misuse of aggregate。行级条件 status='已支付' 放 WHERE,组级条件放 HAVING。
Q3四个渠道的付费用户数相加是 6,318,而全站付费用户只有 2,000。最准确的原因是什么?
解析:2,000 人里有 1,970 人在两个以上渠道下过单。按渠道 COUNT(DISTINCT user_id) 会把同一个人重复计数,相加必然大于全站人数。人数只能回到明细重新去重。
Q4计算退款率时,分母用哪个最合理?
解析:退款的前提是付过款。已取消和待支付的 3,182 笔订单没有退款资格,放进分母会把 7.70% 稀释成 6.0%。
Q5某天没有已支付订单,客单价查询返回 NULL。最合适的处理是?
解析:NULL 表示“没有数据”,0 表示“客单价是 0 元”。NULLIF 让分母为 0 时返回 NULL,既避免报错,也不制造假数据。
得分:0 / 5

12.5常见问题排查

按“现象 → 原因 → 解决”整理,遇到问题先在这里找。

现象原因解决
报错 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),展示为“—”

12.6本章小结与练习

  1. COUNT 三兄弟:数行、数非空、数唯一值。本项目是 20,000 / 19,435 / 2,000。
  2. GROUP BY 的本质是分桶:先分桶再聚合;SELECT 只能拿分组键和聚合结果。
  3. WHERE 管行,HAVING 管组:聚合条件写进 WHERE 会报错,能放 WHERE 的条件不要拖到 HAVING。
  4. 可加性决定汇报方式:金额次数可加;比率重算分子分母;人数重新去重。渠道人数相加 6,318,真实 2,000。
  5. 分母是业务定义:留存用当日活跃,退款率和客单价用已支付订单;换分母,退款率 7.70% → 6.0%,客单价 10,077.71 → 7,868.17。

12.6.1练习

  1. 用一条 SQL 同时输出四个渠道的订单数、GMV、客单价,并按 GMV 降序。写出第一行的三个数。
  2. 按城市计算退款率(退款单 ÷ 已支付单),哪个城市最高?最高和最低差几个百分点?
  3. 如果把全站客单价的分母改成全部订单,结果是多少?与正确值差多少?
  4. 用 HAVING 找出已支付订单数超过 2,000 的城市,结果有几个?分别是谁?
参考答案

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='已支付'。

下一章预告:第 13 章会把订单表、用户表、商品表拼在一起。那里有一个比分组更隐蔽的坑: JOIN 之后行数会变,SUM 出来的 GMV 可能是真实值的 2.32 倍。聚合学得再熟,粒度错了照样翻车。
数据分析师训练营 · 第十二章
DB Browser for SQLite / 命令行 sqlite3 均可