Funciones de categoría de SQL Server: rank, dense_rank, row_number, ntile

Voy a dar un repaso a unas funciones estadísticas que os pueden ser bastante útiles en vuestras queries.
Estas funciones están disponibles a partir del SQL Server 2005.

Nos basaremos en la base de datos AdventureWork y con los siguientes datos:

select FirstName, LastName, TerritoryGroup, SalesQuota FROM Sales.vSalesPerson where SalesQuota is not null

Función row_number()
Esta función nos mostrará un número correlativo según el orden que le especifiquemos.
El orden es obligatorio.
select row_number() over (order by salesquota desc) rowNumber, FirstName, LastName, TerritoryGroup, SalesQuota FROM Sales.vSalesPerson where salesquota is not null

Con esta función hemos conseguido un número de fila correlativo ordenado descendentemente por SalesQuota.

Función RANK()
Si lo que queremos es conocer la posición individual que ocupa (el ranking) en una lista , lo obtenemos de la siguiente forma:
select rank() over (order by salesquota desc) Rank, FirstName, LastName, TerritoryGroup, SalesQuota FROM Sales.vSalesPerson where salesquota is not null

Función DENSE_RANK()
Si lo que queremos es conocer la posición global que ocupa (el ranking) en una lista , lo obtenemos de la siguiente forma:
select dense_rank() over (order by salesquota desc) dense_rank, FirstName, LastName, TerritoryGroup, SalesQuota FROM Sales.vSalesPerson where salesquota is not null

Función NTILE()
Con esta función podemos dividir en grupos de N elementos un conjunto de filas.
select NTILE(4) over (order by salesquota desc) rowNumber, FirstName, LastName, TerritoryGroup, SalesQuota FROM Sales.vSalesPerson where salesquota is not null

En este ejemplo, el conjunto de filas lo hemos dividido en 4 grupos.
En el caso que el grupo no sea divisible, los primeros grupos tendrán más elementos que los últimos.
Es decir, como 14 filas no son divisibles entre 4, el grupo 1 y 2 tiene 4 elementos, y el grupo 3 y 4 tiene 3 elementos.

Particionando los resultados
Si quisiéramos agrupar estos resultados deberíamos de usar el atributo PartitionBy.
select dense_rank() over (Partition by TerritoryGroup order by salesquota desc) rowNumber, FirstName, LastName, TerritoryGroup, SalesQuota FROM Sales.vSalesPerson where salesquota is not null

En este ejemplo, podemos ver el ranking de SalesQuota por territorio.
PartitionBy también se aplica al resto de funciones de categoría.



Otro ejemplo de Partition; esta vez mostraremos el rango que ocupa cada vendedor por territorio
select rank() over (Partition by TerritoryGroup order by salesYTD desc) rango , FirstName, LastName, TerritoryGroup, salesYTD FROM Sales.vSalesPerson

Por último, y a modo de resumen, muestro un resumen con todas las funciones de las que hemos hablado, para mostrar las diferencias.

select row_number() over (order by salesquota desc) rowNumber,  
rank() over (order by salesquota desc) rank,
dense_rank() over (order by salesquota desc) dense_rank,
NTILE(4) over (order by salesquota desc) ntile,
FirstName, LastName, TerritoryGroup, SalesQuota FROM Sales.vSalesPerson where salesquota is not null

Os dejo un enlace al MSDN donde podéis encontrar más información de estas funciones.
http://msdn.microsoft.com/es-es/library/ms189798.aspx


Saludos.