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

一行到底是什么

第 6 章我们清洗过一张 480 行的订单表。现在换成一个 12 MB 的数据库:5 张表、78,817 行。 第一句 SQL 可能只有一行,但背后要先回答三个问题: 从哪张表取数、一行代表什么、哪些行算数。 这三个问题答错,语法再熟也会算出错的数。

本章不讲技巧,只讲地基:表与键、事实与维度、行粒度、执行顺序、NULL、类型转换。 它们是后面七章所有 SQL 的共同前提。

理论 : 实操 = 8 : 2 约 6 学时 前置 第 6–8 章

10.1六块地基

很多人学 SQL 是背语法:SELECT 怎么写、WHERE 怎么写。但真实工作里 80% 的错误不是语法错, 而是对表的理解错了。这一节用六块地基,讲清“数据库眼里的数据”。

10.1.1表、行、列、主键、外键

数据库是一组通过键互相引用的表。列是字段,规定能存什么;行是一条记录,代表一个具体对象。

但每一行都必须能被一个东西唯一地指出来,这个东西叫主键(primary key)。 本库五张表的主键分别是:users.user_id、products.product_id、 orders.order_id、order_items.item_id、events.event_id。 订单号长这样:O24060111090——12 位,第 2–7 位是日期(YYMMDD),后 5 位是当天流水,只属于一笔订单。

订单表里的 user_id 指向用户表的主键,叫外键(foreign key), 它把分散的表连成一张网:

users
2,000 行
1 ─ N
orders
20,000 行
1 ─ N
order_items
39,765 行
N ─ 1
products
120 行
events
16,932 行
N ─ 1
users
2,000 行
一次行为属于一个用户;一个用户可以有很多次行为
图 10.1.1 五张表靠主键和外键连成一张网,箭头两端写着基数

箭头上的 1 ─ N 是后面所有问题的根源:一个用户多笔订单,一笔订单一个用户(游客单除外)。

一个容易被忽略的细节:orders.user_id 允许为 NULL,本库有 565 行这样的订单。 它们不是“数据缺失”,而是游客下单——交易真实发生,只是没有登录账号。 把 NULL 当成“脏数据”删掉,你会直接少掉 565 笔订单。

这个库没有开启外键强制(SQLite 默认关闭),关系是否正确只能自己验证。第 13 章会练这件事。

1

主键是行的身份证,外键是表与表之间的引用

标准 SQL 要求主键唯一、稳定、不为空;外键的值必须能在另一张表的主键里找到(或者为 NULL,表示“没有关联对象”)。

如果不这样会怎样:没有主键,两笔订单号相同的记录你分不清谁是谁,退款退给哪一笔都说不清; 没有外键,订单表里可能出现 U999999 这样在用户表里查不到的值,按用户分层时这些订单会凭空消失。

10.1.2事实表与维度表:谁记“发生了什么”,谁记“是什么”

事实表记录“发生了什么”:订单、商品行、点击。它行数多、随时间增长、有可求和的度量, 用外键指向维度。维度表记录“是什么”:用户、商品。它行数少、相对稳定,用来分组和筛选。

表类型行数一行代表在分析里的角色
orders事实20,000一笔订单算 GMV、客单价、订单数
order_items事实39,765订单里的一个商品行算销量、品类结构
events事实16,932一次用户行为算日活、漏斗、留存
users维度2,000一个注册用户按城市、渠道、会员等级分组
products维度120一个 SKU按品类、品牌分组
图 10.1.2 三张事实表 + 两张维度表,指标从事实表算,分组从维度表取

指标只能从事实表算。GMV 是 15,615 笔已支付订单的 pay_amount 之和, 等于 157,363,380.14,不可能是 2,000 行用户数据加出来的。 想按城市拆分 GMV,才需要 orders.city 这样的维度信息。

错误:把维度表当事实表

SELECT SUM(price) FROM products;
结果 305,579.39。
这是 120 个 SKU 的标价之和,不是销售额,也不是库存价值。它没有任何业务含义。

正确:从事实表算指标

