we are developers

iMaster Developers

iMaster DevelopersPowered by:

Banco de Dados

27 nov, 2014

SQL & relatórios com meses zerados – Parte 1: como abordar o problema

Wagner Crivelini

Tem 91 artigos publicados com 828100 visualizações desde 2010

Wagner Crivelini
Publicidade

Quem nunca? Quem nunca teve que preparar um relatório com movimentação mensal com base numa tabela de transações? Relatórios de vendas, estoques, faturamentos… são inúmeros os relatórios que nos são solicitados. E muitas vezes nossa única ferramenta é a boa e velha linguagem SQL.

Criar relatórios sumarizados usando SQL é tarefa corriqueira e existe uma infinidade de artigos e videoaulas por aí para quem está começando neste terreno. (Eu mesmo publiquei uma série de artigos sobre o tema, alguns deles citados na seção Referências).

Porém é comum que usuários exijam características especiais nesses relatórios. Às vezes pedem que a sumarização dos dados seja feita com base num calendário diferente do padrão janeiro-a-dezembro, por exemplo (nesses casos, sugiro o artigo de Todd Fifield citado nas referências).

Em outros casos, acontece um problema: existem meses em que não há movimentação, mas o usuário quer porque quer que apareça o valor ZERO nesses meses. Parece coisa elementar, mas não é.

Os bancos de dados são modelados para armazenar transações. Se não existem transações no período escolhido, então não existem registros referentes a esse período. E naturalmente o relatório simplesmente não vai exibir nada para o mês em questão.

Como solucionar o problema?

Situando o problema

Para ilustrar, vejamos um exemplo usando a base demo do SQL SERVER AdventureWorks2012. Considere um relatório mensal de faturamento do produto “Men’s Bib-Shorts, M”. Para gerar esse relatório, é necessário consultar dados de três tabelas. Além disso, é preciso usar um pequeno truque com o campo de data para gerarmos um relatório mensal: usar a função DATEADD para encontrar o primeiro dia de cada mês. Depois disso, basta filtrar os dados pelo código do produto desejado e fazer a sumarização dos dados.

Como uso essa consulta diversas vezes neste artigo, eu defino aqui uma visão para utilizá-la nos testes. Veja as declarações SQL na Listagem 1.

Listagem 1: consulta original do relatório mensal

CREATE VIEW vwFaturamento AS
	SELECT 
		  MIN(p.Name) AS produto
		, sd.ProductID 
		, DATEADD(d, -DAY(sh.OrderDate)+1, sh.OrderDate) mes
		, SUM( sd.OrderQty * sd.UnitPrice ) AS faturamento
	FROM [Sales].[SalesOrderHeader] sh
		INNER JOIN [Sales].[SalesOrderDetail] sd 
			ON sh.SalesOrderID = sd.SalesOrderID
		INNER JOIN [Production].[Product] p 
			ON p.ProductID = sd.ProductID
	WHERE sd.ProductID = 856
	GROUP BY 
		sd.ProductID
		, DATEADD(d, -DAY(sh.OrderDate)+1, sh.OrderDate)
GO

SELECT * FROM vwFaturamento
GO

O resultado da consulta a essa visão é mostrado na Tabela 1, na qual eu destaco as ”brechas” desse relatório, ou seja, quando os meses listados não são sequenciais.

Tabela 1: dados do relatório mensal original

tabela-1

Uma vez que o usuário foi bem claro que sejam exibidos todos os meses de um determinado intervalo, independentemente de existirem transações ou não, fica claro que não podemos basear nosso relatório apenas nas tabelas transacionais. É necessário criar uma lista de meses de interesse.

Com essa lista, o problema se resolve. Considere que os dados sumarizados estejam disponíveis na visão vwFaturamento (Listagem 1) e a lista de meses seja o objeto ListaMeses. A instrução SQL para mostrar o relatório com valor zero nos meses em que não ocorrerem transações precisa fazer um LEFT JOIN entre a lista de meses e o relatório de faturamento. Depois isso, é preciso usar a função COALESCE para mostrar valor ZERO ao invés de NULO nos meses sem faturamento. Finalmente, precisamos informar o período que deve ser listado no relatório (meses inicial e final). A instrução completa é mostrada na Listagem 2.

Listagem 2: gerando relatório com todos os meses do período

SELECT L.Mes, COALESCE(V.Faturamento, 0) AS TOTAL
FROM ListaMeses L 
     LEFT JOIN vwFaturamento V ON L.Mes = V.Mes
WHERE L.Mes BETWEEN @dataInicial AND @dataFinal
-- @dataInicial/@dataFinal indicam intervalo analisado no relatório

