業務部的營收和財務部的營收差一截,多數人第一個反應是去翻報表的 SQL。我的經驗是,花在查詢語法上的時間大多白費,問題在資料表設計那天就埋好了。這篇拆三種我在 MoBagel 做資料產品、接各家企業資料時最常撞到的錯法,每一種都講根因和改法。
01錯法一:拿名稱當識別,沒有不會變的編號
最常見的是訂單直接存客戶名稱,而不是客戶編號。「台灣OO股份有限公司」和「台灣OO(股)公司」在系統裡變成兩個客戶,業務報表分開算,財務用統一編號歸戶,兩邊自然對不上。商品也一樣,品名改一個字,歷史銷量就斷成兩段。
根因是把給人看的欄位當成給系統認的欄位。改法:每張主檔表用系統產生、永不修改的 id 當主鍵,名稱只是屬性;統一編號、料號這類外部識別碼另開欄位並加唯一索引。已經亂掉的資料,先建一張對照表把同一實體的不同寫法歸到同一個 id,再回頭修訂單的外鍵。
02錯法二:會變動的資料直接覆寫,歷史就不見了
第二種更隱蔽。商品單價、業務員所屬部門、客戶等級,這些值會隨時間改,很多系統卻直接 UPDATE 蓋掉舊值。結果是上個月的報表今天重跑,數字變了。業務員七月調部門,他六月的業績也跟著搬過去,主管拿著兩份不同天印的報表來問,沒人說得清哪份對。
改法看情境。成交當下就確定的值,例如成交單價、折扣,直接寫進訂單明細,不要每次回主檔查。歸屬關係這種會變的,加 valid_from、valid_to 兩個欄位記錄有效期間,查詢時用交易日期去對當時有效的那一列。資料倉儲叫它緩慢變化維度,名詞不用背,記得歷史不能被改寫就好。
03錯法三:一張表好幾種日期,沒人定義哪個算數
訂單有建立日、付款日、出貨日、發票開立日,退貨又有自己的日期。業務看接單日,倉庫看出貨日,財務看發票日,三個部門都說自己的七月營收才是對的。這不是誰算錯,是資料庫從來沒定義營收認列要以哪個日期為準。
再疊上時區。伺服器存 UTC,報表卻直接拿 UTC 日期切日,台灣時間凌晨零點到早上八點之間的訂單會被算到前一天,月初第一天最明顯。我的規則是:資料庫一律存 UTC 時間戳,報表層統一轉成 Asia/Taipei 再切日,並在欄位註解寫清楚每個日期欄位的業務意義。
04三個錯其實是同一個根因
回頭看,這三種錯都來自同一件事:設計資料表的人只想著這個畫面要存什麼,沒想過三個月後誰會拿這些資料算什麼。開發期程一緊,表結構就照著表單欄位長出來,報表需求等上線後才提,那時資料已經髒了。
我常講,直接解問題是 60 分,找到根本問題是 80 分,解決根本問題是 100 分。每個月手動調報表是 60 分;查出是主鍵設計造成的是 80 分;改表結構、清歷史資料、補上寫入時的檢核,才是 100 分。
05下週一可以先做的四項檢查
1. 列出公司裡最常吵架的三張報表,找出它們共用哪些資料表。2. 檢查這些表的關聯是不是用流水號或 UUID,有沒有拿名稱或可修改的代碼當外鍵。3. 列出所有會被 UPDATE 的主檔欄位,逐一問:舊值重要嗎。4. 把每個日期欄位的定義寫成一頁文件,請業務、財務、倉儲三方簽認。
做完這四項,不一定要馬上重構。先把定義統一,報表差異通常就能解釋到每一筆;接下來要不要動表結構,就有證據判斷值不值得花這個工。