数据分析师训练营/ 第三篇 SQL/ 第十三章
第十三章 · 多表关联

JOIN 之前,先数行

单表查询和聚合能回答的问题,到“每个渠道卖了多少”就结束了。再往下问一层——“北京的金卡用户买什么品类”—— 订单表里没有会员等级,商品表里没有订单号,答案散在三张表里。JOIN 就是按某个键把这些表拼起来。

但 JOIN 也是 SQL 里最容易把数字算错的地方。两张表一拼,行数可能从 20,000 变成 39,765; 再 SUM 一次订单金额,GMV 会从 1.57 亿变成 3.66 亿。本章的核心不是语法,是粒度: 拼之前先预测行数,拼之后先核对行数。13.3 的实验里,你会亲手制造一次 2.32 倍的错误。

理论 : 实操 = 6 : 4 约 8 学时 前置 第 12 章

13.1原理:关系、JOIN 与粒度

JOIN 的语法只有几行,难的是判断“拼完之后一行代表什么”。这一节先认键,再认五种 JOIN, 最后把最容易翻车的粒度陷阱拆开。

13.1.1关系模型:主键、外键、一对多

关系数据库把数据拆成多张表,每张表只负责一件事:用户、商品、订单、商品行。表与表之间靠键建立联系。

主键是表中唯一标识一行的字段。用户表的主键是 user_id,订单表的主键是 order_id,商品表的主键是 product_id。主键必须唯一、不能为 NULL。 外键是另一张表的主键在这个表里的“引用”。orders.user_id 指向 users.user_id,order_items.order_id 指向 orders.order_id。

外键带来三种关系。用户 → 订单是一对多:一个用户可以下多笔订单,一笔订单只属于一个用户。 订单 → 商品行也是一对多:一笔订单可以有三行商品。商品 ↔ 订单是多对多: 一个商品出现在很多订单里,一笔订单也买很多商品,中间必须有 order_items 搭桥。

表主键外键一行代表什么行数
usersuser_id—一个注册用户2,000
productsproduct_id—一个 SKU120
ordersorder_iduser_id → users一笔订单20,000
order_itemsitem_idorder_id → orders;product_id → products一笔订单里的一个商品行39,765
eventsevent_iduser_id → users一次用户行为16,932
图 13.1.1 五张表的主键、外键与粒度:先看清一行是什么,再谈怎么拼
1

先认主键,再认外键,最后问一句“这一行代表什么”

每张表都有一个粒度:用户表一行是一个人,订单表一行是一笔交易,商品行表一行是一个商品行。 JOIN 之前,你必须知道左右两边各自一行是什么,否则拼出来的行你根本解释不了。

如果不这样会怎样:把 order_items 当成订单表,一句 SELECT COUNT(*) FROM order_items 得到 39,765,你会报告“本月有 39,765 笔订单”, 真实只有 20,000 笔,虚高 98.8%。多出来的 19,765 行全是同一笔订单里的第二、第三个商品行。

13.1.2五种 JOIN:保留谁,乘几倍

JOIN 的类型决定了“匹配不上的行怎么办”。INNER 取交集,LEFT 保留左表全部, RIGHT 保留右表全部,FULL 取并集,CROSS 取笛卡尔积。但只看集合图不够,你还得知道行数会变成多少。

写法保留什么本项目实测典型用途
orders INNER JOIN users两边都匹配上的行19,435只要有关联用户的数据
orders LEFT JOIN users左表订单全保留,匹配不上补 NULL20,000补维度、保留游客订单
users LEFT JOIN orders左表用户全保留,一个用户对多笔订单19,435统计每个用户的订单
users RIGHT JOIN orders右表订单全保留20,000少用,可改写成 LEFT
users FULL JOIN orders两边都保留20,000对账,找双边差异
users CROSS JOIN products笛卡尔积,每行配每行240,000生成组合骨架、日期维表
图 13.1.2 五种 JOIN 的保留规则与实测行数:同一对表,方向不同,行数就不同