SELECT SUM(pay_amount) FROM orders WHERE status = '已支付';
结果 157,363,380.14。
“已经收到的钱”,每个数字都能和财务对上。

10.1.3行粒度:全章最重要的一件事

粒度(grain)就是“一行代表什么”。它是 SQL 篇所有错误的源头。 同一份业务,可以有三种完全不同的粒度:

39,765 ÷ 20,000 = 1.99,平均每笔订单接近 2 个商品行。具体分布是:1 行的订单 6,789 笔, 2 行的 6,657 笔,3 行的 6,554 笔——正好 20,000 笔。所以“订单数”和“商品行数”是两个完全不同的指标, 谁也不能替代谁。

orders:一笔订单 = 1 行
order_idamountdiscountpay_amount
O2301010423023,167.23200.6722,966.56

订单金额只出现一次。按订单统计,不会有任何重复。

order_items:同一笔订单 = 3 行
item_idproduct_idqtyamount
8445P0120312,900.63
8446P002327,459.96
8447P001012,806.64

三行金额相加 = 23,167.23,和订单金额完全一致。

图 10.1.3 同一个事实的两种粒度:订单表 1 行,商品行表 3 行,金额合计相同

把两张表按 order_id 连起来再对订单金额求和,订单 O23010104230 的 23,167.23 会跟着三条商品行复制三份,变成 69,501.69。全表更夸张:已支付 GMV 正确答案 157,363,380.14, 直接 JOIN 后求和得到 365,790,418.68,虚增 208,427,038.54。

数据库没算错,是你问错了。你让它“按商品行展开,再把每行的订单金额加起来”,它忠实执行了。 粒度一旦变细,订单级指标就会重复计数。第 13 章专讲这个问题;现在只需记住: 写 FROM 之前,先说清结果里一行代表什么。
2

先定粒度,再写 SQL

粒度是一句单数的话:“一行是一笔订单”“一行是一个商品行”“一行是一个用户在某一天的一次行为”。 这句话决定了你用哪张表、要不要聚合、JOIN 之后会不会翻倍。

如果不这样会怎样:把 order_items 的 39,765 行当成“39,765 笔订单”,订单数直接虚高 98.8%; 把 orders 的 20,000 行当成“卖出了 20,000 件商品”,又少算了将近一半的商品行。 两个错误都不会报错,只会在汇报时被问住。

10.1.4SQL 的执行顺序:你写的顺序,不是它跑的次序

你从 SELECT 开始写,因为想先说要什么;数据库却不是这么读的。执行顺序是:

FROM
→
WHERE
→
GROUP BY
→
HAVING
SELECT
→
ORDER BY
→
LIMIT
图 10.1.4 执行顺序:FROM 先确定行集合,SELECT 直到第五步才发生

逐步拆开看:

  1. FROM:找到表,把所有候选行拿出来。这一步决定了“行宇宙”有多大。
  2. WHERE:逐行判断条件,只留下结果为 TRUE 的行。它不知道 SELECT 要输出什么。
  3. GROUP BY:把剩下的行压成组。第 12 章展开。
  4. HAVING:过滤组,而不是过滤行。
  5. SELECT:这时才计算你要的列、表达式和别名。
  6. ORDER BY:排序。别名已经存在,所以这里可以用别名。
  7. LIMIT:最后截断,只留前 N 行。

这就是 WHERE 不能用 SELECT 别名的原因:WHERE 执行时,别名还没被创建。 你写 pay_amount AS amt 又在 WHERE amt > 40000 里引用它,MySQL 会报 Unknown column 'amt' in 'where clause'。SQLite 允许这种写法, 但那是方言扩展,换库就崩。教材按标准写:WHERE 里用原始列名。

聚合函数也不能出现在 WHERE 里:WHERE SUM(pay_amount) > 1000000 会报 misuse of aggregate: SUM()。WHERE 逐行执行,SUM 要等所有行看完才有结果; 过滤聚合结果用 HAVING。

看一个顺序会直接改变业务结论的例子:

