数据分析师训练营/ 第三篇 SQL/ 第十四章
第十四章 · 子查询与 CTE

把一句话拆成流水线

上一章我们把多张表拼成了一张宽表。但真实问题很少一层 JOIN 就能说完: “哪些城市高于平均”“每个用户属于哪一档”“先算月再算环比”—— 都要在一条 SQL 里分步骤完成。

子查询和 CTE 就是分步骤的工具。它们难的不是语法,而是看清形状: 中间结果是一行一列、一列多行,还是多行多列?形状错了,SQL 要么报错, 要么悄悄返回空集。

本章前一半讲五条规则,其中 NOT IN 撞上 NULL 会让 565 笔游客订单凭空消失; 后一半用 8 步实验,把 5 层嵌套拆成 3 段 CTE。

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

14.1原理:三种形状,一条流水线

子查询的定义很朴素:把一条 SELECT 的结果,当成另一条 SELECT 的输入。 既然是输入,就要先看清它长什么样——这就是形状。 形状决定它能放在 FROM、WHERE,还是只能当一个值用。

14.1.1三种子查询:标量、列、表

按返回结果的形状,子查询只有三类。这里的“行”“列”是字面意思: 单独把子查询跑一遍,看到几行几列,它就是什么形状。

形状返回什么能放在哪典型写法
标量1 行 1 列,本质是一个值SELECT 列表、WHERE、HAVING= (SELECT AVG(...))
列1 列 N 行,是一串值只能配 IN / NOT ININ (SELECT city ...)
表N 行 M 列,是一张临时表只能放在 FROM 后面FROM (SELECT ...) t
图 14.1.1 三种子查询的形状,决定了它唯一合法的位置

标量:一个值,可以直接参与比较和运算。本库已支付订单的平均实付是 10,077.71 元,用它筛出高于平均的订单:

SELECT COUNT(*) AS n, ROUND(SUM(pay_amount), 2) AS gmv
FROM orders
WHERE status = '已支付'
  AND pay_amount > (SELECT AVG(pay_amount) FROM orders WHERE status = '已支付');
n     | gmv
6777  | 112396535.12

15,615 笔已支付订单里,6,777 笔高于平均,占 43.4%,却贡献 71.4% 的 GMV。 金额分布右偏,少数大单把均值拉高,所以“高于平均”的人不到一半。

列:一串值,只能喂给 IN。先找出注册用户不少于 250 人的城市,再拿这串城市去筛订单:

SELECT city, COUNT(*) AS users
FROM users
GROUP BY city
HAVING COUNT(*) >= 250
ORDER BY users DESC;
city | users
上海 | 288
西安 | 257
北京 | 256
武汉 | 251
成都 | 250
SELECT order_id, city, ROUND(pay_amount, 2) AS amount
FROM orders
WHERE status = '已支付'
  AND city IN (SELECT city FROM users GROUP BY city HAVING COUNT(*) >= 250)
ORDER BY pay_amount DESC
LIMIT 3;
order_id     | city | amount
O23011603143 | 北京 | 43168.96
O23030518411 | 成都 | 42253.75
O23082019847 | 上海 | 41546.31

表:一张临时表,放在 FROM 里。下面的派生表把 8 个城市压成 8 行,再排序取前 3:

SELECT city, gmv
FROM (
    SELECT city, ROUND(SUM(pay_amount), 2) AS gmv
    FROM orders
    WHERE status = '已支付'
    GROUP BY city
) t
ORDER BY gmv DESC
LIMIT 3;
city | gmv
杭州 | 20508489.75
武汉 | 20318374.79
上海 | 20009620.63
1

先看形状,再决定子查询放哪

标量当值用,列配 IN,表进 FROM,三者不能互换。 列子查询放到 = 右边,是拿一个值和一串值比大小; 表子查询放进 WHERE,是拿一行记录和条件比,都不成立。

如果不这样会怎样:在 SQLite 里,标量位置放了返回多行的子查询,它不报错, 而是悄悄取第一行。本库执行 SELECT (SELECT city FROM users) 返回“深圳”—— 一个和业务无关的城市。MySQL、PostgreSQL 会直接报错,SQLite 不会,这一点要格外小心。

14.1.2相关子查询 vs 非相关子查询

形状讲“返回什么”,相关与否讲“怎么算”。判断只有一句话: 子查询里有没有引用外层的列。没有是非相关,有是相关。

