Summarizing和群組資料
聚集(aggregate)函式、或集合(set)函式，這會在一個群組的值上執行，產生單一、總結的值。
沒有聚集的查詢會一個一個處理列，每一列會個別處理然後放到結果中，聚集查詢將表格視為一個整體，並且從它建立新的列。
|
|
|
|
1. 聚集函式
- Sum 及 Avg 只能作用於 數值資料
- Min 及 Max 能作用於 字元 , 數值 , 日期時間
- Count 可以作用於所有資料型態
- Count ( * ) 會忽略空值 , 傳回正整數或 0
,它永遠都不會傳回空值 , 而是傳回正整數或零。
- 聚集運算式不能出現在Where子句中。
|
範例：
SELECT type , SUM(sales)
FROM titles
GROUP BY type
在 Select 中 , 聚集函數與 非聚集函數不能混合使用 , 除非使用 Group
By 群組列 |
SELECT MIN(sales) , MAX(sales)
FROM titles
使用超過一個的聚集函數 Select Min(sales)
, Max(sales) |
不合法的
不可以巢狀化聚集函式
SELECT SUM(AVG(sales)) |
|
聚集函數不能出現在 Where 子句中 Select type From tables
Where sales = Max(sales) :
---->不合法的 |
|
|
|
2. 建立聚集運算式
| 4. 無法巢狀化聚集函式 Select
Sum(Avg(sales)) From tables |
| 5. 可在子查詢使用聚集函式 Select name From
tables Where sales = (Select Max(sales) from
tables) |
| 6. 聚集函數中無法使用子查詢 |
|
|
|
3. Min ( ) 最小值
|
Select
Min ( Number ) As 最小數值 |
|
Select
Min ( DateTime ) As 最早的日期或時間 |
|
Select
Min ( String ) As 排序中最低的值 |
|
|
|
4.
Max ( ) 最大值
|
Select Max ( Number ) As 最
大數值 |
|
Select
Max ( DateTime ) As 最
後的日期或時間 |
|
Select
Max ( String ) As
照字母排序中最後的值 |
- Max 與 Min
都能作用於字元、數值及日期時間資料型態
|
|
|
|
|
|
6. Avg
( ) 計算平均
- Avg只能作用於數值資料型態
- 沒有列 (也就是沒有符合條件的列) 的avg(
)為 Null--而不是 0
- 用SQL去做統計是不好的 ,
應該善用DBMS的Analysis Services來做會比較恰當
|
|
|
|
7. Counter 計算列
|
Counter (
expr ) |
求出所有沒有 null
值的列的數目 |
|
Counter (
* ) |
傳回所有列的數目 , 包含
null 和重覆的 |
|
Count(
* ) -
Count( expr
) |
求出所有 null 的數目 |
- count( expr ) 及
count( * ) 做用於所有的資料型態 , 而且不會傳回 null
|
|
|
|
8. Distinct 聚集不同的值 (消除重覆的值)
- 跟sum( ) , avg( ) ,
count ( ) 一起使用時 , distinct 會在處理之前消除重覆的值。
select Sum( distinct
sales
)
- Select
Sum( Distinct
price) ....取得不同值的和
...這個在ACCESS中是不合法的 ,Access不支援distinct聚集函式如左例
但是可以改成以下的方法
select Sum(price) From table where
(Select distinct price from table)
-
Select
Avg(
Distinct price) .....取得不同值的平均
-
count (
distinct ) / count( ).... 計算重覆率的東西
|
|
|
|
9. Group By 群組列
......使用Group By 子句值分割表格成為邏輯群組(種類),
並且計算每個群組的聚集
Select
au_id , Count(*) As
"Au_IDNum"
from tables
Group By au_id |
- au_id稱為群組欄位 ,
群組欄位可以是欄位名稱或衍生欄位
- 如果Select包含多個欄位查詢則Group by也必須精確地符合Select欄位,
如例二
- 如果群組欄位包含空值 , 那一個空值會查詢出單一群組 ,
如果包含多個空值則會依空值的數目而產生出相等數目的群結 ,因為空值彼此不相等。
- Where子句使用在Group By子句前 ,以先行過濾掉其他不要的列
- 無法在Group By 中使用欄位別名
- 如果陳述式中包含Order By 欄位 ,則其排序的欄位名稱必須在 Select
後的欄位名稱也有
- 注意：Count(*)是連空值都計算進去的 , 所以 Group By時會與Count(exp)不同
- 如果使用時沒有聚集函數 , 則Group By的作用與Distinct是一樣的
|
|
Select
au_id, type ,publish , Count(*)
As "Au_IDN"
from
tables Group
By au_id,type,publish |
select type from table Group by type
||
select distinct type from table
用distinct較好 |
|
|
|
10. Having 過濾群組
Select
type , Count(price)
As "ToPrice",
Avg(price) As "AvgPrice"
from
tables
Group By type
Having Avg(Price) > 10000 |
Having 子句在 Group By子句中設定條件
,類似Where與Select互動的方式
就像Where限制Select顯示的數量 , Having限制Group
By群組的數目
**Where過濾From和Join所產生的列
**Group By 群組 Where的輸出
**Having過濾Group By 群組的結果
一般來說左方第二個範例比第三個好 ,雖然它們的輸出結果是相同的 |
|
|
Select pub_id , Sum(sales) From Titles
----->較好
Where pub_id In('p03','p04')
Group By pub_id
Having Sum(sales) > 10000 |
|
|
Select pub_id , Sum(sales) From Titles
Group By pub_id
Having Sum(sales) > 10000 And pub_id
In('p03','p04') |
|
|