SELECT order_id, pay_amount
FROM orders
WHERE status = '已支付'
ORDER BY pay_amount DESC
LIMIT 3;
order_idpay_amount
O2402101871843,186.27
O2301160314343,168.96
O2303051841142,253.75

但如果你先排序、后过滤(也就是把 WHERE 去掉),榜单前三名会变成 44,910.59、43,644.36、39,698.07——三笔全是已取消订单。 同一张表,同一列,只因为有没有先定口径,一份是真实成交榜,一份是取消单排行榜。

3

数据库从 FROM 开始执行,SELECT 排在第五位

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT。 记住这条流水线,你就能解释大部分“为什么这里不能写那个”的问题。

如果不这样会怎样:你以为 WHERE 在 SELECT 之后执行,就会写出引用别名的 SQL,在 SQLite 上碰巧能跑, 上线到 MySQL 立刻报错;你以为 LIMIT 会先截断再排序,就会得到一份“随机前三名”的榜单。

10.1.5NULL 的三值逻辑:未知不是零,也不是空

NULL 不是 0,也不是空字符串:0 是“知道它是零”,'' 是“知道它是空文本”, NULL 是“不知道”。

所以比较结果有第三种:UNKNOWN。SQL 是三值逻辑,WHERE 只保留 TRUE, UNKNOWN 和 FALSE 一样被丢掉。

ABA = BA AND BA OR B
TRUETRUETRUETRUETRUE
TRUEFALSEFALSEFALSETRUE
TRUENULLUNKNOWNUNKNOWNTRUE
FALSENULLUNKNOWNFALSEUNKNOWN
NULLNULLUNKNOWNUNKNOWNUNKNOWN
图 10.1.5 NULL 参与运算时,结果可能是 UNKNOWN,而不是 FALSE

三条能立刻验证的结论:

SELECT NULL = NULL, NULL IS NULL, 1 = NULL;
NULL = NULLNULL IS NULL1 = NULL
NULL(空)1(真)NULL(空)

判断空值只能用 IS NULL / IS NOT NULL;= NULL 永远得不到 TRUE。

聚合函数也会跳过 NULL。看下面这条查询,它一句话暴露了 565 笔游客订单:

SELECT COUNT(*) AS 总行数,
       COUNT(user_id) AS 有用户的订单
FROM orders;
总行数有用户的订单差值
20,00019,435565

COUNT(*) 数行,COUNT(列) 只数这一列非 NULL 的行。 同样的道理,SUM、AVG 也会跳过 NULL。如果一组里全是 NULL, SUM 返回的是 NULL,不是 0——这两个结果在报表里长得完全不同。

最阴的一个坑是 NOT IN。先看这条查询:

SELECT COUNT(*)
FROM orders
WHERE user_id NOT IN (
  SELECT user_id FROM orders WHERE user_id IS NULL
);
直觉答案实际结果
19,4350

子查询返回的是一个全是 NULL 的集合。x NOT IN (NULL) 等价于 x <> NULL,结果是 UNKNOWN;UNKNOWN 不是 TRUE,所以一行都不留下。 NOT IN 的子查询里只要出现一个 NULL,整个条件就废了。解决办法有两个:子查询里加 WHERE user_id IS NOT NULL,或者改用 NOT EXISTS。

4

NULL 表示“未知”,任何和它比较的结果都是 UNKNOWN

判断空值用 IS NULL;统计人数用 COUNT(列) 前先想清楚“NULL 算不算”; NOT IN 前先检查子查询有没有 NULL。

如果不这样会怎样:用 COUNT(user_id) 当“下单用户数”,565 笔游客单消失, 19,435 笔订单还会被当成 19,435 个人;用 NOT IN 做黑名单,子查询里有一个 NULL, 结果直接变成 0 行。

10.1.6隐式类型转换:不报错,不代表算对了

SQLite 只有五种存储类型:NULL、INTEGER、REAL、TEXT、BLOB。本库 pay_amount 是 REAL, order_date 是 TEXT——日期不是日期类型,是字符串,格式固定为 YYYY-MM-DD HH:MM:SS。

