最近完成了個日報表/時段報表的需求，作這種報表當然可以以時間區間一段一段的來取資料，不過那實在太 Low 了。雖然笨方法有時不失為一個好方法，但是如果能以一段 SQL 來直接達成，那成就感是笨方法無可比擬的。
就來分享一下以 SQL 達成日報表的相關實作心得。
首先，先來看看我們有什麼。假設，我們有一個記錄銷售量狀況的資料表，如下：
| tbSellingLog (銷售記錄表) | ||
|---|---|---|
| sellingDateTime (銷售日期) | sellingItem (銷售品項) | sellingVolume (銷售數量) |
| 2013-11-01 09:15 | 品項2 | 10 |
| 2013-11-01 10:35 | 品項3 | 5 |
| 2013-11-01 13:43 | 品項2 | 3 |
| 2013-11-02 11:27 | 品項1 | 6 |
| 2013-11-02 13:17 | 品項2 | 5 |
| 2013-11-02 14:46 | 品項3 | 3 |
| 2013-11-03 15:57 | 品項1 | 9 |
| 2013-11-03 16:23 | 品項3 | 3 |
| 2013-11-05 15:36 | 品項1 | 6 |
| 2013-11-05 17:33 | 品項2 | 7 |
| sellingDateTime (銷售日期) | sellingItem (銷售品項) | sellingVolume (銷售數量) |
|---|---|---|
| 2013-11-01 | 品項1 | 0 |
| 2013-11-01 | 品項2 | 13 |
| 2013-11-01 | 品項3 | 5 |
| 2013-11-02 | 品項1 | 6 |
| 2013-11-02 | 品項2 | 5 |
| 2013-11-02 | 品項3 | 3 |
| 2013-11-03 | 品項1 | 9 |
| 2013-11-03 | 品項2 | 0 |
| 2013-11-03 | 品項3 | 3 |
| 2013-11-04 | 品項1 | 0 |
| 2013-11-04 | 品項2 | 0 |
| 2013-11-04 | 品項3 | 0 |
| 2013-11-05 | 品項1 | 6 |
| 2013-11-05 | 品項2 | 7 |
| 2013-11-05 | 品項3 | 0 |
其實以 SQL 來產出這樣一個報表，重點與難度其實是在產生【完整】的日期區間，並完成空白日期【補零】的動作。
當然，可以撈出資料後，在 AP 層的迴圈處理掉，這貌似容易得多。但是有時候難免面臨寄人籬下的情境，是在既有的報表模組/平台上進行開發，在懶得大刀闊斧地動或是不確定動了會不會有其他意料之外影響的情況下，往往能下手的就只有 Data Source 的部份，就得靠 SQL 去解決。
以 SQL Solution 的方向來思考，就是準備一個時間區間內，包含著各個時間間隔的 Data Set，然後跟前述的銷售量的 GROUP BY 資料 JOIN 起來就搞定了。
所以阿布好想要這樣的指令，由系統幫忙算出某段時間區間內的時間間隔的 Data Set：
SELECT TO_CHAR(date, 'YYYY-MM-DD') AS dateString
FROM dual
WHERE date BETWEEN TO_DATE('2013-11-01', 'YYYY-MM-DD')
AND TO_DATE('2013-11-05', 'YYYY-MM-DD')※【好想要】代表這 SQL 是捏造的，別傻傻抄去用 (It's a FAKE SQL, DO NOT use it.)※看起來跟真的一樣，可是很可惜，並沒辦法這樣用。(或許有，只是阿布才疏學淺，還望先進指教。)
思來想去，阿布想到的 solution 是 UNION，自己去造一個釀的 Data Set 出來。
就是小時候學過的集合概念，看左圖就可以說明一切。UNION: 兩沱資料以不重複方式進行聯集。
UNION ALL: 兩沱資料直接合併的無差別聯集。
INTERSECT: 取兩沱資料都有的交集。
MINUS: A沱資料中去除B沱資料中共有的部分。
很顯然的，要使用 UNION/UNION ALL/INTERSECT/MINUS 的前提是兩沱資料的結構必須一模一樣(含有相同的欄位)，才能使用。
然後，就用下面這段 SQL：
SELECT '2013-11-01' AS dateString FROM dual UNION SELECT '2013-11-02' AS dateString FROM dual UNION SELECT '2013-11-03' AS dateString FROM dual UNION SELECT '2013-11-04' AS dateString FROM dual UNION SELECT '2013-11-05' AS dateString FROM dual
就可以得到一個時間區間內，包含著各個時間間隔的 Data Set。
遺憾的是，阿布還找不到純 SQL 的方法自動去產生，而是借助 AP 層的 DataTime 演算與 FOR 迴圈去動態產出上述的 SQL statement。
SQL 的 JOIN 是把兩沱資料依關聯兜在一起的方式，上圖清楚呈現各個 SQL JOINs 的作用，更詳細的範例說明/演示可以參考這篇 Visual Representation of SQL Joins。
在這就不照本宣科，講述 JOIN 有哪幾種啦，又 LEFT 又 RIGHT 又 INNER 又 OUTER 的讓人眼花撩亂，只講述『心法』，重點整理一下。
- INNER JOIN: 就是符合關聯條件的資料列才會被撈取，取交集。
- LEFT/RIGHT: 用以指定何為主表，主表資料一定撈取。如未指定則以 LEFT 為主表。
- OUTER JOIN: 就是不管副表有沒有相關聯的資料，主表資料一定撈取。
因為需求是要【完整】的日期區間，所以將之前用時間區間內的時間間隔的 Data Set 做為主表，而以 GROUP BY 出來銷售量資料作為附表，進行 OUTER JOIN，接著就來實作吧。
把前述的準備工作兜一兜，就可以得到下面的結果：
-- Oracle Style
SELECT dateList.dateString,
sumSellingLog.sellingItem,
NVL(sellingItem.sumSellingVolume, 0) AS sumSellingVolume
FROM (SELECT '2013-11-01' AS dateString FROM dual
UNION
SELECT '2013-11-02' AS dateString FROM dual
UNION
SELECT '2013-11-03' AS dateString FROM dual
UNION
SELECT '2013-11-04' AS dateString FROM dual
UNION
SELECT '2013-11-05' AS dateString FROM dual) dateList
LEFT JOIN (SELECT dateString, sellingItem,
SUM(sellingVolume) AS sumSellingVolume
FROM (SELECT TO_CHAR(sellingDateTime, 'YYYY-MM-DD') AS dateString,
sellingItem,
sellingVolume
FROM tbSellingLog) tmpSellingLog
GROUP BY dateString, sellingItem) sumSellingLog
ON dateList.dateString = sumSellingLog.dateString撈取出來的資料，就是本次需求的實作成果嚕！
除了各家 SQL 資料庫廠商各有各的 sub-function 之外，DateTime Format 也是各家 SQL 資料庫廠商各玩各的，十分混亂。做這個報表，最大的心得是MS SQL 的 DateTime Format 實在是很鳥蛋，貌似功能豐富卻遠遠不及 Oracle 上直接寫格式來得直覺好用…
可以試試變化型，細到【時段】的報表，依樣畫葫蘆，先造一個格式為 'YYYY-MM-DD HH:00' 的時間區間內所有時間間隔的 Data Set，再把銷售數字 GROUP BY 'YYYY-MM-DD HH:00' 再 SUM 起來，然後把兩沱資料 JOIN 在一起，就搞定了。這樣玩就會發現如果是 MS SQL 上，實在是麻煩得許多。
說個讓阿布被笑也讓阿布笑很久的小故事。
曾經行銷部門的小妹，拿著 EXCEL 的使用問題來問阿布。
阿布瞪大了眼，『怎會跑來問阿布 EXCEL 的使用問題！？』
『阿布可是頂港有名聲下港熊出名的「EXCEL白癡」耶！！』
『在 EXCEL 上出過好幾次讓人翻白眼的糗，遠近馳名的捏！』
「不知道～我老闆說你什麼疑難雜症的統計資料都能做～EXCEL超強的啊！」
阿布只得傻傻地苦笑著。
原來是這麼回事…行銷老闆看的報告，都是阿布用 SQL 撈出資料，然後匯出到 EXCEL。
其實真正強大的是背後 SQL 功力，EXCEL 只是拿來放資料的載具，這個誤會可大了～！XD
0 回應 :
張貼留言
讓阿布知道你對這篇文章的想法吧!