QUERY 函式大解析（四）：進階 SELECT、GROUP BY
2021/08/01閱讀時間約 9 分鐘
今天要介紹的是進階的 SELECT 功能，可以即時對 QUERY 的結果運算，迅速取得數值的平均、總和、最大值、最小值和數量，省去拉資料透視表（pivot table）的麻煩！
如果還沒看過 QUERY 是什麼，還不知道 QUERY 強大的威力在哪的話，我在方格子上寫了一系列使用 QUERY 的教學文章，也歡迎你來看看：
先來複習一下 SELECT 的語法：
=QUERY(資料, "SELECT A, B, C, D... ")
在 SELECT 後面要寫上想要回傳的欄位字母，如 A、B、C、D 等。你也可以視狀況安排順序，也可以是 B、A、D、C，看你需求而定。
也順便複習一下 WHERE：
=QUERY(資料, "SELECT A, B, C, D WHERE B = '台灣'")
WHERE 會寫在 SELECT 後面，限縮回傳結果，只回傳符合條件的資料。這個例子就是只有當資料的 B 欄是「台灣」的時候，回傳欄 A、欄 B、欄 C、欄 D 的意思。
我把這次示範的 Google 試算表放在這邊，歡迎你複製一份，跟著一起做唷！
進階 SELECT：聚集函數
來瞧瞧我們的本日主軸！我們有 SUM()、AVG()、COUNT()、MAX()、MIN() 這五個功能可以用。這些指令後面都跟著一對小括號 ()，我們要在括號裡面填入想要做運算的欄位字母，像這樣：
=QUERY(資料, "SELECT SUM(A)...")=QUERY(資料, "SELECT AVG(B)...")=QUERY(資料, "SELECT COUNT(C)...")=QUERY(資料, "SELECT MAX(D)...")=QUERY(資料, "SELECT MIN(D)...")
來快速了解一下這些函式的定義：
- SUM()：回傳指定欄位內所有數字的總和。
- AVG()：回傳指定欄位內所有數字的平均值。
- COUNT()：回傳指定欄位內所有含有資料的儲存格數量。
- MAX()：回傳指定欄位內最大值的資料。
- MIN()：回傳指定欄位內最小值的資料。
上面這五個東西，我們在這篇文章就叫姑且叫做「聚集函數（aggregate function）」！來看看範例：
上圖是某三個小吃店賣的菜跟價格。我們可以用上面提到的這些語法來求得整張表價格（Ｃ欄）的總和、平均、資料數、最貴的價格和最便宜的價格。用 QUERY 來呈現就是：
=QUERY(A:C, "SELECT SUM(C)")=QUERY(A:C, "SELECT AVG(C)")=QUERY(A:C, "SELECT COUNT(C)")=QUERY(A:C, "SELECT MAX(C)")=QUERY(A:C, "SELECT MIN(C)")
就會得到以下結果：
...欸不是啊，這個我不用 QUERY 也做得出來吧！
沒錯，我也這麼覺得（笑）這些交給一般的 SUM、AVERAGE、COUNT、MAX、MIN 就可以了。但是！如果今天想要快速查詢這些東西的時候：
- 「各家店賣的項目」的價格總和、價格平均、數量、價格最大值、價格最小值
- 「各個品項」的價格總和、價格平均、數量、價格最大值、價格最小值
我們在 Excel 可能會用到樞紐分析表、 Google Spreadsheet 的話會用到資料透視表，指定欄位跟價格。效果會像這樣：
雖然這樣也可以，但是比起資料透視表那樣滑鼠猛點、設定東西，其實還有更進階、更帥氣、更便捷的方法！
隆重介紹 ––––
GROUP BY...
顧名思義，GROUP BY... 就是「把資料依～分組」的意思。在上面的資料透視表裡面，我們把資料按照小吃店分組，列出各個小吃店的項目總額加總起來；換作 GROUP BY 的想法，你可以看成「GROUP BY 小吃店」，也就是「GROUP BY A 欄」。那麼，在 QUERY 的語法裡，我們會這樣寫：
=QUERY(A:C, "SELECT A, SUM(C) GROUP BY A")
翻譯成中文就是「輸出 A 欄、C 欄的總和，並且把結果以 A 來分組」。就會得到以下結果：
只寫一行指令！把製作資料透視表的時間都省起來了！帥氣！
至於執行結果的排序是由 GROUP BY 的欄位來決定的，預設是遞增排序唷！但可惜目前 QUERY 不太認識中文，沒辦法完全以我們習慣的注音符號排序、拼音排序、筆劃等等方式排序。但如果你的資料是英文或數字的話，應該會好好從 A 到 Z、或是由小到大的方式排好。
那我們這次來試試看如果用「品項」（B 欄）分組。我想要求的是依照品項分組、得到各品項的平均價格，就會是：
=QUERY(A:C, "SELECT B, AVG(C) GROUP BY B")
注意，因為我想要顯示各個品項 B 欄的結果，這邊 SELECT 已經不會是 A 欄的小吃店，而該改成 B 欄的品項了！那麼我們就會得到：
太棒了，搞定！你也當然可以對搜尋結果做 WHERE 的篩選，以及 GROUP BY 的排序。我們再看看幾個例子：
=QUERY(A:C, "SELECT A, SUM(C) WHERE A = '甲' GROUP BY A")
選擇 A 欄、C 欄的總和，只出現「甲」店小吃店的結果，並且以小吃店店家做分類：
=QUERY(A:C,"SELECT B, AVG(C)WHERE B = '炒飯' OR B = '炒麵'GROUP BY B")
選擇 B 欄、C 欄的平均，只出現品項是炒飯或炒麵的結果，並且以品項來做分類：
最後的美中不足：沒有 HAVING！
雖然有 WHERE 可以限制某欄位的特定條件，回傳篩選過後的結果，但如果我想要限制某個聚集函數（剛剛看到的 SUM()、AVG()、COUNT()、MAX()、MIN()），例如「只回傳價格平均超過 70 塊的品項」、「只回傳價格最大值小於 60 塊的小吃店」，這樣的特定條件，有辦法做到嗎？
嗚，不行 (ﾉω･､)...
沒錯，截至目前為止（2021 年 8 月），Google 的 Query 目前還不支援聚合函數的條件查詢（這就是 SQL 的 HAVING）！目前在江湖廣為人知的變通方法就是巢狀 QUERY：先用一層 QUERY 包覆起來，對第一次的 QUERY 進行搜尋，並用 WHERE 限制條件。
上個範例好了！我們想要找的資訊是：
- 小吃店各品項的平均價格
- 並以小吃店作為分組
- 只回傳平均價格大於 60 的小吃店（←聚合函數的條件查詢）
截至前兩點，我們可以用剛剛學到的 AVG() 和 GROUP BY 來寫出：
=QUERY(A:C, "SELECT A, AVG(C) GROUP BY A")
就會得到：
但如果我們只要回傳平均價格大於 60 的店，就得用第二個 QUERY 包在外面，用 WHERE 回傳「avg 價格 > 60」的方式來篩選出最後結果。大致上是這個概念（外包的 QUERY 以粗體顯示）：
=QUERY
(QUERY
(A:C, "SELECT A, AVG(C) GROUP BY A"),
"SELECT * WHERE Col2 > 60")
結果放這邊：
由於內層 QUERY 的結果欄沒辦法以欄位字母顯示，只能用 Col2（代表第二欄）這個關鍵字進行搜尋。是不是有點小麻煩？我也覺得！還是希望 Google 可以早日推出 HAVING，節省節省大家時間 ԅ(º﹃ºԅ)
資料透視表（Pivot table）或許也很方便，但如果要爬取大量的資料、或在 Google 試算表上面已經有太多的資料透視表正在使用中了，再加上一個表都有可能成為負擔。這時，用 QUERY 做進階 SELECT 和 GROUP BY 不僅有幾乎相同的功效，執行效率也更好。
如果你喜歡這次的文章，歡迎你透過這些方法支持我：
・按下愛心、按下儲存
・留言告訴我你的想法
・加入喜特先生的官方沙龍，即時看到我發布的教學
・付費訂閱喜特先生的官方沙龍，加入每月小額訂閱方案
・追蹤喜特先生的 Facebook
・按這邊小額贊助我的創作！
想要看更多文章，歡迎來到我的 Notion 頁面找找有沒有你需要的資源喔！
我是喜特先生，Mr. Sheet，我們下個教學見！
留言0
查看全部