ISO 8601 字符串按字典序排序,恰好等于按时间排序,因为年月日时分秒都是定宽补零的。 下面两种写法都能得到 48 行:

-- 写法一:半开区间,推荐
SELECT COUNT(*) FROM orders
WHERE order_date >= '2024-06-01' AND order_date < '2024-06-02';

-- 写法二:前缀匹配,也可以
SELECT COUNT(*) FROM orders
WHERE order_date LIKE '2024-06-01%';
写法结果结论
order_date >= '2024-06-01' AND < '2024-06-02'48 行正确
order_date LIKE '2024-06-01%'48 行正确
order_date = '2024-06-01'0 行错:库里还带着时分秒
order_date LIKE '2024-6-1%'0 行错:月份没补零

第三行是最常见的“日期查不到数”事故。你脑子里是“6 月 1 日”,库里是 2024-06-01 00:19:28,字符串完全相等才怪。MySQL 的 DATE 类型会先转日期再比, 所以同一句 SQL 在 MySQL 能跑、在 SQLite 返回 0 行。不同数据库的转换规则不一样,而且都不报错。

字符串和数字混用也一样。本库 user_id 是 TEXT,写成数字 1 去比:

SELECT COUNT(*) FROM orders WHERE user_id = 1;        -- 0 行
SELECT COUNT(*) FROM orders WHERE user_id = 'U000001'; -- 10 行

SQLite 会按列的 TEXT 类型把 1 转成字符串 '1',再和 'U000001' 比较,自然不相等。 正确做法是:字符串加引号,日期用标准格式,不要指望数据库替你猜。

5

类型不对时,数据库可能转换、可能返回空,但通常不会报错

日期用 YYYY-MM-DD 标准格式,比较用半开区间;字符串常量加单引号; 不把数字和文本混着比。这三条能消灭大部分“有数据却查 0 行”的问题。

如果不这样会怎样:WHERE order_date = '2024-06-01' 返回 0 行, 你以为是“昨天没订单”,实际是 48 笔订单被一句等号藏起来了。日报直接开天窗。

10.2五分钟判断一张表的粒度

面试常给你一张陌生的表,问“一行是什么”。能唯一标识一行的列叫候选键,判断粒度就从它开始。四步,五分钟。

步骤看什么判断依据
1. 看名字表名、列名、注释order_items 带复数、带 items,大概率是“订单里的商品行”;users 大概率是一行一个用户。
2. 看主键主键是一列还是多列主键是 item_id,只能说明每行有编号;业务粒度还要看订单号和商品号。
3. 看重复COUNT(*) 与 COUNT(DISTINCT 候选键)两者相等,候选键才可能是粒度;不等,说明一行不是那个东西。
4. 看样例ORDER BY ... LIMIT 3,再问业务用一句单数的话复述“一行是什么”,说不出来就问,别猜。

把本库五张表按这四步跑一遍,结果如下:

表候选键COUNT(*)COUNT(DISTINCT)结论
usersuser_id2,0002,000一行一个用户
productsproduct_id120120一行一个 SKU
ordersorder_id20,00020,000一行一笔订单
order_itemsitem_id39,76539,765一行一个商品行
order_items(order_id, product_id)39,76539,542不是键:223 对重复
eventsevent_id16,93216,932一行一次行为

最后两行值得停一下。item_id 唯一只证明“自增编号唯一”,不证明“一笔订单里一个商品只出现一次”。 实际有 223 对 (order_id, product_id) 重复。判断粒度不能只看主键, 还要看业务上的“同一个东西”有没有重复。

一句话验收:用单数说“这张表一行是 ______”。话里出现“和”“以及”“分别”,说明还没找到粒度。

10.3实验:第一次打开数据库

理论讲完了,动手确认一遍。本章的实验全部是只读查询,不会改动任何数据。

10.3.1实验环境