上面的 AVG(pay_amount) 是非相关的:只依赖 orders 自己, 和外面正在比较的那一行无关,可以先算成常数 10,077.71。

相关子查询则引用了外层。下面判断“这笔订单是否高于所在城市的平均值”, o.city 来自外层:

SELECT COUNT(*) AS n, ROUND(SUM(pay_amount), 2) AS gmv
FROM orders o
WHERE o.status = '已支付'
  AND o.pay_amount > (
      SELECT AVG(o2.pay_amount)
      FROM orders o2
      WHERE o2.status = '已支付' AND o2.city = o.city
  );
n    | gmv
6771 | 112331577.36

概念上,外层每取一行,内层就按这一行的城市重算一次平均。20,000 行仍是瞬间, 外层几百万行就是灾难——第 17 章会用 EXPLAIN 看计划。能改写成 JOIN 或窗口函数的,优先改写。

2

相关子查询 = 外层每行都要重算一次

非相关只算一次,结果当常量;相关依赖外层当前行,逻辑上要执行 N 次。 写之前问一句:“换个外层行,答案会变吗?”会变就是相关。

如果不这样会怎样:把相关当成非相关,你会以为两个口径结果一样。 全局均值是 6,777 单,城市均值是 6,771 单,只差 6 单, 但具体是哪些订单并不相同。用总数判断口径,是最容易翻车的地方。

14.1.3NOT IN 的 NULL 陷阱

这是本章唯一一个“不报错、但结果完全错”的知识点。 先接受一件事:SQL 的布尔值有三种,TRUE、FALSE 和 UNKNOWN。 WHERE 只保留 TRUE 的行,UNKNOWN 和 FALSE 一样被丢掉。

本库 orders 表有 565 笔游客订单,user_id 是 NULL。现在业务问: “有多少笔订单的下单人不在用户表里?”直觉写法是 NOT IN:

SELECT COUNT(*) AS n
FROM orders
WHERE user_id NOT IN (SELECT user_id FROM users);
n
0

结果是 0。就此写下“所有下单人都是注册用户”就错了:查一句 SELECT COUNT(*) FROM orders WHERE user_id IS NULL,答案是 565。 它们不是“在用户表里”,而是根本没参与比较。

原因在于,x NOT IN (a, b, c) 会展开成 x <> a AND x <> b AND x <> c。x 是 NULL 时, NULL <> 'U000001' 得 UNKNOWN,而 UNKNOWN AND TRUE 仍是 UNKNOWN, 整条链断掉,这一行被丢弃。

xx NOT IN ('U000001','U000002')WHERE 是否保留
U000007TRUE保留
U000001FALSE丢弃
NULLUNKNOWN丢弃
图 14.1.2 NOT IN 遇到 NULL 得到 UNKNOWN,WHERE 只放行 TRUE

更隐蔽的是反向版本:NULL 藏在子查询的列表里。下面想数“不是北京用户下的单”, 子查询的 user_id 里混着 NULL,于是整条 NOT IN 对任何一行都不成立:

-- 错误:子查询结果里含 565 个 NULL
SELECT COUNT(*) AS n
FROM orders
WHERE user_id NOT IN (SELECT user_id FROM orders WHERE city = '北京');
n
0
-- 正确:把 NULL 挡在子查询外面
SELECT COUNT(*) AS n
FROM orders
WHERE user_id NOT IN (
    SELECT user_id FROM orders WHERE city = '北京' AND user_id IS NOT NULL
);
n
5085

0 和 5,085,差五千多行,数据库一声不吭。结论越吓人,越要先怀疑 NULL。

3

NOT IN 的列表里只要有一个 NULL,整条查询就近似空集

NULL 不是 0,也不是空字符串,它是“未知”。拿值和“未知”比较,答案只能是 UNKNOWN, 而 WHERE 只认 TRUE。NULL 一旦进入 NOT IN 的比较链,这一行就留不下来。

如果不这样会怎样:本库 565 笔游客订单、合计 4,274,401.92 元 GMV, 会在“排除已注册用户”这类查询里整体消失,占全站 2.72%。 对账时总差两百多万,却找不到是哪一批单。

两条保命写法:① 能用 NOT EXISTS 就别用 NOT IN, 前者逐行判断存在性,NULL 不会污染结果;② 非用 NOT IN 不可,就在子查询里加 WHERE user_id IS NOT NULL。两种写法在本库都返回 565。

14.1.4CTE:把复杂查询写成流水线