OK. O problema está quase resolvido. Resta definir como será criada esta lista de meses. Como fazer isso e qual a melhor opção?

Detalhando as soluções

Eu apresento a seguir quatro técnicas para gerar a lista de meses. Cada uma tem seus prós e contras, e apresento algumas recomendações a respeito delas.

Alternativa 1: Variável de tabela

O uso de objetos temporários é certamente a opção mais comum para resolver o problema, talvez porque o código da solução seja o mais parecido com o de uma linguagem procedural. A Listagem 3 mostra a construção da tabela temporária e a consulta que apresenta o relatório formatado.

Listagem 3: relatório com variável de tabela (tabela temporária)

DECLARE @dataInicial DATE
DECLARE @dataFinal DATE
DECLARE @ListaMeses TABLE (MES DATE)

SET @dataInicial	= '2007-01-01'
SET @dataFinal  	= '2008-12-01'

DECLARE @dataInserida DATE
SET @dataInserida = @dataInicial

WHILE @dataInserida <= @dataFinal
	BEGIN
	INSERT INTO @ListaMeses VALUES (@dataInserida)
	set @dataInserida = DATEADD(m, 1, @dataInserida )
	END

SELECT L.Mes, COALESCE(V.Faturamento, 0) AS TOTAL
FROM @ListaMeses L 
     LEFT JOIN vwFaturamento V ON L.Mes = V.Mes
-- não precisa do WHERE, 
-- porque a condição já foi testada na criação de @ListaMeses
--WHERE L.Mes BETWEEN @dataInicial AND @dataFinal

Observações importantes:

  • Esse script cria um objeto temporário para cada sessão em que for executado. Portanto, se ele for convertido num procedimento armazenado e acontecer de 1000 usuários pedirem relatórios ao mesmo tempo, serão criados 1000 objetos.
  • Objetos temporários (variáveis de tabela e tabelas temporárias) são armazenados na base TEMPDB pelo tempo que durar a sessão de cada usuário. Num momento de pico de uso do sistema, isso pode causar um problema sério com o espaço reservado para a base TEMPDB.

Alternativa 2: Tabela temporária

O código usando tabela temporária é praticamente idêntico ao da Alternativa 1. Eu apenas fiz uma pequena adaptação para criar a tal tabela temporária antes de entrar no laço do WHILE (e por isso esse laço vai se repetir por 23 e não 24 vezes). A Listagem 4 mostra esse script.

Listagem 4: relatório com tabela temporária

DECLARE @dataInicial DATE
DECLARE @dataFinal DATE

SET @dataInicial	= '2007-01-01'
SET @dataFinal  	= '2008-12-01'

DECLARE @dataInserida DATE
SET @dataInserida = @dataInicial

SELECT @dataInserida as mes  INTO #ListaMeses

WHILE @dataInserida < @dataFinal
	BEGIN
	INSERT INTO #ListaMeses VALUES (@dataInserida)
	set @dataInserida = DATEADD(m, 1, @dataInserida )
	END

SELECT L.Mes, COALESCE(V.Faturamento, 0) AS TOTAL
FROM #ListaMeses L 
     LEFT JOIN vwFaturamento V ON L.Mes = V.Mes
-- não precisa do WHERE, 
-- porque a condição já foi testada na criação de #ListaMeses
--WHERE L.Mes BETWEEN @dataInicial AND @dataFinal 

DROP TABLE #ListaMeses

Aqui valem as mesmas ressalvas: a tabela temporária é associada a uma sessão e escreve dados no TEMPDB. Portanto, o mesmo problema vai acontecer quando houver muitos acessos simultâneos: TEMPDB pode dar alarme de falta de espaço.

Alternativa 3: Tabela de expressão (CTE) recursiva

Este é um recurso menos conhecido da linguagem SQL, mas está disponível na maioria dos SGBDs que aderem ao padrão ANSI SQL. Ele uma CTE, como o indica o título, mas é uma CTE especial que repete sua execução, daí o nome CTE recursiva (veja artigo sobre o tema na seção Referências).

A CTE também é um objeto temporário, mas, ao contrário de tabelas temporárias e variáveis de tabela que sempre causam impacto no TEMPDB , CTEs só usam TEMPDB quando lidam com volumes muito grandes de dados.

A Listagem 5 mostra a sintaxe de definição desta CTE para gerar um relatório idêntico ao da Alternativa 1.

Listagem 5: relatório usando CTE

DECLARE @dataInicial DATE
DECLARE @dataFinal DATE

SET @dataInicial	= '2007-01-01'
SET @dataFinal  	= '2008-12-01'