项目要求
数据库qingning.db(SQLite 3,约 12 MB)
工具DB Browser for SQLite(图形界面,推荐)或 sqlite3 命令行
数据范围orders 2023-01-01 至 2024-06-30;events 只覆盖 2024 年 6 月
纪律只读。不要用 Excel 打开 .db 文件。

下载 qingning.db

已完成 0 / 6 预计 25 分钟
01

打开数据库,先看有哪几张表

3 分钟 · 对应规则 1
做什么
确认这个库里到底有哪些表,别急着写 SELECT。
为什么
FROM 是第一步。表名写错,后面全错;不知道有哪些表,就不知道从哪出发。
怎么做
  1. 打开 DB Browser for SQLite → 打开数据库 → 选择 qingning.db
  2. 切到 执行 SQL 标签页,粘贴下面的语句,按 F5
SELECT name
FROM sqlite_master
WHERE type = 'table' AND name NOT LIKE 'sqlite_%'
ORDER BY name;
看到什么
5 行:events、order_items、orders、products、users。和 10.2 的表一致。
卡住了 / 出错了

sqlite_sequence 是 SQLite 的自增计数器,不是业务表。

只有一张表,多半是打开了空库,检查文件路径。

02

数行数:五张表的规模

4 分钟 · 对应规则 2
做什么
一次查清五张表各有多少行,建立“规模感”。
为什么
行数是判断粒度最直接的线索。order_items 比 orders 多出将近一倍,说明一单不止一个商品行。
怎么做
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;
看到什么
五张表分别是 2,000 / 120 / 20,000 / 39,765 / 16,932 行。
卡住了 / 出错了

UNION ALL 要求每段列数一致;少写一个 COUNT(*) 就会报列数不匹配。

03

看三行真实订单

4 分钟 · 对应规则 1、2
做什么
抽三行订单,看清一行里到底装了什么。
为什么
只看列名容易想当然。看到真实的一行,你才会发现 order_date 带时分秒、pay_amount 是实付、city 是收货城市。
怎么做
SELECT order_id, user_id, order_date, status, pay_amount, city
FROM orders
ORDER BY order_date
LIMIT 3;
看到什么
order_iduser_idorder_datestatuspay_amountcity
O23010116593U0006882023-01-01 00:06:40已支付5,988.49杭州
O23010103782U0019132023-01-01 02:35:19已支付2,015.47深圳
O23010116998U0014742023-01-01 05:08:01已支付4,869.60广州
一行 = 一笔订单;order_date 精确到秒——这就是为什么只写日期会查不到。
为什么一定要加 ORDER BY

不加 ORDER BY,LIMIT 3 返回哪三行由执行计划决定,不保证稳定。看样例也要可复现。

04

看表结构,找到主键

4 分钟 · 对应规则 1
做什么
确认每一列的类型,以及哪一列是主键。
为什么
主键决定“一行能不能被唯一定位”;类型决定“日期怎么比、金额能不能加”。这两件事都在表结构里。
怎么做
  1. 在 DB Browser 里点 数据库结构,展开 orders
  2. 或者执行:
PRAGMA table_info(orders);
看到什么
字段类型非空主键
order_idTEXT否是
user_idTEXT否—
order_dateTEXT是—
statusTEXT是—
channelTEXT是—
amountREAL是—
discountREAL是—
pay_amountREAL是—
cityTEXT否—
9 列,主键是 order_id;user_id 和 city 允许为空。
SQLite 的一个历史坑

标准 SQL 要求主键非空,但 SQLite 允许 TEXT 主键存 NULL(INTEGER PRIMARY KEY 除外)。生产建表请显式写 NOT NULL。

05

验证粒度:一笔订单对三个商品行

5 分钟 · 对应规则 2
做什么
用一笔真实订单,确认订单表和商品行表是 1 对 N。
为什么
规则 2 说“先定粒度”。最有力的证据是自己数出来:一笔订单在两张表里各几行。
怎么做
-- 订单表:查这一笔
SELECT order_id, amount, discount, pay_amount
FROM orders
WHERE order_id = 'O23010104230';

