Uma Exploração Abrangente de Window Functions em SQL

O uso de Window Functions vem crescendo progressivamente no mundo SQL, sobretudo em aplicações de Inteligência de Negócios (BI), relatórios avançados, análise de dados e data warehousing. Embora o recurso não seja tão novo – tendo sido padronizado no SQL:2003 – seu potencial de simplificar cálculos e análises complexas continua a encantar desenvolvedores, analistas e cientistas de dados. Sem a necessidade de recorrer a subqueries complicadas ou junções adicionais, as Window Functions (também conhecidas como Funções de Janela) permitem a realização de cálculos agregados ou cumulativos sem perder o contexto de cada linha individualmente.

A proposta deste artigo é ser um guia extremamente completo – uma referência abrangente – que explique desde a sintaxe de Window Functions até exemplos práticos. Vamos percorrer tópicos fundamentais para esclarecer de forma exaustiva este recurso poderoso do SQL. Ao final, apresentaremos hacks avançados e dicas de performance, garantindo que você não apenas entenda a teoria, mas também possa aplicá-la eficientemente no dia a dia.

O texto é extenso e busca ser detalhado para que, ao final da leitura, você se sinta preparado para explorar janelas, particionar dados, ordenar, classificar, comparar linhas anteriores, linhas seguintes e muito mais.

---

2. O que são Window Functions

Window Functions são funções SQL que permitem executar cálculos sobre um conjunto de linhas (uma “janela” de dados) sem que precisemos agrupar ou consolidar essas linhas em uma única. Trata-se de uma construção poderosa porque, ao contrário das funções de agregação convencionais (SUM, AVG, COUNT, etc.), as Window Functions conseguem manter, ao mesmo tempo, o nível de detalhe de cada linha e o resultado de um cálculo que depende de várias linhas.

Em termos práticos, se você precisa de alguma estatística dentro de um grupo – por exemplo, uma soma cumulativa ordenada por data, uma média móvel, um ranking de vendas, uma comparação com a linha anterior ou um cálculo que envolve valores de linhas subsequentes –, as Window Functions se apresentam como a solução mais elegante.

Exemplos de uso comum de Window Functions incluem:

• Ordenar clientes por valor de vendas e atribuir uma classificação (ranking).
• Calcular saldo cumulativo ou rolling sums (somas móveis) em aplicações financeiras.
• Comparar o valor do mês atual com o valor do mês anterior sem usar subqueries.
• Identificar o primeiro e o último valor de um conjunto ordenado por data.
• Dividir dados em janelas lógicas para análises focadas em subgrupos do conjunto maior (particionamento).

---

3. Diferenças entre Window Functions e Funções de Agregação Convencionais

Para compreender melhor o impacto das Window Functions, compare-as com as funções de agregação tradicionais. Quando usamos uma função de agregação como SUM(coluna), em conjunto com a cláusula GROUP BY, o resultado final é reduzido ao conjunto de colunas agrupadas. Isso implica que você não consegue ver, ao mesmo tempo, o resultado da soma e cada linha original detalhadamente, pois o objetivo do GROUP BY é justamente agregar (compactar) várias linhas em uma só, por grupo.

Por outro lado, as Window Functions permitem que você apresente tanto o detalhamento de cada linha quanto o resultado do cálculo agregado, tudo em uma única consulta, sem precisar “perder” linhas ou duplicar dados via subqueries. Em essência, elas preservam a granularidade linha a linha, mas adicionam colunas que refletem algum tipo de agregação ou análise que considera uma “janela” definida na cláusula OVER.

Em vez de escrever algo como:

SELECT cliente_id,
       SUM(valor_venda)
FROM vendas
GROUP BY cliente_id;

Você poderia escrever:

SELECT cliente_id,
       valor_venda,
       SUM(valor_venda) OVER (PARTITION BY cliente_id) AS soma_por_cliente
FROM vendas;

No segundo caso, você não perde o valor individual da venda, pois a linha continua intacta, mas ganha uma coluna adicional que exibe a soma de vendas para aquele cliente.

