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

Lição 2: Implementando SubConsultas

junho 26, 2011

 

As subconsultas são úteis para construir consultas complexas e para obter resultados que antes pareciam impossíveis.

Existem dois tipos de subconsultas:

Correlacionada:  é uma consulta que depende e faz referência às colunas da consulta externa a qual está contida.

Não correlacionada: é uma consulta independente da consulta externa na qual está contida.

Ambos os tipos de subconsultas podem retornar uma ou mais linhas. Quando o retorno é escalar, somente um valor,  pode ser definido em qualquer lugar de dentro do SELECT onde um ou zero valores são esperados, quando o retorno é múltiplo a subconsulta pode ser utilizada em onde o resultset é esperado.

 

Consultas não correlacionadas

O principal objetivo de consultas não correlacionadas é permitir a escrita de códigos mais dinâmicos. Por exemplo, você precisa listar os produtos onde o nome da categoria inicia com a letra C, porém você desconhece todas as subcategorias necessárias para especificar na cláusula where, veja o exemplo abaixo:

SELECT P.Name, P.Color
FROM Production.Product P
     INNER JOIN Production.ProductSubcategory S
       ON P.ProductSubcategoryID = S.ProductSubcategoryID
WHERE S.ProductCategoryID IN( SELECT C.ProductCategoryID       FROM Production.ProductCategory C
WHERE Name LIKE ‘C%’)

Outro exemplo, supondo que você gostaria de retornar todos os produtos onde o preço de lista está acima da média geral, ao invés de obter o valor e armazenar em uma variável, você pode fazer como no exemplo abaixo:

 
SELECT A.ProductID, A.Name, A.ListPrice
FROM Production.Product A
WHERE A.ListPrice> (
SELECT AVG(B.ListPrice)
                    FROM Production.Product B
)
 
Tabelas derivadas 

Ao invés de armazenar dados em tabelas temporárias para fazer joins e retornar um determinado conjunto de resultados, você pode utilizar as tabelas derivadas.

Tabelas derivadas são consultas realizadas dentro da clausula from de uma consulta externa, é necessário que todos os campos calculados sejam nomeados, a consulta deve estar contida entre parênteses e possuir um apelido.

Veja o exemplo abaixo:

 
SELECT B.BusinessEntityID, B.JobTitle, A.QtdeLinhas
FROM (SELECT E.JobTitle, COUNT(*) QtdeLinhas
      FROM HumanResources.Employee E

      GROUP BY JobTitle) A

INNER JOIN HumanResources.Employee B on A.JobTitle=B.JobTitle

O príncipal benefício de uma tabela derivada é o fato de que o resultado está armazenado inteiramente na memória, resultando em um ganho de desempenho.

Agrupamentos

Você pode realizar agrupamentos dentro de tabelas derivadas e combiná-los externamente.

Como no exemplo abaixo:

SELECT SH3.SalesPersonID,SH3.OrderDate, SH3.DailyTotal, SUM(SH4.DailyTotal) RunningTotal
FROM    (SELECT SH1.SalesPersonID, SH1.OrderDate, SUM(SH1.TotalDue) DailyTotal
                FROM Sales.SalesOrderHeader SH1
                WHERE SH1.SalesPersonID  IS NOT NULL
                GROUP BY SH1.SalesPersonID, SH1.OrderDate) SH3
INNER JOIN  (SELECT SH2.SalesPersonID, SH2.OrderDate, SUM(SH2.TotalDue) DailyTotal
                FROM Sales.SalesOrderHeader SH2
                WHERE SH2.SalesPersonID  IS NOT NULL
                GROUP BY SH2.SalesPersonID, SH2.OrderDate)SH4
ON SH3.SalesPersonID = SH4.SalesPersonID
    AND SH3.OrderDate>SH4.OrderDate
GROUP BY SH3.SalesPersonID, SH3.OrderDate, SH3.DailyTotal
ORDER BY SH3.SalesPersonID, SH3.OrderDate

As tabelas derivadas foram combinadas para retornar todos os pedidos de um vendedor que possuir mais de um pedido por dia. O join por SalesPersonID (vendedor) assegura que você esta acumulando linhas do mesmo vendedor. O join sem igualdade determina que somente os pedidos do vendedor onde data do pedido é anterior à data do pedido dentro do resultset serão retornados.

 

Consultas correlacionadas

