模块 1 · 读懂一张表,不被它骗
第 2 节,共 6 节 · 6 分钟 · 简体中文 · 只读
NULL 表示没有记录到值,所以聚合和比较都会跳过它,除非你用 IS NULL 去问;只有分子和分母描述的是同一组、并且写明了的行,百分比才有意义。
NULL 表示没有可用的值,原因是不知道,或者这一项不适用。它不是 0,也不是空字符串。订单表里,没用优惠券下单的订单,coupon 就是 NULL;而 shipping_fee 为 0 是一个真实的事实:我们没收运费。NULL 是“缺了这个事实”。
多数让人想不到的结果都出在两条规则上。第一,聚合函数(COUNT、SUM、AVG 这类把多行合成一个值的函数)会跳过 NULL。对一列计数,只数有值的行;SUM 和 AVG 也会把 NULL 排除在外。10、NULL、20 的平均数是 15,而不是把 NULL 当成 0 算出的 10,因为除数是 2。
第二,任何值和 NULL 比较,结果既不是真也不是假,而是“未知”,WHERE 只保留条件为真的行。所以 coupon <> 'SPRING' 并不表示“不是 SPRING”,它表示“有优惠券,并且这张优惠券不是 SPRING”。要找缺失的值,就写 IS NULL。
占比等于分子除以分母,而分子和分母各是一组行的计数。“3 月的订单里有多大比例用了优惠券?”在我们的数据里有两种说得通的分母:3 月的全部 7 个订单,或者其中仍是 paid(已付款)的 5 个。两个都合理,答案却不同。选问题真正指的那个,并把它和数字写在一起:“在 3 月已付款的订单里,40% 用了优惠券。”
分子和分母要落在同一组行上。用全部订单里的优惠券数除以已付款订单数,混用了两组行,这个数不是任何一批订单的占比。
除法本身也有陷阱。在 SQLite 和 PostgreSQL 里,两个整数相除得到整数,所以 2 / 7 是 0。先乘以 1.0,就像下面这条查询做的那样。
检查这个材料
sql语言: sql7 条注释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)),march AS ( SELECT * FROM orders WHERE order_ts >= '2025-03-01' AND order_ts < '2025-04-01')SELECT 'all March orders' AS population, COUNT(*) AS orders, COUNT(coupon) AS with_coupon, SUM(shipping_fee) AS shipping, ROUND(1.0 * COUNT(coupon) / COUNT(*), 3) AS shareFROM marchUNION ALLSELECT 'paid March orders', COUNT(*), COUNT(coupon), SUM(shipping_fee), ROUND(1.0 * COUNT(coupon) / COUNT(*), 3)FROM marchWHERE status = 'paid';population orders with_coupon shipping share ----------------- ------ ----------- -------- ----- all March orders 7 2 25 0.286 paid March orders 5 2 15 0.4
两批订单,两个占比:全部 3 月订单的 0.286,已付款订单的 0.4。两个都不是错误,它们回答的是不同的问题。已付款订单的运费合计 15,会在第 4 课再次出现。
材料包含 32 行和 7 条注释。
3 月的 7 个订单,从 102 到 108 依次排列,coupon 的值是:SPRING、NULL、NULL、NULL、SPRING、NULL、NULL。报表需要 3 月没用 SPRING 优惠券的订单,有人写了 WHERE coupon <> 'SPRING'。
在下一步之前先预测
这个筛选条件会保留 7 行里的几行?
你的文字只会留在此页面,不会保存或评估。
逐行过一遍。两个 SPRING 行通不过这个条件,这符合预期。对 5 个 NULL 行,数据库要回答的是“一个未知的值和 SPRING 不同吗”,答案是“未知”,不是“真”。WHERE 只保留“真”,所以这 5 行也被丢掉了。
正确答案是 5,也就是 7 减去 2 个 SPRING 订单。错误的筛选不会报任何错,只是交出一份空报表,仿佛没有人不用优惠券下单。
改法是把缺失的情形写出来:WHERE coupon IS NULL 返回那 5 行。如果还有别的优惠券代码,而你要的是所有不是 SPRING 的订单,就写 WHERE coupon IS NULL OR coupon <> 'SPRING'。再让两个筛选互相核对:用了 SPRING 的 2 个订单,加上没用的 5 个,正好是 3 月的 7 个订单。
想一想
挑一个你经常做或经常读的百分比,用文字写出它的分子和分母。两者描述的是同一组行吗?同事换一个分母,会不会也说得通?