; WITH cteListaMeses (mes) AS (
	--ponto de partida: 1o mes da lista
	SELECT @dataInicial
	UNION ALL
	SELECT DATEADD(m, 1, c.mes)
	FROM cteListaMeses c
	WHERE c.mes < @dataFinal
)

SELECT L.Mes, COALESCE(V.Faturamento, 0) AS TOTAL
FROM cteListaMeses L 
     LEFT JOIN vwFaturamento V ON L.Mes = V.Mes
-- não precisa do WHERE, 
-- porque a condição já foi testada na definição da cteListaMeses
--WHERE L.Mes BETWEEN @dataInicial AND @dataFinal

O maior problema da CTE recursiva é que o desenvolvedor precisa definir muito bem o critério de parada; caso contrário, existe o risco de ela entrar em um laço infinito. Para evitar o problema, pode-se usar a opção MAXRECURSION, que força a parada da CTE recursiva conforme o número máximo de iterações.

Alternativa 4: Tabela física com lista de meses

Esta é certamente a alternativa mais conservadora de todas: criar uma tabela comum que contenha todos os possíveis meses que possam ser usados nos relatórios.

Eu construí uma tabela com os meses entre janeiro/2000 e dezembro/2050. Com isso, espero cobrir todo o período em que esse relatório pode possivelmente ser usado, imaginando que essa aplicação não estará mais no ar em dezembro/2050.

Detalhe importante: a criação e inserção de dados obviamente ocorrerá uma única vez. Não há impacto de escrita em disco de dados temporários, e o impacto no TEMPDB é mínimo (restrito a ações normais que são realizadas no TEMPDB, como ordenação de consultas etc.).

A Listagem 6 mostra o SQL dessa consulta (escrito em cor azul) e o SQL de preparação da tabela física (destacado na cor preto, visto que esse custo não deve ser computado).

Listagem 6: relatório usando tabela física

--===================================================================
-- esta preparacao não deve ser computada no custo da consulta
-- pq ela é executada uma unica vez
CREATE TABLE tbListaMeses (mes DATE NOT NULL PRIMARY KEY) 

DECLARE @dataInserida DATE
SET @dataInserida = '2000-01-01'

WHILE @dataInserida <= '2050-12-01'
	BEGIN
	INSERT INTO tbListaMeses VALUES (@dataInserida)
	set @dataInserida = DATEADD(m, 1, @dataInserida )
	END
GO
-- fim da preparacao
--===================================================================

-- iniciando a execucao do relatorio
DECLARE @dataInicial DATE
DECLARE @dataFinal DATE

SET @dataInicial	= '2007-01-01'
SET @dataFinal  	= '2008-12-01'

SELECT L.Mes, COALESCE(V.Faturamento, 0) AS TOTAL
FROM tbListaMeses L 
     LEFT JOIN vwFaturamento V ON L.Mes = V.Mes
WHERE L.Mes BETWEEN @dataInicial AND @dataFinal

Quando eu faço a modelagem de bancos de dados para consulta e análise de dados, normalmente insiro no modelo essa tabela de calendário com o nível de detalhamento adequado.

No exemplo mostrado acima, era preciso apenas listar os meses de um período. Mas a ideia da tabela de calendário (ou uma “dimensão” tempo, como se diz no universo de Business Intelligence) é muito útil para geração de relatórios e pode ser usada para várias finalidades:

  • Conversão de datas para calendários especiais (como descrito no artigo do Fifield)
  • Sumarização de dados por diferentes períodos (semanas, meses, trimestres etc.)
  • Identificação de filtros de dados especiais (semanas, meses, trimestres etc.)

Conclusão

Existem vários modos de criar uma lista de datas (sejam meses, dias da semana ou outro critério qualquer), mas eu vejo duas vantagens importantíssimas da Alternativa 4, que usa um objeto físico:

  • Permite criar listas mais complexas, como uma dimensão tempo, mencionada anteriormente
  • Reduz dramaticamente impacto na base compartilhada TEMPDB, porque todo usuário que invocar o relatório vai consultar dados numa fonte única, a tabela de calendário

No próximo artigo, apresentarei informações sobre os planos de execução de cada uma das alternativas sugeridas aqui.

Até lá.

Referências

De 0 a 10, o quanto você recomendaria este artigo para um amigo?

Wagner Crivelini
Saiba mais

Wagner Crivelini

edit91 Artigo(s)

Consultor Sênior na Microsoft, na área de Data Insights para América Latina. Especialista em bancos de dados, é colunista em diversos portais de TI do Brasil e do exterior, com mais de 100 artigos técnicos publicados. É também co-produtor do DatabaseCast, primeiro podcast brasileiro sobre bancos de dados.