注意第三行:users LEFT JOIN orders 的结果是 19,435,不是 2,000。 一个用户对多笔订单,左表的 2,000 行每一行都被展开成了约 10 行。 LEFT JOIN 里左表就是驱动表,它保证的是“左表每一行至少出现一次”,不是“左表行数不变”。 只有右表的连接键唯一(N:1)时,LEFT JOIN 才不改变行数。

2

JOIN 默认是 INNER;要保留左表必须写 LEFT,且要确认右表键唯一

只写 JOIN 等于 INNER JOIN,匹配不上的行会静默消失。 想保留左表全部,写 LEFT JOIN;想保证行数不变,还要加一条:右表的连接键必须唯一。

如果不这样会怎样:用 INNER JOIN 给订单补会员等级,565 笔游客订单会直接消失; 其中 439 笔是已支付订单,GMV 合计 4,274,401.92,占 2.7%。 你的月报会凭空少掉 427 万,而且不会报任何错。

版本提示:SQLite 从 3.39 起支持 RIGHT JOIN 和 FULL JOIN,本实验环境是 3.45.1。 更老的版本会报语法错误,用 LEFT JOIN 调换左右表即可等价改写。

13.1.3ON 与 WHERE:匹配条件和过滤条件不是一回事

ON 回答的是“两张表怎么配对”,WHERE 回答的是“配完之后留下哪些行”。 对 INNER JOIN 来说,这两个位置常常可以互换,结果一样;但对 LEFT JOIN,它们完全不是一回事。

假设你想“保留所有订单,同时看看哪些订单来自北京用户”。条件 u.city = '北京' 应该放在 ON 里:匹配时只认北京用户,匹配不上的订单仍然保留,只是用户字段为 NULL。 如果放到 WHERE 里,数据库会先补全所有订单,再筛掉“用户城市不是北京”的行,包括匹配不上的游客订单。 LEFT JOIN 的结果被 WHERE 一过滤,就退化成 INNER JOIN 了。

写法行数含义
LEFT JOIN ... ON u.city = '北京'20,000只匹配北京用户,其他订单保留,用户字段为 NULL
LEFT JOIN ... WHERE u.city = '北京'2,582补全后再过滤,NULL 行被删掉,等价于 INNER JOIN
INNER JOIN ... WHERE u.city = '北京'2,582只留匹配上的北京用户订单
图 13.1.3 同一个条件放 ON 还是放 WHERE,行数从 20,000 变成 2,582
3

LEFT JOIN 后过滤右表,条件必须放 ON;放 WHERE 就退化成 INNER

判断方法:这个条件是在描述“怎么匹配”,还是在描述“最终要哪些行”? 描述匹配的放 ON,描述最终结果的放 WHERE。LEFT JOIN 的右表条件,绝大多数属于前者。

如果不这样会怎样:你想保留 20,000 笔订单,只在能匹配时标出北京用户, 结果把 u.city = '北京' 写进了 WHERE,只剩 2,582 行,87.1% 的订单凭空消失。 更麻烦的是,SQL 不报错,结果看起来还挺合理。

13.1.4粒度陷阱:orders JOIN order_items 会让金额按商品行重复

这是全章最重要的一条,也是 GMV 翻倍的头号原因。 订单表一行是一笔订单,金额字段 pay_amount 是整笔订单的实付; 商品行表一行是一条商品行记录,一笔订单有几条就有几行。两张表按 order_id 一拼, 订单的金额会被复制到每一行商品上。

看一笔真实的订单 O23010104230:原价 23,167.23,优惠 200.67,实付 22,966.56, 里面有三个商品行(服饰 12,900.63、手机 7,459.96、手机 2,806.64)。JOIN 之后,这笔订单变成三行, 每行的 pay_amount 都是 22,966.56。你把这列一加,得到 68,899.68,正好是真实值的 3 倍。

订单号商品品类行金额订单实付
1O23010104230P0120服饰12,900.6322,966.56
2O23010104230P0023手机7,459.9622,966.56
3O23010104230P0010手机2,806.6422,966.56
SUM(行金额) = 23,167.23(正确)SUM(订单实付) =68,899.68(3 倍)
图 13.1.4 一笔订单三行商品:行金额只能加一次,订单金额却被复制了三遍

