Ao trabalhar com o SQL Server, preencher uma dimensão de data é uma tarefa comum no processo de ETL (Extração, Transformação, Carga). Tradicionalmente, isso era feito usando um cursor, mas existem métodos alternativos que podem ser mais eficientes e mais fáceis de manter.

Neste artigo, exploraremos um script que usa Expressões de Tabela Comuns (CTE) e Funções de Janela para preencher uma dimensão de data no SQL Server.

O Script

Vamos dar uma olhada no script:

DECLARE @startdate DATE = '20000101',
        @enddate DATE = '20301231';

WITH c AS (
    SELECT Num = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1
    FROM sys.columns c
    CROSS JOIN sys.columns c1
),
d AS (
    SELECT [date] = DATEADD(day, Num, @startdate),
           Num
    FROM c
    WHERE Num >= 0
    AND Num <= DATEDIFF(day, @startdate, @enddate)
)
SELECT datekey = CAST(CONVERT(VARCHAR(8), DATEADD(day, Num, @startdate), 112) AS INT),
       [date],
       [DiaDoMês] = DATEPART(day, [Date]),
       [NomeDoDia] = DATENAME(weekday, [Date]),
       [DiaDoAno] = DATEPART(dayofyear, [Date]),
       [SemanaDoAno] = DATEPART(week, [Date]),
       [NomeDoMês] = DATENAME(month, [Date]),
       [NúmeroDoMês] = DATEPART(month, [Date]),
       [NúmeroDoTrimestre] = DATEPART(quarter, [Date]),
       [Ano] = YEAR([date]),
       [AnoFiscal] = CASE 
                        WHEN DATEPART(month, [Date]) < 7
                        THEN YEAR([date])
                        ELSE YEAR([date]) + 1
                      END
-- Adicione mais colunas conforme necessário.
FROM d;

Entendendo o Script

O script começa declarando duas variáveis, @startdate e @enddate, que definem o intervalo de datas para o conjunto de resultados.

Em seguida, uma CTE chamada c é definida. Essa CTE usa a função ROW_NUMBER() para gerar números a partir de zero para cada linha na tabela sys.columns. A tabela sys.columns é unida a si mesma para gerar um maior número de linhas.

Outra CTE chamada d é então definida. Essa CTE usa os números da CTE c, filtra-os com base no intervalo de datas e os converte em datas reais usando a função DATEADD().

Por fim, a instrução SELECT recupera as colunas desejadas da CTE d, juntamente com campos adicionais para a dimensão de data. Esses campos adicionais incluem o dia do mês, dia da semana, dia do ano, semana do ano, nome do mês, número do mês, número do trimestre, ano e ano fiscal.

É importante observar que o script fornecido neste artigo não inclui todas as colunas que uma dimensão de data normalmente possui. No entanto, ele serve como ponto de partida e fornece a lógica e a estrutura para adicionar mais colunas conforme necessário.

Vantagens do Uso de CTE e Funções de Janela

O uso de CTE e Funções de Janela para preencher uma dimensão de data oferece várias vantagens em relação aos métodos tradicionais:

• Melhor Desempenho: CTE e Funções de Janela geralmente têm um desempenho melhor do que o uso de um cursor, especialmente ao lidar com grandes conjuntos de dados.
• Código Simplificado: O script que usa CTE e Funções de Janela é mais conciso e mais fácil de entender em comparação com uma abordagem baseada em cursor.
• Flexibilidade: O script pode ser facilmente modificado para adicionar colunas adicionais ou personalizar a dimensão de data de acordo com requisitos específicos.
• Manutenibilidade: O uso de CTE e Funções de Janela simplifica o código e reduz as chances de introduzir erros, tornando-o mais fácil de manter a longo prazo.

Conclusão

Neste artigo, exploramos um método alternativo para preencher uma dimensão de data no SQL Server usando CTE e Funções de Janela. Discutimos o script e seus componentes, bem como os benefícios de usar essa abordagem.

Aproveitando o poder de CTE e Funções de Janela, você pode melhorar o desempenho, simplificar o código e aprimorar a manutenibilidade do processo de preenchimento da dimensão de data.