MS SQL Server：排名函数详解

墨蓝 2022-01-06 05:53 252阅读 0赞

排名函数 详解  
[**SQL**][SQL] [**Server**][SQL] 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

转载于:https://www.cnblogs.com/zhangchenliang/archive/2012/12/07/2806815.html

[SQL]: http://www.cnblogs.com/zhangchenliang/admin/