子查询是“套娃”,层数一多就要从里往外翻。CTE(公用表表达式) 换了个方向:从上往下写,一层一个名字。语法是在 SELECT 前加 WITH:

WITH paid AS (
    SELECT channel, order_date, pay_amount
    FROM orders
    WHERE status = '已支付'
),
monthly AS (
    SELECT channel,
           substr(order_date, 1, 7) AS ym,
           SUM(pay_amount) AS gmv
    FROM paid
    GROUP BY channel, ym
)
SELECT channel,
       ROUND(SUM(gmv), 2) AS gmv,
       ROUND(AVG(gmv), 2) AS avg_month
FROM monthly
GROUP BY channel
ORDER BY gmv DESC;
channel  | gmv          | avg_month
自然搜索 | 63215624.36  | 3511979.13
信息流   | 47042514.15  | 2613473.01
朋友推荐 | 32164193.80  | 1786899.66
线下     | 14941047.83  | 830058.21

paid 是“已支付订单”,monthly 是“渠道 × 月的 GMV”, 最后一步在月粒度上再汇总。自然搜索月均 3,511,979.13、信息流 2,613,473.01, 就是各自总额除以 18。

CTE 有四条性质,必须记清楚:

性质含义
只活在一条语句里查询结束,名字就没了。下一条 SELECT 不能再用。
只能向后引用后面的 CTE 可以引用前面的,前面的不能引用后面的。
不是临时表不落地、不建索引。SQLite 可能把它内联展开,也可能物化,别假设它只算一次。
可以多次引用同一个名字在 FROM 和子查询里各用一次,是允许的,也是 CTE 比派生表方便的地方。
4

CTE 是给中间结果起名字,不是建表

一个 CTE 只做一件事,名字要能读出业务含义:paid、monthly、 city_agg。读到 FROM 那一行,不用翻回上面就知道粒度。 这就是“流水线”和“套娃”的区别。

如果不这样会怎样:把 CTE 当临时表,写 8 个互相引用的 CTE,名字全叫 t1、t2、t3。查询能跑,但没人能改, 三天后你自己也得从头读一遍。

14.1.5CASE WHEN:从上到下,第一个命中就停

CASE 有两种写法,区别只在判断方式:

写法形式适用场景
简单 CASECASE 列 WHEN 值 THEN 结果 ... END只做等值匹配,比如会员等级映射
搜索 CASECASE WHEN 条件 THEN 结果 ... END范围、组合条件,比如按累计消费分层
SELECT member_level,
       CASE member_level
         WHEN '黑卡' THEN 'S'
         WHEN '金卡' THEN 'A'
         WHEN '银卡' THEN 'B'
         ELSE 'C'
       END AS grade,
       COUNT(*) AS users
FROM users
GROUP BY member_level
ORDER BY users DESC;
member_level | grade | users
普通         | C     | 1169
银卡         | B     | 552
金卡         | A     | 196
黑卡         | S     | 83

搜索 CASE 更常用。按累计消费给 2,000 个用户分四层,阈值分别是 10 万、7.5 万、5 万:

SELECT
  CASE WHEN spend >= 100000 THEN 'A 高价值'
       WHEN spend >= 75000  THEN 'B 成长'
       WHEN spend >= 50000  THEN 'C 潜力'
       ELSE 'D 长尾' END AS tier,
  COUNT(*) AS users,
  ROUND(SUM(spend), 2) AS total
FROM (
    SELECT user_id, SUM(pay_amount) AS spend
    FROM orders
    WHERE status = '已支付' AND user_id IS NOT NULL
    GROUP BY user_id
) t
GROUP BY tier
ORDER BY tier;
tier     | users | total
A 高价值 | 482   | 58830222.80
B 成长   | 502   | 43638815.67
C 潜力   | 565   | 35136501.47
D 长尾   | 451   | 15483438.28
累计消费第 1 个分支 ≥10 万第 2 个分支 ≥7.5 万第 3 个分支 ≥5 万命中
120,000TRUE不再判断不再判断A 高价值
80,000FALSETRUE不再判断B 成长
60,000FALSEFALSETRUEC 潜力
30,000FALSEFALSEFALSED 长尾(ELSE)
图 14.1.3 CASE 是短路判断:一旦命中,后面的分支连看都不看

“第一个命中就停”意味着顺序就是逻辑。把条件从小到大写,会得到完全不同的结果:

