--- # System prepended metadata title: 樞紐分析在 Excel、MySQL、SQL Server 中的實現 tags: [統計方法] --- # 樞紐分析在 Excel、MySQL、SQL Server 中的實現 ## 一、何謂樞紐分析 ### 1-1 簡介 在傳統的統計教科書中，往往會這麼解釋「樞紐分析」: >樞紐分析（pivot analysis）是一種用於整理和分析大量資料的工具，通常在電子表格軟體中使用，如 Microsoft Excel 或 Google Sheets。它的主要功能是將數據依照不同的欄位進行「樞紐」轉換，使使用者能夠快速總結和呈現數據的關鍵訊息。 > >常見的樞紐分析，會進行以下操作： >1. **資料彙總**：將大量資料進行匯總，根據選定的維度（如日期、地點、產品類型等）對數據進行分類。 >2. **動態篩選和分類**：允許使用者將資料進行拖放操作，根據不同的維度（行、列、數據等）重新排列，進行更靈活的查看。 >3. **彙總計算**：可以計算總和、平均值、最大值、最小值、計數等統計數據，並用來識別資料中的趨勢或模式。 > >樞紐分析的優點在於其靈活性和直觀性，能夠幫助用戶從複雜的數據中提取有用的見解，並提供互動式的操作方式。 而簡單來說樞紐分析，不過是一種擷取表格資料重點的一種方法罷了。我們可以直接用個簡單的例子去理解它: 當然！這裡有一個具體的例子，說明如何使用樞紐分析來分析銷售數據： ### 1-2 範例： 假設有一個銷售數據表格，記錄了不同地區、產品類型、銷售人員以及銷售額。以下是數據的一部分： | 日期 | 地區 | 產品類型 | 銷售人員 | 銷售額 | |------------|--------|----------|----------|---------| | 2024/01/01 | 台北 | 手機 | 張三 | 5000 | | 2024/01/01 | 台北 | 電腦 | 李四 | 8000 | | 2024/01/02 | 高雄 | 手機 | 王五 | 6000 | | 2024/01/02 | 台中 | 電腦 | 趙六 | 7500 | | 2024/01/03 | 台北 | 手機 | 張三 | 5500 | | 2024/01/03 | 高雄 | 電腦 | 王五 | 7000 | 假設你想瞭解各地區與銷售總額的關係。最直接的作法就是，直接從表格下手的最直接方法(不用任何統計方法、不建立任何模型、不使用任何機器學習或演算方法、不做任何特徵工程，甚至不使用電腦的前提下)，就是直接觀察每個地區的銷售總額和平均銷售額。你可以使用樞紐分析來組織這些資料。 大致步驟應為: 1. **將「地區」設為列**：這樣每一個地區（如台北、高雄、台中）會顯示在樞紐分析表的每一行。 2. **將「銷售額」設為數據值**：計算總銷售額（SUM）或平均銷售額（AVERAGE），並寫在其對應的欄位。 其報表會呈現： | 地區 | 銷售額總和 | 平均銷售額 | |--------|------------|------------| | 台北 | 18500 | 6166.67 | | 高雄 | 13000 | 6500 | | 台中 | 7500 | 7500 | 這個表就是所謂的「樞紐分析表」，而此時的「樞紐」就是 "地區" 這個變數，關注的是 "銷售額"。 在這個例子中，樞紐分析幫助你快速查看每個地區的銷售總額和平均銷售額。有了這些資訊，我們可以做一些簡單的解釋，例如: - 台北的銷售總額最高，且平均銷售額在三個地區中最低。可能代表台北的分店最多，但店面位置較差(不考慮 "銷售員" 變數的前提下)。 - 台中的銷售總額最低，且平均銷售額在三個地區中最高。可能代表台中的分店最少，但店面位置不錯。 - 若需要進一步分析銷售人員的貢獻，可以將「銷售人員」添加到行或列中，這樣你就可以查看每個銷售人員在各自地區的銷售表現。 這樣，你就能夠從原始數據中提取有價值的初步見解，當然我們也能將樞紐分析表做成表格，做進一步的推論或分析。這一整套從建表到解釋的流程，在統計領域稱為「樞紐分析」。 > <補>在統計領域跟數學領域中，所指涉的「樞紐分析(pivot analysis)」，說的是同一件事嗎? > > 對於一些數學或工程背景的朋友，可能會很納悶「奇怪，上面對樞紐分析(pivot analysis)的解釋，怎麼跟我印象中的不太一樣? 這是在講同一件事嗎?」 > 是的! 你猜想的沒錯! 在統計領域跟數學領域中，所指涉的「樞紐分析(pivot analysis)」 是 ** 不一樣 ** 的!!!! > 在數學中，尤其是線性代數或數值方法領域，「樞紐分析」通常指的是樞紐操作（Pivoting），這是一種用於矩陣運算中，將行或列進行交換或調整，以確保數值計算的穩定性或提高效率，避免除以零或小數值，從而提升解算過程之精度的一種技術，是解線性方程組或進行高斯消去法時的主流方法之一。 >而數學課本中說的「樞紐分析」，不過就是指涉一連串透過樞紐操作求解的過程罷了，跟本文欲介紹的樞紐分析無關。 ## 二、樞紐分析的實現方法 這次我就不講 Python 和 R 了，畢竟這對於常看我論壇的朋友們來說，應該已經是一塊小蛋糕了。(想知道還是可以 mail 我的~~ ) 今天說說 excel、SQL Server、MySQL 吧: ### 案例: 假設資料表 Sales 有以下內容： | Staff | Site | Product | Quantity | |-----------|------------|-------------|----------| | Alice | SiteA | Apple11 | 10 | | Alice | SiteA | Note10+ | 5 | | Bob | SiteB | Apple11 | 7 | | Bob | SiteB | Zenfone6 | 12 | | Charlie | SiteA | Note10+ | 9 | | Charlie | SiteA | Zenfone6 | 6 | 我們希望生成一個表格，讓結果按「Staff」和「Site」雙標籤顯示，並展示各產品的銷售數量： | Staff | Site | Apple11 | Note10+ | Zenfone6 | |---------|-------|---------|---------|----------| | Alice | SiteA | 10 | 5 | 0 | | Bob | SiteB | 7 | 0 | 12 | | Charlie | SiteA | 0 | 9 | 6 | ### 2-1 excel excel 中的樞紐分析相較簡單、直觀，我們可以直接打開我們的 excel 輸入以上的表格，並點選: 插入 -> 表格 -> 樞紐分析表 -> 從表格/範圍 -> 表格/範圍(T):【框選整個表格】 -> 選擇您要放置樞紐分析表的位址 -> 確定 此時你的畫面應該會類似這樣: ![image](https://hackmd.io/_uploads/S1nmMj-f1e.png) 我們可以看到 excel 中，把所有變數分為了 "列" 、 "欄" 、 "值" 、　"篩選" 四種。此時我們會將 >「樞紐變數」 放入 "列"； >「目標變數」 放入 "值"； >「目標變數類別或副樞紐變數」 放入 "行"。 > 而 "篩選" 是為樞紐表附加所引用的，其機制在 SQL Server 和 MySQL 中，比較特殊，我們下篇在詳細說明吧 ! 該範例中的「樞紐變數」為 Staff 與 Site 雙標籤放在 "列"，「目標變數」 Quantity 放在 "值"，然後 Product 放在 "行"，為其做類，根據以上概念設定，我們的樞紐分析表就出來了: ![image](https://hackmd.io/_uploads/HkbkFibzJg.png) 接著加上解釋，就是我們的樞紐分析了! 啊有些朋友會問，如果要求 mean、min、max 等聚合函數呢? 其實在 excel 我們中，只要點選各變數右邊的符號 "v" (如下圖)，再點選 "欄位值設定" 就能自行設定了! ![image](https://hackmd.io/_uploads/B1uWRjZf1x.png) ### 2-2 SQL Server 有了 excel 的基礎，我們知道只要搞清楚 "列"、"行"、"值" 所對應要放的變數，就能輕鬆做出樞紐分析表了。而在 SQL Server 中，我們可以依靠 `PIVOT()` 函數實現，其語法如下: ```sql SELECT <列1>, <列2>, ..., <列N>, [行1之名稱], [行2之名稱], ..., [行M之名稱] FROM (<原始查詢>) AS <源表別名> PIVOT (<聚合函數>(<值>) FOR <行變數> IN ([行1之名稱], [行2之名稱], ..., [行M之名稱]) ) AS <樞紐表別名> ``` 同樣的例子，我們來看看 SQL Server 會怎麼寫: ```sql SELECT Staff, Site, [Apple11], [Note10+], [Zenfone6] FROM (SELECT Staff, Site, Product, Quantity FROM Sales) AS SourceTable PIVOT (SUM(Quantity) FOR Product IN ([Apple11], [Note10+], [Zenfone6]) ) AS PivotTable; ``` 我針對兩個重點說明一下，剩下的大家應該看得懂~ 1. **內部查詢**：`(SELECT Staff, Site, Product, Quantity FROM Sales) AS SourceTable` - 包含 `Staff` 和 `Site` 欄位，將它們作為行標籤的雙層標籤。 2. **`PIVOT` 函數**： - `SUM(Quantity)`：對每位員工在不同廠區內的每種產品銷售數量求和。 - `FOR Product IN ([Apple11], [Note10+], [Zenfone6])`：轉換產品名稱列為欄位名。 大家執行後，就會生成包含「Staff」和「Site」雙標籤的樞紐表： | Staff | Site | Apple11 | Note10+ | Zenfone6 | |-----------|------------|---------|---------|----------| | Alice | SiteA | 10 | 5 | 0 | | Bob | SiteB | 7 | 0 | 12 | | Charlie | SiteA | 0 | 9 | 6 | P.s 深色背景用截圖好不明顯，我這邊直接複製貼上比較好看~~ 接下來，加上解釋就搞定了! 也很簡單吧! ### 2-3 MySQL 最後說說 MySQL。個人認為要做樞紐分析表，MySQL 是以上所述的三個實現方法中最麻煩的。 其實它也不是難，就是有點麻。 因為在 MySQL 中，儘管沒有內建的 `PIVOT` 函數，但可以通過結合 `CASE WHEN` 和聚合函數（例如 `SUM`）來手動實現樞紐效果，達到類似的行轉列的效果。 我們直接來看 code: ```sql SELECT Staff, Site, SUM(CASE WHEN Product = 'Apple11' THEN Quantity ELSE 0 END) AS Apple11, SUM(CASE WHEN Product = 'Note10+' THEN Quantity ELSE 0 END) AS Note10Plus, SUM(CASE WHEN Product = 'Zenfone6' THEN Quantity ELSE 0 END) AS Zenfone6 FROM Sales GROUP BY Staff, Site; ``` 語法說明: 1. **`CASE WHEN` 條件式**： 每個產品名稱一列，透過 `CASE WHEN` 判斷 `Product` 欄位的值。如果是特定產品（例如 `'Apple11'`），則返回其 `Quantity` 值；否則返回 0。這樣每列都對應一個產品的銷量，並將不同產品的數量聚合到對應的列中。 2. **`SUM` 聚合函數**： 對每個產品的 `Quantity` 值求和。由於每個員工可能對同一產品有多筆銷售記錄，因此使用 `SUM` 將它們加總。當特定員工和廠區沒有銷售某產品時，該欄位會返回 0。 3. **`GROUP BY Staff, Site`**： 按照 `Staff` 和 `Site` 進行分組，確保每個員工在每個廠區的產品數量統計到各自的行中，模擬出「雙標籤」的效果。 執行此程序會得到以下結果(應與SQL Server相同或類似，但版本不可能可能會長的不太一樣，這邊特別感謝讀者 *林小魚* 提醒> <)： | Staff | Site | Apple11 | Note10+ | Zenfone6 | |---------|-------|---------|---------|----------| | Alice | SiteA | 10 | 5 | 0 | | Bob | SiteB | 7 | 0 | 12 | | Charlie | SiteA | 0 | 9 | 6 | 這樣即可模擬 `PIVOT` 效果，並實現「Staff」、「Site」雙標籤的樞紐分析。 ### 2-4 總結與小心得: 我認為就「樞紐分析」的實現而言，excel是最快、且入手門檻最低的。可是我並表示我們沒有學 SQL 的必要，畢竟 excel 就幾乎無法做為 **大數據** 之資料庫使用，這樣若你想用 excel 實現SQL 資料的樞紐分析時，還要先將 SQL 資料以 試算表 形式輸出，或是考慮兩者的掛接與整合，如此一來 excel 就不見得比較快了。 而 SQL Server 和 MySQL 還是一個比較主流的技巧，該使用哪個? 我認為沒差，它們兩者的程式邏輯與語法很多是類似，甚至完全一樣的，且要將資料在兩者間轉換或是實現掛接，超容易的，點選幾個鍵就完成了，基本上沒難度的 ! 主要還是看各位使用者的習慣咯 ! 但不論是哪種程式語言，都需要常常去練習的~ 還是建議大家可以抓抓網路上的題目或專案做做看，練習自己的編程能力。 以下附上 MySQL 經典的 50 道練習題，大家有空去練習看看吧! >50道MySQL練習題: >https://hackmd.io/@_7vFEnkKTve5g-aFhT8EvQ/Sy-H0QeWr#50%E9%81%93MySQL%E7%B7%B4%E7%BF%92%E9%A1%8C