QUERY 函式大解析(四):進階 SELECT、GROUP BY

更新於 發佈於 閱讀時間約 9 分鐘

今天要介紹的是進階的 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)」!來看看範例:

raw-image

上圖是某三個小吃店賣的菜跟價格。我們可以用上面提到的這些語法來求得整張表價格(C欄)的總和、平均、資料數、最貴的價格和最便宜的價格。用 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)")


就會得到以下結果:

raw-image
...欸不是啊,這個我不用 QUERY 也做得出來吧!

沒錯,我也這麼覺得(笑)這些交給一般的 SUM、AVERAGE、COUNT、MAX、MIN 就可以了。但是!如果今天想要快速查詢這些東西的時候:

  • 「各家店賣的項目」的價格總和、價格平均、數量、價格最大值、價格最小值
  • 「各個品項」的價格總和、價格平均、數量、價格最大值、價格最小值

我們在 Excel 可能會用到樞紐分析表、 Google Spreadsheet 的話會用到資料透視表,指定欄位跟價格。效果會像這樣:

raw-image

雖然這樣也可以,但是比起資料透視表那樣滑鼠猛點、設定東西,其實還有更進階、更帥氣、更便捷的方法!

隆重介紹 ––––


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 來分組」。就會得到以下結果:

raw-image

只寫一行指令!把製作資料透視表的時間都省起來了!帥氣!

至於執行結果的排序是由 GROUP BY 的欄位來決定的,預設是遞增排序唷!但可惜目前 QUERY 不太認識中文,沒辦法完全以我們習慣的注音符號排序、拼音排序、筆劃等等方式排序。但如果你的資料是英文或數字的話,應該會好好從 A 到 Z、或是由小到大的方式排好。

那我們這次來試試看如果用「品項」(B 欄)分組。我想要求的是依照品項分組、得到各品項的平均價格,就會是:

=QUERY(A:C, "SELECT B, AVG(C) GROUP BY B")

注意,因為我想要顯示各個品項 B 欄的結果,這邊 SELECT 已經不會是 A 欄的小吃店,而該改成 B 欄的品項了!那麼我們就會得到:

raw-image

太棒了,搞定!你也當然可以對搜尋結果做 WHERE 的篩選,以及 GROUP BY 的排序。我們再看看幾個例子:

=QUERY(A:C, "SELECT A, SUM(C) WHERE A = '甲' GROUP BY A")

選擇 A 欄、C 欄的總和,只出現「甲」店小吃店的結果,並且以小吃店店家做分類:

raw-image
=QUERY(A:C, 
"SELECT B, AVG(C)
WHERE B = '炒飯' OR B = '炒麵'
GROUP BY B")

選擇 B 欄、C 欄的平均,只出現品項是炒飯或炒麵的結果,並且以品項來做分類:

raw-image


最後的美中不足:沒有 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")

就會得到:

raw-image

但如果我們只要回傳平均價格大於 60 的店,就得用第二個 QUERY 包在外面,用 WHERE 回傳「avg 價格 > 60」的方式來篩選出最後結果。大致上是這個概念(外包的 QUERY 以粗體顯示):

=QUERY
  (QUERY
    (A:C, "SELECT A, AVG(C) GROUP BY A"),
  "SELECT * WHERE Col2 > 60")

結果放這邊:

raw-image

由於內層 QUERY 的結果欄沒辦法以欄位字母顯示,只能用 Col2(代表第二欄)這個關鍵字進行搜尋。是不是有點小麻煩?我也覺得!還是希望 Google 可以早日推出 HAVING,節省節省大家時間 ԅ(º﹃ºԅ)


資料透視表(Pivot table)或許也很方便,但如果要爬取大量的資料、或在 Google 試算表上面已經有太多的資料透視表正在使用中了,再加上一個表都有可能成為負擔。這時,用 QUERY 做進階 SELECT 和 GROUP BY 不僅有幾乎相同的功效,執行效率也更好。

如果你喜歡這次的文章,歡迎你透過這些方法支持我:
・按下愛心、按下儲存
・留言告訴我你的想法
・加入喜特先生的官方沙龍,即時看到我發布的教學
・付費訂閱喜特先生的官方沙龍,加入每月小額訂閱方案
・追蹤喜特先生的 Facebook
・按這邊小額贊助我的創作!

想要看更多文章,歡迎來到我的 Notion 頁面找找有沒有你需要的資源喔!

我是喜特先生,Mr. Sheet,我們下個教學見!



留言
avatar-img
留言分享你的想法!
star theSame-avatar-img
2024/09/22
=QUERY(A:X, "SELECT A, SUM(C) GROUP BY A") 不知道為什麼我用我自己的資料會顯示EOF的錯誤,A的資料是2024-08-01 - 2024-08-07,C則是金額
喜特先生 Mr. Sheet -avatar-img
發文者
2024/09/23
star theSame 感謝留言!我在想會不會是 A 欄那邊資料格式的問題,QUERY 處理有日期的資料會比較麻煩一點點QQ 方便透過下面的表單分享給我試算表嗎?我可以幫你看看: https://docs.google.com/forms/d/e/1FAIpQLSf3uPZEbvOXnARSHVD6MTsN6OcxKi_06xVeZCWn7SJehuUitA/viewform
喜特先生 Mr. Sheet -avatar-img
發文者
2024/01/30
QUERY 函式大解析,系列文索引提及了這篇文章,趕快過去看看吧!
avatar-img
喜特先生官方沙龍
18.7K會員
153內容數
簡潔,快速,有效, 讓你的日常生活、工作生產力大提升! ___ 快按「加入」,馬上追蹤所有喜特先生的更新,有 Google 試算表教學、Google Apps Script 的研究、數據分析課程的開箱,還有 Google 試算表疑難雜症的解題分享唷!💪
2025/04/20
請你試著在 Google 試算表的儲存格上打這個,按下 Enter: =WHATTHEFOXSAY() 會有神奇的事情發生喔 ✨
Thumbnail
2025/04/20
請你試著在 Google 試算表的儲存格上打這個,按下 Enter: =WHATTHEFOXSAY() 會有神奇的事情發生喔 ✨
Thumbnail
2024/06/02
上次介紹了 REPLACE 函式,可以用來取代儲存格內的特定文字。其實我們還有一個相似的函式叫 SUBSTITUTE,也有取代的功能,不過當然有一點不一樣的地方!今天會分享語法的範例,還有跟 REPLACE 的差異在哪。一起來看看!
Thumbnail
2024/06/02
上次介紹了 REPLACE 函式,可以用來取代儲存格內的特定文字。其實我們還有一個相似的函式叫 SUBSTITUTE,也有取代的功能,不過當然有一點不一樣的地方!今天會分享語法的範例,還有跟 REPLACE 的差異在哪。一起來看看!
Thumbnail
2024/05/25
這是文字處理基礎函式的第四篇文章,今天要來介紹 REPLACE 函式! REPLACE 可以取代掉儲存格內的文字,今天會分享一下它語法怎麼寫、也有兩個實際應用的案例。一起來看看! REPLACE 語法 REPLACE 的語法長了一點點,有四個參數要設定: =REPLACE(要取代
Thumbnail
2024/05/25
這是文字處理基礎函式的第四篇文章,今天要來介紹 REPLACE 函式! REPLACE 可以取代掉儲存格內的文字,今天會分享一下它語法怎麼寫、也有兩個實際應用的案例。一起來看看! REPLACE 語法 REPLACE 的語法長了一點點,有四個參數要設定: =REPLACE(要取代
Thumbnail
看更多
你可能也想看
Thumbnail
TOMICA第一波推出吉伊卡哇聯名小車車的時候馬上就被搶購一空,一直很扼腕當時沒有趕緊入手。前陣子閒來無事逛蝦皮,突然發現幾家商場都又開始重新上架,價格也都回到正常水準,估計是官方又再補了一批貨,想都沒想就立刻下單! 同文也跟大家分享近期蝦皮購物紀錄、好用推薦、蝦皮分潤計畫的聯盟行銷!
Thumbnail
TOMICA第一波推出吉伊卡哇聯名小車車的時候馬上就被搶購一空,一直很扼腕當時沒有趕緊入手。前陣子閒來無事逛蝦皮,突然發現幾家商場都又開始重新上架,價格也都回到正常水準,估計是官方又再補了一批貨,想都沒想就立刻下單! 同文也跟大家分享近期蝦皮購物紀錄、好用推薦、蝦皮分潤計畫的聯盟行銷!
Thumbnail
每年4月、5月都是最多稅要繳的月份,當然大部份的人都是有機會繳到「綜合所得稅」,只是相當相當多人還不知道,原來繳給政府的稅!可以透過一些有活動的銀行信用卡或電子支付來繳,從繳費中賺一點點小確幸!就是賺個1%~2%大家也是很開心的,因為你們把沒回饋變成有回饋,就是用卡的最高境界 所得稅線上申報
Thumbnail
每年4月、5月都是最多稅要繳的月份,當然大部份的人都是有機會繳到「綜合所得稅」,只是相當相當多人還不知道,原來繳給政府的稅!可以透過一些有活動的銀行信用卡或電子支付來繳,從繳費中賺一點點小確幸!就是賺個1%~2%大家也是很開心的,因為你們把沒回饋變成有回饋,就是用卡的最高境界 所得稅線上申報
Thumbnail
一起來看看怎麼做 MIN 吧!這篇教學是喜特先生為 Google 試算表新手而寫的「聚集函式御五家」系列文之一,會分享在 Google 試算表上簡單又實用的五種函式 SUM、AVERAGE、COUNT 與 COUNTA、MAX、 MIN ,每篇都會解釋語法、並且附上一個簡單的例子讓你可以一起練習。
Thumbnail
一起來看看怎麼做 MIN 吧!這篇教學是喜特先生為 Google 試算表新手而寫的「聚集函式御五家」系列文之一,會分享在 Google 試算表上簡單又實用的五種函式 SUM、AVERAGE、COUNT 與 COUNTA、MAX、 MIN ,每篇都會解釋語法、並且附上一個簡單的例子讓你可以一起練習。
Thumbnail
來看看怎麼用 MAX 吧!這篇教學是喜特先生為 Google 試算表新手而寫的「聚集函式御五家」系列文之一,會分享在 Google 試算表上簡單又實用的五種函式 SUM、AVERAGE、COUNT 與 COUNTA、MAX、 MIN ,每篇都會解釋語法、並且附上一個簡單的例子讓你可以一起練習。
Thumbnail
來看看怎麼用 MAX 吧!這篇教學是喜特先生為 Google 試算表新手而寫的「聚集函式御五家」系列文之一,會分享在 Google 試算表上簡單又實用的五種函式 SUM、AVERAGE、COUNT 與 COUNTA、MAX、 MIN ,每篇都會解釋語法、並且附上一個簡單的例子讓你可以一起練習。
Thumbnail
來看看 AVERAGE 怎麼寫!這篇教學是喜特先生為 Google 試算表新手而寫的「聚集函式御五家」系列文之一,會分享在 Google 試算表上簡單又實用的五種函式 SUM、AVERAGE、COUNT 與 COUNTA、MAX、 MIN ,每篇都會解釋語法、並且附上一個簡單的例子讓你可以一起練習。
Thumbnail
來看看 AVERAGE 怎麼寫!這篇教學是喜特先生為 Google 試算表新手而寫的「聚集函式御五家」系列文之一,會分享在 Google 試算表上簡單又實用的五種函式 SUM、AVERAGE、COUNT 與 COUNTA、MAX、 MIN ,每篇都會解釋語法、並且附上一個簡單的例子讓你可以一起練習。
Thumbnail
聚合函數 可以對資料的筆數、平均、最大、最小和加總的運算,提供查詢結果:如下表示: COUNT(Column):計算筆數,「*」是統計紀錄數。 AVG(Column):計算欄位平均值。 MAX(Column):計算欄位最大值。 MIN(Column):計算欄位最小值。 SUM(Colum
Thumbnail
聚合函數 可以對資料的筆數、平均、最大、最小和加總的運算,提供查詢結果:如下表示: COUNT(Column):計算筆數,「*」是統計紀錄數。 AVG(Column):計算欄位平均值。 MAX(Column):計算欄位最大值。 MIN(Column):計算欄位最小值。 SUM(Colum
Thumbnail
要怎麼無視大小寫,用 QUERY 抓取資料呢?方法其實很簡單,一起看下去吧!
Thumbnail
要怎麼無視大小寫,用 QUERY 抓取資料呢?方法其實很簡單,一起看下去吧!
Thumbnail
連 Google QUERY 官方文件都沒寫的秘密,在這邊公開啦!如果你想提升處理大型資料庫的清理或分析效率,歡迎來參考「SKIPPING」!
Thumbnail
連 Google QUERY 官方文件都沒寫的秘密,在這邊公開啦!如果你想提升處理大型資料庫的清理或分析效率,歡迎來參考「SKIPPING」!
Thumbnail
你知道 SELECT 除了回傳欄位、使用聚集函數外,還可以更進一步做加減乘除喔!這個系列是 QUERY 函式大解析的第六篇文章,如果還不知道什麼是 QUERY 的話,我還是很建議你從第一篇慢慢看、跟著我們的練習實際操作,就會更有概念囉~
Thumbnail
你知道 SELECT 除了回傳欄位、使用聚集函數外,還可以更進一步做加減乘除喔!這個系列是 QUERY 函式大解析的第六篇文章,如果還不知道什麼是 QUERY 的話,我還是很建議你從第一篇慢慢看、跟著我們的練習實際操作,就會更有概念囉~
Thumbnail
今天要介紹的是進階的 SELECT 功能,可以即時對 QUERY 的結果運算,迅速取得數值的平均、總和、最大值、最小值和數量,省去拉資料透視表(pivot table)的麻煩!
Thumbnail
今天要介紹的是進階的 SELECT 功能,可以即時對 QUERY 的結果運算,迅速取得數值的平均、總和、最大值、最小值和數量,省去拉資料透視表(pivot table)的麻煩!
Thumbnail
除了 SELECT、WHERE 之外,今天再介紹 ORDER BY、LIMIT、OFFSET 和 LABEL 給大家,讓你的 QUERY 更強大!
Thumbnail
除了 SELECT、WHERE 之外,今天再介紹 ORDER BY、LIMIT、OFFSET 和 LABEL 給大家,讓你的 QUERY 更強大!
Thumbnail
這是 QUERY 函式大解析系列文章的第二篇!我們要用 WHERE 語法來指定搜尋條件,要 QUERY 只回傳符合條件的資料。
Thumbnail
這是 QUERY 函式大解析系列文章的第二篇!我們要用 WHERE 語法來指定搜尋條件,要 QUERY 只回傳符合條件的資料。
追蹤感興趣的內容從 Google News 追蹤更多 vocus 的最新精選內容追蹤 Google News