-- 错误:小阈值写在前面
SELECT CASE WHEN spend >= 50000 THEN 'C 潜力'
            WHEN spend >= 75000 THEN 'B 成长'
            WHEN spend >= 100000 THEN 'A 高价值'
            ELSE 'D 长尾' END AS tier,
       COUNT(*) AS users
FROM (
    SELECT user_id, SUM(pay_amount) AS spend
    FROM orders
    WHERE status = '已支付' AND user_id IS NOT NULL
    GROUP BY user_id
) t
GROUP BY tier
ORDER BY tier;
tier   | users
C 潜力 | 1549
D 长尾 | 451

所有 5 万以上的用户都在第一个分支被拦下,A、B 两层一个人都进不去—— 四层塌成两层,SQL 不会给任何提示。

5

CASE 的顺序就是业务规则,阈值要从大到小写

CASE 逐行判断,遇到第一个 TRUE 就返回。范围判断必须按“最严格 → 最宽松”排列, 最后用 ELSE 兜底。不写 ELSE 时返回 NULL,GROUP BY 会把 NULL 单独算成一组, 报表上多出一行空层级。

如果不这样会怎样:顺序写反,A 层 482 人、B 层 502 人全部消失, 1,549 人被贴上“潜力”标签。拿去做投放,预算全打给中间层, 真正的高价值用户一个都没覆盖到。

14.2分析:5 层嵌套读成 3 段 CTE

这一节不讲新语法,只练一件事:拿到一段看不懂的嵌套,怎么在 5 分钟内拆开。 业务问题是——“哪些城市的高价值用户最多?占本地用户多少?”

原始写法,5 个 SELECT 套在一起:

SELECT city, high_users, total_users,                     -- 第 5 层
       ROUND(high_users * 100.0 / total_users, 2) AS pct
FROM (
    SELECT city,                                          -- 第 4 层
           SUM(CASE WHEN tier = '高价值' THEN 1 ELSE 0 END) AS high_users,
           COUNT(*) AS total_users
    FROM (
        SELECT u.city AS city, t.tier AS tier             -- 第 3 层
        FROM (
            SELECT s.user_id,                             -- 第 2 层
                   CASE WHEN s.spend >= 100000 THEN '高价值' ELSE '普通' END AS tier
            FROM (
                SELECT user_id, SUM(pay_amount) AS spend  -- 第 1 层
                FROM orders
                WHERE status = '已支付' AND user_id IS NOT NULL
                GROUP BY user_id
            ) s
        ) t
        JOIN users u ON u.user_id = t.user_id
    ) ut
    GROUP BY city
) c
ORDER BY high_users DESC
LIMIT 3;
city | high_users | total_users | pct
北京 | 68         | 256         | 26.56
成都 | 67         | 250         | 26.80
广州 | 63         | 243         | 25.93

直接读容易晕。换个方法:从最内层开始,每层只问三个问题—— 输入是哪张表?做了什么动作?输出几行?填进下面这张表,嵌套就成了流水线。

层输入动作输出行数该起的名字
第 1 层orders按 user_id 汇总实付2,000user_spend
第 2 层第 1 层CASE 打标签2,000tagged
第 3 层第 2 层 + users按城市 JOIN,取城市名2,000—(并入 city_agg)
第 4 层第 3 层按城市计数8city_agg
第 5 层第 4 层算占比、排序、取前 33主查询
图 14.2.1 每层的输入输出都写清楚,嵌套就只剩“抄一遍”的工作

第 2、3 层都是 2,000 行:打标签和 JOIN users 都是“一行对一行”,不改变粒度。 真正把行数压到 8 的是第 4 层的 GROUP BY。看清这一点,就知道哪一步可能翻倍—— 这是第 13 章粒度意识的延续。

按表里的名字重写,5 层变成 3 段 CTE + 1 个主查询:

WITH user_spend AS (
    SELECT user_id, SUM(pay_amount) AS spend
    FROM orders
    WHERE status = '已支付' AND user_id IS NOT NULL
    GROUP BY user_id
),
tagged AS (
    SELECT s.user_id, s.spend,
           CASE WHEN s.spend >= 100000 THEN '高价值' ELSE '普通' END AS tier
    FROM user_spend s
),
city_agg AS (
    SELECT u.city AS city,
           SUM(CASE WHEN t.tier = '高价值' THEN 1 ELSE 0 END) AS high_users,
           COUNT(*) AS total_users
    FROM tagged t
    JOIN users u ON u.user_id = t.user_id
    GROUP BY u.city
)
SELECT city, high_users, total_users,
       ROUND(high_users * 100.0 / total_users, 2) AS pct
