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