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，我們下個教學見！
此篇文章會顯示動態置底廣告
為什麼會看到廣告
14.7K會員
148內容數
簡潔，快速，有效，
讓你的日常生活、工作生產力大提升！
＿＿＿
快按「加入」，馬上追蹤所有喜特先生的更新，有 Google 試算表教學、Google Apps Script 的研究、數據分析課程的開箱，還有 Google 試算表疑難雜症的解題分享唷！💪
留言0
查看全部
你可能也想看
Google News 追蹤
大家好，我是woody，是一名料理創作者，非常努力地在嘗試將複雜的料理簡單化，讓大家也可以體驗到料理的樂趣而我也非常享受料理的過程，今天想跟大家聊聊，除了料理本身，料理創作背後的成本。
哈囉～很久沒跟各位自我介紹一下了～
大家好～我是爺恩
我是一名圖文插畫家，有追蹤我一段時間的應該有發現爺恩這個品牌經營了好像.....快五年了(汗)時間過得真快！隨著時間過去，創作這件事好像變得更忙碌了，也很開心跟很多厲害的創作者以及廠商互相合作幫忙，還有最重要的是大家的支持與陪伴🥹。
嘿，大家新年快樂~ 新年大家都在做什麼呢？
跨年夜的我趕工製作某個外包設計案，在工作告一段落時趕上倒數。
然後和兩個小孩過了一個忙亂的元旦。在深夜時刻，看到朋友傳來的解籤網站，興致勃勃熬夜體驗了一下，覺得非常好玩，或許有人玩過了，但還是想寫上來分享紀錄一下~
※ 為什麼需要 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)。
※ 語法：要由兩部分構成，第一部分是要 "拿什麼" 資料 (若有多項用逗號隔開)；第二部分則為
大家好，我是woody，是一名料理創作者，非常努力地在嘗試將複雜的料理簡單化，讓大家也可以體驗到料理的樂趣而我也非常享受料理的過程，今天想跟大家聊聊，除了料理本身，料理創作背後的成本。
哈囉～很久沒跟各位自我介紹一下了～
大家好～我是爺恩
我是一名圖文插畫家，有追蹤我一段時間的應該有發現爺恩這個品牌經營了好像.....快五年了(汗)時間過得真快！隨著時間過去，創作這件事好像變得更忙碌了，也很開心跟很多厲害的創作者以及廠商互相合作幫忙，還有最重要的是大家的支持與陪伴🥹。
嘿，大家新年快樂~ 新年大家都在做什麼呢？
跨年夜的我趕工製作某個外包設計案，在工作告一段落時趕上倒數。
然後和兩個小孩過了一個忙亂的元旦。在深夜時刻，看到朋友傳來的解籤網站，興致勃勃熬夜體驗了一下，覺得非常好玩，或許有人玩過了，但還是想寫上來分享紀錄一下~
※ 為什麼需要 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)。
※ 語法：要由兩部分構成，第一部分是要 "拿什麼" 資料 (若有多項用逗號隔開)；第二部分則為