FROM city_agg
ORDER BY high_users DESC
LIMIT 3;
city | high_users | total_users | pct
北京 | 68         | 256         | 26.56
成都 | 67         | 250         | 26.80
广州 | 63         | 243         | 25.93

结果一模一样。改写带来三个好处:能单独验证每一段(拎出来跑,看行数对不对)、 能复用中间结果(city_agg 可以再 JOIN 城市 GMV)、 能一眼看出粒度在哪一步变化。

14.3实验:从子查询到行转列

下面 8 步都在 qingning.db 上完成,每步按四段写,“为什么”对应 14.1 的规则。 做完之后,你应该能写出“先聚合、再筛选、再分层”的任意组合。

先把三个数写在纸上:15,615、157,363,380.14、10,077.71。 后面每一步的结果,都要能和这三个数对上——对不上,就是口径漏了行。
已完成 0 / 8 预计 45 分钟
01

用标量子查询,量出“平均”这条线

4 分钟 · 对应规则 1
做什么
算出全局客单价,再数出高于这条线的订单与 GMV。
为什么
规则 1:标量子查询返回 1 行 1 列,可以当常量放在 WHERE 里。这是所有“高于平均”类问题的标准骨架。
怎么做
SELECT COUNT(*) AS n, ROUND(SUM(pay_amount), 2) AS gmv
FROM orders
WHERE status = '已支付'
  AND pay_amount > (SELECT AVG(pay_amount) FROM orders WHERE status = '已支付');
看到什么
6,777 单,GMV 112,396,535.12。占单量 43.4%,占 GMV 71.4%。
卡住了 / 出错了

结果接近 15,615:子查询里的 status 条件漏了,平均值被未支付订单拉低。

结果接近 7,808:用了 >= 且外层没筛状态,两个口径混在一起了。

02

换成相关子查询:和“自己的城市”比

5 分钟 · 对应规则 2
做什么
把比较对象从“全国平均”换成“同城平均”。
为什么
规则 2:子查询引用了外层的 o.city,外层每行都要重算。这是相关与非相关的分水岭。
怎么做
SELECT COUNT(*) AS n, ROUND(SUM(pay_amount), 2) AS gmv
FROM orders o
WHERE o.status = '已支付'
  AND o.pay_amount > (
      SELECT AVG(o2.pay_amount)
      FROM orders o2
      WHERE o2.status = '已支付' AND o2.city = o.city
  );

再取 3 笔最大的,把“本单金额”和“同城均值”并排看:

SELECT o.order_id, o.city,
       ROUND(o.pay_amount, 2) AS amount,
       ROUND((SELECT AVG(o2.pay_amount) FROM orders o2
              WHERE o2.status = '已支付' AND o2.city = o.city), 2) AS city_avg
FROM orders o
WHERE o.status = '已支付'
  AND o.pay_amount > (SELECT AVG(o2.pay_amount) FROM orders o2
                      WHERE o2.status = '已支付' AND o2.city = o.city)
ORDER BY o.pay_amount DESC
LIMIT 3;
看到什么
6,771 单,GMV 112,331,577.36;最大一单是广州的 O24021018718,43,186.27 元,而同城均值只有 10,174.26。
为什么只差 6 单

八个城市客单价都在 9,962–10,267 之间,非常接近,所以两种口径的人数几乎一样。但两批订单不是同一批:一笔 10,100 元的上海订单,全国口径下高于 10,077.71,同城口径下低于 10,266.61。

结论:单量接近不代表口径等价,要核对订单号。

03

用列子查询,先筛城市再筛订单

4 分钟 · 对应规则 1
做什么
找出“注册用户不少于 250 人”的城市,再在这些城市里取金额最高的 3 笔订单。
为什么
列子查询返回 1 列 5 行,只能配 IN。筛选条件本身是聚合结果(COUNT),WHERE 里写不了聚合,只能靠子查询。
怎么做
SELECT order_id, city, ROUND(pay_amount, 2) AS amount
FROM orders
WHERE status = '已支付'
  AND city IN (
      SELECT city FROM users
      GROUP BY city
      HAVING COUNT(*) >= 250
  )
ORDER BY pay_amount DESC
LIMIT 3;
看到什么
北京 O23011603143(43,168.96)、成都 O23030518411(42,253.75)、上海 O23082019847(41,546.31)。
卡住了 / 出错了

