模块 1 · 读懂一张表,不被它骗
第 1 节,共 6 节 · 7 分钟 · 简体中文 · 只读
表的粒度就是这张表里一行代表什么,所以写查询之前先把它说出来:计数计的就是这个东西,筛选保留或去掉的是这个粒度上的整行。
写查询之前,先把这句话补完整:“这张表的一行是一个……”。“一个”后面填的内容,就是这张表的粒度,它决定了每次计数、求和或筛选的含义。COUNT(*) 数的既不是订单,也不是客户,而是行,所以它返回的数取决于一行代表什么。
这门课用同一家小网店做例子。店里卖 Kettle(水壶)、Mug(马克杯)、Tea tin(茶叶罐)和 Filter(滤网),价格都是整数元,这是示例的假设;SPRING 优惠券可以免运费。三张表描述这家店,每张表的粒度各不相同。
找粒度的办法:先找一列永远不会重复的,再把真实的一行大声说一遍:“这一行是客户 2 在 3 月 9 日下的那个订单。”要是这句话里得加上“另外还有”,就说明这张表混进了两种粒度。
当表的粒度比你要数的东西更细,计数就会出错。订单明细(order_lines)比订单更细,因为一个订单里的每种商品各占一行。拿明细表去回答订单的问题,光数行数是不够的。
这门课里的每条查询都能原样运行。查询开头的 WITH 部分用手写的值搭出几张小表,所以你只需要 SQLite 或 PostgreSQL;换成别的数据库,搭这几张表时可能要做些小改动。每条查询下面的结果是在 SQLite 里运行得到的,PostgreSQL 得到的值相同,只是小数可能显示得更多。
一条查询读起来像一句话。SELECT 列出你要取回的列,FROM 写明这些列来自哪张表,WHERE 只保留通过某个条件的行。UNION ALL 会在好几条查询里出现,它把上面那个 SELECT 的行和下面那个 SELECT 的行叠成一个结果。
检查这个材料
sql语言: sql4 条注释WITH order_lines(line_id, order_id, product, quantity, unit_price) AS ( VALUES ( 1, 101, 'Mug', 2, 8), ( 2, 102, 'Kettle', 1, 30), ( 3, 102, 'Tea tin', 2, 12), ( 4, 103, 'Mug', 1, 8), ( 5, 103, 'Tea tin', 1, 12), ( 6, 103, 'Filter', 3, 4), ( 7, 104, 'Kettle', 1, 30), ( 8, 105, 'Tea tin', 3, 12), ( 9, 106, 'Mug', 4, 8), (10, 106, 'Filter', 2, 4), (11, 107, 'Tea tin', 1, 12), (12, 107, 'Mug', 1, 8), (13, 108, 'Kettle', 1, 30), (14, 108, 'Filter', 1, 4), (15, 109, 'Tea tin', 2, 12))SELECT COUNT(*) AS lines_under_10, COUNT(DISTINCT order_id) AS ordersFROM order_linesWHERE unit_price < 10;lines_under_10 orders -------------- ------ 7 5
订单 103 和 106 各有两种单价低于 10 元的商品,一个 Mug 和一个 Filter。所以 7 条明细只属于 5 个订单。如果把 7 报成“含低价商品的订单数”,就多报了 2。
材料包含 23 行和 4 条注释。
WHERE 保留条件为真的行,丢掉其余的行。业务问题不会明说该保留哪些行,所以这个定义要靠筛选条件写下来。“3 月的订单”里藏着两个决定:3 月从哪天开始、到哪天结束,以及哪些状态算数。
先说日期。时间戳 2025-03-31 18:20 晚于 2025-03-31,因为后者指的是那天零点。所以 BETWEEN '2025-03-01' AND '2025-03-31' 会悄悄丢掉 31 日零点之后下的所有订单。稳妥的写法是:起点包含在内,终点不包含在内,也就是从 3 月 1 日起,到 4 月 1 日之前。
再说状态。订单一旦有取消或退款,“我们接到的订单”和“眼下仍然成立的订单”就是两批不同的订单。选一种,并把它写在数字旁边。
最后去看具体的行。把筛选条件每个边界两侧的订单列出来,这里就是 3 月的最后几单和 4 月的头几单,再读它们的时间戳。计数看不到刚好掉在界外的那个订单,行能看到。
想一想
选一张你工作中用到的表或导出文件,把“一行是一个……”补完整。对着这句话大声读它的十行:有没有哪一行属于另一种粒度,比如合计行或重复出现的表头?
检查这个材料
sql语言: sql5 条注释WITH orders(order_id, customer_id, order_ts, status, coupon, shipping_fee) AS ( VALUES (101, 1, '2025-02-28 21:15', 'paid', NULL, 5), (102, 1, '2025-03-02 10:05', 'paid', 'SPRING', 0), (103, 2, '2025-03-09 14:30', 'paid', NULL, 5), (104, 4, '2025-03-12 11:00', 'refunded', NULL, 5), (105, 3, '2025-03-15 09:40', 'cancelled', NULL, 5), (106, 3, '2025-03-21 16:10', 'paid', 'SPRING', 0), (107, 2, '2025-03-28 12:00', 'paid', NULL, 5), (108, 4, '2025-03-31 18:20', 'paid', NULL, 5), (109, 1, '2025-04-01 08:00', 'paid', NULL, 5))SELECT 'BETWEEN 1 Mar and 31 Mar' AS filter, COUNT(*) AS ordersFROM ordersWHERE order_ts BETWEEN '2025-03-01' AND '2025-03-31'UNION ALLSELECT 'from 1 Mar, before 1 Apr', COUNT(*)FROM ordersWHERE order_ts >= '2025-03-01' AND order_ts < '2025-04-01'UNION ALLSELECT 'same range, status paid', COUNT(*)FROM ordersWHERE order_ts >= '2025-03-01' AND order_ts < '2025-04-01' AND status = 'paid';filter orders ------------------------ ------ BETWEEN 1 Mar and 31 Mar 6 from 1 Mar, before 1 Apr 7 same range, status paid 5
第一个计数是故意写错的:它漏掉了 3 月 31 日 18:20 下的订单 108,返回 6。第二个保留了 3 月的全部 7 个订单,包括 104(refunded,已退款)和 105(cancelled,已取消)。第三个保留 5 个:订单 102、103、106、107 和 108。后两个按各自的定义都是对的。
材料包含 25 行和 5 条注释。