放到全量数据上,错误被放大到 2.32 倍:已支付订单 15,615 笔,JOIN 商品行后变成 31,071 行, SUM(o.pay_amount) 得到 365,790,418.68,而真实 GMV 是 157,363,380.14, 多算了 208,427,038.54 元。这不是小数误差,是 2 亿。

4

一对多 JOIN 后,不能再 SUM“一”那一侧的全量字段

订单是“一”,商品行是“多”。JOIN 之后,订单字段被复制到每一行商品上。 要算金额,只能 SUM 商品行自己的 i.amount;要算订单数,只能 COUNT(DISTINCT o.order_id)。直接 SUM(o.pay_amount) 或 COUNT(*),都会按商品行数放大。

如果不这样会怎样:月报写“GMV 3.66 亿”,财务对账只有 1.57 亿,差 2.08 亿。 你会花一整天排查 ETL、汇率、退款,最后发现只是 SQL 里多了一个 JOIN。

13.1.5多对多与中间表:order_items 为什么必须存在

商品和订单是多对多:一个商品可以出现在很多订单里,一笔订单也可以买多个商品。 关系数据库不能直接在两张表之间画多对多,必须加一张中间表,把多对多拆成两个一对多。 在青柠优选里,这张中间表就是 order_items。

它不是一张“多余的关联表”,本身就是事实表:qty 记录买了几件, unit_price 记录成交单价,amount 记录这一行的金额。 120 个商品、20,000 笔订单,靠 39,765 行商品行连接;每个商品平均出现在约 330 笔订单里, 每笔订单有 1 到 3 行商品。

5

多对多必须有中间表,度量放在中间表上

看到“商品”和“订单”直接相连的需求,先找中间表。中间表既是关系的桥,也是度量的载体。 没有它,两张表只能做笛卡尔积,结果没有任何业务含义。

如果不这样会怎样:products JOIN orders ON 1=1 会生成 120 × 20,000 = 2,400,000 行,每个商品都被配上了全部 20,000 笔订单。 你按商品分组算“订单数”,每个商品都是 20,000 单,看起来整齐得可疑。

13.2分析:JOIN 之前先做行数沙盘

有经验的分析师写 JOIN 之前,会先在纸上写下“预测行数”。执行完再对一次,不等就说明关系判断错了。 这个动作叫行数沙盘。

沙盘只要回答三个问题:左表一行是什么?右表的连接键唯一吗?匹配不上的行要不要保留? 三个答案组合起来,行数只有四种可能:

关系行数公式例子结果
1:1等于左表行数订单 ↔ 订单扩展信息不变
N:1等于左表行数(右表键唯一)订单 → 用户、商品行 → 商品不变
1:N左表行数 × 平均子行数订单 → 商品行、用户 → 订单变多
M:N等于匹配对总数,可能爆炸商品 ↔ 订单(必须有中间表)不可直接预测

把本章的 JOIN 写进沙盘,预测和实测并排。

连接关系预测行数实测行数
orders LEFT JOIN usersN:1,右表键唯一20,00020,000
orders INNER JOIN usersN:1,565 行不匹配19,43519,435
users LEFT JOIN orders1:N,平均 9.7 单/人19,43519,435
orders JOIN order_items1:N,平均 1.99 行/单39,76539,765
orders JOIN items JOIN productsN:1,商品键唯一39,76539,765
users CROSS JOIN productsM:N,无连接条件240,000240,000
图 13.2.1 行数沙盘:先预测,再执行,对不上就说明关系判断错了

沙盘里最容易被忽略的是第三行:users LEFT JOIN orders。左表只有 2,000 个用户, 结果却是 19,435 行。LEFT JOIN 不等于“左表行数不变”,它只保证左表每一行至少出现一次; 右表一对多时,左表的行会被展开。

要算“每个用户的订单数”,不要先 JOIN 再 COUNT(*):先把订单按 user_id 聚合, 再让用户表 LEFT JOIN 这个结果。这就是“先聚合,再 JOIN”,行数在 JOIN 前就固定了。

order_items
39,765 行
→
按 order_id 聚合
20,000 行
→
JOIN orders
20,000 行
→
按维度分组
结果行
图 13.2.2 先聚合再 JOIN:把“多”的一侧先压成“一”,后续 JOIN 就不会放大行数
沙盘对不上时,先别改 SQL。先问:右表连接键真的唯一吗?用 COUNT(*) 验过吗? 键有重复,N:1 就变成 N:N,行数成倍增长。

