搜尋本站文章

2012-10-08

SQL Server:認識「次序函數(Window Ranking Functions)」(2)


延續前一篇的討論:

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

認識 PARTITION BY 引數


/*
PARTITION BY 引數

將查詢結果集分成幾個資料分割。
視窗函數會分別套用至每個資料分割,並且針對每個資料分割重新開始計算。
*/



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


-- 01_依據 UnitPrice 由高到低來排序



-- EX1:認識 ROW_NUMBER() 次序函數與 PARTITION BY 引數

-- 因為沒有指定 PARTITION BY 子句,ROW_NUMBER() 次序函數應用到在結果集的所有資料列。
SELECT ProductID, ProductName, UnitPrice, CategoryID,
 ROW_NUMBER() OVER( ORDER BY UnitPrice DESC) N'RowNumber'
FROM Products;
GO


-- 02_因為沒有指定 PARTITION BY 子句,ROW_NUMBER() 次序函數應用到在結果集的所有資料列



-- ROW_NUMBER() 次序函數,使用 PARTITION BY 引數依據資料行 CategoryID 分割查詢結果集。 
SELECT ProductID, ProductName, UnitPrice, CategoryID,
 ROW_NUMBER() OVER( PARTITION BY CategoryID 
  ORDER BY UnitPrice DESC) N'RowNumber'
FROM Products;
GO


-- 03_ROW_NUMBER() 次序函數,使用 PARTITION BY 引數依據資料行 CategoryID 分割查詢結果集





-- EX2:認識 RANK() 次序函數與 PARTITION BY 引數

-- 因為沒有指定 PARTITION BY 子句,RANK() 次序函數應用到在結果集的所有資料列。
SELECT ProductID, ProductName, UnitPrice, CategoryID, 
 RANK() OVER( ORDER BY UnitPrice DESC) N'Rank'
FROM Products;
GO


-- 04_因為沒有指定 PARTITION BY 子句,RANK() 次序函數應用到在結果集的所有資料列



-- RANK() 次序函數,使用 PARTITION BY 引數依據資料行 CategoryID 分割查詢結果集。 
SELECT ProductID, ProductName, UnitPrice, CategoryID,
 RANK() OVER( PARTITION BY CategoryID 
  ORDER BY UnitPrice DESC) N'Rank'
FROM Products;
GO


-- 05_RANK() 次序函數,使用 PARTITION BY 引數依據資料行 CategoryID 分割查詢結果集






-- EX3:認識 DENSE_RANK() 次序函數與 PARTITION BY 引數

-- 因為沒有指定 PARTITION BY 子句,DENSE_RANK() 次序函數應用到在結果集的所有資料列。
SELECT ProductID, ProductName, UnitPrice, CategoryID,
 DENSE_RANK() OVER( ORDER BY UnitPrice DESC) N'DenseRank'
FROM Products;
GO


-- 06_因為沒有指定 PARTITION BY 子句,DENSE_RANK() 次序函數應用到在結果集的所有資料列



-- DENSE_RANK() 次序函數,使用 PARTITION BY 引數依據資料行 CategoryID 分割查詢結果集。 
SELECT ProductID, ProductName, UnitPrice, CategoryID, 
 DENSE_RANK() OVER( PARTITION BY CategoryID 
  ORDER BY UnitPrice DESC) N'DenseRank'
FROM Products;
GO


-- 07_DENSE_RANK() 次序函數,使用 PARTITION BY 引數依據資料行 CategoryID 分割查詢結果集






-- EX4:認識 NTILE() 次序函數與 PARTITION BY 引數

-- 因為沒有指定 PARTITION BY 子句,NTILE() 次序函數應用到在結果集的所有資料列。
-- (1-26), (27-52), (53-77)
SELECT ProductID, ProductName, UnitPrice, CategoryID,
 NTILE(3) OVER( ORDER BY UnitPrice DESC) N'Ntile'
FROM Products;
GO


-- 08_因為沒有指定 PARTITION BY 子句,NTILE() 次序函數應用到在結果集的所有資料列



-- NTILE() 次序函數,使用 PARTITION BY 引數依據資料行 CategoryID 分割查詢結果集。 
SELECT ProductID, ProductName, UnitPrice, CategoryID, 
 NTILE(3) OVER( PARTITION BY CategoryID 
  ORDER BY UnitPrice DESC) N'Ntile'
FROM Products;
GO


-- 09_NTILE() 次序函數,使用 PARTITION BY 引數依據資料行 CategoryID 分割查詢結果集






參考資料

SQL Server:認識「次序函數(Window Ranking Functions)」(1)
http://sharedderrick.blogspot.tw/2012/10/sql-serverwindow-ranking-functions1.html