QUERY 函式大解析（四）：進階 SELECT、GROUP BY
今天要介紹的是進階的 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，我們下個教學見！
15.1K會員
149內容數
簡潔，快速，有效，
讓你的日常生活、工作生產力大提升！
＿＿＿
快按「加入」，馬上追蹤所有喜特先生的更新，有 Google 試算表教學、Google Apps Script 的研究、數據分析課程的開箱，還有 Google 試算表疑難雜症的解題分享唷！💪
留言0
查看全部
你可能也想看
Google News 追蹤
在創作的路上真的很多人問我說
到底要怎麼做出符合自己期待
但又可以表現得很有美感的作品？🥹
這個問題真的應該是每個創作者都一直在學習的課題吧！
提問的內容越是清晰，強者、聰明人越能在短時間內做判斷、給出精準的建議，他們會對你產生「好印象」，認定你是「積極」的人，有機會、好人脈會不自覺地想引薦給你
※ 為什麼需要 Subquery？
當⼀個任務需要多個 Query 完成任務，可以使⽤ Subquery 把多個 Query 合併成⼀個 Query。
當我們在進行SQL查詢時，每次查詢都需要在Web Server和資料庫之間來回傳遞資料。這個過程會產生網路延遲，特別是當兩者之間的物理距離較遠時
※ GROUP BY
用於將數據表中的數據按照一個或多個列進行分組。例如在處理一個表格的資料時，可以指定欄位，一個或是多個，然後把將其視為ID進行分組處理。
※ 語法
SELECT column1, column2, ..., aggregate_function(column)
FROM
※ 什麼是WHERE？
使用 WHERE來設定條件，可以幫助我們縮小查詢結果的範圍，取得想要的結果。
※ 語法：
※ 解析順序：
From：先看是哪一張table→table裡面符合Where指定條件的record→再看Select指定的是那些欄位→再根據那個欄位進行排序。
※ 使⽤⽅
※ 什麼是ORDER BY？
可以讓SELECT出來的結果，根據你想要的方式排序。簡單說，用於對查詢結果進行排序。
※ 語法：
SELECT select_list
FROM table_name
ORDER BY
column1 [ASC|DESC],
column2 [ASC|DESC]
※ 語法
SELECT select_list
FROM table_name
※ 解析順序
From：從哪裡拿？
SELECT：要 "拿什麼" 資料？
※ 使用場景：
Single column（單一欄位）：
Multiple column（多個欄位）：
All colu
在進行SQL查詢邏輯更改時，需要適當地使用SubQuery和join來達到新的排序需求。本文將介紹原本的撈取邏輯、需求以及如何使用SubQuery來解決新的排序需求。
高效生活，幫助你找回更多自己的時間
歡迎來到 AL 的 Googlesheet 學習筆記系列文章。在這個系列中，我們將一步步介紹各種函數，並將它們應用於日常生活中，加速工作、提高效率。
今天要介紹的是使用 Index 、 Counta 函數尋找最後一列的資料！
如何在SQL實踐中EXCEL 常用功能 篩選 和 擷取文字串？需要熟練地使用分組(GROUP BY) 與 排序 (ORDER BY) 以及SUBSTRING_INDEX函數！
在POWER QUERY從0到1 #6，就有介紹過資料合併這個功能。
#6 從0到1的POWER QUERY 資料合併 神似VLOOKUP但比他好用100倍
資料合併很神似函數的VLOOKUP，但除了單純以VLOOKUP方式查找合併資料之外，總共有6種不同的合併方式。
用一個簡單的範例來做
※ 基本操作：SQL 語法，SELECT, WHERE, CREATE, UPDATE, DELETE。
SELECT：從資料庫中或資料表中指定要選擇的欄位中取得資料，稱之為查詢 (query)。
※ 語法：要由兩部分構成，第一部分是要 "拿什麼" 資料 (若有多項用逗號隔開)；第二部分則為
在創作的路上真的很多人問我說
到底要怎麼做出符合自己期待
但又可以表現得很有美感的作品？🥹
這個問題真的應該是每個創作者都一直在學習的課題吧！
提問的內容越是清晰，強者、聰明人越能在短時間內做判斷、給出精準的建議，他們會對你產生「好印象」，認定你是「積極」的人，有機會、好人脈會不自覺地想引薦給你
※ 為什麼需要 Subquery？
當⼀個任務需要多個 Query 完成任務，可以使⽤ Subquery 把多個 Query 合併成⼀個 Query。
當我們在進行SQL查詢時，每次查詢都需要在Web Server和資料庫之間來回傳遞資料。這個過程會產生網路延遲，特別是當兩者之間的物理距離較遠時
※ GROUP BY
用於將數據表中的數據按照一個或多個列進行分組。例如在處理一個表格的資料時，可以指定欄位，一個或是多個，然後把將其視為ID進行分組處理。
※ 語法
SELECT column1, column2, ..., aggregate_function(column)
FROM
※ 什麼是WHERE？
使用 WHERE來設定條件，可以幫助我們縮小查詢結果的範圍，取得想要的結果。
※ 語法：
※ 解析順序：
From：先看是哪一張table→table裡面符合Where指定條件的record→再看Select指定的是那些欄位→再根據那個欄位進行排序。
※ 使⽤⽅
※ 什麼是ORDER BY？
可以讓SELECT出來的結果，根據你想要的方式排序。簡單說，用於對查詢結果進行排序。
※ 語法：
SELECT select_list
FROM table_name
ORDER BY
column1 [ASC|DESC],
column2 [ASC|DESC]
※ 語法
SELECT select_list
FROM table_name
※ 解析順序
From：從哪裡拿？
SELECT：要 "拿什麼" 資料？
※ 使用場景：
Single column（單一欄位）：
Multiple column（多個欄位）：
All colu
在進行SQL查詢邏輯更改時，需要適當地使用SubQuery和join來達到新的排序需求。本文將介紹原本的撈取邏輯、需求以及如何使用SubQuery來解決新的排序需求。
高效生活，幫助你找回更多自己的時間
歡迎來到 AL 的 Googlesheet 學習筆記系列文章。在這個系列中，我們將一步步介紹各種函數，並將它們應用於日常生活中，加速工作、提高效率。
今天要介紹的是使用 Index 、 Counta 函數尋找最後一列的資料！
如何在SQL實踐中EXCEL 常用功能 篩選 和 擷取文字串？需要熟練地使用分組(GROUP BY) 與 排序 (ORDER BY) 以及SUBSTRING_INDEX函數！
在POWER QUERY從0到1 #6，就有介紹過資料合併這個功能。
#6 從0到1的POWER QUERY 資料合併 神似VLOOKUP但比他好用100倍
資料合併很神似函數的VLOOKUP，但除了單純以VLOOKUP方式查找合併資料之外，總共有6種不同的合併方式。
用一個簡單的範例來做
※ 基本操作：SQL 語法，SELECT, WHERE, CREATE, UPDATE, DELETE。
SELECT：從資料庫中或資料表中指定要選擇的欄位中取得資料，稱之為查詢 (query)。
※ 語法：要由兩部分構成，第一部分是要 "拿什麼" 資料 (若有多項用逗號隔開)；第二部分則為