Window Functions: O Guia Completo para Evoluir de Júnior para Pleno em SQL

As Window Functions (funções de janela) no SQL são ferramentas poderosas para realizar cálculos analíticos e agregações em um conjunto de linhas relacionadas, sem agrupar os resultados como ocorre com funções agregadas tradicionais (GROUP BY). Elas permitem que você calcule valores baseados em uma "janela" de dados (um subconjunto de linhas definido por critérios específicos) enquanto mantém todas as linhas no resultado final. Isso é particularmente útil para análises avançadas, como rankings, acumulados, comparações entre linhas e cálculos baseados em partições.

O que são Window Functions?

• Definição: Uma função de janela realiza cálculos em um conjunto de linhas (a "janela") definido por uma cláusula OVER. Cada linha mantém seu resultado individual, e a função agrega ou calcula valores com base nas linhas relacionadas na janela.
• Diferença de funções agregadas: Diferentemente de SUM, COUNT, etc., com GROUP BY, que reduz o resultado a uma linha por grupo, as window functions preservam todas as linhas e adicionam uma coluna com o resultado do cálculo.
• Componentes principais:
  • Função: Ex.: ROW_NUMBER(), RANK(), SUM(), AVG().
  • Cláusula OVER: Define a janela (linhas a serem consideradas) com PARTITION BY (para dividir os dados em grupos) e ORDER BY (para ordenar dentro da janela).
  • Frame specification (opcional): Define um subconjunto específico de linhas dentro da janela (ex.: ROWS BETWEEN).

Principais Tipos de Window Functions

1. Funções de Ranking:

   • ROW_NUMBER(): Atribui um número único e sequencial a cada linha dentro da janela.
   • RANK(): Atribui um ranking, com empates recebendo o mesmo valor, mas pulando números após empates.
   • DENSE_RANK(): Similar a RANK(), mas não pula números após empates.
   • NTILE(n): Divide as linhas em n grupos iguais.

2. Funções de Agregação:

   • SUM(), AVG(), COUNT(), MIN(), MAX(): Calculam agregações sobre a janela, como totais acumulados ou médias.

3. Funções de Valor:

   • LAG(): Acessa o valor da linha anterior na janela.
   • LEAD(): Acessa o valor da próxima linha.
   • FIRST_VALUE(), LAST_VALUE(): Retornam o primeiro ou último valor da janela.

4. Funções de Distribuição:

   • CUME_DIST(): Calcula a distribuição cumulativa.
   • PERCENT_RANK(): Calcula o percentual do ranking.

Sintaxe Básica

SELECT 
    coluna,
    FUNCAO() OVER (
        [PARTITION BY coluna_particao]
        [ORDER BY coluna_ordem]
        [ROWS ou RANGE frame_specification]
    ) AS nome_coluna
FROM tabela;

• PARTITION BY: Divide os dados em grupos (similar a GROUP BY, mas sem colapsar linhas).
• ORDER BY: Define a ordem das linhas dentro da janela, importante para funções como ROW_NUMBER() ou acumulados.
• ROWS/RANGE: Limita a janela a um subconjunto de linhas (ex.: últimas 3 linhas).

Exemplos Práticos

1. Ranking de Vendas por Cliente

Suponha uma tabela orders com colunas order_id, customer_id, amount, order_date.

SELECT 
    customer_id,
    order_date,
    amount,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS ranking_pedido
FROM orders;

• Explicação:
  • PARTITION BY customer_id: Divide os dados por cliente.
  • ORDER BY amount DESC: Ordena os pedidos de cada cliente por valor decrescente.
  • ROW_NUMBER(): Atribui um número sequencial (1, 2, 3...) para cada pedido dentro do grupo do cliente.
  • Resultado: Cada linha mostra o cliente, a data do pedido, o valor e o ranking do pedido por valor.

2. Total Acumulado de Vendas

Calcular o total acumulado de vendas por cliente ao longo do tempo:

SELECT 
    customer_id,
    order_date,
    amount,
    SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS vendas_acumuladas
FROM orders;

• Explicação:
  • PARTITION BY customer_id: Agrupa por cliente.
  • ORDER BY order_date: Define a ordem cronológica dos pedidos.
  • SUM(amount): Calcula o total acumulado até a linha atual dentro da partição.
  • Resultado: Mostra o valor acumulado de vendas para cada cliente até a data do pedido.

3. Diferença entre Pedidos Consecutivos

Usar LAG para comparar o valor de um pedido com o anterior:

SELECT 
    customer_id,
    order_date,
    amount,
    LAG(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS valor_anterior,
    amount - LAG(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS diferenca
FROM orders;

• Explicação:
  • LAG(amount): Retorna o valor do pedido anterior na mesma partição.
  • Resultado: Mostra o valor do pedido atual, o valor do pedido anterior e a diferença entre eles.

4. Média Móvel

Calcular a média dos últimos 3 pedidos de cada cliente:

SELECT 
    customer_id,
    order_date,
    amount,
    AVG(amount) OVER (
        PARTITION BY customer_id 
        ORDER BY order_date 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS media_movel
FROM orders;

• Explicação:
  • ROWS BETWEEN 2 PRECEDING AND CURRENT ROW: Define a janela como as duas linhas anteriores e a linha atual.
  • AVG(amount): Calcula a média dos valores na janela.
  • Resultado: Mostra a média móvel dos últimos 3 pedidos por cliente.

Quando Usar Window Functions?

• Rankings: Para classificar itens (ex.: top clientes por vendas).
• Análises temporais: Para calcular acumulados ou diferenças entre eventos (ex.: crescimento de vendas).
• Comparações dentro de grupos: Para comparar uma linha com outras no mesmo grupo sem colapsar os dados.
• Relatórios analíticos: Para gerar métricas como médias móveis ou percentuais de contribuição.

Dicas para Dominar Window Functions

1. Pratique com datasets reais: Use datasets como Northwind ou AdventureWorks para testar rankings e acumulados.
2. Entenda a janela: Experimente diferentes combinações de PARTITION BY, ORDER BY e ROWS para ver como afetam o resultado.
3. Teste em bancos compatíveis: PostgreSQL e SQL Server têm suporte robusto. MySQL tem limitações em versões antigas (antes do 8.0).
4. Evite overcomplicação: Comece com funções simples como ROW_NUMBER() antes de avançar para LAG ou NTILE.
5. Combine com CTEs: Use CTEs para organizar queries complexas com window functions.

Exercício Prático

• Tarefa: Usando a tabela orders (order_id, customer_id, amount, order_date), escreva uma query que:
  • Calcule o ranking de pedidos por valor para cada cliente (RANK()).
  • Mostre o valor acumulado de vendas por cliente.
  • Adicione uma coluna com a diferença entre o pedido atual e o anterior (LAG).
• Dica: Use PARTITION BY customer_id e ORDER BY amount DESC para o ranking, e ORDER BY order_date para o acumulado e LAG.

SELECT 
    customer_id,
    order_date,
    amount,
    RANK() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS ranking,
    SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS vendas_acumuladas,
    amount - LAG(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS diferenca
FROM orders;

Publicado por Jefferson Peixoto em 17 de junho de 2025