SQL SERVER - Usando CTE para criar uma tabela de intervalo


Atenção: esse texto é uma boa introdução sobre o assunto mas para quem estiver com o inglês afiado recomendo a leitura deste artigo aqui: http://sqlperformance.com/2013/01/t-sql-queries/generate-a-set-1
Nesse texto tratamos apenas de CTE que é uma boa opção para intervalos pequenos mas que acaba se tornando um gargalo de performance para intervalos muito grandes. De qualquer maneira se você está apenas se familiarizando como o assunto pode continuar sua leitura aqui e depois navegar para lá como um tópico avançado. Boa leitura.

Nesse post vamos ver como utilizar uma CTE para criar uma table-valued UDF que retorna uma tabela de intervalos para ser utilizada em nossos LEFT JOINs.

Essa é uma técnica que eu intui e tenho utilizado com sucesso principalmente em relatórios para garantir  um conjunto mínimo de resultado por exemplo em consultas de ranking ou de agrupamento em intervalo de datas. Entenda consultas de ranking aquelas que respondem a perguntas do tipo: Quais são os X mais? E consulta de agrupamento em intervalo de datas aquelas do tipo: Retorne para mim o total de vendas no período entre a data X e data Y.


Nesse tutorial estamos usando o SQL SERVER 2008 EXPRESS Edition e o banco de dados "AdventureWorks2008_Database.zip" específico dessa versão que pode ser baixado aqui. Não testei (nem vou testar) mas presumo com uma boa margem de segurança que todos os comandos utilizados aqui devem funcionar com qualquer versão do SQL SERVER a partir do 2005. Então se você possui uma versão anterior baixa encontrar a versão do banco Adventure Específico para a sua instalação. Qualquer problema comentem por favor.

O caso da classificação - Ranking



Seu chefe: - "Preciso de uma consulta que retorne o nome, sobrenome e a clasificação dos 15 melhores vendedores em um determinado mês. Considere que melhor é quem vendeu mais itens. Faça uma consulta tomando como exemplo o mês de julho de 2002 pois depois ela será repassada aos desenvolvedores que se encarregaram de parametrizar a data e aplicá-la em um relatório consultado pelo diretor de vendas regional."

Você então prepara essa consulta:

SELECT TOP 15 RANK() over(order BY SUM(OD.OrderQty) DESC) as POSICAO, SUM(OD.OrderQty) TTVENDAS,  SP.FirstName , SP.LastName
FROM Sales.SalesOrderDetail OD INNER JOIN Sales.SalesOrderHeader OH
ON OD.SalesOrderID = OH.SalesOrderID
INNER JOIN Sales.vSalesPerson SP ON SP.BusinessEntityID = OH.SalesPersonID
WHERE OH.OrderDate between '20020501' AND '20020531' AND OH.SalesPersonID IS NOT NULL 
GROUP BY OH.SalesPersonID, SP.FirstName,SP.LastName
ORDER BY TTVENDAS DESC

Isso retorna:

Você então entrega a consulta para o seu chefe e mostra para ele o conjunto de resultados.

Ele observa e então pondera: - "Estão aparecendo somente 9 e eu pedi 15."

Você então responde: - "É que no critério de seleção maio de 2002 realmente só 9 vendedores realizaram vendas."

Ele responde: - "Peguei! Faça o seguinte então: quando o conjunto de resultado for menor que os 15 esperados retorne nas demais linhas a posição em desfalque e no campo FirstName a frase: "Nenhum classificado". Sei que para você é fácil e não quero passar esse tipo de complexidade para o desenvolvedor do relatório pois é um Junior que contratamos por uma pechincha!"

Realmente para você é fácil atender esse pedido. Vejamos a forma pontual de alterar a sua consulta para atender o requisito negligenciado:

