2013年5月8日 星期三

彙總查詢

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

  • COMPUTECOMPUTE 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

沒有留言:

張貼留言