模組 1 · 讀一張資料表,不被它騙了
第 1 節,共 6 節 · 7 分鐘 · 繁體中文 · 唯讀
資料表的粒度是一列所代表的東西,查詢前要先說出它:計數得到的是這個東西的數量,篩選則是在這個粒度上整列保留或整列排除。
寫查詢之前,先把這句話補完:「這張表的一列,是一個……」。「一個」後面接的東西,就是這張表的資料粒度(grain,簡稱粒度),每一次計數、加總和篩選的意義,都由它決定。COUNT(*) 不會數訂單,也不會數顧客,它數的是資料列,所以回傳的數字取決於一列代表什麼。
這門課從頭到尾用同一間小型網路商店當例子,它賣水壺、馬克杯、茶葉罐和濾網(資料裡的商品名稱是 Kettle、Mug、Tea tin 和 Filter)。價格都是整數美元,這是範例的假設;使用 SPRING 優惠碼可以免運費。三張資料表描述這間商店,每一張的粒度都不一樣。
要找出粒度,先找出值不會重複的欄位,再大聲念出一列真實的資料:「這一列是顧客 2 在 3 月 9 日下的那筆訂單。」如果這句話得加上「而且還有」,就表示同一張表裡混了兩種粒度。
粒度比你要數的東西更細時,計數就會出錯。訂單明細比訂單細,因為一筆訂單裡的每項商品都有自己的一列。你想知道有幾筆訂單,卻拿明細表來問,光數列數是不夠的。
這門課的每一段查詢都能直接執行。查詢開頭的 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 報成「有小額商品的訂單數」,就會多報兩筆。
材料包含 23 行和 4 則註解。
WHERE 保留條件為真的資料列,排除其餘的。商業問題不會明說究竟是哪些列,所以篩選條件就是把定義寫下來的地方。「3 月的訂單」藏著兩個決定:一是 3 月的範圍從哪一刻到哪一刻,二是哪些狀態算數。
先看日期。時間戳記 2025-03-31 18:20 比 2025-03-31 晚,因為沒寫時間的日期,指的是那天的 00:00。所以 BETWEEN '2025-03-01' AND '2025-03-31' 會悄悄漏掉 3 月 31 日 00:00 之後下的所有訂單。正確的寫法是「包含起點、不含終點」:從 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 月的全部七筆訂單,包括 104(refunded,已退款)和 105(cancelled,已取消)。第三個保留五筆:訂單 102、103、106、107 和 108。後兩個各自符合自己的定義,所以都是對的。
材料包含 25 行和 5 則註解。