2021-08-01|閱讀時間 ‧ 約 9 分鐘

QUERY 函式大解析（四）：進階 SELECT、GROUP BY

今天要介紹的是進階的 SELECT 功能，可以即時對 QUERY 的結果運算，迅速取得數值的平均、總和、最大值、最小值和數量，省去拉資料透視表（pivot table）的麻煩！

如果還沒看過 QUERY 是什麼，還不知道 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，我們下個教學見！

分享至

成為作者繼續創作的動力吧！

討論區

追蹤

QUERY 函式大解析（四）：進階 SELECT、GROUP BY

進階 SELECT：聚集函數

GROUP BY...

最後的美中不足：沒有 HAVING！

作者的相關文章

喜特先生官方沙龍的其他內容

你可能也想看

發表回應

QUERY 函式大解析（四）：進階 SELECT、GROUP BY

進階 SELECT：聚集函數

GROUP BY...

最後的美中不足：沒有 HAVING！

作者的相關文章

喜特先生官方沙龍 的其他內容

你可能也想看

發表回應

喜特先生官方沙龍的其他內容