QUERY 函式大解析（五）：PIVOT 給你一雙透視眼
2021/08/08閱讀時間約 9 分鐘
終於邁入了 QUERY 系列的第五篇文章了！到這你可以說是很厲害的 QUERY 大師，能把一大筆資料玩弄在手掌心了。這次要介紹的是 QUERY 系列文的最後一個大語法：PIVOT，讓你可以更進一步切入分析 SELECT 出來的資訊。
在開始之前，我會建議你先看過前一篇 QUERY 函式大解析（四）：進階 SELECT、GROUP BY，了解聚集函數（Sum、Avg、Count、Max、Min）和 GROUP BY 的使用規則再回來試做，會更有概念。
如果還沒看過 QUERY 是什麼，還不知道 QUERY 強大的威力在哪的話，我在方格子上寫了一系列使用 QUERY 的教學文章，也歡迎你來看看：
所以 PIVOT 是什麼？
PIVOT 原意是「扭轉」、「樞紐」、「中樞」的意思，你也可以看成一雙透視眼鏡，可以透過 PIVOT 看到聚集 SELECT 內的細部資訊，並且可以指定用某欄位來切割視角。
PIVOT 的應用範圍也很廣，可以用它來做圖表、快速取得聚集資訊、效果跟資料透視表（pivot table）很接近！
這邊放上這次範例出現的試算表給大家複製，歡迎取用～
例如說我現在有這樣的一筆員工資料：
如果今天我想要知道 F 欄的所有公司裡，所有員工的平均薪資的話，我可以做：
=QUERY(A:F, "SELECT F, AVG(E) GROUP BY F")
我就會得到：
但如果今天還想再從部門、年齡區間切入，取得平均薪資（avg 薪水）的資訊，還可以怎麼做呢？這就是 PIVOT 可以進一步做的事了。
像是這是以部門的視角切入透視，得到的資訊：
再來，以年齡區間的視角切入透視，得到的的資訊：
是不是很像我們熟悉的資料透視表（pivot table）？馬上來看看怎麼做吧！
語法
PIVOT 的語法其實很單純，如下：
=QUERY(資料, "SELECT 聚集函數(##) PIVOT ####")
## 代表欄位字母。所以如果你想要以Ａ欄的視角，透視 B 欄的總和，就會是：
=QUERY(資料, "SELECT SUM(B) PIVOT A")
以我們剛剛的員工資料表為例：
如果想知道各個公司的平均薪資，那就是：AVG(薪水)、PIVOT 公司。也就是：
=QUERY(A:F, "SELECT AVG(E) PIVOT F")
執行結果就會是：
其實執行結果跟 GROUP BY 差不多，只是表格方向有一點不太一樣就是了。跟 GROUP BY 的語法和執行結果比較一下：
=QUERY(A:F, "SELECT F, AVG(E) GROUP BY F")
其實計算結果相同，都 OK 的！如果你用了 PIVOT，比較喜歡把公司資訊放在一欄、平均薪水放在另一欄，你可以在 QUERY 前面都加一個 TRANSPOSE 函式，把行轉換成欄、欄轉換成行：
=TRANSPOSE(QUERY(A:F, "SELECT AVG(E) PIVOT F"))
就會得到：
對聚集函數透視！
接下來就是本篇的重點：對聚集函數（Sum、Avg、Count、Max、Min）做透視。其實語法也很單純，就是要記得把 PIVOT 放在 GROUP BY 後面就是了。
=QUERY(A:F,"SELECT 首欄 ##, AGG(##), ... GROUP BY ## PIVOT ###")
（AGG(##) 代表聚集函數，請自由代換；# 代表欄位字母）
這邊欄位字母變多了，該要填什麼才好呢？先釐清一下各個語法的欄位意義到底是什麼吧。假如手上有一份資料長這樣：
你可以用班級或是性別來作為切入視角，去透視期末考總分的不同樣貌。那麼：
- 首欄 ##：GROUP BY 的標題欄位，GROUP BY 寫什麼欄位這邊就寫什麼。填這個會顯示出 GROUP BY 分組的標題和分組內容，如果不寫的話，就會沒有標題。
- AGG(##)：你要執行聚集函數（SUM、AVG、COUNT、MAX、MIN）。你的資料的 C 欄有某考試的總分，那你就可以用 AVG (C) 取得 C 欄的平均，也就是某考試的平均分數。
- GROUP BY ##：你希望你的聚集函數結果以「##」為分組、且組別會出現在「同一欄」上的意思。我想要看到 C 欄某考試的全校平均分數、還要看到各個班（B 欄）的班平均，那就會用 GROUP BY B 來分組。
順帶一提，第一個 ## 的部分也會填 B 喔！也就是 B, AVG (C) GROUP BY B。 - PIVOT ##：在 GROUP BY 的組別裡面，再用「##」為視角切入來查詢資訊，且這視角的名稱會出現在「同一列」上。再接著上面的例子，我想要知道各個班級內男女之間的平均總分數，所以我的角度就是 D 欄的性別了，所以我就會寫 PIVOT D。
簡單說，GROUP BY 的資料會出現在同一欄上、PIVOT 則是同一列上。
我們回到這篇文章開頭的例子，再附上要查詢的 QUERY 的資料：
假設我們今天要找的是，各公司裡各部門的平均薪資。如果你覺得有點難判斷要下什麼欄位字母，我們按照剛剛的思路走：
- 首欄 ##：GROUP BY 的標題欄位，GROUP BY 寫什麼欄位這邊就寫什麼。
➡️ 嗯，還不知道，先空著。 - AGG(##)：執行聚集函數。
➡️ 我要找平均薪資，所以就是 AVG(E)。 - GROUP BY ##：聚集函數結果以「##」為分組，組別在同一欄。
➡️ 我想要以公司作為分組，所以是 GROUP BY F。
➡️ 所以首欄就是 F。 - PIVOT ##：在 GROUP BY 的組別裡面，再用「##」為角度切入來查詢資訊，且這視角會出現在「同一列」上。
➡️ 我要進一步用部門來切入取得資訊，所以是 PIVOT B。
用神秘拼裝魔法湊起來就會得到：
=QUERY(A:F,"SELECT F, AVG(E) GROUP BY F PIVOT B")
結果如下：
搞定！⎝( OωO)⎠
試試看第二個角度，用年齡區間來切入查詢資訊吧：
要找的是，各個公司裡各個年齡區間的平均薪資：
- 首欄 ##：GROUP BY 的標題欄位，GROUP BY 寫什麼欄位這邊就寫什麼。
➡️ 嗯，還不知道，還是先空著。 - AGG(##)：執行總和、平均、含有資料的儲存格個數、最大值、最小值的欄位。 ➡️ 我要找平均薪資，所以就是 AVG(E)。
- GROUP BY ##：聚集函數結果以「##」為分組，組別在同一欄。
➡️ 我想要以公司作為分組，所以是 GROUP BY F。
➡️ 所以首欄就是 F。 - PIVOT ##：在 GROUP BY 的組別裡面，再用「##」為角度切入來查詢資訊，且這視角會出現在「同一列」上。
➡️ 我要進一步用年齡層區間來切入取得資訊，所以是 PIVOT D。
拼拼裝裝就會得到這個語法：
=QUERY(A:F,"SELECT F, AVG(E) GROUP BY F PIVOT D")
結果就會是！
最後小叮嚀
我們當然可以用其他的語法跟 PIVOT 的搜尋結果互動，例如再進一步用 WHERE 去限縮條件、用 LIMIT 指定輸出行數、用 ORDER BY、用 OFFSET 都可以。但是記得，順序要寫對！Google QUERY 對於語法的順序比較嚴格，必須要按照下面的順序來寫：
SELECT / WHERE / GROUP BY / PIVOT / ORDER BY / LIMIT / OFFSET / LABEL
如果不照上面的順序寫的話，QUERY 會直接跑不出結果！
以上就是 PIVOT 的教學了。QUERY 已經幾乎被我們看完囉！要下字母的時候往往會猶豫一下，但是對自己有耐心、照著思路慢慢想的話，應該都會沒問題的！
直接做資料透視表（Pivot Table）也是個好方法，但表太多的話，也會讓整個工作表的載入效率變低，這時候 PIVOT 就是個很方便的選擇！(灬ºωº灬)
如果你喜歡這次的文章，歡迎你透過這些方法支持我：
・按下愛心、按下儲存
・留言告訴我你的想法
・加入喜特先生的官方沙龍，即時看到我發布的教學
・付費訂閱喜特先生的官方沙龍，加入每月小額訂閱方案
・追蹤喜特先生的 Facebook
・按這邊小額贊助我的創作！
想要看更多文章，歡迎來到我的 Notion 頁面找找有沒有你需要的資源喔！
我是喜特先生，Mr. Sheet，我們下個教學見！
留言0
查看全部
