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


 

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

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

  1. rique disse:

    Cara, parabens pela Iniciativa
    A area de TI seria muito mais democratica si tivesemos mais pessoas com iniciativas como a sua
    parabens novamente

  2. Nane disse:

    Oi Dre..

    To passando seu blog para um amigo que tá se preparando para certificação..

    Nane

  3. Simão disse:

    Parabéns pela sua iniciativa, estou começando nesta área e vejo que pessoas como você merecem ser divulgadas, espero poder ler mais sobre seus pots.

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s

%d blogueiros gostam disto: