Day 10— 群組和整合SQL結果

今天我們要把熟知的 Select — From — Where 再往外擴充。要怎麼樣可以概述SQL的搜尋結果呢?

用 Group By 做簡單的統計

讓我們現在應該很熟悉的基礎與法寫下下面這個查詢:

Select typeID, Avg(price) 
From menu
Where typeID = 'T001';

透過這個查詢我們可以找到在Menu關係表中，T001 這個食物類別裡面，產品的平均價位，但是，如果我們今天想要查詢的是，每一種食物類別，產品的平均價位呢?

這時候我們就可以使用 Group By。

Select typeID, avg(price)
From menu
Group By typeID;

Group By 會將指定欄位相同的資料集合 (Tuple) 整合再一起，他的運行順序一樣是 From 之後。在找到資料及之後，Group By 會將相同的資料排在一起，向下圖一樣一個一個的群組，並對每個群組做 Select 的指令，也就是選取MenuID 並取得平均。

但是要注意，Group By 之後的表格在 Select 只能選取兩種資料，一種是Group By 的那個(或那些)欄位，另外一個是你要統計和運算的「合併值」。這是什麼意思，讓我們來看看如果同一個指令我們放入了其他欄位的名稱。當我們選擇一個不可以合併的值，例如 Item，就會出現奇怪的事情 — 檸檬蛋糕怎麼會是77.5? 對照一下上面的表格，原來是MySQL 自作主張，把兩個整併過後的資料自己捨去了一個。所以，除非那個數值是可以自然整併的，不然不可以在 Group By 之後 Select。(註一)

應用 Group By

既然 Group By 也是一個合法的指令，我們就可以寫出複雜一點點的查詢: 「列出每個品項的平均價格並由低價往高價排列。」

Select foodtype.TypeName, avgTable.AveragePrice
From foodtype natural join 
	(Select typeId, avg(price) as AveragePrice
		From menu
		Group By typeID
		Order By AveragePrice) as avgTable;

首先我們要先透過 Group By 找出每個類別的平均值，並將平均值的欄位命名為 AveragePrice 然後依照 AveragePrice 由大到小排列。我們把這個子查詢和 Foodtype 關係表透過 Natural Join 取得品項名稱。(註二)

用Having 來塞選 Group By 的資料

讓我們回到上面最單純一個表的例子，如果我想要過濾掉平均50元以下種類的品項，該怎麼做呢? 你可能會想到在前面我們用 Where 來選取資料可不可以以此類推?`

恩對，不行。因為Where 是用來選取表格中的資料。語法上，Where 必須發生在 Group By 之前，整合完的資料必須要用 Having 來做過濾。

Select typeID, avg(price) as AvgPrice
From menu
Group By typeID
Having AvgPrice > 50;

多欄位 Group By

在使用 Group By 的時候，我們也可以選擇用多於一個欄位來分組。

例如說，我想找同一個顧客針對同一個商品的平均評分，就可以寫下下面這個查詢:

Select name, item, avg(rating)
From review natural join customer natural join menu
Group By name, item;

這次我們為了要拿到顧客的名字和商品的名字，我們在From 做了兩個Natural Join，接下來，我們依照顧客和商品名稱分組，最後取每個分組的平均，就會獲得

明天我們要解釋 SQL 中的 Views 當作第一部分的收尾啦!

註一: MySQL 可以運行一些Group by 後無法正確整合的查詢不代表其他SQL語言可以。這邊希望讀者可以養成好習慣，Group By 當作是整合後的值。整合後的值可以再去Join其他的資料表或是把它當作 Sub-query來使用。有興趣的讀者可以參考這篇文章。

註二: 為了示範Subquery + group by 的應用所以這樣寫，不然簡單來說可以寫成。

Select typename, avg(price) as AveragePrice
from foodtype natural join menu
group by typename;