模組 1 · 讀一張資料表,不被它騙了
第 2 節,共 6 節 · 6 分鐘 · 繁體中文 · 唯讀
NULL 表示沒有記錄到值,所以彙總和比較都會略過它,除非你用 IS NULL 明說;百分比要有意義,分子和分母必須描述同一群明確說明的資料列。
NULL 表示沒有可用的值,原因可能是未知,也可能是不適用。它不是 0,也不是空字串。在 orders 資料表裡,沒用優惠碼的訂單,coupon 都是 NULL;而 shipping_fee 是 0,則是一項真實的事實:我們沒收運費。NULL 是缺少這項事實。
兩條規則涵蓋大部分的意外。第一,彙總函式(aggregate function,例如 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 月全部七筆訂單,或其中仍是已付款的五筆。兩個都合理,答案卻不同。選出題目指的那一個,連同數字一起寫下來:「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 月訂單(102 到 108,依序)的 coupon 值是:SPRING、NULL、NULL、NULL、SPRING、NULL、NULL。報表要的是沒用 SPRING 優惠碼的 3 月訂單,有人寫了 WHERE coupon <> 'SPRING'。
在下一步之前先預測
這個篩選會從七列中保留幾列?
你的文字只會留在此頁面,不會儲存或評估。
逐列檢查。兩列 SPRING 沒通過測試,這是預期中的。至於五列 NULL,資料庫要判斷的是:一個未知的值和 SPRING 不同嗎?答案是未知,不是真。WHERE 只保留真,所以這五列也被排除了。
正確答案是 5,也就是 7 減去 2 筆 SPRING 訂單。寫錯的篩選不會報錯,只會交出一份空報表,宣稱沒有人不用優惠碼就下單。
修正的做法,是把缺漏的情況明說出來:WHERE coupon IS NULL 會回傳那 5 列。如果還有其他優惠碼,而你要的是所有不是 SPRING 的訂單,就要寫成 WHERE coupon IS NULL OR coupon <> 'SPRING'。把兩個篩選互相核對:用 SPRING 的 2 筆加上沒用的 5 筆,剛好是 3 月的 7 筆訂單。
想一想
選一個你經常報告或閱讀的百分比,用文字寫出它的分子和分母。兩者描述的是同一群資料列嗎?如果同事選了不同的分母,會不會也不算錯?