QUERY 函式大解析（四）：進階 SELECT、GROUP BY
閱讀時間約 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，我們下個教學見！
11.7K會員
147內容數
簡潔，快速，有效，
讓你的日常生活、工作生產力大提升！
＿＿＿
快按「加入」，馬上追蹤所有喜特先生的更新，有 Google 試算表教學、Google Apps Script 的研究、數據分析課程的開箱，還有 Google 試算表疑難雜症的解題分享唷！💪
留言0
查看全部
你可能也想看
Google News 追蹤
這個秋，Chill 嗨嗨！穿搭美美去賞楓，裝備款款去露營⋯⋯你的秋天怎麼過？秋日 To Do List 等你分享！
秋季全站徵文，我們準備了五個創作主題，參賽還有機會獲得「火烤兩用鍋」，一起來看看如何參加吧～
美國總統大選只剩下三天， 我們觀察一整週民調與金融市場的變化（包含賭局）， 到本週五下午3:00前為止， 誰是美國總統幾乎大概可以猜到60-70%的機率， 本篇文章就是以大選結局為主軸來討論近期甚至到未來四年美股可能的改變
Faker昨天真的太扯了，中國主播王多多點評的話更是精妙，分享給各位
王多多的點評
「Faker是我們的處境，他是LPL永遠繞不開的一個人和話題，所以我們特別渴望在決賽跟他相遇，去直面我們的處境。
我們曾經稱他為最高的山，最長的河，以為山海就是盡頭，可是Faker用他28歲的年齡...
條件資料行是POWER QUERY裡面一個可以設定指定條件，進而判斷符合條件的資料產生指定的內容，就跟EXCEL中的IF函數很像。
但是POWER QUERY的條件資料行，他是利用對話式的視窗，所以不需要自己動手寫函數，就可以順利達到相同的功能。
今天就用1個資料3個題目來學習這個功能
日前在LINE社群，有網友提出一個問題，要把資料進行分析，用日期來計算出將對應的資料。
原始資料，密密麻麻的數據，都看不清楚了
放大一點點
要把這些資料不同『料號』的各種『狀態』依據『日期』進行分析。
有興趣可以下載試著挑戰看看:檔案下載
作法有很多種，當然也可以用函數處
網友提問的一個問題，這個問題其實也蠻常見，資料中相同日期的項目全部合併到同一個儲存格中，來簡化資料的顯示。
這邊分享三種作法，通用的函數法、365函數法、POWER QUERY法
💡通用函數法
通用版本的函數需要輔助欄才可以達成這個需求
輔助欄C2=VLOOKUP(A2,A2:B10,
Media query可以很複雜，但是這裡KP只談最簡單、最實用的基礎。
粉絲提問需求
儲存格中這一段字串:baseccy=usd,baseccyamoun=1300,type=short}
要如何把字串中1300的數字給抓出來，其實方式蠻多的，今天來介紹3種方式
第一種剖析法:
先將資料選取出來之後利用資料剖析，因為資料的規則數字是在=之後與逗號之前，所以可以先將逗號剖
不得不說仁川機場的機場本體建築設計很有設計感，大韓航空跟韓亞航的主場都在仁川機場，尤其是大韓航空KOREAN AIR，在其官網上寫著韓國第一的航空公司，多麼自信的口號，讓人想像著充滿設計感的韓國第一的航空公司貴賓室應該有一些驚艷旅客的設計吧。實際上，到了貴賓室的感覺是驚嚇，因為好像來到東南亞的某個不
如果你正在學習SQL，這邊介紹一個方便簡易的方式，不須透過安裝傳統的MySQL，只要利用Google提供的BigQuery開源示範dataset就可以實作練習，另一方面，如果你想應用自己手邊上的資料，也能簡單的使用匯入功能（如何匯入看這邊）。
輸入名稱「data-to-insights」。
在【刁民國】一定要買房，不然就會變成「社會邊緣倫」，與財富重分配絕緣，而且你一定要買「會賺錢」的房子，那種拿來【遮風避雨】的房子，求求你千萬不要買，不如租房子，剩下錢出國旅遊，充實自己總比住在【財務地獄】好。
在這個【刁民寶島】，拿房地產來抵抗通貨膨脹？～就你腦袋進水信這套～有錢人當然為了賺大錢。
又到了愉悅的蕾蕾週五影院
而且是連續假期的週五，真是太令人開心了👏🎉
週五電影-旅遊篇暫時告一段落
上週看完金鐘獎，決定要來開啟台灣篇
好好的介紹一下台灣優秀的影劇們 （Netflix前三都是韓片有點Sad🥲）
第一部就獻給本次得獎的大債時代。
Netflix 上面有哦
※劇透 這篇文章盡可能解析女主角昭容王妃、男主角哲宗皇帝以及男神張奉煥三人的角色關係與變化，內容較多還請大家耐心看完。情感的導師、扶持的戰友、陪伴的摯友、曖昧的情人、牽掛的靈魂，這些身分都是在《哲仁王后》中一位身兼多職的角色，沒錯，就是....
這個秋，Chill 嗨嗨！穿搭美美去賞楓，裝備款款去露營⋯⋯你的秋天怎麼過？秋日 To Do List 等你分享！
秋季全站徵文，我們準備了五個創作主題，參賽還有機會獲得「火烤兩用鍋」，一起來看看如何參加吧～
美國總統大選只剩下三天， 我們觀察一整週民調與金融市場的變化（包含賭局）， 到本週五下午3:00前為止， 誰是美國總統幾乎大概可以猜到60-70%的機率， 本篇文章就是以大選結局為主軸來討論近期甚至到未來四年美股可能的改變
Faker昨天真的太扯了，中國主播王多多點評的話更是精妙，分享給各位
王多多的點評
「Faker是我們的處境，他是LPL永遠繞不開的一個人和話題，所以我們特別渴望在決賽跟他相遇，去直面我們的處境。
我們曾經稱他為最高的山，最長的河，以為山海就是盡頭，可是Faker用他28歲的年齡...
條件資料行是POWER QUERY裡面一個可以設定指定條件，進而判斷符合條件的資料產生指定的內容，就跟EXCEL中的IF函數很像。
但是POWER QUERY的條件資料行，他是利用對話式的視窗，所以不需要自己動手寫函數，就可以順利達到相同的功能。
今天就用1個資料3個題目來學習這個功能
日前在LINE社群，有網友提出一個問題，要把資料進行分析，用日期來計算出將對應的資料。
原始資料，密密麻麻的數據，都看不清楚了
放大一點點
要把這些資料不同『料號』的各種『狀態』依據『日期』進行分析。
有興趣可以下載試著挑戰看看:檔案下載
作法有很多種，當然也可以用函數處
網友提問的一個問題，這個問題其實也蠻常見，資料中相同日期的項目全部合併到同一個儲存格中，來簡化資料的顯示。
這邊分享三種作法，通用的函數法、365函數法、POWER QUERY法
💡通用函數法
通用版本的函數需要輔助欄才可以達成這個需求
輔助欄C2=VLOOKUP(A2,A2:B10,
Media query可以很複雜，但是這裡KP只談最簡單、最實用的基礎。
粉絲提問需求
儲存格中這一段字串:baseccy=usd,baseccyamoun=1300,type=short}
要如何把字串中1300的數字給抓出來，其實方式蠻多的，今天來介紹3種方式
第一種剖析法:
先將資料選取出來之後利用資料剖析，因為資料的規則數字是在=之後與逗號之前，所以可以先將逗號剖
不得不說仁川機場的機場本體建築設計很有設計感，大韓航空跟韓亞航的主場都在仁川機場，尤其是大韓航空KOREAN AIR，在其官網上寫著韓國第一的航空公司，多麼自信的口號，讓人想像著充滿設計感的韓國第一的航空公司貴賓室應該有一些驚艷旅客的設計吧。實際上，到了貴賓室的感覺是驚嚇，因為好像來到東南亞的某個不
如果你正在學習SQL，這邊介紹一個方便簡易的方式，不須透過安裝傳統的MySQL，只要利用Google提供的BigQuery開源示範dataset就可以實作練習，另一方面，如果你想應用自己手邊上的資料，也能簡單的使用匯入功能（如何匯入看這邊）。
輸入名稱「data-to-insights」。
在【刁民國】一定要買房，不然就會變成「社會邊緣倫」，與財富重分配絕緣，而且你一定要買「會賺錢」的房子，那種拿來【遮風避雨】的房子，求求你千萬不要買，不如租房子，剩下錢出國旅遊，充實自己總比住在【財務地獄】好。
在這個【刁民寶島】，拿房地產來抵抗通貨膨脹？～就你腦袋進水信這套～有錢人當然為了賺大錢。
又到了愉悅的蕾蕾週五影院
而且是連續假期的週五，真是太令人開心了👏🎉
週五電影-旅遊篇暫時告一段落
上週看完金鐘獎，決定要來開啟台灣篇
好好的介紹一下台灣優秀的影劇們 （Netflix前三都是韓片有點Sad🥲）
第一部就獻給本次得獎的大債時代。
Netflix 上面有哦
※劇透 這篇文章盡可能解析女主角昭容王妃、男主角哲宗皇帝以及男神張奉煥三人的角色關係與變化，內容較多還請大家耐心看完。情感的導師、扶持的戰友、陪伴的摯友、曖昧的情人、牽掛的靈魂，這些身分都是在《哲仁王后》中一位身兼多職的角色，沒錯，就是....