QUERY 函式大解析（七）：簡易日期時間擷取術

喜特先生 Mr. Sheet
喜特先生 Mr. Sheet
發佈於Google 試算表教學站
2022/04/10閱讀時間約 9 分鐘
不知不覺寫到第七篇了！QUERY 真的有好多好多東西可以說 (ﾉ>ω<)ﾉ
QUERY 其實還能處理有日期、時間的資料，而且語法也相當容易，和我們之前就看過的聚集函式很像。你如果會了之前的聚集函式，相信這次處理日期和時間也會對你來說很簡單！
我在方格子上寫了一系列使用 QUERY 的教學文章，也歡迎你來看看：
QUERY 函式大解析，系列文索引

(包起來就好)

我們先來介紹這次要利用的東西，叫「純量函式（Scalar Function）」。( ˘•ω•˘ )
不過那什麼純什麼的名字其實不是很重要，簡單來說你可以把它想像成一種濾鏡，把資料透過去濾鏡後就是另一種顏色或形狀。比如說：
  • 我有個資料叫 apple，用「大寫濾鏡」就可以把 apple 轉成 APPLE。
  • 我有個資料叫 TEA，用「小寫濾鏡」就可以把 TEA 變成 tea。
  • 我有個資料叫 2022/01/23，用「年份濾鏡」就可以把 2022/01/23 變成 2022。
目前在 QUERY 可以操作的有「日期」、「時間」和「字串大小寫轉換」這三種濾鏡，使用方法其實很簡單，就是拿這些濾鏡後的括號把欄位字母包起來就好：
濾鏡(A)
濾鏡(B)
這就跟我們之前學到的聚集函式一樣，包～起來就好。
濾鏡(A), 濾鏡(B), 濾鏡(C)...
SUM(A), AVG(B), COUNT(C)...
那我們今天就來看一些實用的「日期」和「時間」濾鏡有哪些：

日期

  • year()：回傳日期的年份。
在 A 欄的某資料是 2022/01/23
year(A) --> 2022
  • month()：回傳日期的月份。
    要注意的是，這裡的月份是以 0 為基底，要記得加 1 才會顯示正確的月份。舉例：
在 A 欄的某資料是 2022/01/23
month(A) --> 0
month(A) + 1 --> 1

在 B 欄的某資料是 2022/02/23
month(B) --> 1
month(B) + 1 --> 2

在 C 欄的某資料是 2022/10/23
month(C) --> 9
month(C) + 1 --> 10
  • day()：回傳日期的日子。
在 A 欄的某資料是 2022/01/23
day(A) --> 23
  • quarter()：回傳日期的季別。1 就是第一季、2 就是第二季，以此類推。
在 A 欄的某資料是 2022/01/23
quarter(A) --> 1

在 B 欄的某資料是 2022/04/23
quarter(B) --> 2
  • dayOfWeek()：回傳日期的星期。要注意 QUERY 是以星期天當做一個星期裡的第一天，所以 1 是星期天、2 是星期一、3 是星期二，以此類推。
在 A 欄的某資料是 2022/01/23（這天是星期天）
dayOfWeek(A) --> 1

在 B 欄的某資料是 2022/02/23（這天是星期三）
dayOfWeek(B) --> 4

時間

邏輯跟剛剛的日期其實是一樣的，只要把欄位字母放進去括號裡面就可以了。
  • hour()：回傳時間的小時。
在 A 欄的某資料是 14:25:19
hour(A) --> 14
  • minute()：回傳時間的分。
在 A 欄的某資料是 14:25:19
minute(A) --> 25
  • second()：回傳時間的秒。
在 A 欄的某資料是 14:25:19
second(A) --> 19
  • millisecond()：回傳時間的毫秒。
在 A 欄的某資料是 14:25:19.487
millisecond(A) --> 487
看起來很複雜，其實做起來不難喔！我們馬上來練習一下。

練習

假設我們是間傢俱販賣商，有個 2022 年一月到十二月的訂單資訊，歡迎來這邊做一個副本練習唷！打開來就像這樣：
由左到右有訂單成立日期（Ａ）、時間（Ｂ）、訂單 ID（Ｃ）、客戶 ID（Ｄ）、售出品項（Ｅ）、品項單價（Ｆ）、售出件數（Ｇ）、合計（Ｈ）和訂單狀態（Ｉ）。
有天，老闆希望我們找到以下資訊：
  • 想知道每個月的總銷售額。
  • 每個月的五號是全館九折的活動日，想知道各品項的總銷售額。
  • 每天晚上 9 點 00 分到 11 點 59 分有會員點數加倍活動，想知道各品項的總銷售件數。
  • 想知道星期一到星期日裡，各品項的平均銷售額。
