前言
在前一篇文章中，介紹了認識「SQL視窗」、認識OVER子句、認識視窗資料分割、認識視窗框架與排序、認識SQL視窗函數、以及說明如何使用視窗彙總函數等主題。
在本次文章中，將繼續討論視窗統計分佈函數，包含有：CUME_DIST、PERCENT_RANK、PERCENTILE_CONT和PERCENTILE_DISC等主題。
認識視窗統計分佈函數
「視窗統計分佈函數(Window Distribution Functions)」用於在視窗或視窗資料分割的資料列上執行統計分析。可以不需要PARTITON BY來設定資料分割，但必須使用ORDER BY次子句來排序。而且，統計分布函數所處理的每一筆資料列皆會傳回排名值，其值域的範圍是介於0.0到1.0之間。
在SQL Server 2012版本中提供了視窗統計分佈函數：PERCENT_RANK和CUME_DIST。也提供了「逆統計分佈函數(inverse distribution functions)」：PERCENTILE_CONT和PERCENTILE_DISC。
認識CUME_DIST
CUME_DIST視窗統計分布函數可以用來計算群組內各筆資料列的「累計分佈值(cumulative distribution value)」，也就是各筆資料列在群組內的相對位置。CUME_DIST統計分佈函數與PERCENT_RANK統計分佈函數類似。
以下是CUME_DIST統計分佈函數的運算公式：
CUME_DIST() = RANK() / 總資料列筆數
- RANK()：使用RANK()次序函數來計算每筆資料列的次序編號。若遇到相同的值時，RANK()次序函數會計算傳回相同的次序值，而下一個編號會跳號後再編，因此，不一定會傳回連續的整數值。
- 總資料列筆數：此視窗資料分割內的總資料列筆數。
其語法如下：
CUME_DIST( )
OVER ( [ PARTITION BY 子句 ] ORDER BY 子句 )
使用的引數：
- PARTITON BY：將FROM子句產生的結果集，依據PARTITON BY分割為函數所要套用的資料分割群組。若未指定，此函數會將查詢結果集的所有資料列視為單一群組。
- ORDER BY：決定執行作業的邏輯順序，這是必要項目。
注意事項：
- 其傳回的資料類型：float(53)，這是回傳介於0到1數值結果。
- 但不支援指定OVER語法內的「視窗框架」功能，也就是不支援ROWS或RANGE子句。
- 若是遇到NULL值，並將其視為最低的可能值加以處理。
- 若是遇到相同值，將會回傳相同的累積分佈值。
接下來，使用範例程式碼5來說明CUME_DIST視窗統計分佈函數的使用方式。請參考下圖8所示：
SELECT CustomerID, OrderDate, Subtotal,
RANK() OVER (PARTITION BY CustomerID ORDER BY Subtotal) N'RANK',
CUME_DIST() OVER (PARTITION BY CustomerID ORDER BY Subtotal) N'CUME_DIST',
CUME_DIST() OVER (PARTITION BY CustomerID ORDER BY Subtotal) * 100 N'CUME_DIST(%)'
FROM dbo.CReport1
GO
範例程式碼5：認識CUME_DIST視窗統計分佈函數
圖8：檢視執行結果集-認識CUME_DIST視窗統計分佈函數
在範例程式碼5中，使用CUME_DIST視窗統計分佈函數，在同一群CustomerID內，依據Subtotal來指定邏輯方式的遞增排序，計算出累計分佈統計值。
在圖8中，檢視回傳的結果集，以資料行CustomerID為ANATR時為例，小計有四筆資料列，其累計分佈統計值的運算方式如下：
- 第一筆資料列，CUME_DIST = (1)/(4)= 0.25。
- 第二筆資料列，CUME_DIST = (2)/(4)= 0.5。
- 第三筆資料列，CUME_DIST = (3)/(4)= 0.75。
- 第四筆資料列，CUME_DIST = (4)/(4)= 1。
執行範例程式碼6，新增一筆資料列，資料行CustomerID為ANATR時，指定Subtotal值為514.40，刻意讓兩筆資料列的Subtotal值是相同的。
--新增一筆資料列，資料行CustomerID為ANATR時，指定Subtotal值為514.40，刻意讓兩筆資料列的Subtotal值是相同的。
INSERT dbo.CReport1 VALUES('ANATR', '1998-07-07','514.40', 'México D.F.')
GO
範例程式碼6：新增一筆資料列，刻意讓兩筆資料列的Subtotal是相同的
再度執行範例程式碼5，檢視其執行結果，請參考下圖9所示：
圖9：檢視執行結果集-新增資料列後，使用CUME_DIST視窗統計分佈函數
在圖9中，檢視回傳的結果集，以資料行CustomerID為ANATR時為例，小計有五筆資料列，但其中有兩筆資料列的Subtotal皆是為 514.40，其累計分佈統計值的運算方式如下：
- 第一筆資料列，CUME_DIST = (1)/(5)= 0.2。
- 第二筆資料列，CUME_DIST = (2)/(5)= 0.4。
- 第三筆資料列，CUME_DIST = (3)/(5)= 0.6。
- 第四、五筆資料列，其Subtotal值是相同的，CUME_DIST= (5)/(5)= 1。
也就是說，若遇到相同值時，使用CUME_DIST視窗統計分佈函數將會回傳相同的累積分佈值。
執行範例程式碼7，刪除先所新增加的資料列：
-- 刪除先前新增加的資料列
DELETE dbo.CReport1 WHERE CustomerID ='ANATR' AND OrderDate ='1998-07-07'
GO
範例程式碼7：刪除先前新增加的資料列
若是沒有指定PARTITON BY次子句，此函數會將查詢結果集的所有資料列視為單一群組，請參考範例程式碼8，以及下圖10所示：
-- 若沒有搭配 PARTITION BY 次子句
SELECT CustomerID, OrderDate, Subtotal,
RANK() OVER (ORDER BY Subtotal) N'RANK',
CUME_DIST() OVER (ORDER BY Subtotal) N'CUME_DIST',
CUME_DIST() OVER (ORDER BY Subtotal) * 100 N'CUME_DIST(%)'
FROM dbo.CReport1
GO
範例程式碼8：CUME_DIST函數沒有指定PARTITON BY次子句，將所有資料列視為單一群組
圖10：檢視執行結果集-沒有指定PARTITON BY次子句，將所有資料列視為單一群組
認識PERCENT_RANK
PERCENT_RANK視窗統計分布函數可以用來計算群組內各筆資料列的「累計分佈值」，也就是各筆資料列在群組內的相對位置。PERCENT_RANK視窗統計分布函數與CUME_DIST統計分佈函數類似
以下是PERCENT_RANK統計分佈函數的運算公式：
PERCENT_RANK() = (RANK()-1) / (總資料列筆數-1)
- RANK()：使用RANK()次序函數來計算每筆資料列的次序編號。若遇到相同的值時，RANK()次序函數會計算傳回相同的次序值，而下一個編號會跳號後再編，因此，不一定會傳回連續的整數值。
- 總資料列筆數：此視窗資料分割內的總資料列筆數。
其語法如下：
PERCENT_RANK( )
OVER ( [ PARTITION BY 子句 ] ORDER BY 子句 )
使用的引數：
- PARTITON BY：將FROM子句產生的結果集，依據PARTITON BY分割為函數所要套用的資料分割群組。若未指定，此函數會將查詢結果集的所有資料列視為單一群組。
- ORDER BY：決定執行作業的邏輯順序，這是必要項目。
注意事項：
- 其傳回的資料類型：float(53)，這是回傳介於0到1數值結果。
- 但不支援指定OVER語法內的「視窗框架」功能，也就是不支援ROWS或RANGE子句。
- 若是遇到NULL值，並將其視為最低的可能值加以處理。
- 若是遇到相同值，將會回傳相同的累積分佈值。
接下來，使用範例程式碼9來說明PERCENT_RANK視窗統計分佈函數的使用方式。請參考下圖11所示：
SELECT CustomerID, OrderDate, Subtotal,
RANK() OVER (PARTITION BY CustomerID ORDER BY Subtotal) N'RANK',
PERCENT_RANK() OVER (PARTITION BY CustomerID ORDER BY Subtotal) N'PERCENT_RANK',
PERCENT_RANK() OVER (PARTITION BY CustomerID ORDER BY Subtotal) * 100 N'PERCENT_RANK(%)'
FROM dbo.CReport1
GO
範例程式碼9：認識PERCENT_RANK視窗統計分佈函數
圖11：檢視執行結果集-認識PERCENT_RANK視窗統計分佈函數
在範例程式碼9中，使用PERCENT_RANK視窗統計分佈函數，在同一群CustomerID內，依據Subtotal來指定邏輯方式的遞增排序，計算出累計分佈統計值。
在圖11中，檢視回傳的結果集，以資料行CustomerID為ANATR時為例，小計有四筆資料列，其累計分佈統計值的運算方式如下：
- 第一筆資料列，PERCENT_RANK = (1-1)/(4-1)= 0。
- 第二筆資料列，PERCENT_RANK = (2-1)/(4-1)= 0.333333333333333。
- 第三筆資料列，PERCENT_RANK = (3-1)/(4-1)= 0.666666666666667。
- 第四筆資料列，PERCENT_RANK = (4-1)/(4-1)= 1。
執行範例程式碼6，新增一筆資料列，資料行CustomerID為ANATR時，指定Subtotal值為514.40，刻意讓兩筆資料列的Subtotal值是相同的。
再度執行範例程式碼9，檢視其執行結果，請參考下圖12所示：
圖12：檢視執行結果集-新增資料列後，使用PERCENT_RANK視窗統計分佈函數
在圖12中，檢視回傳的結果集，以資料行CustomerID為ANATR時為例，小計有五筆資料列，但其中有兩筆資料列的Subtotal皆是為 514.40，其累計分佈統計值的運算方式如下：
- 第一筆資料列，PERCENT_RANK = (1-1)/(5-1)= 0。
- 第二筆資料列，PERCENT_RANK = (2-1)/(5-1)= 0.25。
- 第三筆資料列，PERCENT_RANK = (3-1)/(5-1)= 0.5。
- 第四、五筆資料列，其Subtotal值是相同的，PERCENT_RANK = (4-1)/(5-1)= 0.75。
也就是說，若遇到相同值時，使用PERCENT_RANK視窗統計分佈函數將會回傳相同的累積分佈值。
執行範例程式碼7，刪除先所新增加的資料列。
若是沒有指定PARTITON BY次子句，此函數會將查詢結果集的所有資料列視為單一群組，請參考範例程式碼10，以及下圖13所示：
-- 若沒有搭配 PARTITION BY 次子句
SELECT CustomerID, OrderDate, Subtotal,
RANK() OVER (ORDER BY Subtotal) N'RANK',
PERCENT_RANK() OVER (ORDER BY Subtotal) N'PERCENT_RANK',
PERCENT_RANK() OVER (ORDER BY Subtotal) * 100 N'PERCENT_RANK(%)'
FROM dbo.CReport1
GO
範例程式碼10：PERCENT_RANK函數沒有指定PARTITON BY次子句，將所有資料列視為單一群組
圖13：檢視執行結果集-PERCENT_RANK函數沒有指定PARTITON BY次子句，將所有資料列視為單一群組
接下來，讓我們繼續來比較PERCENT_RANK與CUME_DIST視窗統計分佈函數兩者的輸出結果集，請參考範例程式碼11，以及下圖14所示：
SELECT CustomerID, OrderDate, Subtotal,
RANK() OVER (PARTITION BY CustomerID ORDER BY Subtotal) N'RANK',
CUME_DIST() OVER (PARTITION BY CustomerID ORDER BY Subtotal) N'CUME_DIST',
PERCENT_RANK() OVER (PARTITION BY CustomerID ORDER BY Subtotal) N'PERCENT_RANK'
FROM dbo.CReport1
GO
範例程式碼11：PERCENT_RANK與CUME_DIST視窗統計分佈函數兩者的輸出結果集
圖14：檢視執行結果集-比較PERCENT_RANK與CUME_DIST視窗統計分佈函數
在圖14中，可以觀察到這兩個視窗統計分佈函數都能夠使用在求取累計分佈值上，只是在基礎公式的稍許不同：
- CUME_DIST() = RANK() / 總資料列筆數
- PERCENT_RANK() = (RANK()-1) / (總資料列筆數-1)
認識PERCENTILE_DISC
PERCENTILE_DISC視窗統計分布函數會依據資料行值的離散分佈(discrete distribution)特性來計算百分位數，其回傳的結果會是資料行中既存在的特定值。PERCENTILE_DISC有一點類似於CUME_DIST視窗統計分布函數。
運算方式如下：
- 先設定PERCENTILE_DISC視窗統計分布函數所使用的百分位數(例如：P)，系統依據ORDER BY次子句來邏輯排序結果集內的資料列，
- 然後參考CUME_DIST視窗統計分布函數的計算值，傳回其所對應的資料行值。
以下說明如何參考CUME_DIST視窗統計分布函數的計算值
- 依據所設定的百分位數(P)，相對應到此最小CUME_DIST視窗統計分布函數的計算值，若沒有正好對應的計算值，將會是取大於或是等於所設定百分位數(P)之值來對應。
其語法如下：
PERCENTILE_DISC ( numeric_literal )
WITHIN GROUP ( ORDER BY order_by_expression [ ASC | DESC ] )
OVER ( [ <partition by 次子句> ] )
使用的引數：
- numeric_literal：要運算的百分位數。其值的範圍必須介於0.0到1.0之間。
- WITHIN GROUP ( ORDER BY order_by_expression [ ASC | DESC ])：指定要用以邏輯排序以及計算百分位數的數值清單。只允許一個order_by_expression。而預設的排序順序為遞增。
- OVER ( <partition by 次子句> )：將FROM子句所產生的結果集，分割成套用百分位數函數的資料分割。目前不支援PERCENTILE_DISC視窗統計分布函數中指定OVER語法的ORDER BY次子句以及ROWS或RANGE子句。
注意事項：
- 傳回類型，是依據order_by_expression資料類型來決定。
- WITHIN GROUP關鍵字是屬於資料庫相容性層級110內的保留關鍵字。
- 結果集中若是有NULL 值，將會忽略不計算，不影響原本結果集的運算結果。
接下來，使用範例程式碼12來說明PERCENTILE_DISC視窗統計分佈函數的使用方式。請參考下圖15、16所示：
-- 認識PERCENTILE_DISC 函數，以 0.1 為一個級距，分割為 10 份。
SELECT CustomerID, Subtotal,
RANK() OVER (PARTITION BY CustomerID ORDER BY Subtotal) N'RANK',
CUME_DIST() OVER (PARTITION BY CustomerID ORDER BY Subtotal) N'CUME_DIST',
PERCENTILE_DISC(0.1) WITHIN GROUP ( ORDER BY Subtotal) OVER (PARTITION BY CustomerID) N'PD(0.1)',
PERCENTILE_DISC(0.2) WITHIN GROUP ( ORDER BY Subtotal) OVER (PARTITION BY CustomerID) N'PD(0.2)',
PERCENTILE_DISC(0.3) WITHIN GROUP ( ORDER BY Subtotal) OVER (PARTITION BY CustomerID) N'PD(0.3)',
PERCENTILE_DISC(0.4) WITHIN GROUP ( ORDER BY Subtotal) OVER (PARTITION BY CustomerID) N'PD(0.4)',
PERCENTILE_DISC(0.5) WITHIN GROUP ( ORDER BY Subtotal) OVER (PARTITION BY CustomerID) N'PD(0.5)',
PERCENTILE_DISC(0.6) WITHIN GROUP ( ORDER BY Subtotal) OVER (PARTITION BY CustomerID) N'PD(0.6)',
PERCENTILE_DISC(0.7) WITHIN GROUP ( ORDER BY Subtotal) OVER (PARTITION BY CustomerID) N'PD(0.7)',
PERCENTILE_DISC(0.8) WITHIN GROUP ( ORDER BY Subtotal) OVER (PARTITION BY CustomerID) N'PD(0.8)',
PERCENTILE_DISC(0.9) WITHIN GROUP ( ORDER BY Subtotal) OVER (PARTITION BY CustomerID) N'PD(0.9)',
PERCENTILE_DISC(1.0) WITHIN GROUP ( ORDER BY Subtotal) OVER (PARTITION BY CustomerID) N'PD(1.0)'
FROM dbo.CReport1
-- WHERE CustomerID = 'BSBEV'
GO
範例程式碼12：認識PERCENTILE_DISC視窗統計分佈函數
圖15：檢視執行結果集-認識PERCENT_DISC視窗統計分佈函數1
在圖15中，以資料行CustomerID值是ANATR為例，小計有四筆資料列，其運算方式如下：
- PD(0.1)，對應的CUME_DIST函數值是：0.25，PERCENTILE_DISC函數傳回Subtotal值是88.80。
- PD(0.2)，對應的CUME_DIST函數值是：0.25，PERCENTILE_DISC函數傳回Subtotal值是88.80。
- PD(0.3)，對應的CUME_DIST函數值是：0.5，PERCENTILE_DISC函數傳回Subtotal值是320.00。
- …以此類推。
可以觀察到，使用PERCENTILE_DISC視窗統計分佈函數都一定會傳回在結果集中的實際值。
圖16：檢視執行結果集-認識PERCENT_DISC視窗統計分佈函數2
在圖16中，以資料行CustomerID值是BSBEV為例，小計有十筆資料列，利用PERCENTILE_DISC視窗統計分佈函數分成十份來計算，其運算方式如下：
- PD(0.1)，對應的CUME_DIST函數值是：0.1，PERCENTILE_DISC函數傳回Subtotal值是139.80。
- PD(0.2)，對應的CUME_DIST函數值是：0.2，PERCENTILE_DISC函數傳回Subtotal值是220.00。
- …以此類推。
執行範例程式碼13，新增一筆資料列，資料行CustomerID為ANATR時，指定Subtotal值為514.40，刻意讓兩筆資料列的Subtotal值是相同的。
--新增一筆資料列，資料行CustomerID為FOLKO時，指定Subtotal值為9999.40，讓此客戶的資料列有20筆。
INSERT dbo.CReport1 VALUES('FOLKO', '1998-07-07', '9999.00', N'Bräcke')
GO
範例程式碼13：新增一筆資料列，讓此客戶的資料列有20筆
執行範例程式碼14，檢視其執行結果，請參考下圖17所示：
-- PERCENTILE_DISC 視窗統計分佈函數，以 0.05 為一個級距，分割為 20 份。
SELECT CustomerID, Subtotal,
RANK() OVER (PARTITION BY CustomerID ORDER BY Subtotal) N'RANK',
CUME_DIST() OVER (PARTITION BY CustomerID ORDER BY Subtotal) N'CUME_DIST',
PERCENTILE_DISC(0.05) WITHIN GROUP ( ORDER BY Subtotal) OVER (PARTITION BY CustomerID) N'PD(0.05)',
PERCENTILE_DISC(0.1) WITHIN GROUP ( ORDER BY Subtotal) OVER (PARTITION BY CustomerID) N'PD(0.1)',
PERCENTILE_DISC(0.15) WITHIN GROUP ( ORDER BY Subtotal) OVER (PARTITION BY CustomerID) N'PD(0.15)',
PERCENTILE_DISC(0.2) WITHIN GROUP ( ORDER BY Subtotal) OVER (PARTITION BY CustomerID) N'PD(0.2)',
PERCENTILE_DISC(0.25) WITHIN GROUP ( ORDER BY Subtotal) OVER (PARTITION BY CustomerID) N'PD(0.25)',
PERCENTILE_DISC(0.3) WITHIN GROUP ( ORDER BY Subtotal) OVER (PARTITION BY CustomerID) N'PD(0.3)',
PERCENTILE_DISC(0.35) WITHIN GROUP ( ORDER BY Subtotal) OVER (PARTITION BY CustomerID) N'PD(0.35)',
PERCENTILE_DISC(0.4) WITHIN GROUP ( ORDER BY Subtotal) OVER (PARTITION BY CustomerID) N'PD(0.4)',
PERCENTILE_DISC(0.45) WITHIN GROUP ( ORDER BY Subtotal) OVER (PARTITION BY CustomerID) N'PD(0.45)',
PERCENTILE_DISC(0.5) WITHIN GROUP ( ORDER BY Subtotal) OVER (PARTITION BY CustomerID) N'PD(0.5)',
PERCENTILE_DISC(0.55) WITHIN GROUP ( ORDER BY Subtotal) OVER (PARTITION BY CustomerID) N'PD(0.55)',
PERCENTILE_DISC(0.6) WITHIN GROUP ( ORDER BY Subtotal) OVER (PARTITION BY CustomerID) N'PD(0.6)',
PERCENTILE_DISC(0.65) WITHIN GROUP ( ORDER BY Subtotal) OVER (PARTITION BY CustomerID) N'PD(0.65)',
PERCENTILE_DISC(0.7) WITHIN GROUP ( ORDER BY Subtotal) OVER (PARTITION BY CustomerID) N'PD(0.7)',
PERCENTILE_DISC(0.75) WITHIN GROUP ( ORDER BY Subtotal) OVER (PARTITION BY CustomerID) N'PD(0.75)',
PERCENTILE_DISC(0.8) WITHIN GROUP ( ORDER BY Subtotal) OVER (PARTITION BY CustomerID) N'PD(0.8)',
PERCENTILE_DISC(0.85) WITHIN GROUP ( ORDER BY Subtotal) OVER (PARTITION BY CustomerID) N'PD(0.85)',
PERCENTILE_DISC(0.9) WITHIN GROUP ( ORDER BY Subtotal) OVER (PARTITION BY CustomerID) N'PD(0.9)',
PERCENTILE_DISC(0.95) WITHIN GROUP ( ORDER BY Subtotal) OVER (PARTITION BY CustomerID) N'PD(0.95)',
PERCENTILE_DISC(1.0) WITHIN GROUP ( ORDER BY Subtotal) OVER (PARTITION BY CustomerID) N'PD(1.0)'
FROM dbo.CReport1
WHERE CustomerID = 'FOLKO'
GO
範例程式碼14：：檢視執行結果集-PERCENT_DISC視窗統計分佈函數，以0.05為一個級距
圖17：檢視執行結果集- PERCENT_DISC視窗統計分佈函數，以0.05為一個級距
在圖17中，我們使用PERCENTILE_DISC視窗統計分佈函數，設定的百分位數是0.05為基數，也就是以0.05為一個級距，將資料分割為20份。
執行範例程式碼15，刪除先所新增加的資料列：
-- 刪除先前新增加的資料列
DELETE dbo.CReport1 WHERE CustomerID ='FOLKO' AND OrderDate ='1998-07-07'
GO
範例程式碼15：刪除先前新增加的資料列
若是沒有指定PARTITON BY次子句，此PERCENTILE_DISC函數會將查詢結果集的所有資料列視為單一群組，請參考範例程式碼16，以及下圖18所示：
-- 若沒有搭配 PARTITION BY，此PERCENTILE_DISC函數會將查詢結果集的所有資料列視為單一群組，總共有830筆資料列
SELECT CustomerID, Subtotal,
RANK() OVER (PARTITION BY CustomerID ORDER BY Subtotal) N'RANK',
CUME_DIST() OVER (ORDER BY Subtotal) N'CUME_DIST',
PERCENTILE_DISC(0.1) WITHIN GROUP ( ORDER BY Subtotal) OVER () N'PD(0.1)',
PERCENTILE_DISC(0.2) WITHIN GROUP ( ORDER BY Subtotal) OVER () N'PD(0.2)'
FROM dbo.CReport1
-- WHERE CustomerID = 'BSBEV'
GO
範例程式碼16：PERCENTILE_DISC函數沒有指定PARTITON BY次子句，將所有資料列視為單一群組
圖18：檢視執行結果集-沒有指定PARTITON BY次子句，將所有資料列視為單一群組
在圖18中，沒有指定PARTITON BY次子句，此PERCENTILE_DISC視窗統計分佈函數會將查詢結果集的所有資料列視為單一群組，總共有830筆資料列，其運算方式如下：
- PD(0.1)，對應的CUME_DIST函數值是：0.1，PERCENTILE_DISC函數傳回Subtotal值是190.00。
認識PERCENTILE_CONT
PERCENTILE_CONT視窗統計分布函數會依據資料行值的連續分佈(continuous distribution)特性來計算百分位數，而其回傳的計算值則是以內插值(interpolated)來取代，這可能會不等於資料行內的任何特定值。這是與PERCENTILE_DISC視窗統計分布函數回傳的計算值是不同的。
以下是PERCENTILE_CONT統計分佈函數的運算公式：
如果 CEILING(RN) = FLOOR(RN) = RN，那麼回傳的結果是 RN。
否則，PERCENTILE_CONT其計算的方式如下：
(CEILING(RN) - RN) * (依據FLOOR(RN)函數運算所對應的資料行值) + (RN - FLOOR(RN)) * (依據CEILING(RN)函數運算所對應的資料行值)
--
RN = (1 + (P * ( N - 1 ))
P = 要運算的百分位數
N = 資料列的筆數
CEILING()：傳回大於或等於指定數值運算式的最小整數。也就是說，無條件進位到整數。
FLOOR()：傳回小於或等於指定數值運算式的最大整數。也就是說，無條件捨去到整數。
截至目前為止，SQL Server 2012線上說明都未進一步說明PERCENTILE_CONT視窗統計分布函數所使用的公式和運算方式，筆者是參考以下的文件來整理：
- 書本：Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions
- Oracle 10g R2 線上說明 PERCENTILE_CONT
其語法如下：
PERCENTILE_CONT ( numeric_literal )
WITHIN GROUP ( ORDER BY order_by_expression [ ASC | DESC ] )
OVER ( [ <partition by 次子句> ] )
使用的引數：
- numeric_literal：要運算的百分位數。其值的範圍必須介於0.0到1.0之間。
- WITHIN GROUP ( ORDER BY order_by_expression [ ASC | DESC ])：指定要用以邏輯排序以及計算百分位數的數值清單。只允許一個order_by_expression。此運算式計算出來結果，必須是精確數值類型(例如：int、bigint、smallint、tinyint、numeric、bit、decimal、smallmoney、money等)或是近似數值類型(例如：float、real等)。而預設的排序順序為遞增。
- OVER ( <partition by 次子句> )：將FROM子句所產生的結果集，分割成套用百分位數函數的資料分割。目前不支援PERCENTILE_CONT視窗統計分布函數中指定OVER語法的<ORDER BY次子句>以及<ROWS或RANGE子句>。
注意事項：
- 傳回類型，float(53)，回傳介於0到1的float(53)類型數值結果。
- WITHIN GROUP關鍵字是屬於資料庫相容性層級110內的保留關鍵字。
- 結果集中若是有NULL 值，將會忽略不計算，不影響原本結果集的運算結果。
接下來，使用範例程式碼17來說明PERCENTILE_CONT視窗統計分佈函數的使用方式。請參考下圖19所示：
需求：使用PERCENTILE_CONT以及PERCENTILE_DISC視窗統計分佈函數，來計算各CustomerID的Subtotal之中間值，所以，設定其百分位數是：0.5。
-- 認識PERCENTILE_CONT視窗統計分佈函數
SELECT CustomerID, OrderDate, Subtotal,
RANK() OVER (PARTITION BY CustomerID ORDER BY Subtotal) N'RANK',
CUME_DIST() OVER (PARTITION BY CustomerID ORDER BY Subtotal) N'CUME_DIST',
PERCENTILE_DISC(0.5) WITHIN GROUP ( ORDER BY Subtotal) OVER (PARTITION BY CustomerID) N'PD(0.5)',
PERCENTILE_CONT(0.5) WITHIN GROUP ( ORDER BY Subtotal) OVER (PARTITION BY CustomerID) N'PC(0.5)'
FROM dbo.CReport1
WHERE CustomerID = 'ALFKI'
GO
範例程式碼17：認識PERCENTILE_CONT視窗統計分佈函數
圖19：檢視執行結果集-認識PERCENT_CONT視窗統計分佈函數
在圖19中，以資料行CustomerID值是ALFKI為例，小計有六筆資料列，其運算方式如下：
已知：
- P = 要運算的百分位數 = 0.5。
- N = 資料列的筆數 = 6。
計算PERCENTILE_DISC(0.5)值：
- 第一個值大於或等於0.5 (依據CUME_DIST()視窗統計分布函數計算出的內容值)是第三筆資料列，所以PERCENTILE_DISC(0.5) 是814.50。
計算PERCENTILE_CONT(0.5)：
- 資料列的筆數N = 6(偶數)，而要運算的百分位數P = 0.5。
- 計算RN值 = (1 + (0.5 * ( 6 - 1 )) = 3.5。
- 分別利用CEILING()以及FLOOR()函數，來計算CEILING(RN)與FLOOR(RN)的值。
- CEILING(RN) = CEILING(3.5) = 4。
- FLOOR(RN) = FLOOR(3.5) = 3。
- 依據公式，CEILING(RN)不等於FLOOR(RN)。
- 因此，PERCENTILE_CONT(0.5)的值是：(4 - 3.5) * 814.50 + (3.5 - 3) * 845.80 = 830.150。
以下的範例是使用PERCENTILE_CONT視窗統計分布函數：設定要運算的百分位數，由 0.0 計算到 1.0，請參考範例程式碼18，以及下圖20所示：
-- 使用PERCENTILE_CONT視窗統計分布函數：設定要運算的百分位數，由 0.0 計算到 1.0，以 0.1 為一個級距，分割為 10 份。
SELECT CustomerID, OrderDate, Subtotal,
RANK() OVER (PARTITION BY CustomerID ORDER BY Subtotal) N'RANK',
CUME_DIST() OVER (PARTITION BY CustomerID ORDER BY Subtotal) N'CUME_DIST',
PERCENTILE_CONT(0.0) WITHIN GROUP ( ORDER BY Subtotal) OVER (PARTITION BY CustomerID) N'PC(0.0)',
PERCENTILE_CONT(0.1) WITHIN GROUP ( ORDER BY Subtotal) OVER (PARTITION BY CustomerID) N'PC(0.1)',
PERCENTILE_CONT(0.2) WITHIN GROUP ( ORDER BY Subtotal) OVER (PARTITION BY CustomerID) N'PC(0.2)',
PERCENTILE_CONT(0.3) WITHIN GROUP ( ORDER BY Subtotal) OVER (PARTITION BY CustomerID) N'PC(0.3)',
PERCENTILE_CONT(0.4) WITHIN GROUP ( ORDER BY Subtotal) OVER (PARTITION BY CustomerID) N'PC(0.4)',
PERCENTILE_CONT(0.5) WITHIN GROUP ( ORDER BY Subtotal) OVER (PARTITION BY CustomerID) N'PC(0.5)',
PERCENTILE_CONT(0.6) WITHIN GROUP ( ORDER BY Subtotal) OVER (PARTITION BY CustomerID) N'PC(0.6)',
PERCENTILE_CONT(0.7) WITHIN GROUP ( ORDER BY Subtotal) OVER (PARTITION BY CustomerID) N'PC(0.7)',
PERCENTILE_CONT(0.8) WITHIN GROUP ( ORDER BY Subtotal) OVER (PARTITION BY CustomerID) N'PC(0.8)',
PERCENTILE_CONT(0.9) WITHIN GROUP ( ORDER BY Subtotal) OVER (PARTITION BY CustomerID) N'PC(0.9)',
PERCENTILE_CONT(1.0) WITHIN GROUP ( ORDER BY Subtotal) OVER (PARTITION BY CustomerID) N'PC(1.0)'
FROM dbo.CReport1
WHERE CustomerID = 'BSBEV'
GO
範例程式碼18：使用PERCENTILE_CONT視窗統計分佈函數1
圖20：檢視執行結果集-使用PERCENTILE_CONT視窗統計分佈函數1
在圖20中，以資料行CustomerID值是BSBEV為例，小計有十筆資料列，可以觀察使用PERCENTILE_CONT視窗統計分布函數要運算的百分位數，以0.1為一個級距，以內插值方式，分割為10份。
以下的範例是使用PERCENTILE_CONT視窗統計分布函數，當資料行CustomerID為ALFKI時，求取Subtotal的中位數，請參考範例程式碼19，以及下圖21所示：
-- PERCENTILE_CONT視窗統計分佈函數，求取Subtotal的中位數
SELECT CustomerID, Subtotal,
PERCENTILE_DISC(0.5) WITHIN GROUP ( ORDER BY Subtotal) OVER (PARTITION BY CustomerID) N'中位數_DISC(0.5)',
PERCENTILE_CONT(0.5) WITHIN GROUP ( ORDER BY Subtotal) OVER (PARTITION BY CustomerID) N'中位數_CONT(0.5)',
PERCENT_RANK() OVER (PARTITION BY CustomerID ORDER BY Subtotal) N'PERCENT_RANK'
FROM dbo.CReport1
WHERE CustomerID = 'ALFKI'
GO
範例程式碼19：使用PERCENTILE_CONT視窗統計分佈函數，求取Subtotal的中位數
圖21：檢視執行結果集-使用PERCENTILE_CONT視窗統計分佈函數，求取Subtotal的中位數
在圖21中，以資料行CustomerID值是ALFKI為例，小計有六筆資料列，使用PERCENTILE_CONT視窗統計分佈函數，求取Subtotal的中位數，其運算方式如下：
- 使用PERCENT_RANK()視窗統計分佈函數計算出的值是：0、0.2、0.4、0.5、0.6、0.8以及1。
- 需求是：使用內插值方式，計算出PERCENT_RANK()的值為0.5時，Subtotal的中位數為多少？
- 求取的中位數是：介於814.50(排名百分率為0.4)以及845.80(排名百分率為0.6)之間時，其計算結果是：(814.5+845.8)/2 = 830.150。
以下的範例是使用PERCENTILE_CONT視窗統計分布函數，求取每一位客戶Subtotal的中位數，請參考範例程式碼20，以及下圖22所示：
-- 使用PERCENTILE_CONT視窗統計分佈函數，求取每一位客戶Subtotal的中位數
SELECT DISTINCT CustomerID,
PERCENTILE_DISC(0.5) WITHIN GROUP ( ORDER BY Subtotal) OVER (PARTITION BY CustomerID) N'中位數_DISC(0.5)',
PERCENTILE_CONT(0.5) WITHIN GROUP ( ORDER BY Subtotal) OVER (PARTITION BY CustomerID) N'中位數_CONT(0.5)'
FROM dbo.CReport1
GO
範例程式碼20：使用PERCENTILE_CONT視窗統計分佈函數，求取每一位客戶Subtotal的中位數
圖22：檢視執行結果集-使用PERCENTILE_CONT視窗統計分佈函數，求取每一位客���Subtotal的中位數
在圖22中，需求是求取每一位客戶Subtotal的中位數：
- 以使用PERCENTILE_DISC視窗統計分佈函數為例，傳回在結果集中的實際值。
- 以使用PERCENTILE_CONT視窗統計分佈函數為例，以內插值方式來計算。
再舉一個範例來說明，先建立範例資料表tbInterpolated，請參考範例程式碼21，以及下圖23所示：
-- 建立範例資料表 tbInterpolated
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tbInterpolated]') AND type in (N'U'))
DROP TABLE [dbo].[tbInterpolated]
GO
CREATE TABLE tbInterpolated
(CustomerID varchar(10), OrderDate date, Subtotal money)
GO
INSERT dbo.tbInterpolated VALUES
('A00', '20111201', '0'),
('A01', '20120101', '1'), ('A02', '20120102', '2'), ('A03', '20120103', '3'), ('A04', '20120104', '4'), ('A05', '20120105', '5'),
('A06', '20120106', '6'), ('A07', '20120107', '7'), ('A08', '20120108', '8'), ('A09', '20120109', '9'), ('A10', '20120110', '10');
GO
-- 查詢資料表 tbInterpolated
SELECT * FROM dbo.tbInterpolated
GO
範例程式碼21：建立範例資料表tbInterpolated
圖23：檢視執行結果集-範例資料表tbInterpolated
在圖22中，可以觀察到範例資料表tbInterpolated的資料行Subtotal之值由0.00開始，每次增加1.0，逐筆增加到10.00為主。
接下來，使用範例程式碼22，對範例資料表tbInterpolated使用PERCENTILE_CONT視窗統計分佈函數，由0.00開始，以0.1為一個級距，以內插值方式來計算。請參考下圖24所示：
-- 使用 PERCENTILE_CONT() 函數，設定要運算的百分位數，由 0.0 開始，以0.1為一個級距，以內插值方式來計算
SELECT CustomerID, OrderDate, Subtotal,
RANK() OVER (ORDER BY Subtotal) N'RANK',
CUME_DIST() OVER (ORDER BY Subtotal) N'CUME_DIST',
PERCENTILE_CONT(0.0) WITHIN GROUP ( ORDER BY Subtotal) OVER () N'PC(0.0)',
PERCENTILE_CONT(0.1) WITHIN GROUP ( ORDER BY Subtotal) OVER () N'PC(0.1)',
PERCENTILE_CONT(0.2) WITHIN GROUP ( ORDER BY Subtotal) OVER () N'PC(0.2)',
PERCENTILE_CONT(0.3) WITHIN GROUP ( ORDER BY Subtotal) OVER () N'PC(0.3)',
PERCENTILE_CONT(0.4) WITHIN GROUP ( ORDER BY Subtotal) OVER () N'PC(0.4)',
PERCENTILE_CONT(0.5) WITHIN GROUP ( ORDER BY Subtotal) OVER () N'PC(0.5)',
PERCENTILE_CONT(0.6) WITHIN GROUP ( ORDER BY Subtotal) OVER () N'PC(0.6)',
PERCENTILE_CONT(0.7) WITHIN GROUP ( ORDER BY Subtotal) OVER () N'PC(0.7)',
PERCENTILE_CONT(0.8) WITHIN GROUP ( ORDER BY Subtotal) OVER () N'PC(0.8)',
PERCENTILE_CONT(0.9) WITHIN GROUP ( ORDER BY Subtotal) OVER () N'PC(0.9)',
PERCENTILE_CONT(1.0) WITHIN GROUP ( ORDER BY Subtotal) OVER () N'PC(1.0)'
FROM dbo.tbInterpolated
GO
範例程式碼22：使用PERCENTILE_CONT視窗統計分佈函數，以0.1為一個級距
圖24：檢視執行結果集-使用PERCENTILE_CONT視窗統計分佈函數，以0.1為一個級距
結語
在本期文章中，介紹了認識視窗統計分佈函數、認識CUME_DIST、認識PERCENT_RANK、認識PERCENTILE_DISC、認識PERCENTILE_CONT等主題。
在下一期的文章裡，我們將繼續討論其他SQL視窗函數。
訂閱