13.3实验:八步拼出订单宽表

打开 qingning.db,按顺序执行。每一步先写下预测行数,再运行核对。 预测错了不要紧,弄清楚为什么错更有价值。

已完成 0 / 8 预计 50 分钟
01

数清五张表,确认主键唯一

4 分钟 · 对应规则 1
做什么
把五张表的行数一次列出来,再验证订单号确实唯一。
为什么
规则 1:行数是沙盘的底数,主键唯一是 N:1 关系成立的前提。底数错了,后面全错。
怎么做
SELECT 'users'       AS 表名, COUNT(*) AS 行数 FROM users
UNION ALL SELECT 'products',    COUNT(*) FROM products
UNION ALL SELECT 'orders',      COUNT(*) FROM orders
UNION ALL SELECT 'order_items', COUNT(*) FROM order_items
UNION ALL SELECT 'events',      COUNT(*) FROM events;
SELECT COUNT(*)                AS 订单行数,
       COUNT(DISTINCT order_id) AS 唯一订单号
FROM orders;
看到什么
表名          行数
users         2000
products      120
orders        20000
order_items   39765
events        16932

订单行数   唯一订单号
20000     20000
订单号 20,000 / 20,000,主键唯一。商品行 39,765 ÷ 订单 20,000 = 1.99 行/单。
卡住了 / 出错了

UNION ALL 要求每列的列名以第一个 SELECT 为准;如果表名写错,SQLite 会报 “no such table”。

02

订单 LEFT JOIN 用户,补城市与会员等级

6 分钟 · 对应规则 2
做什么
给每笔订单补上注册城市和会员等级,同时保住游客订单。
为什么
规则 2:订单 → 用户是 N:1,右表键唯一,LEFT JOIN 不改变行数;INNER 会丢掉 565 笔游客订单。
怎么做
SELECT COUNT(*)                        AS 行数,
       COUNT(u.user_id)                AS 匹配上的行,
       COUNT(*) - COUNT(u.user_id)     AS 游客订单
FROM orders o
LEFT JOIN users u ON o.user_id = u.user_id;
SELECT o.order_id,
       o.city        AS 收货城市,
       u.city        AS 注册城市,
       u.member_level,
       o.channel     AS 下单渠道,
       u.channel     AS 注册渠道
FROM orders o
LEFT JOIN users u ON o.user_id = u.user_id
LIMIT 5;
看到什么
行数    匹配上的行   游客订单
20000   19435       565

order_id       收货城市  注册城市  会员等级  下单渠道  注册渠道
O23101400001   上海     武汉     普通     信息流    自然搜索
O24032900002   西安     西安     普通     自然搜索  信息流
O23070100003   武汉     广州     普通     自然搜索  自然搜索
O23030400004   武汉     北京     银卡     线下      自然搜索
O23092800005   成都     上海     银卡     线下      信息流
行数仍是 20,000。565 笔游客订单被保留,用户字段为 NULL。注意收货城市和注册城市不一定相同。
把 LEFT 改成 INNER 会怎样

行数变成 19,435,565 笔游客订单消失;其中 439 笔已支付,GMV 4,274,401.92,占 2.7%。

03

订单 JOIN 商品行,看行数怎么变成 39,765

6 分钟 · 对应规则 4
做什么
把订单表和商品行表按 order_id 拼起来,数行数,并看每笔订单有几行商品。
为什么
规则 4:订单 → 商品行是 1:N,一笔订单会被复制成多行。这是后面金额重复的根源。
怎么做
SELECT COUNT(*)                  AS 行数,
       COUNT(DISTINCT o.order_id) AS 订单数
FROM orders o
JOIN order_items i ON o.order_id = i.order_id;
SELECT 商品行数, COUNT(*) AS 订单数
FROM (
  SELECT order_id, COUNT(*) AS 商品行数
  FROM order_items
  GROUP BY order_id
) AS t
GROUP BY 商品行数
ORDER BY 商品行数;
看到什么
行数    订单数
39765   20000

