SQL 挑戰，一個讓你彎道超車的關鍵

變強的關鍵「轉置」

我的第一份工作在開發報表系統，當時我的 SQL 能力是 「select * from table」這種初學者等級，我看同事們都很強實在緊張，於是我帶著敬畏的心去請教直屬主管，看有沒有讓自己進步的好辦法。結果直屬主管跟我說了這句話，霸氣外露的一句話讓我印象極為深刻，請教結束後主管還出了一門作業「SQL 轉置」，並特別交代我說：「你只要把這個技術學會就會變強」。先插個話，可能有蠻多人沒聽過轉置(Transpose)，有興趣了解細節的人可以先參考維基百科-轉置矩陣 說明。 「SQL 轉置」簡單說，就是用 SQL 語法把一個資料集合，例如，資料表格(table)、檢視表(view)、子查詢(sub query) 等的「直行轉橫列」或「橫列轉直行」的技巧。我問主管說為什麼學會這招就會變強？主管只是笑笑跟我講，你先回去功課做好再來。可惜這道題目我當時也只解開了前半「直行轉橫列」，另一半實在解不出來，還好直屬主管人不錯，他說我沒經驗能做到這樣算不錯了，隨後給我演示了另一半「橫列轉直行」的技巧，如此神操作讓我驚為天人！這個 SQL 轉置技巧也讓我一路沿用下來，特別是後來的 ETL 改善與檢視表(view) / 預儲程式 (stored procedure) 的設計上。

Pivot 也是一招

現在 SQL 轉置已經有專用的語法了，只要去 google 一下「 sql pivot」 關鍵字就能找到不錯資料，例如這一篇「**[SQL] 使用 PIVOT 扭轉資料，由直列轉為橫向資料」**就有不錯的 Pivot 使用說明。那為什麼會說學會這種 SQL 轉置技巧就會變強呢？因為資料轉置可以讓我們結算好資料一次撈出來效率極佳。我舉個例子給你聽你就懂了，當時我負責的系統有很多 24 小時都在跑的 SQL ETL，在我接手維護的一段時間後，注意到很多 99.9% 相似的 SQL ETL，彼此間唯一的差異就是 Where 的條件帶了一個不同的條件參數，例如：機台 AVA010, AVA020…，所以有多少台設備就有多少 SQL ETL 。我問了前輩為什麼，他回我：「大家都是這樣寫，讓每一隻 SQL 只負責一個控制常數這樣比較簡單」。我說：「可是如果來源表格結構有變，例如欄位改名字，這麼多 SQL 要怎麼處理？」前輩回我：「那就全部一起改，很快！」；我：「那如果有新的參數呢？」前輩：「直接 Copy 其中一隻改一下即可」。你看出問題了嗎？就像是寫程式明明可以寫成函數呼叫，卻硬要用 Ctrl+C, Ctrl+V 一樣糟糕。

看見價值

後來我學會了 SQL 轉置技巧後，嘗試把相似的 16 隻 SQL ETL 整成 1 隻，意外發現速度跟原本差不多但維護成本減少 90% 以上。這給了我很大的信心，隨著我把一兩百隻 SQL ETL 全部整理過到剩下 20 隻左右，我才終於明白了當時直屬主管說的「沒有什麼是寫不出來的」的真正意思，是「一次就能把資料結算好撈出來的真．SQL 功夫」。這種方法在開發管理報表的時候特別有用。橫列轉直行可以用來 group by 統計資料、直行轉橫列可以組合要顯示的欄位，也因為需要在層層的 SQL 中不斷地應用，通常會寫得很長，透過內縮排版來增加可讀性、可維護性也是非常重要的。

順帶一提另一種極端的做法就是用最簡單的「 select * 」把所有的資料 raw data 全部拉出來到網頁伺服器程式端再來計算數據的方式，你知道問題在哪嗎？

1. 資料庫只發揮了儲存資料的能力，運算能力完全沒用到
2. 網路上傳輸大量 Raw data 會佔用很大的頻寬會影響到其他生產線的資訊系統，傳輸百萬筆 Raw data 資料跟結算後的千筆資料時間相比天差地遠
3. 伺服器記憶體不足，必須先準備 RecordSet 來承接這些資料才能開始運算，記憶體一路狂飆，伺服器就會無法回應，使用者體驗極差無比

結語

最後我想說的是，我非常感謝帶我入門的直屬主管與同事們，沒有他們的狂言豪語刺激，我不會有深刻的學習體悟。因為不管學習哪一門技術，最看重的還是追求「真理」的心，一定要先知其然而後要知其所以然，才能做到以一通百千變萬化。好了，接下來換我要出作業給你了：「要請你試試在不用專門的 Pivot 的語法，只用 SQL 語法做出轉置的結果，任何資料庫都可以」，我非常期待你的答案。