# 樞紐分析在 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)」 是 **<font color='red' size=5> 不一樣 </font>** 的!!!!
> 在數學中，尤其是線性代數或數值方法領域，「樞紐分析」通常指的是樞紐操作（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):【框選整個表格】 ->
選擇您要放置樞紐分析表的位址 -> 確定
此時你的畫面應該會類似這樣:
我們可以看到 excel 中，把所有變數分為了 "列" 、 "欄" 、 "值" 、 "篩選" 四種。此時我們會將
>「樞紐變數」 放入 "列"；
>「目標變數」 放入 "值"；
>「目標變數類別或副樞紐變數」 放入 "行"。
>
而 "篩選" 是為樞紐表附加所引用的，其機制在 SQL Server 和 MySQL 中，比較特殊，我們下篇在詳細說明吧 !
該範例中的「樞紐變數」為 Staff 與 Site 雙標籤放在 "列"，「目標變數」 Quantity 放在 "值"，然後 Product 放在 "行"，為其做類，根據以上概念設定，我們的樞紐分析表就出來了:
接著加上解釋，就是我們的樞紐分析了!
啊有些朋友會問，如果要求 mean、min、max 等聚合函數呢?
其實在 excel 我們中，只要點選各變數右邊的符號 "v" (如下圖)，再點選 "欄位值設定" 就能自行設定了!
### 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
Sign in via Google
Sign in via Facebook
Sign in via X(Twitter)
Sign in via GitHub
Sign in via Dropbox
Sign in with Wallet
Connect another wallet