適用版本: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
沒有留言:
張貼留言