SQL Server：認識「次序函數(Window Ranking Functions)」(1)

適用版本：SQL Server 2005、2008、2008 R2、2012

SQL Server 2005 版本提供了「次序函數(Window Ranking Functions)」。

排名函數會傳回資料分割中每個資料列的次序值。
根據所用的函數而定，有些資料列可能會收到與其他資料列相同的值。

排名函數不具決定性。

Transact-SQL 會提供下列排名函數：
ROW_NUMBER、RANK、DENSE_RANK、NTILE 等。

(1) ROW_NUMBER
傳回結果集資料分割內某資料列的序號，序號從 1 開始，每個資料分割第一個資料列的序號是 1。

(2) RANK
傳回結果集資料分割內，每個資料列的次序。
資料列的次序等於一加上問題資料列前面的次序數目。

(3) DENSE_RANK
傳回結果集資料分割內之資料列次序，次序中沒有任何間距。 資料列次序是一個加上相關資料列前面之相異次序的數目。

(4) NTILE
將排序資料分割中的資料列散發到指定數目的群組中。
這些群組從 1 開始編號。 對於每個資料列，NTILE 都會傳回資料列所屬群組的號碼。

---

「次序函數(Window Ranking Functions)」

請參考以下的範例程式碼：

-- 依據 UnitPrice 由高到低來排序
USE Northwind
GO
SELECT ProductID, ProductName, UnitPrice
FROM Products
ORDER BY UnitPrice DESC;
GO

-- 00_使用範例資料表

---

EX1：認識 ROW_NUMBER() 次序函數

-- EX1：認識 ROW_NUMBER() 次序函數
/*
ROW_NUMBER() 次序函數

傳回結果集資料分割內某資料列的序號，序號從 1 開始，
每個資料分割第一個資料列的序號是 1。

語法
ROW_NUMBER ( ) 
    OVER ( [ PARTITION BY value_expression , ... [ n ] ] order_by_clause )

除非下列條件成立，否則不保證使用 ROW_NUMBER() 之查詢所傳回的資料列一定會在每次執行時依照相同的方式排列。
(1) 資料分割資料行的值是唯一的。

(2) ORDER BY 資料行的值是唯一的。

(3) 資料分割資料行和 ORDER BY 資料行的值組合是唯一的。
*/
SELECT ProductID, ProductName, UnitPrice, 
 ROW_NUMBER() OVER( ORDER BY UnitPrice DESC) N'RowNumber'
FROM Products;
GO

-- 01_認識 ROW_NUMBER() 次序函數

-- Error，不能直接使用 WHERE 條件式
SELECT ProductID, ProductName, UnitPrice, 
 ROW_NUMBER() OVER( ORDER BY UnitPrice DESC) N'RowNumber'
FROM Products
WHERE RowNumber BETWEEN  11 AND 20;
GO

/*
錯誤訊息：
訊息 207，層級 16，狀態 1，行 4
無效的資料行名稱 'RowNumber'。
訊息 207，層級 16，狀態 1，行 4
無效的資料行名稱 'RowNumber'。
*/

-- 02_Error，不能直接使用 WHERE 條件式

-- 使用「子查詢(SubQuery)」與次序函數
SELECT * FROM (
 SELECT ProductID, ProductName, UnitPrice, 
 ROW_NUMBER() OVER( ORDER BY UnitPrice DESC) N'RowNumber'
 FROM Products ) rn
WHERE RowNumber BETWEEN 11 AND 10;
GO

-- 03_使用「子查詢(SubQuery)」與次序函數

-- 使用「一般資料表運算式(Common Table Expressions，CTE)」與次序函數
WITH CTE01
AS (
 SELECT ProductID, ProductName, UnitPrice, 
  ROW_NUMBER() OVER( ORDER BY UnitPrice DESC) N'RowNumber'
 FROM Products )

SELECT * FROM CTE01
WHERE RowNumber BETWEEN 6 AND 10;
GO

-- 04_使用「一般資料表運算式(Common Table Expressions，CTE)」與次序函數

---

EX2：認識 RANK() 次序函數