SELECT ISNULL(principal.Posicao, intervalo.Posicao) Posicao, ISNULL(principal.TotalVendas,0) TotalVendas,
ISNULL(principal.FirstName,intervalo.Mensagem)  FirstName, principal.LastName
FROM (
 SELECT TOP 15 RANK() over(order BY SUM(OD.OrderQty) DESC) as Posicao, SUM(OD.OrderQty) TotalVendas,  SP.FirstName , SP.LastName
 FROM Sales.SalesOrderDetail OD INNER JOIN Sales.SalesOrderHeader OH
 ON OD.SalesOrderID = OH.SalesOrderID
 INNER JOIN Sales.vSalesPerson SP ON SP.BusinessEntityID = OH.SalesPersonID
 WHERE OH.OrderDate between '20020501' AND '20020531' AND OH.SalesPersonID IS NOT NULL 
 GROUP BY OH.SalesPersonID, SP.FirstName,SP.LastName
) principal right join 
(
 SELECT Posicao, Mensagem FROM ( 
  SELECT 1 Posicao UNION
  SELECT 2 UNION
  SELECT 3 UNION
  SELECT 4 UNION
  SELECT 5 UNION
  SELECT 6 UNION
  SELECT 7 UNION
  SELECT 8 UNION
  SELECT 9 UNION
  SELECT 10 UNION
  SELECT 11 UNION
  SELECT 12 UNION
  SELECT 13 UNION
  SELECT 14 UNION
  SELECT 15 
 )  v CROSS JOIN (
  SELECT 'Nenhum classificado' Mensagem
 ) m
) intervalo  on principal.Posicao = intervalo.Posicao
ORDER BY intervalo.Posicao 

O que fizemos foi criar uma sub-consulta que chamamos de intervalo e retorna um conjunto de resultados contendo o intervalo de 1 a 15 com desejado. Feito isso unimos sub-consulta a direita com da nossa tabela usando com critério de junção o campo posição existente em ambas sub-consultas.

Observe que na criação da nossa sub-consulta auxiliar utilizamos um UNION para gerar um intervalo de 1 a 15 e dessa forma garantir um conjunto de resultados contendo a quantidade mínima de linhas necessárias ao nosso requisito. Imagine agora que o requisito mude para os 100 ou os 1000 melhores vendedores. Imaginou? É claro que você poderia gerar esse UNION gigante, mas nesse caso seria mais esperto criar uma view contendo esses UNION. Criar uma tabela e popular uma tabela no banco somente para isso também seria uma solução válida.

Entretanto nossa proposta aqui é criar uma UDF para substituir essa sub-consulta e ousamos ainda propor a utilização de uma CTE recursiva em nossa TABLE-VALUED UDF para alcançar o nosso objetivo.

Depois de pronta a nossa TABLE-VALUED UDF que se chamará ufn_IntervaloDeInteiros a nossa consulta poderá ser feita da seguinte forma:
SELECT ISNULL(principal.Posicao, intervalo.valor) Posicao, ISNULL(principal.TotalVendas,0) TotalVendas,
ISNULL(principal.FirstName,intervalo.Mensagem)  FirstName, principal.LastName
FROM (
 SELECT TOP 15 RANK() over(order BY SUM(OD.OrderQty) DESC) as Posicao, SUM(OD.OrderQty) TotalVendas,  SP.FirstName , SP.LastName
 FROM Sales.SalesOrderDetail OD INNER JOIN Sales.SalesOrderHeader OH
 ON OD.SalesOrderID = OH.SalesOrderID
 INNER JOIN Sales.vSalesPerson SP ON SP.BusinessEntityID = OH.SalesPersonID
 WHERE OH.OrderDate between '20020501' AND '20020531' AND OH.SalesPersonID IS NOT NULL 
 GROUP BY OH.SalesPersonID, SP.FirstName,SP.LastName
) principal right join 
(
 SELECT valor, Mensagem FROM dbo.ufn_IntervaloDeInteiros(1,15) v 
 CROSS JOIN (
  SELECT 'Nenhum classificado' Mensagem
 ) m
) intervalo  on principal.Posicao = intervalo.valor
ORDER BY intervalo.valor 

Observe que substituímos toda a subconsulta v sobre carregada de UNIONS por uma simples chamada da função dbo.ufn_IntervaloDeInteiros(1,15). Bem mais elegante concordam?

Existem mil e uma maneira de criara essa função mas não se esqueça que na nossa proposta usaremos uma CTE recursiva. Vamos a definição da nossa função:

use AdventureWorks2008
GO
CREATE FUNCTION dbo.ufn_IntervaloDeInteiros
                 ( @valor_inicial as int, @valor_final as int ) 
