你知道 SELECT 除了回傳欄位、使用聚集函數外,還可以更進一步做加減乘除喔!這個系列是 QUERY 函式大解析的第六篇文章,如果還不知道什麼是 QUERY 的話,我還是很建議你從第一篇慢慢看、跟著我們的練習實際操作,就會更有概念囉!
我在方格子上寫了一系列使用 QUERY 的教學文章,也歡迎你來看看:
SELECT 可以玩的四則運算
四則運算就是加、減、乘、除,還有括號,而在 SELECT 的四則運算可以用來計算欄跟欄的加減乘除,像是:
A + B、A - B
A * B、A / B
(A + B) * C、A / (B-C)
(A + B + C + D) / 4
四則運算的規則是「括號優先,先乘除、後加減」。你也可以再利用聚集函數、搭配 GROUP BY 做更完整的分組計算,像是:
SELECT A, sum(B) + sum(C) + sum(D) GROUP BY A
SELECT A, avg(B) + sum(C) + count(D) GROUP BY A
排列組合有很多,你可以用你的需求安排看看各種不同可能性!
另外,這類四則運算的輸出結果中,欄位都會顯得有點長,像這樣:
我會建議搭配 LABEL 一起使用、讓結果可以更好閱讀。例如:
- SELECT A, sum(B) + sum(C) + sum(D) GROUP BY A
LABEL sum(B) + sum(C) + sum(D) ‘加總結果’
那,我們馬上就來看一個簡單的案例:
範例
假如你要對下面的資料做個簡單的分析(練習用的試算表一樣放在
這邊喔):
你會看到地點有台北、台中、高雄、宜蘭、花蓮、台東,有不同店舖,分別是青青文旅、雨農蔬食、山山露營還有海海衝浪。從 C 到 N 欄是各個店鋪在各月份的營收,從一月到十二月都有。來試試看第一題:
得到各個店鋪第一季的總營收
第一季就是一月到三月,那如果要得到一月到三月的總和,要計算的欄位就是 C + D + E 囉。所以就可以下:
=QUERY(A:N, "SELECT A, B, C + D + E")
來看看結果:
其實效果就和 SUM(C:E) 一樣的!
得到台中地區店鋪第一季的總營收
我們當然也可和 WHERE 結合,請 QUERY 算只符合條件的加減乘除。例如,我只想算台中地區的店舖的總營收,那就是「WHERE A = '台中'」。那我們來試試看:
=QUERY(A:N, "SELECT A, B, C + D + E WHERE A = '台中'")
來看看結果:
得到各店鋪全年的營收,並用季來分
要做出類似資料透視表的方法也是可以的!我們也可以把一月、二月、三月分組、四月、五月、六月分組等,就可以這麼寫:
=QUERY(A:N,"SELECT A, B,
sum(C) + sum(D) + sum(E),
sum(F) + sum(G) + sum(H),
sum(I) + sum(J) + sum(K),
sum(L) + sum(M) + sum(N)
WHERE A IS NOT NULL
GROUP BY A, B")
來看看結果:
不過這邊的抬頭還是有點醜醜的,不好讀,我們可以再用 LABEL 規定好名稱:
=QUERY(A:N,
"SELECT A, B,
sum(C) + sum(D) + sum(E),
sum(F) + sum(G) + sum(H),
sum(I) + sum(J) + sum(K),
sum(L) + sum(M) + sum(N)
WHERE A IS NOT NULL
GROUP BY A, B
LABEL sum(C) + sum(D) + sum(E) '第一季',
sum(F) + sum(G) + sum(H) '第二季',
sum(I) + sum(J) + sum(K) '第三季',
sum(L) + sum(M) + sum(N) '第四季'")
來看看結果吧!
學會這個的話,方便做欄與欄之間的加減乘除,讓你除了簡單的 SELECT、聚集函數之外也有更多可能性。
如果你喜歡這次的文章,歡迎你透過這些方法支持我:
・按下愛心、按下儲存
・留言告訴我你的想法
・加入喜特先生的官方沙龍,即時看到我發布的教學
・付費訂閱喜特先生的官方沙龍,加入每月小額訂閱方案
・追蹤喜特先生的 Facebook
・按這邊小額贊助我的創作!
我是喜特先生,Mr. Sheet,我們下個教學見!