時間分桶與 SCD:SQL 處理時間的兩個坑

接著上一篇的「區間」,這篇收尾第二幕,講時間在 SQL 裡的兩個坑——它們都源自同一件事:時間是連續的,但你的資料是離散的事件。 中間必然有「什麼都沒發生」的空隙,以及「值變了但你沒記」的變化。SQL 不會自動幫你處理這些,你得主動出手。

坑一:時間分桶會漏掉空桶

做「每日/每小時」聚合,標準做法是 date_trunc('day', ts) 把時間戳歸到桶裡再 GROUP BY。但這裡藏了一個安靜的坑:GROUP BY 只會產出「有資料」的桶——沒有任何事件的那天,整列直接消失,你的時間序列就這樣斷了一個洞:

問題的根在「缺席被靜默地當成不存在」。修法是別讓資料決定有哪些桶,自己先造出完整的時間軸:用 generate_series 產生每一天,再把資料 LEFT JOIN 上去,沒對到的用 COALESCE 補 0:

-- ❌ 沒訂單的日子整列不會出現
SELECT date_trunc('day', created_at) AS day, COUNT(*) AS orders
FROM orders
GROUP BY 1 ORDER BY 1;

-- ✅ 先造完整日期軸,再補洞
SELECT d.day, COALESCE(COUNT(o.id), 0) AS orders
FROM generate_series(DATE '2026-07-01', DATE '2026-07-04', INTERVAL '1 day') AS d(day)
LEFT JOIN orders o ON date_trunc('day', o.created_at) = d.day
GROUP BY d.day ORDER BY d.day;

坑二:維度會變,你要記住歷史

第二個坑:維度會慢慢變——客戶搬家、產品改分類、業務換負責區。如果你直接把舊值覆蓋掉,歷史就永遠回不去了:去年那張訂單當時算的是哪個稅區?那筆成交掛在誰名下?資料倉儲對這題的標準解法叫 SCD(Slowly Changing Dimension),而最常用的是 Type 2:不覆蓋,而是每次變化新增一列,用有效區間標記:

有了這張歷史表,「回到某個時間點看當時的樣子」就只是一個區間查詢:

-- 查 2026-02-01 當下,客戶 1 登記的城市(答案:台北)
SELECT city FROM customer_history
WHERE customer_id = 1
  AND valid_from <= DATE '2026-02-01'
  AND DATE '2026-02-01' < valid_to;

對照之下,Type 1 就是直接 UPDATE 覆蓋舊值——省事,但歷史永遠消失。要不要留歷史,是個要在建模時就想清楚的取捨:會被拿來做歷史分析、稽核、「當時到底是多少」的維度,幾乎都該用 Type 2。

反思

「沒有資料」也是一種資料

分桶漏空桶這個坑,本質是「缺席被靜默地當成不存在」。但在時間序列裡,「那天是 0」跟「那天沒有這一列」意義天差地遠——一個是明確的資訊(那天真的沒訂單),一個是資料的破洞(你根本沒產出那格)。畫成圖、餵給下游模型時,這個差別會放大成錯誤的趨勢判讀。這跟 NULL 那篇是同一種病灶:「跑得出來、但悄悄少了東西」。所以我現在做任何時間序列,第一件事就是先想「空的時段要怎麼呈現」,主動用 generate_series 把軸補滿,不讓 GROUP BY 幫我把 0 藏成「不存在」。

SCD Type 2 是資料的「版本控制」

Type 2 想通之後,我覺得它本質就是給維度資料做 git——每次改動存一個帶時間戳的版本,而不是覆蓋。差別只在你存的是「有效區間」而不是 commit。這個視角讓我更容易判斷該用 Type 1 還 Type 2:你會不會想 git blame 這個欄位? 會(想知道當時的價格、當時的歸屬、當時的分類)就用 Type 2;不會、只在乎現值(例如使用者的顯示暱稱)就 Type 1 覆蓋掉沒關係。覆蓋很省事,代價是你永遠回不去——這個取捨在任何存狀態的系統裡都存在,不只資料倉儲。

時間讓 SQL 變難,因為它有「不存在的區間」

回頭看,這兩個坑其實同源,也跟上一篇的 gaps and islands 是一家人:時間是連續的,資料卻是離散的——中間一定有「什麼都沒發生的空隙」和「值換了但沒被記錄的變化」。SQL 不會自動幫你補這些,你得主動造出完整的時間軸(generate_series)、主動記錄變化的區間(valid_from/valid_to)。認清「時間有你必須自己填的空隙與邊界」,是處理任何時間序列的第一課;而這一整幕(去重、連續區間、時間)反覆在講的,其實是同一件事——真實世界的資料很髒、很不連續,把它整理成乾淨、可分析的形狀,正是資料工程的日常手藝。