RETURNS @tabela_de_intervalos TABLE (
 valor int
)
AS
BEGIN
       WITH intervalo_de_inteiros(valor) AS(
   select @valor_inicial vi
   union all
   select valor + 1 from intervalo_de_inteiros
   where valor + 1 <= @valor_final 
  )
  INSERT INTO @tabela_de_intervalos 
  select * from intervalo_de_inteiros option (MAXRECURSION 0)
  RETURN
END       
GO

Observe que nossa função dbo.ufn_IntervaloDeInteiros possui dois parâmetros do tipo inteiro: 

  1. @valor_inicial
  2. @valor_final
Eles representam respectivamente o início e o fim do intervalo a ser retornado pola nossa função. O nosso retorno é uma tabela é uma tabela de uma única coluna chamada valor que retorna todos os valores contidos no intervalo em suas linhas. Experimente a função com outros parâmetros, como por exemplo:

SELECT * FROM dbo.ufn_IntervaloDeInteiros(1,100) -- OU por exemplo
SELECT * FROM dbo.ufn_IntervaloDeInteiros(400,1000)

Porque não uma INLINE TABLE-VALUED UDF?

Observe que o corpo da nossa função é sucinto o suficiente para utilizar uma INLINE TABLE-VALUED UDF. Nesse caso a definição da nossa função ficaria assim:

USE AdventureWorks2008
GO
CREATE FUNCTION dbo.ufn_IntervaloDeInteiros_TesteInline
                 ( @valor_inicial as int, @valor_final as int ) 
RETURNS  TABLE 
AS
RETURN
       WITH intervalo_de_inteiros(valor) AS(
   select @valor_inicial vi
   union all
   select valor + 1 from intervalo_de_inteiros
   where valor + 1 <= @valor_final 
  )
  select * from intervalo_de_inteiros 
GO

Entretanto note em nossa primeira versão utilizamos a query hint OPTION( MAXRECURSION 0) para garantir um nível de recursividade infinito na nossa CTE e no caso das INLINE TABLE-VALUED UDF essa query hint não é permitida. Nesse caso para obtermos intervalos superiores a 100, que é o nível de recursividade utilizado por omissão no SQL SERVER, teríamos que fazer a chamada da seguinte maneira:

SELECT * FROM dbo.ufn_IntervaloDeInteiros_TesteInline(1,1000) OPTION (MAXRECURSION 1000)-- OU por exemplo
SELECT * FROM dbo.ufn_IntervaloDeInteiros_TesteInline(400,1000) OPTION (MAXRECURSION 0)

Na primeira chamada estou utilizando OPTION (MAXRECURSION 1000), isso indica ao SQL SERVER para limitar a recursividade até 1000 que é bem mais do que eu preciso. Quando não sabemos o nível de recursividade que iremos precisar podemos utilizar OPTION (MAXRECURSION 0) como na segunda chamada pois OPTION (MAXRECURSION 0) indica ao SQL SERVER para permitir recursividade infinita.

É muito importante que você saiba exatamente o que está fazendo quando utilizar OPTION (MAXRECURSION 0) pois um pequeno deslize pode fazer sua a chamada a sua função consumir recurso suficiente para derrubar o servidor, eu presumo.

Como comentário final gostaria de dizer que sei que essa técnica não é a mais performática e que dependendo do volume de dados uma tabela temporária indexada é muito mais adequada. Mas de qualquer forma a nossa UDF não deixa de ser um bom atalho para a criação desse tabela. Para os DBAs de plantão deixo a pergunta: A má utilização do MAXRECURSION pode realmente derrubar o servidor como eu presumi? E se puder, teria o SQL SERVER alguma funcionalidade para salvaguardar o ambiente da má utilização da má utilização dessa QUERY HINT?

Comentários

  1. Esse artigo abaixo explica como usar a mesma abordagem para criar uma tabela de intervalo da datas. Esse tema estava no meu TODO porém antes deu realizar recebi esse post no feed do SQLSERVERCENTRAL. Confiram:

    http://www.sqlservercentral.com/articles/Cursor+vs+Recursive+CTE/99795/

    ResponderExcluir

Postar um comentário

Obrigado pelo seu comentário!

Postagens mais visitadas deste blog

Curtir publicações automaticamente no Facebook. Por que isso não é uma boa idéia?