商品行数  订单数
1        6789
2        6657
3        6554
行数从 20,000 涨到 39,765,订单数仍是 20,000。6,789 笔订单只有 1 行商品,6,657 笔有 2 行,6,554 笔有 3 行。
卡住了 / 出错了

如果行数也是 20,000,说明你连错了键,可能用了 o.user_id = i.order_id 之类的条件;检查 ON 两边是不是同一业务含义。

04

故意犯一次错:SUM 出 3.66 亿

6 分钟 · 对应规则 4
做什么
在 JOIN 后的结果上直接 SUM 订单实付,看看会放大多少。
为什么
规则 4:订单金额被复制到每一行商品上,SUM 会把同一笔钱加 1 到 3 次。必须亲眼看到这个数,才会记住。
怎么做
SELECT COUNT(*)                  AS 行数,
       ROUND(SUM(o.pay_amount),2) AS 错误GMV
FROM orders o
JOIN order_items i ON o.order_id = i.order_id
WHERE o.status = '已支付';
SELECT o.order_id,
       o.pay_amount,
       COUNT(*)                     AS 商品行数,
       ROUND(o.pay_amount * COUNT(*),2) AS 重复后的金额
FROM orders o
JOIN order_items i ON o.order_id = i.order_id
WHERE o.order_id = 'O23010104230'
GROUP BY o.order_id;
看到什么
行数    错误GMV
31071   365790418.68

order_id       pay_amount   商品行数   重复后的金额
O23010104230   22966.56     3         68899.68
真实 GMV 是 157,363,380.14,错误结果是 365,790,418.68,放大 2.3245 倍,多算 208,427,038.54 元。
为什么是 2.32 倍,不是 3 倍

不是每笔订单都有 3 行商品。全部订单平均 1.99 行;已支付订单按金额加权的平均行数是 2.3245:3 行订单平均实付 15,140.83,远高于 1 行的 5,042.43,权重更大。单笔 3 行仍放大 3 倍。

05

正确算法:按商品行算品类 GMV

8 分钟 · 对应规则 4、5
做什么
从商品行出发,JOIN 商品表取品类、JOIN 订单表筛已支付,再按品类 SUM 行金额。
为什么
规则 4:聚合的对象是商品行,i.amount 一行只出现一次,不会被复制。规则 5:品类在商品表,订单状态在订单表,必须通过中间表把它们连起来。
怎么做
SELECT p.category                  AS 品类,
       COUNT(*)                    AS 商品行数,
       ROUND(SUM(i.amount),2)      AS 品类金额
FROM order_items i
JOIN products p ON i.product_id = p.product_id
JOIN orders o   ON i.order_id = o.order_id
WHERE o.status = '已支付'
GROUP BY p.category
ORDER BY 品类金额 DESC;
看到什么
品类   商品行数   品类金额
手机   7785      47570925.64
电脑   7761      33756338.97
家电   5192      25939444.18
配件   5202      25503426.77
服饰   5131      25105176.08
五行相加 157,875,311.64,商品行数相加 31,071。与第 4 步的错误结果 3.66 亿相比,这才是能进报表的数。
为什么不是 157,363,380.14

因为这里加的是商品行原价 i.amount,还没有扣优惠。第 6 步会对账,差额正好是全站优惠 511,931.50。

06

口径对账:原价、优惠、实付

5 分钟 · 对应规则 4
做什么
回到订单表单表,把原价、优惠、实付三个数一次算出来,解释第 5 步的差额。
为什么
品类金额按商品行原价,订单 GMV 按实付。两个口径都对,但不能混着汇报,否则一定被问“为什么加不起来”。
怎么做
SELECT ROUND(SUM(amount),2)     AS 订单原价,
       ROUND(SUM(discount),2)   AS 优惠,
       ROUND(SUM(pay_amount),2) AS 实付
FROM orders
WHERE status = '已支付';
看到什么
订单原价        优惠        实付
157875311.64   511931.50   157363380.14
第 5 步的 157,875,311.64 就是订单原价;减去优惠 511,931.50,正好是 GMV 157,363,380.14。
那品类实付 GMV 怎么算