把 HAVING 写成 WHERE COUNT(*) >= 250:报 misuse of aggregate: count()。聚合条件只能放 HAVING。

04

用派生表,筛出高于平均的城市 核心

6 分钟 · 对应规则 1
做什么
先算出 8 个城市的 GMV,再和这 8 个值的平均比较。
为什么
平均值本身是聚合结果,不能直接写进 WHERE。要么 HAVING 配标量子查询,要么做成派生表——后者更好读,代价是同一段聚合要写两遍。
怎么做
SELECT city, ROUND(gmv, 2) AS gmv
FROM (
    SELECT city, SUM(pay_amount) AS gmv
    FROM orders
    WHERE status = '已支付'
    GROUP BY city
) t
WHERE gmv > (SELECT AVG(gmv) FROM (
    SELECT SUM(pay_amount) AS gmv
    FROM orders
    WHERE status = '已支付'
    GROUP BY city
) x)
ORDER BY gmv DESC;
看到什么
5 个城市高于平均(19,670,422.52):杭州 20,508,489.75、武汉 20,318,374.79、上海 20,009,620.63、广州 19,707,537.02、深圳 19,691,841.98。
为什么不是 4 个

深圳 19,691,841.98 只比平均线高 21,419.46 元,约 0.11%,但确实在线上。凭感觉数“前四名”会漏掉它。

另外,8 城平均 19,670,422.52 和全站客单价 10,077.71 不是一回事。

05

用 CASE WHEN 给 2,000 个用户分层 核心

6 分钟 · 对应规则 5
做什么
按累计消费把登录用户分成 A/B/C/D 四层,统计每层人数和消费总额。
为什么
规则 5:CASE 从上到下短路,阈值必须从 10 万写到 5 万。写反了,四层会塌成两层。
怎么做
SELECT
  CASE WHEN spend >= 100000 THEN 'A 高价值'
       WHEN spend >= 75000  THEN 'B 成长'
       WHEN spend >= 50000  THEN 'C 潜力'
       ELSE 'D 长尾' END AS tier,
  COUNT(*) AS users,
  ROUND(SUM(spend), 2) AS total
FROM (
    SELECT user_id, SUM(pay_amount) AS spend
    FROM orders
    WHERE status = '已支付' AND user_id IS NOT NULL
    GROUP BY user_id
) t
GROUP BY tier
ORDER BY tier;
看到什么
A 482 人 / 5,883 万;B 502 人 / 4,364 万;C 565 人 / 3,514 万;D 451 人 / 1,548 万。四层人数合计 2,000。
四个数加不到 1.57 亿,差在哪

四层合计 153,088,978.22,比 GMV 少 4,274,401.92。差额正是游客订单,被 user_id IS NOT NULL 排除了,第 8 步会对账。

06

用 CTE 重写第 4 步

5 分钟 · 对应规则 4
做什么
把两层嵌套的派生表,改成一段 CTE + 一次引用,结果必须完全一致。
为什么
规则 4:CTE 起名字是为了读懂,不是跑得更快。判断改写成功的标准只有一个——结果一模一样。
怎么做
WITH city_gmv AS (
    SELECT city, SUM(pay_amount) AS gmv
    FROM orders
    WHERE status = '已支付'
    GROUP BY city
)
SELECT city, ROUND(gmv, 2) AS gmv
FROM city_gmv
WHERE gmv > (SELECT AVG(gmv) FROM city_gmv)
ORDER BY gmv DESC;

这里 city_gmv 用了两次:FROM 一次,子查询求平均一次。派生表没有名字,只能把整段 SQL 再抄一遍。

看到什么
5 行,数值与第 4 步逐位相同。行数相同、金额相同、顺序相同,才算改写成功。
卡住了 / 出错了

no such table: city_gmv:CTE 只在同一条语句里有效,分号后就失效了。

把 avg_gmv 写在前面:CTE 不能引用后面的,调整顺序即可。

07

用 CASE WHEN 做行转列(按月)