---

4. Sintaxe Geral das Window Functions

A sintaxe de uma Window Function pode variar ligeiramente dependendo do banco de dados (Oracle, SQL Server, PostgreSQL, MySQL 8+, etc.), mas em geral segue o padrão:

função_window( [argumentos] ) OVER (
  [PARTITION BY lista_de_colunas]
  [ORDER BY lista_de_colunas]
  [ window_frame_clause ]
)

Essa cláusula OVER é onde “mora” a principal diferença entre uma função de agregação tradicional e uma Window Function. A OVER define a janela de linhas sobre as quais a função atuará.

Os elementos básicos dentro de OVER(...) são:

1. PARTITION BY: define como os dados são particionados (divididos em subconjuntos).
2. ORDER BY: estabelece a ordenação das linhas, o que é crítico para funções como ROW_NUMBER(), RANK(), LAG(), etc.
3. Window Frame: delimita a “janela” de linhas relativamente à linha atual, usando clausulas como ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Vamos explorar cada parte em detalhes.

---

4.1. A cláusula OVER

A palavra-chave OVER designa que a função será calculada como uma Window Function, e não como uma função de agregação convencional. Tudo que estiver dentro de OVER(...) define o “escopo” da análise ou agregação.

• Exemplo simples (sem particionar nem ordenar):SELECT valor_venda, SUM(valor_venda) OVER () AS soma_total FROM vendas; Neste caso, a Window Function SUM(valor_venda) OVER () fará a soma de todas as linhas da tabela, mas exibirá esse valor em todas as linhas. Você terá, por exemplo, a “soma total de vendas” repetida em cada linha.

---

4.2. A expressão PARTITION BY

O PARTITION BY é equivalente a um “GROUP BY dentro do contexto de janelas”. É ele que define como o conjunto será dividido em partições lógicas, sem quebrar a granularidade das linhas. Todos os cálculos feitos pela função se restringem às linhas da partição atual.

• Exemplo:SELECT cliente_id, data_venda, valor_venda, SUM(valor_venda) OVER (PARTITION BY cliente_id) AS soma_por_cliente FROM vendas; Neste caso, para cada linha, a soma retornada corresponde apenas aos valores de vendas para aquele cliente_id. Se existirem 10 registros do mesmo cliente, eles formam uma partição, e a soma exibida será idêntica para todos esses 10 registros.

---

4.3. A expressão ORDER BY

O ORDER BY dentro de uma Window Function não é o mesmo ORDER BY que usamos para ordenar o resultado final da consulta (embora muitas vezes ele coincida). Quando usado dentro do OVER, esse ORDER BY define a sequência de linhas para a janela.

• Por que isso é importante?
  Em funções como ROW_NUMBER(), RANK(), LAG() e LEAD(), a ordem das linhas define qual linha é “primeira”, “segunda” ou “anterior”, “próxima”. Em funções de agregação com frames, a ordenação é fundamental para delimitar a janela cumulativa.

Por exemplo, se quisermos uma soma cumulativa ao longo do tempo, precisamos ordenar as vendas pela data para saber como a soma irá progredir:

SELECT
    data_venda,
    valor_venda,
    SUM(valor_venda) OVER (ORDER BY data_venda
                           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS soma_cumulativa
FROM vendas;

---

4.4. Window Frames (ROWS e RANGE)

O conceito de Window Frame é o “recorte” que delimita quais linhas entram no cálculo da função para cada linha corrente. Ele pode ser definido com base em posição (usando ROWS) ou em valores (usando RANGE).

4.4.1. ROWS BETWEEN

• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: Inclui desde a primeira linha da partição até a linha atual.
• ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING: Inclui a linha anterior, a linha atual e a próxima linha.
• ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING: Inclui todas as linhas da partição (equivalente a não especificar frame, mas explicitamente).

4.4.2. RANGE BETWEEN

• RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: Similar ao ROWS, mas com base em intervalos de valores. Em geral, usado para cálculos de janela baseados em datas ou valores numéricos, como “somar todos os valores até a data atual”.

Um erro comum é esquecer que o RANGE pode agrupar linhas que têm o mesmo valor de ordenação. Já ROWS é mais específico: considera a posição exata das linhas, independentemente de empates na ordenação.

---

5. Funções de Ranking

As funções de ranking (ou de classificação) são algumas das mais populares Window Functions. Elas servem para atribuir um valor de posição/classificação a cada linha dentro de uma partição. As quatro principais são:

1. ROW_NUMBER()
2. RANK()
3. DENSE_RANK()
4. NTILE(n)

Vamos entender cada uma delas.

---

5.1. ROW_NUMBER()

A função ROW_NUMBER() retorna um número sequencial para cada linha dentro da partição, começando em 1. Não importa se existem empates ou não no valor de ordenação, cada linha receberá um número único na partição.

Exemplo:

SELECT
    cliente_id,
    valor_venda,
    ROW_NUMBER() OVER (PARTITION BY cliente_id ORDER BY data_venda) AS row_num
FROM vendas;

Nesse caso, para cada cliente, as vendas serão ordenadas pela data, e cada linha receberá um número distinto. Caso um cliente tenha 5 vendas registradas, elas terão row_num de 1 a 5 na ordem cronológica.

---

5.2. RANK()

A função RANK() também atribui uma classificação, mas leva em conta empates de forma que, se duas linhas “empatam”, elas recebem a mesma classificação, e a classificação seguinte pula um número.

Exemplo:

SELECT
    vendedor_id,
    total_vendas,
    RANK() OVER (ORDER BY total_vendas DESC) AS rank_vendas
FROM vendas_anuais;

Se houver dois vendedores empatados no primeiro lugar (por exemplo, ambos venderam 100 mil), ambos terão rank_vendas = 1. O próximo vendedor, mesmo que tenha menos vendas, terá rank_vendas = 3 (pois as duas primeiras classificações foram ocupadas pelos empatados em 1).

---

5.3. DENSE_RANK()

O DENSE_RANK() funciona quase como o RANK(), exceto que ele não “pula” números após um empate. Se dois vendedores estão empatados em primeiro lugar, ambos terão dense_rank = 1, porém o vendedor seguinte terá dense_rank = 2, e não 3, como no caso do RANK().

---

5.4. NTILE()

O NTILE(n) divide as linhas em n “baldes” (ou buckets) iguais, atribuindo um número que vai de 1 até n. É útil quando você quer segmentar uma coluna em quartis, quintis ou qualquer distribuição.

Exemplo:

SELECT
    vendedor_id,
    total_vendas,
    NTILE(4) OVER (ORDER BY total_vendas DESC) AS quartil
FROM vendas_anuais;

As linhas (vendedores) serão divididas em quatro grupos (quartis), do melhor para o pior em volume de vendas. Cada quartil recebe valores de 1 a 4.

---

6. Funções de Agregação como Window Functions

Além das funções de ranking, as Window Functions também são compostas pelas funções de agregação já conhecidas, mas usadas em conjunto com OVER. Eis as principais:

• SUM()
• COUNT()
• AVG()
• MIN()
• MAX()

Exemplo 1: Soma por partição

SELECT
    departamento,
    salario,
    SUM(salario) OVER (PARTITION BY departamento) AS salario_total_depto
FROM funcionarios;

Exemplo 2: Média móvel de 3 períodos

SELECT
    data,
    valor,
    AVG(valor) OVER (
        ORDER BY data
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS media_movel_3
FROM dados_diarios;

---

7. Funções de Análise: LAG, LEAD, FIRST_VALUE e LAST_VALUE

Essas funções permitem acessar valores de outras linhas sem precisar de subqueries. São especialmente úteis para comparações, cálculos de variação ou até mesmo para exibir múltiplas colunas relativas ao histórico ou futuro.

1. LAG(coluna, offset, [valor_padrão]): Retorna o valor da coluna na linha anterior (definido pelo offset, que por padrão é 1).
2. LEAD(coluna, offset, [valor_padrão]): Retorna o valor da coluna na linha seguinte (por padrão, também offset = 1).
3. FIRST_VALUE(coluna): Retorna o primeiro valor da coluna na janela.
4. LAST_VALUE(coluna): Retorna o último valor da coluna na janela.

Exemplo de LAG:

SELECT
    data_venda,
    valor_venda,
    LAG(valor_venda, 1, 0) OVER (ORDER BY data_venda) AS venda_anterior
FROM vendas;

Aqui, para cada linha, incluímos a informação de quanto foi vendido na linha anterior. Se não houver linha anterior (como na primeira data), usamos 0 como valor padrão.

Exemplo de LEAD:

SELECT
    data_venda,
    valor_venda,
    LEAD(valor_venda, 1, 0) OVER (ORDER BY data_venda) AS venda_proxima
FROM vendas;

Isso retorna a venda prevista/registrada na linha seguinte. Se não houver próxima linha, retorna 0, conforme especificado.

Exemplo de FIRST_VALUE e LAST_VALUE:

SELECT
    cliente_id,
    data_venda,
    valor_venda,
    FIRST_VALUE(valor_venda) OVER (PARTITION BY cliente_id ORDER BY data_venda) AS primeira_venda,
    LAST_VALUE(valor_venda) OVER (PARTITION BY cliente_id ORDER BY data_venda
                                  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
                                 ) AS ultima_venda
FROM vendas;

Observa-se que, para LAST_VALUE, é comum definir o frame como UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, garantindo que a “última linha” seja realmente a última no conjunto da partição.

---

8. Exemplos Práticos

Vamos agora aplicar os conceitos aprendidos em cenários mais completos.

---

8.1. Exemplo 1 – Classificação de vendas

Cenário: Temos uma tabela chamada vendas_mensais que contém as colunas:

• vendedor_id
• mes (por exemplo, ‘2025-01’, ‘2025-02’, …)
• valor_venda

Queremos listar todas as vendas de cada vendedor e, em cada linha, mostrar a posição (ranking) do vendedor naquele mês em relação aos demais, e também a soma total de vendas do vendedor no mês.

Código:

SELECT
    vendedor_id,
    mes,
    valor_venda,
    RANK() OVER (PARTITION BY mes ORDER BY valor_venda DESC) AS classificacao_no_mes,
    SUM(valor_venda) OVER (PARTITION BY vendedor_id, mes) AS vendas_totais_vendedor_mes
FROM vendas_mensais;

• RANK() OVER (PARTITION BY mes ORDER BY valor_venda DESC): define que queremos ranquear os vendedores dentro de cada mês (PARTITION BY mes), ordenando pelos maiores valores de venda para os menores.
• SUM(valor_venda) OVER (PARTITION BY vendedor_id, mes): soma o valor de venda de cada vendedor dentro do mês, exibindo esse total em cada linha pertencente à partição do mesmo vendedor e mesmo mês.

Resultado hipotético:

vendedor_id	mes	valor_venda	classificacao_no_mes	vendas_totais_vendedor_mes
1	2025-01	5000	1	8000
1	2025-01	3000	2	8000
2	2025-01	4000	1	4000
3	2025-01	2500	2	2500
…	…	…	…	…

(Observe que o rank é atribuído dentro do particionamento por mês. A soma permanece por vendedor e por mês.)

---

8.2. Exemplo 2 – Comparação de valores da linha anterior

Cenário: Suponhamos que temos uma tabela producao_diaria com colunas:

• data
• quantidade_produzida

Queremos comparar a quantidade produzida em cada dia com a do dia anterior e calcular a variação percentual.

Código:

SELECT
    data,
    quantidade_produzida,
    LAG(quantidade_produzida, 1, 0) OVER (ORDER BY data) AS quantidade_dia_anterior,
    (quantidade_produzida - LAG(quantidade_produzida, 1, 0) 
     OVER (ORDER BY data)
    ) AS diferenca_absoluta,
    CASE 
      WHEN LAG(quantidade_produzida, 1, 0) OVER (ORDER BY data) = 0 THEN 0
      ELSE ((quantidade_produzida - LAG(quantidade_produzida, 1, 0) 
             OVER (ORDER BY data)
            ) / CAST(LAG(quantidade_produzida, 1, 0) OVER (ORDER BY data) AS DECIMAL(10,2))) * 100
    END AS variacao_percentual
FROM producao_diaria;

Este exemplo mostra como a função LAG() permite resgatar o valor da quantidade produzida no dia anterior, sem subconsultas. A partir desses valores, calculamos a variação absoluta e a variação percentual.

---

8.3. Exemplo 3 – Cálculo de médias móveis

Cenário: Ainda com a tabela de produção diária, podemos querer uma média móvel de 7 dias, para entender como a produção está evoluindo em uma semana.

Código:

SELECT
    data,
    quantidade_produzida,
    AVG(quantidade_produzida) OVER (
       ORDER BY data
       ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS media_movel_7
FROM producao_diaria
ORDER BY data;

Explicando: Para cada linha (dia atual), o banco vai considerar as 6 linhas anteriores mais a linha atual, totalizando 7 dias. Então calcula-se a média de quantidade_produzida nesse intervalo.

---

9. Hacks Avançados e Boas Práticas

Depois de entender os conceitos básicos e alguns exemplos práticos, vamos mergulhar em técnicas e hacks avançados que podem elevar a produtividade e desempenho no uso de Window Functions.

---

9.1. Janelas Overlaps e uso de Partições Complexas

É possível combinar várias partições e frames para criar análises sofisticadas. Por exemplo, você pode dividir o conjunto de dados por múltiplas colunas. Imagine um cenário em que você quer particionar por região e ano, mas também aplicar um frame específico.

SELECT
    regiao,
    ano,
    mes,
    valor_venda,
    SUM(valor_venda) OVER (
        PARTITION BY regiao, ano
        ORDER BY mes
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS soma_cumulativa_regiao_ano
FROM vendas;

Aqui, para cada combinação de (região, ano), ordenamos pelos meses e construímos uma soma cumulativa. Se a tabela tiver dados de vários anos e regiões, cada partição funcionará independentemente, sem interferir na outra.

Overlaps e janelas que se sobrepõem

Um “overlap” pode ocorrer quando definimos partições mais amplas ou frames com RANGE que incluem linhas em potencial duplicidade. Em alguns cenários (por exemplo, quando datas se repetem ou quando valores podem conflitar), é preciso atenção para garantir que a “janela” esteja bem definida e não cause resultados inesperados.

---

9.2. Filtros internos nas Window Functions

Nem todos os SGBDs suportam essa funcionalidade, mas alguns (como PostgreSQL a partir da versão 14, Oracle e outros) permitem usar a cláusula FILTER dentro das Window Functions. Esse filtro possibilita calcular agregações condicionais dentro da mesma linha. Exemplo:

SELECT
    cliente_id,
    data_venda,
    valor_venda,
    SUM(valor_venda) FILTER (WHERE valor_venda > 1000)
      OVER (PARTITION BY cliente_id) AS soma_vendas_acima_1000
FROM vendas;

O resultado é que a função SUM só considera linhas em que valor_venda > 1000, mas exibirá esse valor para todas as linhas, respeitando a partição. Esse recurso elimina a necessidade de subqueries condicionais ou CASE dentro da agregação.

---

9.3. Indexação e Performance

As Window Functions podem consumir muitos recursos, pois exigem, por definição, uma ordenação e/ou particionamento dos dados. Algumas dicas:

1. Crie índices que reflitam o particionamento e ordenação:
   Se você sempre usa PARTITION BY departamento e ORDER BY data, é bom ter um índice que inicie por departamento, data.
2. Cuidado ao usar Frames grandes:
   Um frame “muito amplo” (UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) pode gerar operações de varredura total (full scan) na partição. Se a partição for grande, isso pode impactar a performance.
3. Use Particionamento de Tabela se for relevante:
   Em bancos como PostgreSQL, Oracle ou SQL Server, você pode particionar fisicamente as tabelas (não confundir com PARTITION BY das Window Functions) para reduzir o volume de dados que uma consulta precisa varrer.
4. Avalie o Custo de Funções Encadeadas:
   Cada Window Function é um passo a mais no plano de execução. Se você utiliza muitas em sequência, verifique se o banco de dados consegue otimizá-las em um único pass. Em alguns SGBDs, a engine combina processos de ordenação em um único passo, mas em outros pode ser necessário rever a consulta.
5. Mantenha Estatísticas de Tabelas Atualizadas:
   As estatísticas ajudam o otimizador a decidir sobre estratégias de execução. Atualizar as estatísticas (comandos como ANALYZE no PostgreSQL, por exemplo) é fundamental para desempenho.

---

10. Conclusão

As Window Functions revolucionaram a forma como análises e relatórios avançados são feitos no universo SQL. Elas possibilitam realizar em uma única consulta o que antes exigia subqueries complexas ou manipulação de dados em linguagens de programação externa. A capacidade de manter o contexto de cada linha, ao mesmo tempo em que se calcula agregações ou faz comparações entre linhas, confere grande expressividade ao SQL moderno.

Recapitulando:

1. Diferença Central:
   Ao contrário das funções de agregação clássicas (com GROUP BY), as Window Functions mantêm cada linha visível, adicionando colunas que representam o resultado de análises aplicadas a uma “janela” de dados.
2. Componentes Fundamentais:
   • PARTITION BY: Divide os dados em subconjuntos sem perder a granularidade.
   • ORDER BY: Ordena os dados, crucial para funções de ranking e análise temporal.
   • Window Frame: Define o “recorte” da janela (linhas anteriores, posteriores etc.).
3. Funções de Ranking: ROW_NUMBER(), RANK(), DENSE_RANK() e NTILE(), úteis para cenários de classificação e distribuição de dados.
4. Funções Analíticas: LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE(), que facilitam comparações entre linhas anteriores, posteriores, ou localizam valores extremidade dentro de partições.
5. Funções de Agregação: Podem ser usadas como Window Functions, permitindo somas, médias, contagens e outros agregados em cada linha, sem “esmagar” a granularidade dos dados.
6. Hacks Avançados: Uso de FILTER (quando suportado), frames específicos, múltiplos particionamentos, combinações de funções e bom planejamento de índices podem otimizar e ampliar ainda mais as possibilidades de análise.

Aplicações Práticas

Em qualquer cenário de BI, data analytics ou até mesmo em sistemas transacionais mais complexos, as Window Functions podem reduzir muito a complexidade das consultas, consolidando lógicas que, em outras circunstâncias, dependeriam de várias tabelas derivadas ou estruturas temporárias.

Para um cientista de dados, por exemplo, que precise calcular métricas de variação, médias móveis, ranking de relevância ou mesmo manipulação de dados para feature engineering, o domínio das Window Functions pode poupar um tempo enorme.

Próximos Passos

• Praticar: Escolha um conjunto de dados (pode ser público ou de sua própria aplicação) e crie consultas explorando as Window Functions.
• Analisar Planos de Execução: Verifique como o seu SGBD executa essas janelas e busque otimizações.
• Explorar Funcionalidades Específicas do SGBD: Bancos diferentes podem ter sintaxes adicionais ou variações. Pesquise a documentação do seu banco.
• Combinar com Outras Técnicas: Subqueries, CTEs (Common Table Expressions) e Window Functions podem ser combinadas para resolver problemas complexos de forma clara e performática.

Em resumo, se você quiser produzir análises robustas em SQL, dominar as Window Functions é fundamental. Esperamos que este guia, ainda que longo e detalhado, seja um mapa para você navegar com segurança e criatividade nesse universo. A partir de agora, com cada consulta que você escrever usando Window Functions, seu arsenal de soluções vai se expandir.