需要把每笔订单的优惠按商品行金额比例分摊到行上,再按品类汇总。这要用到子查询或窗口函数,第 14、15 章会讲。

07

LEFT JOIN 找“对不上”的行

5 分钟 · 对应规则 3
做什么
用 LEFT JOIN + IS NULL 找出没有对应用户的订单,以及没有订单的用户。
为什么
规则 3:LEFT JOIN 保留左表全部,匹配不上的右表字段为 NULL。再用 IS NULL 一筛,就是“缺失清单”。
怎么做
SELECT COUNT(*) AS 游客订单
FROM orders o
LEFT JOIN users u ON o.user_id = u.user_id
WHERE u.user_id IS NULL;
SELECT COUNT(*) AS 零订单用户
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
WHERE o.order_id IS NULL;
看到什么
游客订单
565

零订单用户
0
565 笔订单没有账号;0 个注册用户没有订单。0 不是写错,它说明 2,000 个用户全部下过单。
为什么“0 行”也是有效结果

LEFT JOIN + IS NULL 的语义就是“左边有、右边没有”。返回 0 行,证明右表对左表是全覆盖的,这本身就是一条结论。

08

用宽表算会员等级客单价

8 分钟 · 对应规则 2、4
做什么
在订单 + 用户的宽表上,按会员等级算订单数、下单人数、GMV、客单价。
为什么
规则 2:订单 → 用户是 N:1,行数不变;规则 4:人数要用 COUNT(DISTINCT),不能 COUNT(*)。
怎么做
SELECT COALESCE(u.member_level,'游客') AS 会员等级,
       COUNT(*)                        AS 订单数,
       COUNT(DISTINCT o.user_id)       AS 下单用户数,
       ROUND(SUM(o.pay_amount),2)      AS GMV,
       ROUND(AVG(o.pay_amount),2)      AS 客单价
FROM orders o
LEFT JOIN users u ON o.user_id = u.user_id
WHERE o.status = '已支付'
GROUP BY COALESCE(u.member_level,'游客')
ORDER BY GMV DESC;
看到什么
会员等级  订单数   下单用户数   GMV           客单价
普通      8786     1169        87934726.93   10008.51
银卡      4199     552         42595219.32   10144.13
金卡      1553     196         16183870.28   10421.04
黑卡      638      83          6375161.69    9992.42
游客      439      0           4274401.92    9736.68
订单数相加 15,615,与已支付订单总数一致。游客行的“下单用户数”是 0,因为 guest 的 user_id 全是 NULL。
金卡客单价最高,说明什么

只能说明金卡的平均订单金额最高(10,421.04),不能推导“升级会员会提高客单价”。黑卡反而只有 9,992.42,样本 638 单,噪声很大。

13.4结果验证

两个数字:JOIN 后的行数,和错误算法下的 GMV。第一个考粒度,第二个考后果。

用 COUNT(*),不加状态过滤。
只筛 status='已支付',保留两位小数。

13.4.1五道选择题

Q1orders LEFT JOIN users ON orders.user_id = users.user_id 返回多少行?
解析:订单 → 用户是 N:1,右表 user_id 唯一,LEFT JOIN 不改变左表行数。565 笔游客订单匹配不上,用户字段补 NULL,但订单行仍在。
Q2orders JOIN order_items ON orders.order_id = order_items.order_id 返回多少行?
解析:订单 → 商品行是 1:N,平均每单 1.99 行。20,000 笔订单展开成 39,765 行,订单号会重复出现。
Q3在 Q2 的结果上执行 SUM(orders.pay_amount)(只筛已支付),得到 365,790,418.68。正确 GMV 是 157,363,380.14。原因是什么?
解析:一笔订单如果有三行商品,它的 pay_amount 就被加三次。整体放大 2.3245 倍,多算 2.08 亿。要算商品金额就用 SUM(i.amount),要算订单金额就先聚合再 JOIN。
Q4orders LEFT JOIN users ON ... WHERE users.city = '北京' 的结果,等价于下面哪个写法?
解析:WHERE 在 JOIN 之后执行,匹配不上的行 users.city 是 NULL,会被 = '北京' 过滤掉。LEFT JOIN 的“保留左表”被 WHERE 抵消,结果退化成 INNER JOIN。
Q5要计算“每个品类的 GMV”,下面哪种写法粒度正确?
解析:品类在商品表,金额在商品行表,订单状态在订单表。以商品行为粒度,SUM 行金额,再按品类分组,每一行的金额只算一次。A 会把订单金额按商品行重复,B、C 是笛卡尔积。
得分:0 / 5