-- EX2：認識 RANK() 次序函數
/*
RANK() 次序函數

傳回結果集資料分割內，每個資料列的次序。
資料列的次序等於一加上問題資料列前面的次序數目。

語法
RANK ( ) OVER ( [ partition_by_clause ] order_by_clause )

如果針對某個次序聯結了兩個或更多資料列，每個聯結的資料列都會收到相同的次序。

例如，如果兩位超級業務員有相同的 SalesYTD 值，將會並列第一。
SalesYTD 次高的業務員會排名第三，因為有兩個資料列次序比它高。

因此，RANK 函數不一定會傳回連續整數。
整個查詢所用的排序順序，決定了資料列在結果集中的出現順序。
*/
SELECT ProductID, ProductName, UnitPrice, 
 RANK() OVER( ORDER BY UnitPrice DESC) N'Rank'
FROM Products;
GO

-- 05_認識 RANK() 次序函數

---

EX3：認識 DENSE_RANK() 次序函數

-- EX3：認識 DENSE_RANK() 次序函數
/*
DENSE_RANK() 次序函數

傳回結果集資料分割內之資料列次序，次序中沒有任何間距。
資料列次序是一個加上相關資料列前面之相異次序的數目。

語法：
DENSE_RANK ( ) OVER ( [  ] < order_by_clause > )

如果在相同資料分割中，針對某個次序聯結了兩個或更多資料列，每個聯結的資料列都會收到相同的次序。

例如，如果兩位超級業務員有相同的 SalesYTD 值，則會並列第一。
SalesYTD 次高的業務員之次序便是第二。 

這便是在這個資料列之前的相異資料列數加一。
因此，DENSE_RANK 函數所傳回的數目不會有間距，次序一律是連續的。

整個查詢的排序順序決定了資料列在結果中的出現順序。
這暗示著次序編號第一的資料列，並不一定是資料分割中的第一個資料列。
*/
SELECT ProductID, ProductName, UnitPrice, 
 DENSE_RANK() OVER( ORDER BY UnitPrice DESC) N'DenseRank'
FROM Products;
GO

-- 06_認識 DENSE_RANK() 次序函數

---

EX4：認識 NTILE () 次序函數

-- EX4：認識 NTILE () 次序函數
/*
NTILE() 次序函數

將排序資料分割中的資料列散發到指定數目的群組中。
這些群組從 1 開始編號。

語法
NTILE (integer_expression) OVER ( [  ] < order_by_clause > )

對於每個資料列，NTILE 都會傳回資料列所屬群組的號碼。

如果 integer_expression 無法整除資料分割中的資料列數，兩個大小的群組會相差一個成員。
在 OVER 子句所指定的順序中，較大群組會在較小群組的前面。

例如，如果資料列總數是 53，群組數目是 5，前三個群組會有 11 個資料列，後兩個群組會有 10 個資料列。
如果群組數目可以整除資料列的總數，資料列就會平均分散在各個群組中。

例如，如果資料列總數是 50，有 5 個群組，每個值區都會包含 10 個資料列。
*/
SELECT ProductID, ProductName, UnitPrice, 
 NTILE(7) OVER( ORDER BY UnitPrice DESC) N'Ntile'
FROM Products;
GO

-- 07_認識 NTILE () 次序函數

-- NTILE () 次序函數搭配 CASE() 運算式
-- (1-26), (27-52), (53-77)
SELECT ProductID, ProductName, UnitPrice, 
 CASE NTILE(3) OVER( ORDER BY UnitPrice DESC)
   WHEN 1 THEN N'高價位'
   WHEN 2 THEN N'中價位'
   WHEN 3 THEN N'低價位'
 END N'價格分類'
FROM Products;
GO

-- 08_NTILE () 次序函數搭配 CASE() 運算式

---

參考資料

次序函數 (Transact-SQL)
http://technet.microsoft.com/zh-tw/library/ms189798

ROW_NUMBER (Transact-SQL)
http://technet.microsoft.com/zh-tw/library/ms186734

RANK (Transact-SQL)
http://technet.microsoft.com/zh-tw/library/ms176102

DENSE_RANK (Transact-SQL)
http://technet.microsoft.com/zh-tw/library/ms173825

NTILE
http://technet.microsoft.com/zh-tw/library/ms175126

內建函數 (Transact-SQL)
http://technet.microsoft.com/zh-tw/library/ms174318

OVER 子句 (Transact-SQL)
http://technet.microsoft.com/zh-tw/library/ms189461