7 分钟 · 对应规则 5
做什么
把“渠道 × 月”的 24 行长表,转成 4 行 × 6 列的宽表。
为什么
行转列的本质是“条件聚合”:每个目标列只累加属于它的金额。CASE 判断归属,SUM 负责合并。Excel 透视表的“列字段”干的是同一件事。
怎么做
SELECT channel,
  ROUND(SUM(CASE WHEN substr(order_date,1,7) = '2024-01' THEN pay_amount ELSE 0 END), 2) AS m1,
  ROUND(SUM(CASE WHEN substr(order_date,1,7) = '2024-02' THEN pay_amount ELSE 0 END), 2) AS m2,
  ROUND(SUM(CASE WHEN substr(order_date,1,7) = '2024-03' THEN pay_amount ELSE 0 END), 2) AS m3,
  ROUND(SUM(CASE WHEN substr(order_date,1,7) = '2024-04' THEN pay_amount ELSE 0 END), 2) AS m4,
  ROUND(SUM(CASE WHEN substr(order_date,1,7) = '2024-05' THEN pay_amount ELSE 0 END), 2) AS m5,
  ROUND(SUM(CASE WHEN substr(order_date,1,7) = '2024-06' THEN pay_amount ELSE 0 END), 2) AS m6,
  ROUND(SUM(pay_amount), 2) AS total
FROM orders
WHERE status = '已支付' AND order_date >= '2024-01-01'
GROUP BY channel
ORDER BY total DESC;
看到什么
4 行 × 8 列。自然搜索合计 21,347,987.26,信息流 16,404,309.95,朋友推荐 10,814,388.26,线下 5,010,325.49;四行相加 53,577,010.96。
渠道2024-012024-02
长表自然搜索3,686,170.74—
长表自然搜索—3,288,474.41
↓月份从“行”搬到“列”
宽表自然搜索3,686,170.743,288,474.41
图 14.3.1 行转列不是换显示,是把每个月的金额装进各自的列
卡住了 / 出错了

某个月整列是 0:日期写成 '2024-1'。库里是 2024-01,字符串比较必须补零。

合计比 5,357 万大:忘了加 order_date >= '2024-01-01',把 2023 年的数据也算进了各列。

08

对账:分层合计 + 游客 = GMV

5 分钟 · 对应铁律“可复现”
做什么
把分层总额、游客订单、全站 GMV 放一张表,验证差额来源。
为什么
第 5 步加了 user_id IS NOT NULL,主动排除了 565 笔游客订单。凡是被 WHERE 排除的行,都必须能解释——这是第 6 章“每个动作留痕”的 SQL 版。
怎么做
SELECT '登录用户累计消费' AS item, ROUND(SUM(spend), 2) AS amount
FROM (
    SELECT user_id, SUM(pay_amount) AS spend
    FROM orders
    WHERE status = '已支付' AND user_id IS NOT NULL
    GROUP BY user_id
) t
UNION ALL
SELECT '游客订单', ROUND(SUM(pay_amount), 2)
FROM orders
WHERE status = '已支付' AND user_id IS NULL
UNION ALL
SELECT 'GMV 合计', ROUND(SUM(pay_amount), 2)
FROM orders
WHERE status = '已支付';
看到什么
153,088,978.22 + 4,274,401.92 = 157,363,380.14,一分不差。游客占 GMV 的 2.72%。
为什么不是 565 笔全算

565 是游客订单总数,其中只有 439 笔已支付。GMV 只算已支付,所以游客贡献是 439 笔、4,274,401.92 元。

14.4结果验证

先填三个数,再做题。任何一个对不上,都说明某一层的口径漏了行。

平均线是 19,670,422.52,注意深圳。
第 5 步结果的第一行。
注意:565 笔里只有 439 笔已支付。

14.4.1五道选择题

不看正文,凭第一反应选。选完立刻出解析。

Q1子查询放在 = 右边时,必须是什么形状?
解析:= 是标量比较,右边必须是一个值,即 1 行 1 列。1 列 N 行配 IN,N 行 M 列进 FROM。
Q2WHERE user_id NOT IN (SELECT user_id FROM orders WHERE city = '北京') 返回 0 行,最可能的原因是什么?
解析:游客订单的 user_id 是 NULL,被带进子查询的返回值。NULL 参与 NOT IN 得到 UNKNOWN,WHERE 只保留 TRUE,于是返回空集。加 AND user_id IS NOT NULL 后是 5,085。
Q3相关子查询和非相关子查询的根本区别是?
解析:只有一条:子查询是否依赖外层当前行。依赖就是相关,外层每行重算一次;不依赖就是非相关,可以先算成常量。
Q4CASE 分层时把 >= 50000 写在 >= 100000 前面,会发生什么?
解析:CASE 短路求值,第一个 TRUE 就返回。5 万的门槛写最前,10 万的人也会先命中。实测四层塌成两层:C 层 1,549 人、D 层 451 人。
Q5关于 CTE,下面哪句是对的?
解析:CTE 是语句级的命名结果集,不落地;只能向后引用;性能取决于优化器,不保证更快。它的第一价值是可读、可分段验证。
得分:0 / 5

