不知不覺寫到第七篇了！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。
執行結果就會像這樣：
每月 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 的資料都會回傳，這就沒有限縮範圍的意義了。
又或是：
... 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)")
就會得到：
這次試著把之前學過的一些內容綜合起來，設計了幾道題目給大家參考參考。如果還是不確定怎麼做，也歡迎你回到之前的文章複習一下，留言發問當然也可以的！
如果你喜歡這次的文章，歡迎你透過這些方法支持我：
・按下愛心、按下儲存
・留言告訴我你的想法
・加入喜特先生的官方沙龍，即時看到我發布的教學
・付費訂閱喜特先生的官方沙龍，加入每月小額訂閱方案
・追蹤喜特先生的 Facebook
・按這邊小額贊助我的創作！
想要看更多文章，歡迎來到我的 Notion 頁面找找有沒有你需要的資源喔！
我是喜特先生，Mr. Sheet，我們下個教學見！
