Louis學習筆記

概述

SQL Server 2005以後就開始提供一些好用的視窗函數(Window Function)
而在SQL Server 2012時再提供了一些超殺的新函數
對於使用資料庫撈資料繪製報的工作人員有非常大的助益

視窗函數(Window Function)的特色

1. 節省組合過多語法
2. 提申語法的可讀性
3. 避免過多資料表連結
4. 避免撰寫出效能不好的語法

在組像是統計型報表所要用的資料集時，往往會連結許多次資料表、使用複雜的語法來組出想要的資料集
為了兼顧到取出資料的效能跟成果，需要有足夠的經驗跟邏輯才能達到使用較少運算資源快速取得資料
而透過SQL Server提供的視窗函數，大量的減少語法的使用，並且讓之後維護的人員能夠快速了解語法
而產出的過程中也能有一定品質的效率（使用視窗函數不一定是最有效率的方法）

Window Function的種類

1. Ranking Function(次序)
2. Aggregate Function(彙總)
3. Distrubtion Funcation(統計分佈)
4. Offset Function(相對位移)

在使用Window Function時，都會使用到Over子句

OVER ( [<PARTITION BY 子句>] [<ORDER BY 子句>] [<ROWS 或 RANGE 子句>])

Over字句前面可放置任何一種Window Function

Over是鎖定前面的Window function的目標結果集

根據Patition By做結果集的分割（ex:根據什麼條件去做排序、彙總），注意到，是分割而不是像Group By的群組合併

被分割後的各區塊依照Order By子句做排序，最後可以根據Rows(or Range) Between子句抓出最後的目標區塊

Ranking Funcation

由於篇幅關係這邊先談跟次序有關的函數，後續會再補上其他三種Window Function

Ranking Funcation種類

1. Row_Number()：編號
2. Rank()：排序(跳號處理)
3. Dense_Rank()：排序(號碼連續)
4. Ntile()：平均分配

使用之前的範例資料表做說明

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)

Row_Number

在產出報表時有時候會需要將資料列逐一進行編號，此時就可以透過Row_number()將資料表的各列進行編號

(1)單純編號

SELECT ROW_NUMBER() OVER (ORDER BY ProductID) as No,CategoryID,ProductID,ProductName
FROM dbo.Product

(2)根據特定欄位分割後編號

SELECT ROW_NUMBER() OVER (PARTITION BY CategoryID ORDER BY UnitPrice) as No,CategoryID,ProductID,ProductName
FROM dbo.Product

Rank

對資料表內的資料進行排名的動作，當遇到同名時後面的資料會有跳號的現象

(1)單純根據售價排序

SELECT Rank()OVER(ORDER BY UnitPrice)as No,CategoryID,ProductID,ProductName,UnitPrice
FROM dbo.Product

結果

No CategoryID ProductID ProductName UnitPrice
1 　　　1 　　　　　3 　　　　　C 　　　　　5
1 　　　3 　　　　　10 　　　　 J 　　　　　 5
3 　　　1 　　　　　1 　　　　　A 　　　　　10
3 　　　2 　　　　　5 　　　　　E 　　　　　10
3 　　　3 　　　　　9　　　　　 I 　　　　　 10
6 　　　1 　　　　　4　　　　　 D 　　　　　15
7 　　　3 　　　　　8 　　　　　H               20
7 　　　2 　　　　　6 　　　　　F                20
9 　　　2 　　　　　7 　　　　　G               25
9 　　　1 　　　　　2 　　　　　B                25

在這邊可以看到排名的順序由1,1,3,3,3,6,....，當出現同名的現象後，後面的排名會有跳號的現象

(2)根據特定欄位分割後依照售價排序

SELECT Rank()OVER(PARTITION BY CategoryID ORDER BY UnitPrice) as No,CategoryID,ProductID,ProductName,UnitPrice
FROM dbo.Product

Dense_Rank

同Rank是做排名的函數，差別在於Dense_Rank()的排名是連續的沒有跳號現象

(1)單純根據售價排序

SELECT Dense_Rank()OVER(ORDER BY UnitPrice)as No,CategoryID,ProductID,ProductName,UnitPrice
FROM dbo.Product

