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

更新於 發佈於 閱讀時間約 4 分鐘
MySQL Database
沒有什麼是 SQL 寫不出來的 — By 我工作的第一個主管

變強的關鍵「轉置」

我的第一份工作在開發報表系統,當時我的 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 語法做出轉置的結果,任何資料庫都可以」,我非常期待你的答案。
即將進入廣告,捲動後可繼續閱讀
為什麼會看到廣告
avatar-img
16會員
61內容數
WarrenLo's 軟體設計武功祕笈
留言0
查看全部
avatar-img
發表第一個留言支持創作者!
Warren Lo的沙龍 的其他內容
還有三個指標都是利用前一篇的產能指標計算出來的,焦點都在設備上。因為一間工廠要賺錢就要生產產品,要生產產品就要投資設備,當有重大設備投資前工廠管理團隊就會評估 ROI (投資報酬率),並期望未來兩三年回收開始「攤平」賺錢
不查不知道,一查嚇一跳,足足七種之多,分別是超鍵 (Super key)、候選鍵 (Candidate key)、主鍵 (Primary key)、替代鍵 (Alternative key)、複合鍵 (Composite key)、唯一鍵 (Unique key) 和外鍵 (Foreign key)
每隔 3 秒鐘就會有一個產品在你眼前出現。我開始算:一個產品 3 秒,一天有 86400 秒,這條生產線一天可以產出 28800 個產品… 廠長說,你那是理論值,工廠實際產能不是這樣算的,還要考慮工廠利用率、可用率、設備、還有製程良率才能得出產能數字,幫助管理人員做決策。
這幾年客戶對品質要求越來越高了,這份 guideline 就是他們的一個大咖客戶給的,每個新機種都會進行 Golden Line 評估,定期稽核也是用這張評分表打分數,所有相同類型供應商們都要通過考核才有機會拿訂單。
遊戲這種「屍骨無存」方式處理被打倒的怪物們就是記憶體管理機制,是程式設計的時候一定會被拿出來討論的一門技術。
在軟體設計的工作裡除了你熟悉的 coding 技能外,還有一塊非常重要經常被忽略,那就是處理資料存儲的部分。我們現在用的軟體功能都很複雜,為了有更好的使用者體驗,軟體設計師會在後台儲存有很多的設定資料並且紀錄軟體運作過程中的資料流數據,為了更好的記錄應用這些資訊,通常會搭配一個資料庫在程式裡面
還有三個指標都是利用前一篇的產能指標計算出來的,焦點都在設備上。因為一間工廠要賺錢就要生產產品,要生產產品就要投資設備,當有重大設備投資前工廠管理團隊就會評估 ROI (投資報酬率),並期望未來兩三年回收開始「攤平」賺錢
不查不知道,一查嚇一跳,足足七種之多,分別是超鍵 (Super key)、候選鍵 (Candidate key)、主鍵 (Primary key)、替代鍵 (Alternative key)、複合鍵 (Composite key)、唯一鍵 (Unique key) 和外鍵 (Foreign key)
每隔 3 秒鐘就會有一個產品在你眼前出現。我開始算:一個產品 3 秒,一天有 86400 秒,這條生產線一天可以產出 28800 個產品… 廠長說,你那是理論值,工廠實際產能不是這樣算的,還要考慮工廠利用率、可用率、設備、還有製程良率才能得出產能數字,幫助管理人員做決策。
這幾年客戶對品質要求越來越高了,這份 guideline 就是他們的一個大咖客戶給的,每個新機種都會進行 Golden Line 評估,定期稽核也是用這張評分表打分數,所有相同類型供應商們都要通過考核才有機會拿訂單。
遊戲這種「屍骨無存」方式處理被打倒的怪物們就是記憶體管理機制,是程式設計的時候一定會被拿出來討論的一門技術。
在軟體設計的工作裡除了你熟悉的 coding 技能外,還有一塊非常重要經常被忽略,那就是處理資料存儲的部分。我們現在用的軟體功能都很複雜,為了有更好的使用者體驗,軟體設計師會在後台儲存有很多的設定資料並且紀錄軟體運作過程中的資料流數據,為了更好的記錄應用這些資訊,通常會搭配一個資料庫在程式裡面
你可能也想看
Google News 追蹤
Thumbnail
大家好,我是woody,是一名料理創作者,非常努力地在嘗試將複雜的料理簡單化,讓大家也可以體驗到料理的樂趣而我也非常享受料理的過程,今天想跟大家聊聊,除了料理本身,料理創作背後的成本。
Thumbnail
哈囉~很久沒跟各位自我介紹一下了~ 大家好~我是爺恩 我是一名圖文插畫家,有追蹤我一段時間的應該有發現爺恩這個品牌經營了好像.....快五年了(汗)時間過得真快!隨著時間過去,創作這件事好像變得更忙碌了,也很開心跟很多厲害的創作者以及廠商互相合作幫忙,還有最重要的是大家的支持與陪伴🥹。  
Thumbnail
嘿,大家新年快樂~ 新年大家都在做什麼呢? 跨年夜的我趕工製作某個外包設計案,在工作告一段落時趕上倒數。 然後和兩個小孩過了一個忙亂的元旦。在深夜時刻,看到朋友傳來的解籤網站,興致勃勃熬夜體驗了一下,覺得非常好玩,或許有人玩過了,但還是想寫上來分享紀錄一下~
Thumbnail
本文介紹瞭如何使用 Power Query 和 Excel VBA 來將【矩陣資料】轉換為【結構化資料】的技巧。透過 Meiko 老師的教學視頻,讀者可以快速瞭解 Power Query 的用法。作者分享運用 Excel VBA 的 ListObject 進行表格的資料處理方法。
Thumbnail
這篇文章主要是介紹了SQL查詢效能調校的方法,針對索引最佳化做了整理和分享,並提供了一些注意事項和建議。
Thumbnail
在進行SQL查詢邏輯更改時,需要適當地使用SubQuery和join來達到新的排序需求。本文將介紹原本的撈取邏輯、需求以及如何使用SubQuery來解決新的排序需求。
如何在SQL實踐中EXCEL 常用功能 篩選 和 擷取文字串?需要熟練地使用分組(GROUP BY) 與 排序 (ORDER BY) 以及SUBSTRING_INDEX函數!
Thumbnail
ETL是資料倉儲領域中一個重要的概念,全稱為Extract-Transform-Load,中文可譯為"抽取-轉換-載入"。ETL的作用是將來自不同來源的資料抽取出來,經過清理、轉換、整合等處理後,最終將處理好的資料載入到資料倉儲或其他單一的資料存放區
轉職數據分析師是一項需要長期努力的過程。對於文組生來說,由於缺乏數學、統計、程式設計等方面的基礎,在轉職過程中往往會面臨更多的挑戰。因此,制定一個合理的學習計劃,對於提高轉職成功率至關重要。 以下是一些規劃轉職數據分析師學習進度表的建議: 第一步:確認你的優勢和劣勢 在開始學習之前,我們首先要
Thumbnail
本文將介紹 SQL 中的連接(JOIN),連接(JOIN)是用於結合來自兩個或多個資料表的相關數據,建議讀過我之前發佈的幾篇"SQL學習筆記"之後再來看這篇。
Thumbnail
※ 基本操作:SQL 語法,SELECT, WHERE, CREATE, UPDATE, DELETE。 SELECT:從資料庫中或資料表中指定要選擇的欄位中取得資料,稱之為查詢 (query)。 ※ 語法:要由兩部分構成,第一部分是要 "拿什麼" 資料 (若有多項用逗號隔開);第二部分則為
Thumbnail
這是文科轉職數據工程師系列的第一篇文章。 許多人會在轉職前上許多數據分析課程,該怎麼選擇比較適合自己,但又不會噴錢呢? 這篇文章要介紹這個轉職過程前的準備工作。
Thumbnail
大家好,我是woody,是一名料理創作者,非常努力地在嘗試將複雜的料理簡單化,讓大家也可以體驗到料理的樂趣而我也非常享受料理的過程,今天想跟大家聊聊,除了料理本身,料理創作背後的成本。
Thumbnail
哈囉~很久沒跟各位自我介紹一下了~ 大家好~我是爺恩 我是一名圖文插畫家,有追蹤我一段時間的應該有發現爺恩這個品牌經營了好像.....快五年了(汗)時間過得真快!隨著時間過去,創作這件事好像變得更忙碌了,也很開心跟很多厲害的創作者以及廠商互相合作幫忙,還有最重要的是大家的支持與陪伴🥹。  
Thumbnail
嘿,大家新年快樂~ 新年大家都在做什麼呢? 跨年夜的我趕工製作某個外包設計案,在工作告一段落時趕上倒數。 然後和兩個小孩過了一個忙亂的元旦。在深夜時刻,看到朋友傳來的解籤網站,興致勃勃熬夜體驗了一下,覺得非常好玩,或許有人玩過了,但還是想寫上來分享紀錄一下~
Thumbnail
本文介紹瞭如何使用 Power Query 和 Excel VBA 來將【矩陣資料】轉換為【結構化資料】的技巧。透過 Meiko 老師的教學視頻,讀者可以快速瞭解 Power Query 的用法。作者分享運用 Excel VBA 的 ListObject 進行表格的資料處理方法。
Thumbnail
這篇文章主要是介紹了SQL查詢效能調校的方法,針對索引最佳化做了整理和分享,並提供了一些注意事項和建議。
Thumbnail
在進行SQL查詢邏輯更改時,需要適當地使用SubQuery和join來達到新的排序需求。本文將介紹原本的撈取邏輯、需求以及如何使用SubQuery來解決新的排序需求。
如何在SQL實踐中EXCEL 常用功能 篩選 和 擷取文字串?需要熟練地使用分組(GROUP BY) 與 排序 (ORDER BY) 以及SUBSTRING_INDEX函數!
Thumbnail
ETL是資料倉儲領域中一個重要的概念,全稱為Extract-Transform-Load,中文可譯為"抽取-轉換-載入"。ETL的作用是將來自不同來源的資料抽取出來,經過清理、轉換、整合等處理後,最終將處理好的資料載入到資料倉儲或其他單一的資料存放區
轉職數據分析師是一項需要長期努力的過程。對於文組生來說,由於缺乏數學、統計、程式設計等方面的基礎,在轉職過程中往往會面臨更多的挑戰。因此,制定一個合理的學習計劃,對於提高轉職成功率至關重要。 以下是一些規劃轉職數據分析師學習進度表的建議: 第一步:確認你的優勢和劣勢 在開始學習之前,我們首先要
Thumbnail
本文將介紹 SQL 中的連接(JOIN),連接(JOIN)是用於結合來自兩個或多個資料表的相關數據,建議讀過我之前發佈的幾篇"SQL學習筆記"之後再來看這篇。
Thumbnail
※ 基本操作:SQL 語法,SELECT, WHERE, CREATE, UPDATE, DELETE。 SELECT:從資料庫中或資料表中指定要選擇的欄位中取得資料,稱之為查詢 (query)。 ※ 語法:要由兩部分構成,第一部分是要 "拿什麼" 資料 (若有多項用逗號隔開);第二部分則為
Thumbnail
這是文科轉職數據工程師系列的第一篇文章。 許多人會在轉職前上許多數據分析課程,該怎麼選擇比較適合自己,但又不會噴錢呢? 這篇文章要介紹這個轉職過程前的準備工作。