Lição 3: Aplicando funções de rank

julho 2, 2011

 

As funções de rank são utilizadas para prover uma simples visão analítica como também uma ordenação estatística ou segmentação.

Classificando dados

Há quatro funções de rank disponíveis no SQL Server, são elas: ROW_NUMBER, RANK, DENSE_RANK E NTILE.

ROW_NUMBER retorna um número de 1 a n baseado na ordenação definida pelo usuário. Caso haja valores duplicados o ROW_NUMBER irá atribuir uma nova numeração, ignorando a repetição.

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

image

Você pode utilizar o ROW_NUMBER com um agrupamento para que a numeração seja feita de acordo com os grupos. Para conseguir esse resultado você precisará utilizar a cláusula opcional PARTITION BY.

SELECT P.FirstName
,P.LastName
,S.TerritoryID,ROW_NUMBER() OVER(PARTITION BY S.TerritoryID 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

image

Se você quiser numerar o resultado em camadas você precisará utilizar o RANK, caso não haja camadas o RANK produzirá o mesmo resultado do ROW_NUMBER.

Caso haja camadas o RANK irá repetir a mesma numeração para cada linha, deixando uma lacuna na sequência numérica correspondente ao número de linhas da camada.

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

image

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 A.LocationID,‘Rank’

image

Caso não queira lacunas na numeração você pode utilizar a função DENSE_RANK. DENSE_RANK atribui o mesmo valor para cada valor duplicado mas não deixa lacuna na sequência numérica.

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 A.LocationID,‘DenseRank’

 

image

NTILE divide o resultado da consulta em grupo, por exemplo , se você quiser  dividir um resultado  em quatro grupos com o aproximadamente o mesmo número de linhas em cada grupo, você pode utilizar o NTILE(4).

SELECT P.FirstName
            ,P.LastName
            ,NTILE(4) OVER(ORDER BY S.SalesYTD DESC) AS ‘Grupo’
            ,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

image

 

SELECT P.FirstName
,P.LastName
,NTILE(2) OVER(PARTITION BY S.TerritoryID ORDER BY S.SalesYTD DESC) AS ‘Grupo’
,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                                                     ORDER BY S.TerritoryID, ‘Grupo’

image

 

Dica para o exame

Você precisa entender a diferença entre os quatro funções de rank e quando produzem o mesmo resultado.

 

Quick Check

1 – Qual a diferença entre RANK e DENSE_RANK?

2 – Quando o ROW_NUMBER, RANK e DENSE_RANK produzem o mesmo resultado?

Respostas:

1 – RANK atribui o mesmo valor para as camadas porém deixa lacunas na sequência numérica. DENSE_RANK atribui o mesmo valor para as camadas e não deixa lacunas na sequência.

2-ROW_NUMBER, RANK e DENSE_RANK produzem o mesmo resultado quando as colunas ordenadas não possuem valores duplicados.

Anúncios