結果

No CategoryID ProductID ProductName UnitPrice
1 　　　1 　　　　　3 　　　　　C 　　　　　5
1 　　　3 　　　　　10 　　　　 J 　　　　　 5
2 　　　1 　　　　　1 　　　　　A 　　　　　10
2 　　　2 　　　　　5 　　　　　E 　　　　　10
2 　　　3 　　　　　9　　　　　 I 　　　　　 10
3 　　　1 　　　　　4　　　　　 D 　　　　　15
4 　　　3 　　　　　8 　　　　　H               20
4 　　　2 　　　　　6 　　　　　F                20
5 　　　2 　　　　　7 　　　　　G               25
5 　　　1 　　　　　2 　　　　　B                25

很明顯的可以看出與Rank()的差異，在這邊排名是1,1,2,2,2,3,4,....，沒有中斷號碼

根據報表的需求選擇Rank()或Dense_Rank()使用

(2)根據特定欄位分割後依照售價排序

SELECT Dense_Rank()OVER(PARTITION BY CategoryID ORDER BY UnitPrice) as No,CategoryID,ProductID,ProductName,UnitPrice
FROM dbo.Product

NTILE

Ntile函數跟其他三個在語法上有點不同，需要額外添加一個參數Ntile(n)，n代表是要切個幾等分

(1)單純根據售價排序後再平分成兩等份

SELECT NTILE(2)OVER(ORDER BY UnitPrice)as No,CategoryID,ProductID,ProductName,UnitPrice
FROM dbo.Product

結果

No CategoryID ProductID ProductName UnitPrice
1 　　　1 　　　　　3 　　　　　C 　　　　　5
1 　　　3 　　　　　10 　　　　 J 　　　　　 5
1 　　　1 　　　　　1 　　　　　A 　　　　　10
1 　　　2 　　　　　5 　　　　　E 　　　　　10
1 　　　3 　　　　　9　　　　　 I 　　　　　 10
2 　　　1 　　　　　4　　　　　 D 　　　　　15
2 　　　3 　　　　　8 　　　　　H               20
2 　　　2 　　　　　6 　　　　　F                20
2 　　　2 　　　　　7 　　　　　G               25
2 　　　1 　　　　　2 　　　　　B                25

由於設定切割兩等分，所以編號上只會出現1,2，但當如果資料筆數無法整除時，會由最前頭的區塊逐一添加一筆，盡可能地達到平分的效果

(2)根據特定欄位分割後依照售價排序

SELECT NTILE(2)OVER(PARTITION BY CategoryID ORDER BY UnitPrice) as No,CategoryID,ProductID,ProductName,UnitPrice
FROM dbo.Product

結果

No CategoryID ProductID ProductName UnitPrice
1 　　　1 　　　　　3 　　　　　C 　　　　　5
1 　　　1 　　　　　1 　　　　　A 　　　　　10
2 　　　1 　　　　　4 　　　　　D 　　　　　15
2 　　　1 　　　　　2 　　　　　B 　　　　　25
1 　　　2 　　　　　5 　　　　　E 　　　　　10
1 　　　2 　　　　　6 　　　　　F 　　　　　20
2 　　　2 　　　　　7 　　　　　G 　　　　　25
1 　　　3 　　　　　10 　　　　　J 　　　　　5
1 　　　3 　　　　　9 　　　　　I 　　　　　10
2 　　　3 　　　　　8 　　　　　H 　　　　　20

這邊可以明顯地看到無法整除後的平分，在CategoryID=3的資料上，因為只有三筆資料無法平分兩等份，所以第一等份會多一筆資料

透過Ranking Function可以簡單的將資料表內的資料作編號

而有時候編號完的資料要再做後續加工（篩選）

這邊要注意的是，必須在外面再包一層（原語法作為子查詢），在對編號做篩選的動作（ex.只取編號為1，或編號前三的)

不能在同一層語法中直接在WHERE條件下做篩選，主要是Window Function的處理在WHERE、GROUP BY..等後面

所以要再次加工時要特別注意到這點