Aggregate functions allow you to apply calculations on values in a column. Adding the GROUP BY clause allows you to provide aggregate on subsets of the data.
Aggregate Functions
當使用彙總函式時要先知道的是:這些函式都會忽略 NULL 欄位值。 底下是 SQL 2008 支援的彙總函式:
- AVG :
- COUNT :
- COUNT_BIG :同 COUNT 函式,只是回傳型別為 bigint 。
- MAX :
- MIN :
- SUM :
- CHECKSUM_AGG :傳回群組中各個值的總和檢查碼。
- STDEV :標準差 (standard deviation )
- STDEVP :擴展標準差 (standard deviation for the population)
- VAR :變異數 (variance)
- VARP :擴展變異數 (variance for the population)
- GROUPING :是否彙總 GROUP BY 清單中指定的資料行運算式。
以下針對幾特定函式簡列說明:
STDEV(標準差)
簡單來說,標準差是一組數值自平均值分散開來的程度的一種測量觀念。 一個較大的標準差,代表大部分的數值和其平均值之間差異較大;一個較小的標準差,代表這些數值較接近平均值。 例如:{3, 5, 9, 11} 和 {5, 6, 8, 9} 這二組數的平均值都是 7 ,但是第一組的標準差為 3.65 ,第二組的標準差為 1.82 。
DECLARE @AgeList1 table( age int NOT NULL); DECLARE @AgeList2 table( age int NOT NULL); insert @AgeList1 values (3),(5),(9),(11) insert @AgeList2 values (5),(6),(8),(9) SELECT AVG(age) From @AgeList1 --7 SELECT AVG(age) From @AgeList2 --7 SELECT STDEV(age) From @AgeList1 --3.65148371670111 SELECT STDEV(age) From @AgeList2 --1.82574185835055
STDEVP(變異數)
變異數是由資料值與期望值的差異計算而來,也是用來描述一組數的密度(變化程度)。變異數的平方根=標準差。
SELECT VAR(age) From @AgeList1 --13.3333333333333 SELECT VAR(age) From @AgeList2 --3.33333333333333
CHECKSUM_AGG
CHECKSUM_AGG 會傳回群組中各個值的總和檢查碼,常用在判斷集合中的資料是有被異動過。
select * from Emp SELECT DepNo, CHECKSUM_agg(EmpNo) CheckValue FROM Emp GROUP BY DepNo
SELECT CHECKSUM_AGG(EmpNo) FROM Emp WHERE EmpNo in (1) -- 1 SELECT CHECKSUM_AGG(EmpNo) FROM Emp WHERE EmpNo in (1,2) -- 3 SELECT CHECKSUM_AGG(EmpNo) FROM Emp WHERE EmpNo in (1,2,3) -- 0 SELECT CHECKSUM_AGG(EmpNo) FROM Emp WHERE EmpNo in (1,2,3,4) -- 4 SELECT CHECKSUM_AGG(EmpNo) FROM Emp WHERE EmpNo in (1,2,3,4,5) -- 1
CHECKSUM_AGG 的參數值必須是整數,也就是只適用於整數型別的欄位,它的檢查法就是將所有欄位值以二進位互斥運算。
CHECKSUM_AGG VS CHECKSUM
- CHECKSUM_AGG 是彙總函式,它可以彙總多筆資料中的單一欄位做總和檢查碼計算。
- CHECKSUM 不是彙總函式,它可以針對一筆資料中的所有欄位做總和檢查碼計算。
--依據EmpNo,EmpName欄位,求每一筆資料的總和檢查碼 SELECT CHECKSUM(EmpNo,EmpName) FROM Emp --依據所有欄位,求每一筆資料的總和檢查碼 SELECT CHECKSUM(*) FROM Emp --以下方法可用來比對二個 table 中的資料是否一樣 SELECT CHECKSUM_agg(CHECKSUM(*)) FROM Emp
使用 Group By 子句
當使用彙總函式時,用來分組統計。
SELECT OrderID, COUNT(1) as [COUNT], SUM(UnitPrice) as [SUM], AVG(UnitPrice) as [AVG], MAX(UnitPrice) as [MAX], MIN(UnitPrice) as [MIN] FROM OrderDetails GROUP BY OrderID
COMPUTE 和 COMPUTE BY
- COMPUTE 和 COMPUTE BY 這二個子句必須與彙總函式搭配使用。
- COMPUTE 和 COMPUTE BY 子句可以針對特定欄位,再額外彙總一個結果。
- COMPUTE 和 COMPUTE BY 子句要放在整個查詢陳述式的後方。
- 可搭配的彙總函式包含 SUM、AVG、MIN、MAX 或 COUNT。
SELECT * FROM tblGrade COMPUTE SUM(Grade);
COMPUTE BY 則可以根據特定欄位值,進行群組彙總。
SELECT StudentNo,ClassID,Grade FROM tblGrade ORDER BY StudentNo, ClassID COMPUTE SUM(Grade) BY StudentNo;
下一版的 Microsoft SQL Server 將不再提供此功能。請勿在新的開發工作中使用此功能,並且儘速修改使用此功能的應用程式。 改用 ROLLUP 。
使用 Having 子句
指定群組或彙總的搜尋條件。
HAVING 只能搭配 SELECT 陳述式使用。
HAVING 通常用在 GROUP BY 子句中。
當未使用 GROUP BY 時,HAVING 的行為會如同 WHERE 子句。
SELECT CASE WHEN (GROUPING(TypeA)=1) THEN 'don''t care' ELSE TypeA END as TYPEA, CASE WHEN (GROUPING(TypeB)=1) THEN 'don''t care' ELSE TypeB END as TYPEB, CASE WHEN (GROUPING(TypeC)=1) THEN 'don''t care' ELSE TypeC END as TYPEC, SUM(Qty) AS QtySum FROM @List GROUP BY TypeA , GROUPING SETS (TypeB,TypeC) HAVING SUM(Qty)>10
沒有留言:
張貼留言