Já faz muito tempo que o SQL Server suporta as chamadas funções de janela, que se baseiam na cláusula OVER. Eu mesmo escrevi pela primeira vez sobre este tema em 2012.
As funções de janela oferecidas na época eram poucas, mas bastante versáteis. Porém, o que realmente me incomodava era a sintaxe dessas consultas: excessivamente detalhada e repetitiva. Por essa razão as declarações SQL ficavam quase sempre muito longas e difíceis de entender.
A partir do SQL Server 2022, a MICROSOFT incorporou a nova cláusula WINDOW, que trabalha em conjunto com a cláusula OVER e facilita bastante criação desse tipo de consulta.
Neste artigo apresento alguns exemplos e considerações sobre o uso da cláusula WINDOW e sua operação com algumas funções de janela.
Como Funciona Cláusula WINDOW
A ideia da nova cláusula WINDOW é criar um alias para a janela de particionamento dos dados, de modo que não seja necessário repetir a descrição do particionamento para cada coluna calculada com a cláusula OVER.
Na realidade, é possível definir múltiplas janelas na cláusula WINDOW, conforme a necessidade da sua consulta.
Porém, como esta funcionalidade está associada ao SQL Server 2022, o seu banco de dados necessariamente precisa ter um nível de compatibilidade 160 ou superior (para mais informação, veja este artigo).
Vamos ver um exemplo sobre o banco de dados AdventureWorksDW2022 (clique aqui para download). O modelo de dados que iremos usar é apresentado na Figura 01.
A seguir, apresento uma consulta com as vendas mensais pela internet para os
produtos da categoria BIKES em todo o mundo no ano de 2013. Considero aqui
apenas as vendas realizadas em dólar (USD), para evitar a questão de conversão de
moedas. Esta consulta é a base para os próximos exemplos. Por esta razão, eu gravo
os dados numa tabela temporária para reutilizá-los a seguir. Veja Quadro 01.
Agora começa a discussãosobre o tema desse artigo. Antigamente existia apenas a cláusula OVER para fazer o tipo de análise que apresento a seguir. Conhecendo as vendas mensais por país, quero adicionar colunas que apresentemas vendas totais, máximas, mínimas e médias para cada país, no período estipulado. Mas cada nova coluna precisoinformar o tipo de particionamento que será usado para aquela métrica. O resultado é a repetição da informação para cada coluna criada, como mostra o Quadro 02.
Veja no Quadro 02 que o critério de particionamento é muito simples e mesmo assim a declaração cresceu muito rapidamente. Não é difícil imaginar que chega um ponto em que vai ficar difícil entender e/ou debugar uma consulta tão extensa.
Uma das melhorias do SQL Server 2022 é a inclusão da cláusula WINDOW, que facilita a escrita de consultas complexas. Esta cláusula permite criar um alias para definição do particionamento de dados a ser usado na cláusula OVER. O resultado é uma declaração SQL muito mais limpa, como se vê no Quadro 03.
Para o leitor que tem pouca experiência com funções de janela, esta pode parecer uma mudança pequena. Mas não é.
À medida que se cria cálculos mais sofisticados, o particionamento de dados se torna cada vez mais complexo e o fato de se isolar a definição dos particionamentos do cálculo da métrica ajuda bastante na depuração do código.
Diferença entre Cláusulas WINDOW e OVER
Importante ter em mente que a cláusula WINDOW otimiza a escrita da declaração, mas ela não altera a maneira como a declaração é executada.
Veja que o plano de execução das consultas dos quadros anteriores é exatamente igual, como mostra a Figura 02.
Exemplo de Uso: Médias Móveis (AVG)
Neste novo exemplo, a consulta calcula a média de vendas de todo período (que neste caso é de 12 meses fixos) e adiciona a média móvel dos últimos 03 meses.
Veja que agora é necessário definir 02 janelas, uma para cada cálculo. Isso não é problema, como se vê no Quadro 04.
Exemplo de Uso: Ranking Percentual (PERCENT_RANK)
Uma das funções de janelaque mais gosto é PERCENT_RANK(), que calcula a posição relativa de um número dentro do conjunto que está sendo estudado.
O nome da função causa muitas dúvidas, visto que o “PERCENT” não diz respeito a porcentagem, mas sim ao percentil a que pertence aquele valor dentro do conjunto (para saber mais).
O resultado da função é um valor decimal entre 0 e 1 que informa a “proximidade” do número avaliado em relação aos percentis de todo o conjunto de dados. Exemplo: se o ranking de percentis retorna o valor “0,18” para um determinado número, significa que ele está muito próximo do percentil 20% (0.20) de todo o conjunto.
Portanto essa função informa não só a posição de um valor dentro de uma série, mas também dá uma noção das “distâncias” entre cada valor.
Cada dado do conjunto analisado recebe um valor calculado pela fórmula do Quadro 05.
PERCENT_RANK
Vejamos o resultado da função PERCENT_RANK para o nosso exemplo. O Quadro 05 mostra essa consulta SQL e os resultados (parciais) retornados.
Observe que o maior valor da série aconteceu no mês 12/2013 e este valor tem o ranking de percentil 1. O menor valor é de 02/2013 e, naturalmente, recebe o valor 0.
O 2º menor valor nessa lista é o de 04/2013 (veja a coluna RANKING_ABS) e ele mostra o valor de 0,09 para RANKING_PERC. Isso quer dizer que este valor está dentro do percentil de 10% da série.
Se esses números fossem usados num gráfico, teríamos a distribuição de frequências do conjunto de resultados de vendas mensais. Veja a Figura 03.
Exemplo de Uso: Marketshare
Marketshare é um indicador importante em qualquer negócio e é simples trabalhar com ele usando funções de janela e, particularmente, a cláusula WINDOW.
Imagine que seu chefe pediu para definir marketshare de cada Território como as Vendas Totais dos últimos 3 meses naquele país dividido pelas vendas Totais dos últimos 3 meses no mundo inteiro.
Eu criei uma expressão de tabela comum (CTE) que informa as vendas por país a cada mês e total mundial. Em seguida fiz a consulta que calcula as somas para cada 3 meses móveis e adicionei o cálculo do marketshare diretamente nessa consulta (veja Quadro 07).
Pronto! Plotando estes dados, temos o gráfico a seguir, que mostra uma leve tendência ascendente para vendas nos EUA e uma leve tendência de queda nas vendas francesas (Figura 04).
Comentários Finais
Espero que tenha ficado claro para o leitor que esta cláusula é opcional. O desenvolvedor que preferir manter suas consultas usando a cláusula OVER isoladamente, não terá problemas com seu código e nem desvantagens em questão de performance.
No entanto, eu acho que as vantagens da cláusula WINDOW são bastante interessantes para debugs e alterações das referidas consultas, pois facilitam o trabalho. Por essa razão, pretendo usá-las como padrão sempre que trabalhar com funções de janela.
LEIA TAMBÉM
Scripts SQL e Tabelas de Sistema – Parte 4: Atuando em Toda Instância
De 0 a 10, o quanto você recomendaria este artigo para um amigo?