第 6 章我们清洗过一张 480 行的订单表。现在换成一个 12 MB 的数据库:5 张表、78,817 行。
第一句 SQL 可能只有一行,但背后要先回答三个问题:
从哪张表取数、一行代表什么、哪些行算数。
这三个问题答错,语法再熟也会算出错的数。
本章不讲技巧,只讲地基:表与键、事实与维度、行粒度、执行顺序、NULL、类型转换。
它们是后面七章所有 SQL 的共同前提。
很多人学 SQL 是背语法:SELECT 怎么写、WHERE 怎么写。但真实工作里 80% 的错误不是语法错, 而是对表的理解错了。这一节用六块地基,讲清“数据库眼里的数据”。
数据库是一组通过键互相引用的表。列是字段,规定能存什么;行是一条记录,代表一个具体对象。
但每一行都必须能被一个东西唯一地指出来,这个东西叫主键(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),
它把分散的表连成一张网:
箭头上的 1 ─ N 是后面所有问题的根源:一个用户多笔订单,一笔订单一个用户(游客单除外)。
orders.user_id 允许为 NULL,本库有 565 行这样的订单。
它们不是“数据缺失”,而是游客下单——交易真实发生,只是没有登录账号。
把 NULL 当成“脏数据”删掉,你会直接少掉 565 笔订单。
这个库没有开启外键强制(SQLite 默认关闭),关系是否正确只能自己验证。第 13 章会练这件事。
标准 SQL 要求主键唯一、稳定、不为空;外键的值必须能在另一张表的主键里找到(或者为 NULL,表示“没有关联对象”)。
如果不这样会怎样:没有主键,两笔订单号相同的记录你分不清谁是谁,退款退给哪一笔都说不清;
没有外键,订单表里可能出现 U999999 这样在用户表里查不到的值,按用户分层时这些订单会凭空消失。
事实表记录“发生了什么”:订单、商品行、点击。它行数多、随时间增长、有可求和的度量, 用外键指向维度。维度表记录“是什么”:用户、商品。它行数少、相对稳定,用来分组和筛选。
| 表 | 类型 | 行数 | 一行代表 | 在分析里的角色 |
|---|---|---|---|---|
| orders | 事实 | 20,000 | 一笔订单 | 算 GMV、客单价、订单数 |
| order_items | 事实 | 39,765 | 订单里的一个商品行 | 算销量、品类结构 |
| events | 事实 | 16,932 | 一次用户行为 | 算日活、漏斗、留存 |
| users | 维度 | 2,000 | 一个注册用户 | 按城市、渠道、会员等级分组 |
| products | 维度 | 120 | 一个 SKU | 按品类、品牌分组 |
指标只能从事实表算。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。
“已经收到的钱”,每个数字都能和财务对上。
粒度(grain)就是“一行代表什么”。它是 SQL 篇所有错误的源头。 同一份业务,可以有三种完全不同的粒度:
orders 一行 = 一笔订单,共 20,000 行。order_items 一行 = 一笔订单里的一个商品,共 39,765 行。events 一行 = 一次点击/浏览/加购,共 16,932 行。39,765 ÷ 20,000 = 1.99,平均每笔订单接近 2 个商品行。具体分布是:1 行的订单 6,789 笔, 2 行的 6,657 笔,3 行的 6,554 笔——正好 20,000 笔。所以“订单数”和“商品行数”是两个完全不同的指标, 谁也不能替代谁。
| order_id | amount | discount | pay_amount |
|---|---|---|---|
| O23010104230 | 23,167.23 | 200.67 | 22,966.56 |
订单金额只出现一次。按订单统计,不会有任何重复。
| item_id | product_id | qty | amount |
|---|---|---|---|
| 8445 | P0120 | 3 | 12,900.63 |
| 8446 | P0023 | 2 | 7,459.96 |
| 8447 | P0010 | 1 | 2,806.64 |
三行金额相加 = 23,167.23,和订单金额完全一致。
把两张表按 order_id 连起来再对订单金额求和,订单 O23010104230 的 23,167.23
会跟着三条商品行复制三份,变成 69,501.69。全表更夸张:已支付 GMV 正确答案 157,363,380.14,
直接 JOIN 后求和得到 365,790,418.68,虚增 208,427,038.54。
粒度是一句单数的话:“一行是一笔订单”“一行是一个商品行”“一行是一个用户在某一天的一次行为”。 这句话决定了你用哪张表、要不要聚合、JOIN 之后会不会翻倍。
如果不这样会怎样:把 order_items 的 39,765 行当成“39,765 笔订单”,订单数直接虚高 98.8%;
把 orders 的 20,000 行当成“卖出了 20,000 件商品”,又少算了将近一半的商品行。
两个错误都不会报错,只会在汇报时被问住。
你从 SELECT 开始写,因为想先说要什么;数据库却不是这么读的。执行顺序是:
逐步拆开看:
这就是 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_id | pay_amount |
|---|---|
O24021018718 | 43,186.27 |
O23011603143 | 43,168.96 |
O23030518411 | 42,253.75 |
但如果你先排序、后过滤(也就是把 WHERE 去掉),榜单前三名会变成 44,910.59、43,644.36、39,698.07——三笔全是已取消订单。 同一张表,同一列,只因为有没有先定口径,一份是真实成交榜,一份是取消单排行榜。
FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT。 记住这条流水线,你就能解释大部分“为什么这里不能写那个”的问题。
如果不这样会怎样:你以为 WHERE 在 SELECT 之后执行,就会写出引用别名的 SQL,在 SQLite 上碰巧能跑, 上线到 MySQL 立刻报错;你以为 LIMIT 会先截断再排序,就会得到一份“随机前三名”的榜单。
NULL 不是 0,也不是空字符串:0 是“知道它是零”,'' 是“知道它是空文本”,
NULL 是“不知道”。
所以比较结果有第三种:UNKNOWN。SQL 是三值逻辑,WHERE 只保留 TRUE, UNKNOWN 和 FALSE 一样被丢掉。
| A | B | A = B | A AND B | A OR B |
|---|---|---|---|---|
| TRUE | TRUE | TRUE | TRUE | TRUE |
| TRUE | FALSE | FALSE | FALSE | TRUE |
| TRUE | NULL | UNKNOWN | UNKNOWN | TRUE |
| FALSE | NULL | UNKNOWN | FALSE | UNKNOWN |
| NULL | NULL | UNKNOWN | UNKNOWN | UNKNOWN |
三条能立刻验证的结论:
SELECT NULL = NULL, NULL IS NULL, 1 = NULL;
| NULL = NULL | NULL IS NULL | 1 = NULL |
|---|---|---|
| NULL(空) | 1(真) | NULL(空) |
判断空值只能用 IS NULL / IS NOT NULL;= NULL 永远得不到 TRUE。
聚合函数也会跳过 NULL。看下面这条查询,它一句话暴露了 565 笔游客订单:
SELECT COUNT(*) AS 总行数,
COUNT(user_id) AS 有用户的订单
FROM orders;
| 总行数 | 有用户的订单 | 差值 |
|---|---|---|
| 20,000 | 19,435 | 565 |
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,435 | 0 |
子查询返回的是一个全是 NULL 的集合。x NOT IN (NULL) 等价于
x <> NULL,结果是 UNKNOWN;UNKNOWN 不是 TRUE,所以一行都不留下。
NOT IN 的子查询里只要出现一个 NULL,整个条件就废了。解决办法有两个:子查询里加
WHERE user_id IS NOT NULL,或者改用 NOT EXISTS。
判断空值用 IS NULL;统计人数用 COUNT(列) 前先想清楚“NULL 算不算”;
NOT IN 前先检查子查询有没有 NULL。
如果不这样会怎样:用 COUNT(user_id) 当“下单用户数”,565 笔游客单消失,
19,435 笔订单还会被当成 19,435 个人;用 NOT IN 做黑名单,子查询里有一个 NULL,
结果直接变成 0 行。
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' 比较,自然不相等。
正确做法是:字符串加引号,日期用标准格式,不要指望数据库替你猜。
日期用 YYYY-MM-DD 标准格式,比较用半开区间;字符串常量加单引号;
不把数字和文本混着比。这三条能消灭大部分“有数据却查 0 行”的问题。
如果不这样会怎样:WHERE order_date = '2024-06-01' 返回 0 行,
你以为是“昨天没订单”,实际是 48 笔订单被一句等号藏起来了。日报直接开天窗。
面试常给你一张陌生的表,问“一行是什么”。能唯一标识一行的列叫候选键,判断粒度就从它开始。四步,五分钟。
| 步骤 | 看什么 | 判断依据 |
|---|---|---|
| 1. 看名字 | 表名、列名、注释 | order_items 带复数、带 items,大概率是“订单里的商品行”;users 大概率是一行一个用户。 |
| 2. 看主键 | 主键是一列还是多列 | 主键是 item_id,只能说明每行有编号;业务粒度还要看订单号和商品号。 |
| 3. 看重复 | COUNT(*) 与 COUNT(DISTINCT 候选键) | 两者相等,候选键才可能是粒度;不等,说明一行不是那个东西。 |
| 4. 看样例 | ORDER BY ... LIMIT 3,再问业务 | 用一句单数的话复述“一行是什么”,说不出来就问,别猜。 |
把本库五张表按这四步跑一遍,结果如下:
| 表 | 候选键 | COUNT(*) | COUNT(DISTINCT) | 结论 |
|---|---|---|---|---|
| users | user_id | 2,000 | 2,000 | 一行一个用户 |
| products | product_id | 120 | 120 | 一行一个 SKU |
| orders | order_id | 20,000 | 20,000 | 一行一笔订单 |
| order_items | item_id | 39,765 | 39,765 | 一行一个商品行 |
| order_items | (order_id, product_id) | 39,765 | 39,542 | 不是键:223 对重复 |
| events | event_id | 16,932 | 16,932 | 一行一次行为 |
最后两行值得停一下。item_id 唯一只证明“自增编号唯一”,不证明“一笔订单里一个商品只出现一次”。
实际有 223 对 (order_id, product_id) 重复。判断粒度不能只看主键,
还要看业务上的“同一个东西”有没有重复。
理论讲完了,动手确认一遍。本章的实验全部是只读查询,不会改动任何数据。
| 项目 | 要求 |
|---|---|
| 数据库 | qingning.db(SQLite 3,约 12 MB) |
| 工具 | DB Browser for SQLite(图形界面,推荐)或 sqlite3 命令行 |
| 数据范围 | orders 2023-01-01 至 2024-06-30;events 只覆盖 2024 年 6 月 |
| 纪律 | 只读。不要用 Excel 打开 .db 文件。 |
qingning.dbSELECT name
FROM sqlite_master
WHERE type = 'table' AND name NOT LIKE 'sqlite_%'
ORDER BY name;
sqlite_sequence 是 SQLite 的自增计数器,不是业务表。
只有一张表,多半是打开了空库,检查文件路径。
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;
UNION ALL 要求每段列数一致;少写一个 COUNT(*) 就会报列数不匹配。
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_id | user_id | order_date | status | pay_amount | city |
|---|---|---|---|---|---|
O23010116593 | U000688 | 2023-01-01 00:06:40 | 已支付 | 5,988.49 | 杭州 |
O23010103782 | U001913 | 2023-01-01 02:35:19 | 已支付 | 2,015.47 | 深圳 |
O23010116998 | U001474 | 2023-01-01 05:08:01 | 已支付 | 4,869.60 | 广州 |
不加 ORDER BY,LIMIT 3 返回哪三行由执行计划决定,不保证稳定。看样例也要可复现。
ordersPRAGMA table_info(orders);
| 字段 | 类型 | 非空 | 主键 |
|---|---|---|---|
| order_id | TEXT | 否 | 是 |
| user_id | TEXT | 否 | — |
| order_date | TEXT | 是 | — |
| status | TEXT | 是 | — |
| channel | TEXT | 是 | — |
| amount | REAL | 是 | — |
| discount | REAL | 是 | — |
| pay_amount | REAL | 是 | — |
| city | TEXT | 否 | — |
user_id 和 city 允许为空。标准 SQL 要求主键非空,但 SQLite 允许 TEXT 主键存 NULL(INTEGER PRIMARY KEY 除外)。生产建表请显式写 NOT NULL。
-- 订单表:查这一笔
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_id | amount | discount | pay_amount |
|---|---|---|---|
O23010104230 | 23,167.23 | 200.67 | 22,966.56 |
| item_id | product_id | qty | unit_price | amount |
|---|---|---|---|---|
| 8445 | P0120 | 3 | 4,300.21 | 12,900.63 |
| 8446 | P0023 | 2 | 3,729.98 | 7,459.96 |
| 8447 | P0010 | 1 | 2,806.64 | 2,806.64 |
商品行是 0 行,多半是订单号抄错。订单号是文本,必须加引号,否则 SQLite 会当成列名并报 no such column。
COUNT(*) 和 COUNT(user_id) 都返回 20,000。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。
先核对三个数字,再做八道选择题。数字对了,说明你会查;题对了,说明你懂为什么。
SELECT COUNT(*) FROM orders;SELECT COUNT(*) FROM order_items;WHERE user_id IS NULLSELECT COUNT(*), COUNT(user_id) FROM orders; 的结果是?SELECT user_id FROM orders WHERE user_id IS NULL 作为 NOT IN 的参数,外层查询返回多少行?WHERE order_date = '2024-06-01' 返回 0 行,最可能的原因是什么?LIKE '2024-06-01%' 或半开区间 [2024-06-01, 2024-06-02)。实际有 48 笔。按“现象 → 原因 → 解决”整理。错误大多不是语法错,是理解错。
| 现象 | 原因 | 解决 |
|---|---|---|
| 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 的列数不同 | 每段都写同样的列数和顺序 |
user_id 为 NULL 的有多少行?占全部订单的百分之几(保留两位小数)?O23010104230 的 amount 等于它三条商品行 amount 之和,并说明这两个数字为什么必须相等。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'。