iT邦幫忙

第 12 屆 iThome 鐵人賽

DAY 10
0
AI & Data

與資料庫共舞系列 第 10

Day 10— 群組和整合SQL結果

  • 分享至 

  • xImage
  •  

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;

https://ithelp.ithome.com.tw/upload/images/20200910/20129829O98JpeKm0s.png

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

https://ithelp.ithome.com.tw/upload/images/20200910/20129829C6CLq13sgB.png

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

https://ithelp.ithome.com.tw/upload/images/20200910/20129829ItCwYJAhd3.png

應用 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 取得品項名稱。(註二)

https://ithelp.ithome.com.tw/upload/images/20200910/201298297HOHG6bUM4.png

用Having 來塞選 Group By 的資料

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

https://ithelp.ithome.com.tw/upload/images/20200910/20129829fs9mwfVU8A.png

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

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

https://ithelp.ithome.com.tw/upload/images/20200910/20129829eQs0xlM7iP.png

多欄位 Group By

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

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

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

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

https://ithelp.ithome.com.tw/upload/images/20200910/20129829p3SWlct4Ib.png

明天我們要解釋 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;

上一篇
Day 9 — 「正直」的資料庫,從設定好 Key Constraint 開始
下一篇
Day 11 — 創建不同視角
系列文
與資料庫共舞30
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

1 則留言

0
fortunately17
iT邦新手 4 級 ‧ 2021-11-15 15:40:37

不好意思
可以請問一下
Select name, item, avg(rating)
From review natural join customer natural join menu
Group By name, item;
這一段裡面的 review natural join customer是因為有CustomerID可以自然聯集
可是再跟natural join menu做自然聯集 沒有一個欄位是可以的
是我忽略了什麼嗎

不好意思 我看到了 是MenuID 抱歉 打擾了

我要留言

立即登入留言