Capitulo 4 – Lección 3: Aplicando funciones de ranking

Las funciones de clasificación se utilizan para proporcionar análisis simples como ordenar estadístico o segmentación. T-SQL tiene cuatro funciones que se pueden utilizar para los datos de clasificación: ROW_NUMBER, RANK, DENSE_RANK y NTILE.

La función ROW_NUMBER asigna un número del 1 al n sobre la base de un usuario determinado orden de clasificación. ROW_NUMBER no tiene en cuenta las relaciones dentro del conjunto de resultados, así que si tiene filas con los mismos valores en la columna (s) que usted está pidiendo, los reiterados llamados a la base de datos para el mismo conjunto de resultados puede producir diferente numeración de filas.
El ejemplo siguiente devuelve el vendedor, junto con sus ventas del año hasta la fecha y se numeran en orden descendente de acuerdo a la cantidad del año a la fecha de venta:

SELECT p.FirstName, p.LastName, ROW_NUMBER() 

OVER(ORDER BY s.SalesYTD DESC)

AS 'RowNumber', s.SalesYTD, a.PostalCode

FROM Sales.SalesPerson s INNER JOIN Person.Person p
ON s.BusinessEntityID = p.BusinessEntityID
INNER JOIN Person.BusinessEntityAddress ba ON 

p.BusinessEntityID = ba.BusinessEntityID
INNER JOIN Person.Address a ON a.AddressID = ba.AddressID
WHERE s.TerritoryID IS NOT NULL

También puede utilizar la función ROW_NUMBER de un conjunto para proporcionar una secuencia de números dentro de cada grupo. Se genera un número dentro de cada grupo, proporcionando una opción (cláusula ) PARTITION BY de la siguiente manera:

SELECT p.FirstName, p.LastName ,ROW_NUMBER() 

OVER (PARTITION BY s.TerritoryID ORDER BY SalesYTD DESC) 

AS 'RowNumber', s.SalesYTD, s.TerritoryID
FROM Sales.SalesPerson s INNER JOIN Person.Person p
ON s.BusinessEntityID = p.BusinessEntityID
INNER JOIN Person.BusinessEntityAddress ba 

ON p.BusinessEntityID = ba.BusinessEntityID
INNER JOIN Person.Address a ON a.AddressID = ba.AddressID
WHERE s.TerritoryID IS NOT NULL

Si necesita un número del conjunto de resultados, pero también se refiere a las relaciones, puede utilizar la función RANK. Si el resultado no tiene ningún vínculo, RANK produce los mismos resultados que ROW_NUMBER.
Sin embargo, si hay relaciones, RANK asigna el mismo valor para cada fila que está vinculada y luego salta al siguiente valor, dejando un vacío en la secuencia correspondiente al número de filas que estaban vinculadas. Los siguientes ejemplos muestran cómo RANK se aplica a los duplicados, así como dentro de cada grupo de agregados:

SELECT a.ProductID, b.Name, a.LocationID, a.Quantity,RANK() 

OVER (PARTITION BY a.LocationID ORDER BY a.Quantity DESC) AS 'Rank'
FROM Production.ProductInventory a INNER JOIN Production.Product b
ON a.ProductID = b.ProductID
ORDER BY b.Name

SELECT a.ProductID, b.Name, a.LocationID, a.Quantity,RANK() 

OVER (PARTITION BY a.LocationID ORDER BY a.Quantity DESC) AS 'Rank'
FROM Production.ProductInventory a INNER JOIN Production.Product b
ON a.ProductID = b.ProductID
ORDER BY 'Rank'

Si no desea ninguno de los vacios en una secuencia, puede utilizar la función DENSE_RANK. DENSE_RANK asigna el mismo valor a cada duplicado, pero no produce saltos en la secuencia.
Los dos ejemplos siguientes muestran el mismo conjunto de resultados cuando se aplica DENSE_RANK:

SELECT a.ProductID, b.Name, a.LocationID, a.Quantity, DENSE_RANK() 

OVER (PARTITION BY a.LocationID ORDER BY a.Quantity DESC) 

AS 'DenseRank'
FROM Production.ProductInventory a INNER JOIN Production.Product b
ON a.ProductID = b.ProductID
ORDER BY b.Name

SELECT a.ProductID, b.Name, a.LocationID, a.Quantity, DENSE_RANK() 

OVER (PARTITION BY a.LocationID ORDER BY a.Quantity DESC) 

AS DenseRank
FROM Production.ProductInventory a INNER JOIN Production.Product b
ON a.ProductID = b.ProductID
ORDER BY DenseRank

NTILE se utiliza para dividir un conjunto de resultados en grupos aproximadamente iguales. Por ejemplo, si usted quería dividir un conjunto de resultados en seis grupos, con aproximadamente el mismo número de filas de cada grupo, se puede utilizar NTILE(6). Los siguientes ejemplos muestran cómo NTILE se puede utilizar para segmentar un conjunto de resultados:

SELECT p.FirstName, p.LastName,
NTILE(4) OVER(ORDER BY s.SalesYTD DESC) AS QuarterGroup,
s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s INNER JOIN Person.Person p
ON s.BusinessEntityID = p.BusinessEntityID
INNER JOIN Person.BusinessEntityAddress ba 

ON p.BusinessEntityID = ba.BusinessEntityID
INNER JOIN Person.Address a ON a.AddressID = ba.AddressID
WHERE s.TerritoryID IS NOT NULL

SELECT p.FirstName, p.LastName,NTILE(2) 

OVER(PARTITION BY s.TerritoryID ORDER BY s.SalesYTD DESC) 

AS QuarterGroup,
s.SalesYTD, s.TerritoryID
FROM Sales.SalesPerson s INNER JOIN Person.Person p
ON s.BusinessEntityID = p.BusinessEntityID
INNER JOIN Person.BusinessEntityAddress ba 

ON p.BusinessEntityID = ba.BusinessEntityID
INNER JOIN Person.Address a ON a.AddressID = ba.AddressID
WHERE s.TerritoryID IS NOT NULL

Resumen de la lección

• ROW_NUMBER: se utiliza para numerar las filas de forma secuencial en un conjunto de resultados, pero puede no producir los mismos resultados si hay vínculos en la columna(s) que se utiliza para la clasificación.
• RANK: asigna el mismo valor para cada fila que está vinculada y luego salta al siguiente valor, dejando un vacío en la secuencia correspondiente al número de filas que estaban vinculadas
• DENSE_RANK: asigna el mismo valor a cada duplicado, pero no produce saltos en la secuencia.
• NTILE: le permite dividir un conjunto de resultados en grupos de aproximadamente igual tamaño.