SQL Server 2005新聚合函数： Row_Number, Rank, Dense_Rank and nTile介绍

 1, Row_Number()

Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.    为查询出来的每一行记录生成一个序号。

 

Syntax:  ROW_NUMBER () OVER ( [ <partition_by_clause> ] <order_by_clause> )

                  partition 用来分组，如果有partition语句，行号将按组分配，如果没有，行号将按整个结果集分配。

                  order by 用来决定按什么顺序分配行号，这里的order by与select语句的order by没有关系。

 

使用： 数据分页， 取某一段数据， 去除重复行

 

eg. : 

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;

 

 

2, RANK()

 Returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question.   排名函数，生成的序号不一定连续，例如，有2条记录排名都为1，则接下来的排名为3，而不会出现排名为2的记录。

 

Syntax:    RANK () OVER( [ < partition_by_clause > ] < order_by_clause > )

 

使用： 排名

 

eg. :

USE AdventureWorks;
GO
SELECT i.ProductID, p.Name, i.LocationID, i.Quantity, RANK() OVER (PARTITION BY i.LocationID order by i.Quantity) as RANK
FROM Production.ProductInventory i JOIN Production.Product p
ON i.ProductID = p.ProductID
ORDER BY p.Name
GO

 

3， DENSE_RANK()

 Returns the rank of rows within the partition of a result set, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question.  dense_rank函数的功能与rank函数类似，只是在生成序号时是连续的

 

Syntax:   DENSE_RANK ( )    OVER ( [ < partition_by_clause > ] < order_by_clause > )

 

eg. : 

USE AdventureWorks;
GO
SELECT  i.ProductID, p.Name, i.LocationID, i.Quantity, DENSE_RANK() OVER (PARTITION BY i.LocationID order by i.Quantity) as DENSE_RANK
FROM Production.ProductInventory i JOIN Production.Product p ON i.ProductID = p.ProductID
ORDER BY Name;
GO

 

 

4， NTILE()

Distributes the rows in an ordered partition into a specified number of groups. The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs.  ntile函数可以对序号进行分组处理。这就相当于将查询出来的记录集放到指定长度的数组中，每一个数组元素存放一定数量的记录。

 

Syntax:   NTILE (integer_expression)    OVER ( [ <partition_by_clause> ] < order_by_clause > )

Remarks:
    If the number of rows in a partition is not divisible by expression, this will cause groups of two sizes that differ by one member. Larger groups come before smaller groups in the order specified by the OVER clause. For example if the total number of rows is 53 and the number of buckets is five, the first three buckets will have 11 rows and the two remaining buckets will have 10 rows each. If on the other hand the total number of rows is divisible by the number of buckets, the rows will be distributed evenly among the buckets. For example, if the total number of rows is 50, and there are five buckets, each bucket will contain 10 rows.

 

eg. : 

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 JOIN Person.Contact c on s.SalesPersonID = c.ContactID
JOIN Person.Address a ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL AND SalesYTD <> 0;
GO