第四章

使用聚合函數分析數據

•Aggregate是一個函數執行一個簡單的計算，一列數據，並返回結果集的結果

•select aggregate_name ( [ distinct ] column_name) from table_name [ where condition]

•distinct應用Aggregate前刪除重複值

•distinct 可以和 count、sum和avg一起使用

•distinct 不可以和 count(*), max, min

•Aggregates不可以和where混用

•錯誤範例：select title from titles where advance > avg(advance)

•除COUNT（*），所有聚集忽略NULL值

•select aggregate ( isnull ( column_with_NULLs, non-NULL value ) from table_name

•snull是一個T-SQL的擴展，取代指定的非NULL值的結果集返回NULL值

–此功能表中的NULL值不會受到影響

•一般Aggregate是用在group by

•select column_list from table group by column_list

•應用順序

–首先使用 [ where ] 篩選掉不符合的資料

–然後剩餘的資料進行分組

–最後，應用Aggregate

•Group by之後，還需要條件判斷的情況下使用having

–這種過濾發生後已應用於Aggregate

•select column_list from table group by column_list having condition

•grouping by

•grouping by可以通過列名或任何不包含聚合函數的表達

•grouping by不能被派生列標題

•grouping by 的條件通常包含在select表達

•compute

•compute子句是一個T-SQL的擴展，提供一個單一的數字，在報告結束

•select column_list from table_name where condition group by column_list having condition [ order by column_name [ asc | desc ] [ , column_name [ asc | desc ] … ] ] compute aggregate ( column_name )

•compute by

•compute by 子句是一個T-SQL的擴展，提供了一個單一數字，每個組值

•select column_list from table_name order by column_name [ asc | desc ] [ , column_name [ asc | desc ] … ] compute aggregate ( column_name ) by column_list

•Compute by 一定要使用 order by

•Compute 不一定要使用 order by

•例

–Order by a, b, c

•Compute avg(price) a, b, c

•Compute avg(price) a, b

•Compute avg(price) a