並且，我們只算狀態「已發貨」的訂單，其他「已取消」的訂單不列入計算。那我們來看看怎麼做：

想知道每個月的總銷售額

要拿到總銷售額的話可以用 SUM(H)，我們再用欄 A 的「月份」做 GROUP BY 就可以了。
=QUERY('訂單資料'!A:I,
"SELECT Month(A) + 1, SUM(H)
 WHERE I = '已發貨'
 GROUP BY Month(A) + 1")
記得要 Month(A) 之後要加個 1，才會顯示正確的月份唷！如果沒 +1 會顯示 0 到 11 月。GROUP BY 要跟顯示欄位一致，所以也需要寫上 Month(A) + 1。
執行結果就會像這樣：
可以再搭配 LABEL 讓標題列顯示得更漂亮。

每月 5 號各品項的總銷售件數

這種操作除了 SELECT 之外，當然也可以用在 WHERE 上面。設下每月五號的條件、再做 COUNT （計算件數）和 GROUP BY 就好囉！來看看我的操作：
=QUERY('訂單資料'!A:I,
"SELECT E, COUNT(C)
WHERE Day(A) = 5 and I = '已發貨'
GROUP BY E")
來看看結果吧！

每天晚上 9 點 00 分到 11 點 59 分各品項的總銷售件數

如果要找晚上 9 點整到 11 點 59 分的資料，或許可以從 Hour() 切入，取得小時數，也就是 21、22、23 這三個數字了。
那為什麼不是取 0 呢？因為 0 的話就會是凌晨 12 點以後的資料了，我們不想要這些。24 也不行，因為 Hour() 最大值就是 23 喔。
關於這邊 WHERE 的條件寫法，我想到兩個：
... WHERE (Hour(B) >= 21 and Hour(B) <= 23)
and I = '已發貨'
用括號告訴 QUERY 這段含有 Hour(B) 的條件要先判斷、並且要用 and 限縮 Hour(B) 的範圍。 如果這邊是用 or 的話，任何大於 21 或是小於 23 的資料都會回傳，這就沒有限縮範圍的意義了。
可以看看 and 和 or 的差異 ⬆️
又或是：
... WHERE (Hour(B) = 21 or Hour(B) = 22 or Hour(B) = 23)
and I = '已發貨'
用括號告訴 QUERY 要先判斷 Hour(B)，用列舉的方式寫出時數。
兩種 WHERE 的執行效果都一樣，我們來看看結果：

星期一到星期日各品項的平均銷售額

這可以看成兩樣資訊：
  • 各品項的平均銷售額
  • 星期一到星期日
平均銷售額可以用 SELECT E, AVG(H) 來做，各品項再用 GROUP BY E 就可以對聚集的數字分組。
至於「星期一到星期日」，就可以用 dayOfWeek(A) 來做 PIVOT，對已經 GROUP BY 的結果進一步透視，看到星期裡每個日子裡平均銷售額的表現。
把上面這兩個想法拼起來，再加上只統計「已發貨」的條件，你可以寫出：
=QUERY('訂單資料'!A:I,
 "SELECT E, AVG(H)
  WHERE I = '已發貨'
  GROUP BY E
  PIVOT dayOfWeek(A)")
就會得到：
記得 dayOfWeek 顯示結果的 1 是星期天、2 才是星期一喔！
這次試著把之前學過的一些內容綜合起來，設計了幾道題目給大家參考參考。如果還是不確定怎麼做，也歡迎你回到之前的文章複習一下，留言發問當然也可以的！
如果你喜歡這次的文章，歡迎你透過這些方法支持我：
・按下愛心、按下儲存
・留言告訴我你的想法
・加入喜特先生的官方沙龍，即時看到我發布的教學
・付費訂閱喜特先生的官方沙龍，加入每月小額訂閱方案
・追蹤喜特先生的 Facebook
・按這邊小額贊助我的創作！
想要看更多文章，歡迎來到我的 Notion 頁面找找有沒有你需要的資源喔！
我是喜特先生，Mr. Sheet，我們下個教學見！
喜特先生官方沙龍Google 試算表教學站🛠️｜函式用法｜
喜特先生官方沙龍
4.7K會員
137內容數
前往沙龍
簡潔，快速，有效， 讓你的日常生活、工作生產力大提升！ ＿＿＿ 快按「加入」，馬上追蹤所有喜特先生的更新，有 Google 試算表教學、Google Apps Script 的研究、數據分析課程的開箱，還有 Google 試算表疑難雜症的解題分享唷！💪
前往沙龍
留言0
查看全部
發表第一個留言支持創作者！