SQL Window Function(二)：Aggregate Function @ Louis學習筆記

Aggregate Function

彙整函數是大多撰寫SQL Script很常使用的，以下我簡單進行分類

Aggregate Function 種類

1. 計次型 - Count、Count_Big

2. 最大最小型 - MAX、MIN

3. 統計型 - AVG、SUM、STDEV、STDEVP、VAR、VARP

使用先前用過的範例資料表來說明

CREATE TABLE dbo.Product
( ProductID int primary key,ProductName nvarchar(20),CategoryID int,UnitPrice int )
GO

INSERT INTO dbo.Product
VALUES(1,'A',1,10),(2,'B',1,25),(3,'C',1,5),(4,'D',1,15),(5,'E',2,10),
(6,'F',2,20),(7,'G',2,25),(8,'H',3,20),(9,'I',3,10),(10,'J',3,5)

1. 計次型

COUNT - 傳回欄位值非null的資料筆數

COUNT_BIG - 同COUNT，差別再傳回來的資料型別為BIGINT

2. 最大最小

MAX - 傳回欄位值中最大值

MIN - 傳回欄位值中最小值

3. 統計型

AVG - 傳回欄位值非null項目其加總後的平均值

SUM - 傳回欄位值非null項目其加總

格式

SELECT [視窗函數] ( [計算欄位] ) OVER ( PARTITION BY [分組欄位] ) FROM [資料表]

簡單來說就是將[計算欄位]根據[分組欄位]進行[視窗函數]的計算

直接使用以下語法可以看到如圖中所是結果

SELECT ProductID,ProductName,CategoryID,UnitPrice,
COUNT(UnitPrice) OVER (PARTITION BY CategoryID) AS [COUNT],
COUNT_BIG(UnitPrice) OVER (PARTITION BY CategoryID) AS [COUNT_BIG],
MAX(UnitPrice) OVER (PARTITION BY CategoryID) AS [MAX],
MIN(UnitPrice) OVER (PARTITION BY CategoryID) AS [MIN],
AVG(UnitPrice) OVER (PARTITION BY CategoryID) AS [AVG],
STDEV(UnitPrice) OVER (PARTITION BY CategoryID) AS [STDEV],
STDEVP(UnitPrice) OVER (PARTITION BY CategoryID) AS [STDEVP],
SUM(UnitPrice) OVER (PARTITION BY CategoryID) AS [SUM],
VAR(UnitPrice) OVER (PARTITION BY CategoryID) AS [VAR],
VARP(UnitPrice) OVER (PARTITION BY CategoryID) AS [VARP]
FROM dbo.Product

可以看到結果跟單純使用彙整函數計算出來的一樣，將CategoryID進行分組後計算，差別就在於保存了每一筆資料的顯示。

應用上的情境有，需要顯示每一筆資料，但又需要將其中一個欄位跟加總後的結果作計算。

像是計算百分比 UnitPrice / SUM(UnitPrice) OVER (PARTITION BY CategoryID) 這一類型的應用

但彙整視窗函數的功能不僅於此

格式

SELECT [視窗函數] ( [計算欄位] ) OVER ( PARTITION BY [分組欄位] ORDER BY [排序欄位] ) FROM [資料表]

如果單純地進行統計計算，可能不需要用到視窗函數，使用GROUP BY + 彙整函數就行

視窗函數應用的場景我個人認為要需要考慮到[排序欄位]的狀況才能顯示彙整視窗函數的功效

透過以下語法來說明有加[排序欄位]與否的差異

SELECT ProductID,ProductName,CategoryID,UnitPrice,
COUNT(UnitPrice) OVER (PARTITION BY CategoryID) AS [COUNT],
COUNT(UnitPrice) OVER (PARTITION BY CategoryID ORDER BY ProductID) AS [COUNT(ORDER BY)],
SUM(UnitPrice) OVER (PARTITION BY CategoryID) AS [SUM],
SUM(UnitPrice) OVER (PARTITION BY CategoryID ORDER BY ProductID) AS [SUM(ORDER BY)]
FROM dbo.Product

圖中可以看到沒加排序欄位的加總，會將數值根據CategoryID一口氣進行加總

而有加排序欄位的部分則是根據排列的順序一筆一筆把資料納入加總

這是因為 ORDER BY 在彙整視窗函數的運算過程，會將 UNBOUNDED PRECEDINF & CURRENT ROW 的概念加進來

可以應用在需要累加計算相關的情境，在加上沒加排序的視窗函數計算，可以做到累加百分比

SELECT ProductID,ProductName,CategoryID,UnitPrice,
SUM(UnitPrice) OVER (PARTITION BY CategoryID) AS [SUM],
UnitPrice * 1.0 / SUM(UnitPrice) OVER (PARTITION BY CategoryID) AS [佔比],
SUM(UnitPrice) OVER (PARTITION BY CategoryID ORDER BY ProductID) AS [SUM(ORDER BY)],
SUM(UnitPrice) OVER (PARTITION BY CategoryID ORDER BY ProductID) * 1.0
/ SUM(UnitPrice) OVER (PARTITION BY CategoryID) AS [累加百分比]
FROM dbo.Product