MS SQL Server：次序函數詳解

次序函數 詳解
SQL Server 2005 能夠在 T-SQL 代碼中對資料行進行排名。次序函數（ranking function）能對每一個資料行進行排名，從而提供一種以升序來組織輸出的方法。可以給每一行一個唯一的序號，或者給每一組相似的行相同的序號。

次序函數有四種類型：
        ROW_NUMBER ：為查詢的結果行提供連續的整數值。
        RANK ：為行的集合提供升序的、非唯一的排名序號，對於具有相同值的行，給予相同的序號。由於行的序號有相同的值，因此，要跳過一些序號。
        DENSE_RANK ：與RANK類似，不過，無論有多少航具有相同的序號，DENSE_RANK放回的每一行的序號將比前一個序號增加1.
        NTILE ：把從查詢中擷取的行放置到具有相同的（或儘可能相同的）行數的、特定序號的組中，NTILE 返回行所屬的組的序號。

次序函數文法如下：
<function_name>() OVER ([PARTITION BY <partition_by_list>])
ORDER BY < order_by_list >

按照選項到來的順序給定這些選項，可以看到能夠怎樣在 SELECT 語句中放置選項，例如：
        function_name ：可以是 ROW_NUMBER / RANK / DENSE_RANK / NTILE 之一。
        OVER ：定義排名應該如何對資料排序或劃分。
        PARTITION BY ：定義列將使用什麼資料作為劃分的基準。
        ORDER BY ：定義資料排序的詳情。

以下樣本顯示了用在同一查詢中的四個次序函數。有關每個函數的具體樣本，請參閱每個次序函數。
USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName
    ,ROW_NUMBER() OVER (ORDER BY a.PostalCode) AS 'Row Number'
    ,RANK() OVER (ORDER BY a.PostalCode) AS 'Rank'
    ,DENSE_RANK() OVER (ORDER BY a.PostalCode) AS 'Dense Rank'
    ,NTILE(4) OVER (ORDER BY a.PosthalCode) AS 'Quartile'
    ,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s
    INNER JOIN Person.Contact c
        ON s.SalesPersonID = c.ContactID
    INNER JOIN Person.Address a
        ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL
    AND SalesYTD <> 0;

下面進行詳細的說明。

1. ROW_NUMBER
說明：返回結果集分區內行的序號，每個分區的第一行從 1 開始。
文法：
ROW_NUMBER ( ) OVER ( [ <partition_by_clause> ] <order_by_clause> )

例1：以下樣本將根據年初至今的銷售額，返回 AdventureWorks 中銷售人員的 ROW_NUMBER。
USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName
    ,ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS 'Row Number'
    ,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s
    INNER JOIN Person.Contact c
        ON s.SalesPersonID = c.ContactID
    INNER JOIN Person.Address a
        ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL
    AND SalesYTD <> 0;

注意：
OVER 子句中的 ORDER BY 將對 ROW_NUMBER 進行排序。如果將 ORDER BY 子句添加到以非 'Row Number' the 的某列（或多列）作為排序依據的 SELECT 語句，則結果集將以外部的 ORDER BY 為依據進行排序。

例2：以下樣本將返回行號為 50 到 60 的行（包含這兩行），並按 OrderDate 進行排序。
USE AdventureWorks;
GO
WITH OrderedOrders AS
(
    SELECT SalesOrderID, OrderDate,
    ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber'
    FROM Sales.SalesOrderHeader
)
SELECT *
FROM OrderedOrders
WHERE RowNumber BETWEEN 50 AND 60;
GO

例3：以下樣本說明了如何使用 PARTITION BY 參數。
USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName
    ,ROW_NUMBER() OVER
    (PARTITION BY PostalCode ORDER BY SalesYTD DESC) AS 'Row Number'
    ,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s
    INNER JOIN Person.Contact c
        ON s.SalesPersonID = c.ContactID
    INNER JOIN Person.Address a
        ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL
    AND SalesYTD <> 0;
GO

2. RANK
說明：返回結果集的分區內每行的排名。行的排名是相關行之前的排名數加一。

樣本：以下樣本按照數量對清單中的產品進行了排名。行集按 LocationID 分區，按 Quantity 排序。注意，OVER 子句中的 ORDER BY 對 RANK 進行排序，SELECT 語句的 ORDER BY 對結果集進行排序。
USE AdventureWorks;
GO
SELECT i.ProductID, p.Name, i.LocationID, i.Quantity
    ,RANK() OVER
    (PARTITION BY i.LocationID ORDER BY i.Quantity DESC) AS 'RANK'
FROM Production.ProductInventory i
    INNER JOIN Production.Product p
        ON i.ProductID = p.ProductID
ORDER BY p.Name;
GO

3. DENSE_RANK

4. NTILE
說明：將有序分區中的行分發到指定數目的組中。各個組有編號，編號從一開始。對於每一個行，NTILE 將返回此行所屬的組的編號。

參數：
integer_expression ：
一個正整數常量運算式，用於指定每個分區必須被劃分成的組數。integer_expression 的類型可以為 int 或 bigint。

注意：
integer_expression 只能引用 PARTITION BY 子句中的列。integer_expression 不能引用在當前 FROM 子句中列出的列。

備忘：
如果分區的行數不能被 integer_expression 整除，則將導致一個成員有兩種大小不同的組。按照 OVER 子句指定的順序，較大的組排在較小的組前面。例如，如果總行數是 53，組數是 5，則前三個組每組包含 11 行，其餘兩個組每組包含 10 行。另一方面，如果總行數可被組數整除，則行數將在組之間平均分布。例如，如果總行數為 50，有五個組，則每組將包含 10 行。

例1：將行分為組
以下樣本將行分成四組。由於總行數不能被組數整除，因此第一個組將包含四行，其餘每組包含三行。
USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName
    ,NTILE(4) OVER(ORDER BY SalesYTD DESC) AS 'Quartile'
    ,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s
    INNER JOIN Person.Contact c
        ON s.SalesPersonID = c.ContactID
    INNER JOIN Person.Address a
        ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL
    AND SalesYTD <> 0;
GO

例2：使用 PARTITION BY 劃分結果集
以下樣本將 PARTITION BY 參數添加到樣本 A 中的代碼。首先按 PostalCode 將行分區，然後在每個 PostalCode 內將行分成四個組。注意，OVER 子句中的 ORDER BY 對 NTILE 進行排序，SELECT 語句的 ORDER BY 對結果集進行排序。
USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName
    ,NTILE(4) OVER(PARTITION BY PostalCode ORDER BY SalesYTD DESC) AS 'Quartile'
    ,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s
    INNER JOIN Person.Contact c
        ON s.SalesPersonID = c.ContactID
    INNER JOIN Person.Address a
        ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL
    AND SalesYTD <> 0
ORDER BY LastName;
GO