14.5常见问题排查

按“现象 → 原因 → 解决”整理。报错大多指向同一个根因:形状或位置。

现象原因解决
子查询返回多行,SQLite 却不报错标量位置放了列子查询,SQLite 取第一行加聚合或 LIMIT 1;确认业务上真的只要一行
NOT IN 返回 0 行比较链里有 NULL(左值或子查询结果)改 NOT EXISTS,或在子查询加 IS NOT NULL
near "select": syntax error子查询没加括号,写成了 IN SELECT ...补上括号:IN (SELECT ...)
misuse of aggregate: sum()把聚合函数写进了 WHERE聚合条件放 HAVING,或先做成派生表再筛
CASE 分层少了几档阈值顺序反了,大范围写在了前面按从大到小重排,最后加 ELSE 兜底
no such table: city_gmvCTE 只在一条语句内有效,语句已结束把后续查询并进同一条 WITH 语句
行转列出现整列 0月份字符串没补零(2024-1 ≠ 2024-01)统一用 substr(order_date,1,7) 生成月份
分层金额加总小于 GMV过滤掉了 user_id 为 NULL 的游客订单差额单独列一行,或把游客作为独立分层

14.6本章小结与练习

  1. 子查询按形状分三种:标量当值用、列配 IN、表进 FROM。形状决定位置,位置错了要么报错,要么静默出错。
  2. 相关与否只看一件事:子查询有没有引用外层列。引用了,外层每行重算一次。
  3. NOT IN 的 NULL 陷阱:列表里有一个 NULL,整条查询近似空集。565 笔游客订单就是这样消失的。保命写法是 NOT EXISTS。
  4. CTE 是流水线:从上往下读,一段一件事,名字即粒度。它不是临时表,只在一条语句里活着。
  5. CASE 是短路判断:第一个 TRUE 就返回,阈值从大到小。顺序写反,四层塌成两层,SQL 不会提醒。

14.6.1练习

  1. 用子查询找出“客单价高于全国平均”的城市(提示:HAVING + 标量子查询),并说明为什么不能把 AVG 直接写进 WHERE。
  2. 用 CASE WHEN 按订单金额分四档(<5,000 / 5,000–9,999 / 10,000–19,999 / ≥20,000),统计各档单量、GMV 和客单价。
  3. 用两段 CTE 分别算出“城市 GMV”和“城市高价值用户数”,按城市 JOIN,找出“GMV 前 4 但高价值人数不在前 4”的城市。
  4. 用 CASE WHEN 行转列输出 2024 上半年四个渠道的月度 GMV,再回答:3 月环比增幅最大的是哪个渠道?增幅多少?
参考答案

1. 把 GROUP BY city 的结果放进 HAVING,再和 (SELECT AVG(pay_amount) FROM orders WHERE status='已支付') 比。WHERE 在 GROUP BY 之前执行,那时还没有“城市客单价”这个值,写 AVG 会报 misuse of aggregate。

2. A 档 4,353 单 / 11,475,931.43 / 2,636.33;B 档 4,391 单 / 32,546,938.77 / 7,412.19;C 档 5,372 单 / 76,205,447.30 / 14,185.68;D 档 1,499 单 / 37,135,062.64 / 24,773.22。四档合计 15,615 单、157,363,380.14。

3. 杭州(GMV 第 1,高价值 60 人排第 6)、武汉(第 2 / 47 人排第 8)、上海(第 3 / 61 人排第 5)三个城市符合。广州 GMV 第 4、高价值 63 人排第 3,不在答案里。

4. 朋友推荐,3 月 2,107,458.78 对 2 月 1,636,665.91,环比 +28.77%;同期自然搜索 −0.81%、信息流 +14.11%、线下 −12.45%。

下一章预告:本章第 4 步为了筛出“高于平均的城市”,套了两层派生表。 下一章的窗口函数,可以用一句 RANK() OVER (ORDER BY SUM(pay_amount) DESC) 直接给城市排名,还能顺手算出占比、累计和环比——不用再套娃。
数据分析师训练营 · 第十四章
SQLite 3.45 · DB Browser for SQLite