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