-- 商品行表:查同一笔
SELECT item_id, product_id, qty, unit_price, amount
FROM order_items
WHERE order_id = 'O23010104230';
看到什么
order_idamountdiscountpay_amount
O2301010423023,167.23200.6722,966.56
item_idproduct_idqtyunit_priceamount
8445P012034,300.2112,900.63
8446P002323,729.987,459.96
8447P001012,806.642,806.64
订单表 1 行,商品行表 3 行;三行金额合计 23,167.23,与订单金额一致。
卡住了 / 出错了

商品行是 0 行,多半是订单号抄错。订单号是文本,必须加引号,否则 SQLite 会当成列名并报 no such column。

06

五道判断:检验你是不是真的懂了

5 分钟 · 对应规则 1–5
做什么
不查资料,凭理解判断下面五句话的对错。
为什么
这五句正好对应五条规则。能全说对,10.1 就过关了。
怎么做
  1. orders 有 20,000 行,所以这家店一共卖出了 20,000 件商品。
  2. order_items 有 39,765 行,比 orders 多,说明一笔订单可以包含多个商品行。
  3. 在 orders 上,COUNT(*) 和 COUNT(user_id) 都返回 20,000。
  4. WHERE 在 SELECT 之前执行,所以 WHERE 里不能直接使用 SELECT 起的别名。
  5. NULL = NULL 的结果是 TRUE,所以可以用 = 判断空值。
看到什么
五句里只有第二句和第四句是对的。逐句对照下面的解析。
五道判断的答案与依据

1. 错。一行是一笔订单,不是一件商品。商品行有 39,765 行,件数还要把 qty 加起来,比 39,765 更多。

2. 对。39,765 ÷ 20,000 = 1.99,平均一单两个商品行。这是 1 对 N 的直接证据。

3. 错。COUNT(*) 是 20,000,COUNT(user_id) 是 19,435。差的 565 行是游客订单,user_id 为 NULL。

4. 对。执行顺序是 FROM → WHERE → … → SELECT。别名在 SELECT 阶段才创建,WHERE 阶段还不存在。

5. 错。NULL = NULL 返回 NULL(UNKNOWN),不是 TRUE。判断空值必须用 IS NULL。

10.4结果验证

先核对三个数字,再做八道选择题。数字对了,说明你会查;题对了,说明你懂为什么。

10.4.1三个数字

用 SELECT COUNT(*) FROM orders;
用 SELECT COUNT(*) FROM order_items;
用 WHERE user_id IS NULL

10.4.2八道选择题

Q1主键的作用是什么?
解析:主键的第一职责是身份,不是性能。有了它,退款、改地址才知道操作哪一行。
Q2下面哪一张是事实表?
解析:order_items 记录“发生了什么”:某笔订单卖了某个商品几件、多少钱。users 和 products 是描述主体的维度表。
Q3order_items 表的一行代表什么?
解析:39,765 行 ÷ 20,000 单 = 1.99 行/单。一行是“订单 × 商品”的组合,不是整笔订单。
Q4下面哪个是 SQL 的实际执行顺序?
解析:FROM 先确定行集合,WHERE 逐行过滤,SELECT 第五步才计算输出列,ORDER BY 和 LIMIT 在最后。
Q5为什么 WHERE 里不能使用 SELECT 起的别名?
解析:执行顺序决定可见性。别名在 SELECT 阶段才创建,WHERE 阶段它还不存在。SQLite 允许这种写法是方言扩展,MySQL 会报 Unknown column。
Q6SELECT COUNT(*), COUNT(user_id) FROM orders; 的结果是?
解析:COUNT(*) 数行,COUNT(列) 跳过 NULL。565 笔游客订单的 user_id 是 NULL,所以少了 565。
Q7子查询 SELECT user_id FROM orders WHERE user_id IS NULL 作为 NOT IN 的参数,外层查询返回多少行?
解析:NOT IN 的集合里有 NULL,整个比较变成 UNKNOWN;WHERE 只保留 TRUE,所以一行都不返回。改用 NOT EXISTS,或在子查询里排除 NULL。
Q8WHERE order_date = '2024-06-01' 返回 0 行,最可能的原因是什么?
解析:库里存的是 2024-06-01 00:19:28,和 2024-06-01 不相等。用 LIKE '2024-06-01%' 或半开区间 [2024-06-01, 2024-06-02)。实际有 48 笔。
得分:0 / 8

