Olá a todos! Fiquei um tempo sem blogar porque mudei de estado (SC) e ainda estou me adaptando ao novo ambiente (lindo, por sinal).
Pois bem! Muito de nós, em algum ponto de nossas vidas, tivemos a necessidade de criar relatórios ou consultas totalizadoras que mostrassem somatórios mensais, anuais, semanais, etc. Na maioria das vezes, nos pediam para fazer essas atividades muito rapidamente (o famoso “para ontem”) e nos deparávamos com algum problema para montar algumas estruturas.
É aqui que entram as famosas “tally tables”. Em somatórios, elas normalmente são usadas para retornar uma quantidade fixa de linhas quando a tabela original que estamos usando para os cálculos não possui informações suficientes para ser usada sozinha. Então, usamos essa tabela auxiliar junto com a tabela original. A intenção deste artigo é mostrar um exemplo prático de como isso pode ser útil.
Suponhamos que seu chefe pediu um relatório simples de vendas mês a mês de determinado produto ou vendedor. A tabela de vendas da sua empresa (bem simplificada, para o exemplo) encontra-se abaixo:
OBS: Estou usando o SQL Server 2008 aqui. Caso esteja usando uma versão mais antiga, talvez seja necessário editar os scripts.
USE tempdb; GO CREATE TABLE dbo.Venda ( IdVendedor INT NOT NULL, ValorTotal DECIMAL(15,2) NOT NULL DEFAULT 0, DataCadastro DATETIME NOT NULL DEFAULT SYSDATETIME() );
Vamos inserir alguns registros na tabela para o ano de 2012:
INSERT INTO dbo.Venda (IdVendedor, ValorTotal, DataCadastro) VALUES (1, 150, '20120101'), (1, 761.17, '20120412'), (2, 780.00, '20120510'), (3, 233.75, '20120510');
Certo! Agora, tudo o que precisamos é de um SELECT simples usando a função agregadora SUM() e um GROUP BY, correto?
Seguindo essa lógica, nossa consulta ficaria assim:
SELECT MONTH(DataCadastro) AS Mes,
SUM(ValorTotal) AS Total
FROM dbo.Venda
GROUP BY MONTH(DataCadastro);
E os dados retornados, assim:
Ao mostrar para o seu chefe, todo contente, ele lhe faz a seguinte pergunta:
- Muito legal, mas e quanto aos outros meses?
Você responde:
- Mas não houve venda nos outros meses!
E ele rebate:
- Tudo bem. Então mostre um valor zerado para os que não tiveram venda nenhuma!
É aqui onde uma tally table pode facilitar as coisas para a gente!
A primeira ideia será criar uma tabela contendo uma coluna numeral “Mes”, onde a gente incluiria doze registros (números de 1 a 12). Irei usar uma CTE para mostrar rapidamente isso, mas você pode criar uma tabela no banco com os doze meses, se preferir (até mesmo se for usar em mais de uma consulta). O segredo aqui é unir essa tabela auxiliar com doze linhas, cada uma contendo um mês, com a nossa tabela de vendas. Vamos ao código:
WITH tbMes AS
(
SELECT 1 AS Mes UNION ALL
SELECT 2 AS Mes UNION ALL
SELECT 3 AS Mes UNION ALL
SELECT 4 AS Mes UNION ALL
SELECT 5 AS Mes UNION ALL
SELECT 6 AS Mes UNION ALL
SELECT 7 AS Mes UNION ALL
SELECT 8 AS Mes UNION ALL
SELECT 9 AS Mes UNION ALL
SELECT 10 AS Mes UNION ALL
SELECT 11 AS Mes UNION ALL
SELECT 12 AS Mes
)
SELECT TD.Mes AS Mes,
ISNULL(SUM(V.ValorTotal), 0) AS Total
FROM tbMes TD
LEFT JOIN dbo.Venda V ON MONTH(V.DataCadastro) = TD.Mes
GROUP BY TD.Mes;
E o resultado ficou mais apresentável, com “cara” de relatório:
Apesar de ter atendido à consulta mensal daquela atividade, ainda assim, a nossa consulta está incompleta, pois eu estava contando que minha tabela de vendas teria somente vendas de 2012. E quando outro ano aparecer na equação? Vamos dar uma dificultada no nosso cenário? Vamos incluir uma venda do ano de 2011 e ver o que acontece:
INSERT INTO dbo.Venda (IdVendedor, ValorTotal, DataCadastro) VALUES (3, 505.67, '20110406');
Agora, precisaríamos agrupar usando o ano também. Já que tenho registros de 2011 e 2012, precisaria mostrar os doze meses de 2011 e os doze meses de 2012, isto é, 24 linhas. Primeiramente, preciso saber quais são os anos envolvidos, pois eles podem mudar dinamicamente. Para isso, pensei em algo simples assim:
SELECT YEAR(DataCadastro) AS Ano FROM dbo.Venda GROUP BY YEAR(DataCadastro) -- 2011 -- 2012
Posso aqui, usar o CROSS JOIN para ligar cada mês com cada ano, usando a rotina acima em uma tabela derivada:
Opção 1: Trazer o ano a partir da tabela de vendas diretamente
WITH tbMes AS
(
SELECT 1 AS Mes UNION ALL
SELECT 2 AS Mes UNION ALL
SELECT 3 AS Mes UNION ALL
SELECT 4 AS Mes UNION ALL
SELECT 5 AS Mes UNION ALL
SELECT 6 AS Mes UNION ALL
SELECT 7 AS Mes UNION ALL
SELECT 8 AS Mes UNION ALL
SELECT 9 AS Mes UNION ALL
SELECT 10 AS Mes UNION ALL
SELECT 11 AS Mes UNION ALL
SELECT 12 AS Mes
)
SELECT A.Ano,
M.Mes AS Mes
FROM tbMes M
CROSS JOIN (SELECT YEAR(DataCadastro) AS Ano
FROM dbo.Venda
GROUP BY YEAR(DataCadastro)) A;
Note que eu usei o agrupamento da tabela de vendas para me trazer o ano. Mas eu poderia perfeitamente criar uma tabelinha auxiliar de anos ou usar uma CTE, como eu fiz com os meses. Segue abaixo o exemplo:
Opção 2: trazer o ano a partir de uma tabela auxiliar ou CTE (tbAno)
WITH tbMes AS
(
SELECT 1 AS Mes UNION ALL
SELECT 2 AS Mes UNION ALL
SELECT 3 AS Mes UNION ALL
SELECT 4 AS Mes UNION ALL
SELECT 5 AS Mes UNION ALL
SELECT 6 AS Mes UNION ALL
SELECT 7 AS Mes UNION ALL
SELECT 8 AS Mes UNION ALL
SELECT 9 AS Mes UNION ALL
SELECT 10 AS Mes UNION ALL
SELECT 11 AS Mes UNION ALL
SELECT 12 AS Mes
),
tbAno AS
(
SELECT 2011 AS Ano UNION ALL
SELECT 2012 AS Ano
)
SELECT A.Ano,
M.Mes AS Mes
FROM tbMes M
CROSS JOIN tbAno A
E o resultado é o mesmo para as duas opções:
Normalmente, é interessante criar tabelas persistidas no banco em vez de CTEs por causa da reusabilidade, indexação, melhor leitura, etc. Vamos analisar algumas das vantagens e desvantagens das duas opções acima.
Opção 1: Trazer o ano a partir da tabela de vendas diretamente
Vantagens:
Desvantagens:
Opção 2: trazer o ano a partir de uma tabela auxiliar ou CTE (tbAno)
Vantagens:
Desvantagens:
Apesar de ter mostrado as duas opções, uma tally table contendo os anos seria o ideal. Jeff Moden, um dos escritores de artigos do site SQLServerCentral.com e grande nome do universo SQL Server, sempre ressalta que uma tally table é mais útil quando ela é compacta e pode caber totalmente na memória. A nossa tabela de meses seria ideal, pois é sempre estática (vai ter 12 registros eternamente). No caso de uma tabela de anos, quanto menos registros, melhor. Poderíamos já criar previamente os registros desde 1950 até 2050 (apenas 100 linhas com uma coluna SMALLINT) e teríamos a solução mais otimizada possível.
Mas continuando no nosso exemplo, estamos usando CTEs em vez de tabelas, somente para simplificar.
Ainda ficou faltando juntarmos as duas CTEs de ano e mês com nossa tabela de vendas e chegarmos ao resultado esperado. Um LEFT JOIN simples poderá resolver nosso problema:
WITH tbMes AS
(
SELECT 1 AS Mes UNION ALL
SELECT 2 AS Mes UNION ALL
SELECT 3 AS Mes UNION ALL
SELECT 4 AS Mes UNION ALL
SELECT 5 AS Mes UNION ALL
SELECT 6 AS Mes UNION ALL
SELECT 7 AS Mes UNION ALL
SELECT 8 AS Mes UNION ALL
SELECT 9 AS Mes UNION ALL
SELECT 10 AS Mes UNION ALL
SELECT 11 AS Mes UNION ALL
SELECT 12 AS Mes
),
tbAno AS
(
SELECT 2011 AS Ano UNION ALL
SELECT 2012 AS Ano
)
SELECT A.Ano,
M.Mes AS Mes,
ISNULL(SUM(V.ValorTotal), 0) AS Total
FROM tbMes M
CROSS JOIN tbAno A
LEFT JOIN dbo.Venda V
ON YEAR(V.DataCadastro) = A.Ano AND MONTH(V.DataCadastro) = M.Mes
GROUP BY A.Ano, M.Mes;
Observação sobre a consulta: usar as funções YEAR() e MONTH() na cláusula ON, como eu usei acima, pode não ser a melhor maneira para uma tabela muito grande, pois o otimizador de consultas do SQL Server pode não usar o índice correto ou da melhor maneira possível. De qualquer forma, é um método interessante para se chegar em um resultado rapidamente. Se a tabela (de vendas, no nosso exemplo) for muito grande, você pode tentar um range de datas.
As possibilidades são muitas e as tally tables podem ser usadas de diversas maneiras criativas!
O resultado final ficou assim:
Qualquer sugestão para melhoria do código é bem-vinda!
Até a parte 2! Obrigado pela atenção e leitura.