13.5常见问题排查

JOIN 的错,八成体现在行数上。先看行数,再看金额。

现象原因解决
JOIN 后行数变多 右表一对多,左表行被展开(规则 4) 先聚合右表,或改用 COUNT(DISTINCT)
LEFT JOIN 后行数反而变少 WHERE 里过滤了右表字段(规则 3) 把右表条件移到 ON,或确认是否本来就想要 INNER
SUM 金额正好翻倍 / 三倍 订单金额被商品行复制(规则 4) 改 SUM i.amount,或先按订单聚合再 JOIN
JOIN 后结果为空 键的格式不一致:空格、大小写、类型不同 先 SELECT 两边的键各看 10 行,再 TRIM / 转类型
游客订单整批消失 用了 INNER JOIN(规则 2) 改成 LEFT JOIN,接受右表字段为 NULL
多表 JOIN 后行数爆炸 多对多没走中间表,或漏写连接条件 补中间表;检查每个 ON 是否都用上了主外键
品类合计与总 GMV 对不上 原价口径 vs 实付口径(第 6 步) 统一口径;差额应等于优惠 511,931.50

13.6本章小结与练习

  1. 先认粒度,再写 JOIN:每张表一行代表什么,比 JOIN 语法重要得多。用 order_items 数订单,会把 20,000 数成 39,765。
  2. LEFT JOIN 不等于行数不变:右表一对多时左表会被展开;只有右表键唯一,N:1 才保持行数。
  3. ON 管匹配,WHERE 管结果:LEFT JOIN 后在 WHERE 过滤右表,会退化成 INNER。20,000 行会变成 2,582 行。
  4. 粒度陷阱会放大金额:订单 JOIN 商品行后 SUM 订单实付,GMV 从 1.57 亿变 3.66 亿,放大 2.3245 倍。
  5. 多对多必须有中间表:order_items 既连接商品与订单,也承载数量、单价、行金额。

13.6.1练习

  1. orders 表与 users 表关联,INNER JOIN 和 LEFT JOIN 各返回多少行?差在哪 565 行?
  2. 如果每笔订单都恰好有 3 行商品,第 4 步的错误 SUM 会放大几倍?为什么当前实测是 2.3245 倍?
  3. 用 LEFT JOIN + IS NULL 找出“订单表里有、用户表里没有”的订单。已支付的部分有多少笔、多少 GMV?
  4. 写一条 SQL,按“渠道 × 品类”统计已支付 GMV,要求粒度正确。写出 GMV 最高的一行。
参考答案

1. INNER 19,435 行,LEFT 20,000 行。差的 565 行是 user_id 为 NULL 的游客订单,INNER 匹配不上就丢掉了。

2. 放大 3 倍。当前实测 2.3245 倍,不是简单的 1.99 行,而是按订单金额加权的平均行数:3 行订单的平均实付远高于 1 行订单,权重更大。

3. 共 565 笔;其中已支付 439 笔,GMV 4,274,401.92 元。SQL 是 orders LEFT JOIN users ... WHERE users.user_id IS NULL,再按状态筛。

4. FROM order_items i JOIN products p ON i.product_id=p.product_id JOIN orders o ON i.order_id=o.order_id WHERE o.status='已支付' GROUP BY o.channel,p.category,金额用 SUM(i.amount)。最高一行是自然搜索 / 手机:19,450,458.74。

下一章预告:第 14 章会把本章的 JOIN 装进子查询和 CTE:同一份 GMV 可以写成 5 层嵌套, 也可以拆成 3 段流水线。还会遇到 NOT IN 与 NULL 的经典陷阱——565 笔游客订单会凭空消失。
数据分析师训练营 · 第十三章
DB Browser for SQLite / 命令行 sqlite3 均可