10.5常见问题排查

按“现象 → 原因 → 解决”整理。错误大多不是语法错,是理解错。

现象原因解决
no such table: order 表名写错,或少写了 s 先跑 10.3 步骤 1 的查询,确认真实表名;本库是 orders
no such column: amt 在 WHERE 里引用了 SELECT 的别名 WHERE 里写原始列名;别名留给 ORDER BY
查询返回 0 行,但表明明有数据 日期用了等值比较,或字符串没加引号 用 LIKE '2024-06-01%' 或半开区间;文本值加单引号
COUNT 比预期少 COUNT(列) 跳过了 NULL(规则 4) 想数行用 COUNT(*);想数非空值才用 COUNT(列)
Join 之后金额翻倍 一对多粒度被展开(规则 2) 先在子查询里把明细聚合成订单级,再 JOIN;或直接算行级金额
NOT IN 返回空结果 子查询结果里含 NULL(规则 4) 子查询加 IS NOT NULL,或改用 NOT EXISTS
UNION 报列数不一致 各段 SELECT 的列数不同 每段都写同样的列数和顺序

10.6本章小结与练习

  1. 表靠键连起来。主键唯一标识一行,外键指向另一张表的主键;外键可以为 NULL,那是“没有关联对象”,不是脏数据。
  2. 事实表记发生,维度表记属性。指标从事实表算(orders / order_items / events),分组从维度表取(users / products)。
  3. 粒度是一句单数的话。orders 一行一笔订单,order_items 一行一个商品行;39,765 ÷ 20,000 = 1.99。粒度变细,订单级指标就会重复计数。
  4. 执行顺序是 FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT。WHERE 在 SELECT 之前,所以看不见别名。
  5. NULL 是未知,不是 0。比较用 IS NULL,COUNT(列) 跳过 NULL,NOT IN 遇 NULL 返回空。
  6. 类型不对不会报错,只会悄悄返回错的结果。日期用半开区间,字符串加引号,不依赖隐式转换。

10.6.1练习

  1. 用一条 SQL 同时返回五张表的行数,并按行数从多到少排序。
  2. orders 表里 user_id 为 NULL 的有多少行?占全部订单的百分之几(保留两位小数)?
  3. 证明订单 O23010104230 的 amount 等于它三条商品行 amount 之和,并说明这两个数字为什么必须相等。
  4. 解释 SELECT COUNT(*) FROM orders WHERE user_id <> 'U000001'; 为什么返回 19,425,而不是 19,990。
参考答案

1. 用 UNION ALL 把五个 COUNT(*) 拼起来,再 ORDER BY 行数 DESC。结果:order_items 39,765 → orders 20,000 → events 16,932 → users 2,000 → products 120。

2. 565 行;565 ÷ 20,000 = 2.83%。

3. 三条商品行分别是 12,900.63、7,459.96、2,806.64,合计 23,167.23,正好等于订单的 amount。因为订单金额本来就是商品行金额的汇总,pay_amount 再减去优惠 200.67 得 22,966.56。

4. 20,000 行里,10 行的 user_id 是 U000001,565 行是 NULL。NULL 与任何值比较都是 UNKNOWN,不会被 <> 留下,所以 20,000 − 10 − 565 = 19,425。要包含游客单,得写 WHERE user_id IS NULL OR user_id <> 'U000001'。

下一章预告:地基打完,第 11 章开始写真正的查询。我们会用三条 SQL 做出运营要的“6 月 1 日日报”: 日活 280、下单人数 37、GMV 395,466.51。每个数字都会被追问口径,而答案就藏在本章的五条规则里。
数据分析师训练营 · 第十章
SQLite 3 · 所有查询均在 qingning.db 上跑通