Google Spreadsheet中,利用Query對資料進行Group By的展示方法

Google Spreadsheet(試算表)的Query功能很強大,但是對於其觀念我還是有一點不太能夠理解與接受,今天在做一些線上統計的調查,需要用到這個類似SQL Group By的功能,但是在網路查了半天的資料卻是不得其門而入,因此在這邊寫成一篇文章,供給日後查詢用。

先來談談SQL的做法

如果我們有一張如下列圖示的資料表,這張資料基本上就是類似「訂購單」、「捐贈單」的數量、金額之類的初始資料,然後我們要加以統計總個數或總金額。例如:張飛捐了100元、趙雲捐了200元之類的以此類推,那麼我想要知道張飛總共捐了多少錢?趙雲總共捐了幾次錢?

回到主軸,我們要統計所有的人總共捐了幾次錢,也就是我們熟悉的GROUP BY上場的時刻了。如下圖所示,SQL指令下一下,馬上就蹦出來了,豈不簡單乎?

SQL指令如下:

SELECT cName, COUNT(cName) AS count
FROM TEST
GROUP BY cName

場景轉到Google Spreadsheet後,一切都不一樣了

很高興看到Google試算表有提供Query這樣的功能讓我們使用,因此先創造了一張基本資料圖表:

然後我們開始膽大妄為的,按照我們對於SQL以及EXCEL的了解,下了這個指令:

=QUERY(A:C, "SELECT B, COUNT(B) WHERE B<>'' GROUP BY B ORDER BY COUNT(B) DESC")

登登登,Google拋出一個叫做「SELECT_WITH_AND_WITHOUT_AGGB」的錯誤,事實上那個AGGB中的B是表示欄位變數,因此正確的錯誤應該是叫做「SELECT_WITH_AND_WITHOUT_AGG」。AGG是Aggregate的縮寫,也就是表明SELECT的B欄位中必須具備聚合函數,這是啥?

經過數次的測試後,找到了下列的指令可以完成我們要的統計次數的功能。

=QUERY(A:C, "SELECT B, COUNT(C) WHERE B<>'' GROUP BY B ORDER BY COUNT(C) DESC")

但是事實上,我根本看不懂我自己在寫什麼。SELECT B GROUP BY B可以理解,就是把B欄位的東西拿出來GROUP BY,那麼為何要多一個COUNT(C)呢?我對C欄位做COUNT並不是我要的意思啊,但為何結果會是正確的?或許會有一些人說,你搞錯了,事實上他真的是在對C欄位進行COUNT,但事實並非如此,你可以把全部的金額都改成一樣的數字,就會發現事實的結果比較偏向Google Spreadsheet是先GROUP BYB欄位之後得到結果,再把C欄位的金額計算出現的次數一次,然後再把「B欄位」、「次數欄位」顯示出來,接著做後面的排序之類的事情。

這樣的推論不一定是對的,但是老實說我對於這種「不太流行」的標準不是很感興趣,因此就這樣吧!能動就好,這不是我的主力資訊項目,不需要浪費太多的時間,日後回來翻翻抄抄即可。

Google Spreadsheet之GROUP BY正規做法

東找西找,又找到一個PIVOT(轉軸)指令的解法,放在最後給大家參考,自己變通一下,套用在自己的資料表上吧。

=QUERY(A:C, "SELECT COUNT(C) PIVOT B")
GoogleSpreadsheet GroupBy Distinct Count SELECT_WITH_AND_WITHOUT_AGG COL_AGG_NOT_IN_SELECT CANNOT_GROUP_WITHOUT_AGG