As consultas correlacionadas dependem de valores da consulta externa. No exemplo abaixo será listado o Id do Produto e o preço de lista, porém será retornado apenas os produtos que já foram vendidos anteriormente.

SELECT a.ProductID, a.ListPrice 
FROM Production.Product A
WHERE EXISTS (SELECT 1 FROM Sales.SalesOrderDetail B
WHERE B.ProductID=A.ProductID)

Usando um Join ou uma cláusula where será retornado o produto para cada vez que ele foi vendido, já o comando EXISTS provém melhor desempenho, pois procura será por uma única ocorrência na tabela.

 

[]’s

Andressa Alves Martins

Twitter: Dre_Martins Skype: Dre.alvesm

Blog: http://sqlgo.wordpress.com


Lição 1: Construindo consultas recursivas com CTE

junho 1, 2011

 

O conceito de CTE é muito similar a tabelas derivadas, porém com CTEs você pode cruzar com outros resultados para resolver um ou mais problemas, executando consultas recursivas.

 

Expressão de Tabela Comum (Common Table Expressions)

 

Sintaxe:

WITH – Cláusula onde contém o comando válido de select.

Select – Consulta que será realizada utilizando a CTE.

 

WITH EmpTitle AS

(SELECT JobTitle, COUNT(*) NumTitles

FROM HumanResources.Employee

GROUP BY JobTitle)

 

SELECT b.BusinessEntityID, b.JobTitle, a.NumTitles

FROM EmpTitle a INNER JOIN HumanResources.Employee b ON b.JobTitle = a.JobTitle

GO

 

Uma CTE recursiva expande a definição de uma expressão de tabela e consiste em duas partes:

– Uma consulta âncora, que é a fonte da recursividade, juntamente com a declaração “UNION ALL” e uma segunda consulta.

– Uma consulta externa que referencia a rotina e especifica o numero de níveis de recursão.

Por exemplo, a consulta abaixo permite que você especifique um employee e retorna  os niveis da gerencia acima desse empregado na organização, com um limite de 25 niveis de organização.

 

DECLARE @EmployeeToGetOrgFor INT=126;

WITH EMP_cte(BusinessEntityID, OrganizationNode, FirstName, LastName, JobTitle, RecursionLevel)

AS (SELECT E.BusinessEntityID, E.OrganizationNode, P.FirstName, P.LastName, E.JobTitle, 0

       FROM HumanResources.Employee e INNER JOIN Person.Person AS p

           ON P.BusinessEntityID = E.BusinessEntityID

       WHERE E.BusinessEntityID = @EmployeeToGetOrgFor

  UNION ALL

      SELECT E.BusinessEntityID, E.OrganizationNode, P.FirstName, P.LastName,     E.JobTitle, RecursionLevel+1

    FROM HumanResources.Employee E INNER JOIN EMP_cte

           ON E.OrganizationNode = EMP_cte.OrganizationNode.GetAncestor(1)

       INNER JOIN Person.Person P ON P.BusinessEntityID = E.BusinessEntityID)

 

SELECT EMP_cte.RecursionLevel, EMP_cte.BusinessEntityID, EMP_cte.FirstName, EMP_cte.LastName,   EMP_cte.OrganizationNode.ToString() AS OrganizationNode,

P.FirstName AS ‘ManagerFirstName’, P.LastName AS ‘ManagerLastName’

FROM EMP_cte INNER JOIN HumanResources.Employee E

                         ON EMP_cte.OrganizationNode.GetAncestor(1) = E.OrganizationNode

                      INNER JOIN Person.Person P ON P.BusinessEntityID = E.BusinessEntityID

ORDER BY RecursionLevel,EMP_cte.OrganizationNode.ToString()

OPTION (MAXRECURSION 25);

 

image

 

A primeira consulta com a cláusula WITH define o resultado ancora. A segunda consulta executada recursivamente até o nível máximo de recursão contra a consulta âncora.

A recursividade é realizada pelo INNER JOIN na CTE:

INNER JOIN EMP_cte

A consulta externa é então utilizada para retornar o resultado da operação recursiva enquanto dados adicionais são necessários. A cláusula “OPTION” especifica o número máximo de níveis de recursão serão permitidos.

Dica para o exame:

Caso haver um bloco de comando antes da palavra chave “WITH” utilizar “;” para finalizar o bloco de comando anterior.

 

[]’s

Andressa Alves Martins

Twitter: Dre_Martins Skype: Dre.alvesm

Blog: http://sqlgo.wordpress.com