RANKING FUNCTIONS

Hola que tal amigos, hoy les explicaré lo que en el mundo de SQL SERVER se conoce como RANKING FUNCTIONS, las cuales son:

1. ROW_NUMBER.
2. RANK.
3. DENSE_RANK.
4. NTILE.

Utilizaremos la base de datos de ejemplo AdventureWorks2012 ( aquí podemos encontrar la liga para descargar los archivos y la forma de instalarlo ), además de la vista vSalesPerson, para nuestros ejercicios de ejemplo solo utilizaremos las siguientes columnas.

SELECT BusinessEntityID, FirstName, LastName, City
, StateProvinceName, CountryRegionName, SalesYTD, SalesLastYear
FROM Sales.vSalesPerson

ROW_NUMBER

Nos ayuda a enumerar las filas obtenidas en nuestro resultado, es indispensable la clausula OVER( ORDER BY nombreColumna ):

SELECT
ROW_NUMBER() OVER( ORDER BY CountryRegionName ) as colRowNumber
,BusinessEntityID, FirstName, LastName, City, StateProvinceName
, CountryRegionName, SalesYTD, SalesLastYear
FROM Sales.vSalesPerson

Ahora, agreguemos la clausula PARTITION BY:

SELECT
ROW_NUMBER() OVER( PARTITION BY CountryRegionName ORDER BY CountryRegionName ) as colRowNumber
,BusinessEntityID, FirstName, LastName, City, StateProvinceName
, CountryRegionName, SalesYTD, SalesLastYear 
FROM Sales.vSalesPerson

El valor del consecutivo se reinicia una vez que el valor de la columna que especifiquemos en la clausula PARTITION BY cambia.

RANK, DENSE_RANK:

Para poder explicárselos, les mostraré el resultado del siguiente query:

SELECT 
ROW_NUMBER() OVER( ORDER BY StateProvinceName DESC ) as colRowNumber
,RANK() OVER( ORDER BY StateProvinceName DESC ) as colRank
,DENSE_RANK() OVER( ORDER BY StateProvinceName DESC ) as colDenseRank
,BusinessEntityID, FirstName, LastName, City, StateProvinceName
,CountryRegionName, SalesYTD, SalesLastYear
 FROM Sales.vSalesPerson

Como se observa, RANK ofrece resultados salteados al contrario de DENSE_RANK, con este ultimo jamás se perderá la secuencia y con RANK dependerá de la cantidad de filas con similar resultado en la columna que especificamos ( que en este caso es StateProvinceName ); como la ejecución del query encontró 4 filas con el valor Washington, el siguiente será 5 para RANK y 2 para DENSE_RANK.

Agregando la clausula PARTITION BY, funciona de la misma manera que en la función ROW_NUMBER(), reinicia el valor cada vez que el valor de la columna especificada cambia:

SELECT
RANK() OVER( PARTITION BY CountryRegionName ORDER BY StateProvinceName DESC ) as colRank
,DENSE_RANK() OVER( PARTITION BY CountryRegionName ORDER BY StateProvinceName DESC ) as colDenseRank
,BusinessEntityID, FirstName, LastName, City
,StateProvinceName, CountryRegionName, SalesYTD, SalesLastYear 
FROM Sales.vSalesPerson

Los cuadros de colores señalan los grupos de acuerdo a la columna indicada en la clausula PARTITION BY, a su vez en el último grupo podemos observar numeración salteada para el caso de la columna colRank y numeración continua para colDenseRank.

NTILE

Distribuye las filas y las divide en cierta cantidad de particiones indicadas en la función. Cabe señalar que esta distribución la realiza el motor automáticamente.

SELECT
NTILE(3) OVER( ORDER BY CountryRegionName ) as colNTile
,BusinessEntityID, FirstName, LastName, City
, StateProvinceName, CountryRegionName, SalesYTD, SalesLastYear 
FROM Sales.vSalesPerson

SELECT
NTILE(5) OVER( ORDER BY CountryRegionName ) as colNTile
,BusinessEntityID, FirstName, LastName, City
,StateProvinceName, CountryRegionName, SalesYTD, SalesLastYear 
FROM Sales.vSalesPerson

SELECT
NTILE(10) OVER( ORDER BY CountryRegionName ) as colNTile
,BusinessEntityID, FirstName, LastName, City
,StateProvinceName, CountryRegionName, SalesYTD, SalesLastYear 
FROM Sales.vSalesPerson

SELECT
NTILE(20) OVER( ORDER BY CountryRegionName ) as colNTile
,BusinessEntityID, FirstName, LastName, City
,StateProvinceName, CountryRegionName, SalesYTD, SalesLastYear 
FROM Sales.vSalesPerson

Cabe señalar que si la cantidad de grupos deseada es mayor a la cantidad de filas, mostrará un resultado como el anterior, muy parecido a un ROW_NUMBER.

Agregando la clausula PARTITION BY, las particiones ahora se realizan por grupos, estos grupos se definen por el valor de la columna indicada en la clausula. En el ejemplo, deseamos obtener particiones de 3 por cada grupo de la columna CountryRegionName:

SELECT
NTILE(3) OVER( PARTITION BY CountryRegionName ORDER BY BusinessEntityID ) as colNTile
,BusinessEntityID, FirstName, LastName, City, StateProvinceName
,CountryRegionName, SalesYTD, SalesLastYear 
FROM Sales.vSalesPerson

El único grupo del ejemplo que permite particionar sus filas en 3 subgrupos más es United States, debido a que es el único que contiene más de 3 filas.

A grandes rasgos, este es el funcionamiento de las llamadas RANKING FUNCTIONS, es importante tenerlas presente porque en muchas ocasiones pueden ayudarnos a obtener el resultado esperado.

Cualquier duda, sugerencia o comentario, será bien recibido( y aclarado).

SALUDOS

COMPARTE ESTA INFORMACION SI TE PARECIO INTERESANTE