Window functions para ranking, comparações e análise de tendência

Window functions (funções de janela) são um recurso do SQL para calcular métricas “por linha”, mas levando em conta um conjunto de linhas relacionado (a “janela”) sem colapsar o resultado como acontece em agregações com GROUP BY. Isso permite fazer ranking, comparações linha a linha, participação no total, acumulados e análises de tendência mantendo o nível de detalhe original (por exemplo, uma linha por pedido, por cliente, por dia).

A ideia central é: você escreve uma função (muitas vezes agregadora, como SUM ou AVG) e adiciona OVER (...) para definir como as linhas serão agrupadas e ordenadas dentro da janela. Em vez de retornar uma linha por grupo, a função retorna um valor para cada linha, calculado com base nas linhas da janela.

1) Anatomia de uma window function

Uma window function costuma ter esta forma:

FUNCAO(...) OVER (PARTITION BY ... ORDER BY ... ROWS/RANGE ...)

• PARTITION BY: define “grupos” independentes dentro do resultado (por exemplo, por cliente, por categoria, por loja). Cada partição tem seus próprios cálculos.
• ORDER BY: define a ordem dentro da partição (por exemplo, por data, por valor). É essencial para ranking, tendências e acumulados.
• ROWS / RANGE: define o “frame” (moldura) da janela, isto é, quais linhas ao redor da linha atual entram no cálculo. Se você não especificar, o padrão depende do banco, mas em geral, com ORDER BY, o frame tende a ser “do início até a linha atual” (acumulado) ou “toda a partição” (para algumas funções). Para evitar ambiguidades, vale explicitar quando estiver fazendo acumulados ou médias móveis.

Você vai encontrar três famílias muito usadas:

• Funções de ranking: ROW_NUMBER, RANK, DENSE_RANK, NTILE.
• Funções de navegação: LAG, LEAD, FIRST_VALUE, LAST_VALUE.
• Agregações como window: SUM(...) OVER, AVG(...) OVER, COUNT(...) OVER, etc.

2) Ranking: ordenar e classificar sem perder o detalhe

Ranking é um dos usos mais diretos de window functions: você quer classificar itens (clientes, produtos, vendedores) dentro de um contexto (mês, categoria, região) e ainda manter as colunas originais na mesma linha.

Continue em nosso aplicativo e ...

• Ouça o áudio com a tela desligada
• Ganhe Certificado após a conclusão
• + de 5000 cursos para você explorar!

ou continue lendo abaixo...

Baixar o aplicativo

2.1) ROW_NUMBER vs RANK vs DENSE_RANK

Considere um cenário típico: você tem uma tabela de vendas diárias por vendedor e quer ranquear vendedores por receita dentro de cada mês.

SELECT  month_ref, seller_id, revenue,  ROW_NUMBER() OVER (PARTITION BY month_ref ORDER BY revenue DESC) AS rn,  RANK()       OVER (PARTITION BY month_ref ORDER BY revenue DESC) AS rnk,  DENSE_RANK() OVER (PARTITION BY month_ref ORDER BY revenue DESC) AS drnkFROM seller_monthly;

• ROW_NUMBER: sempre gera 1,2,3… sem empates. Se dois vendedores tiverem a mesma receita, um ficará “na frente” do outro por algum critério implícito (ou arbitrário) se você não adicionar um desempate no ORDER BY.
• RANK: empates recebem o mesmo rank, mas “pula” números depois do empate (ex.: 1,1,3).
• DENSE_RANK: empates recebem o mesmo rank e não há “pulos” (ex.: 1,1,2).

Boa prática: sempre que usar ROW_NUMBER para “pegar o top 1”, inclua um critério de desempate no ORDER BY para tornar o resultado determinístico.

ROW_NUMBER() OVER (PARTITION BY month_ref ORDER BY revenue DESC, seller_id ASC)

2.2) Passo a passo: Top N por grupo (ex.: top 3 produtos por categoria)

Objetivo: listar os 3 produtos com maior receita dentro de cada categoria, mantendo as colunas do produto e a receita.

Passo 1 — Calcular o ranking por categoria

SELECT  category_id,  product_id,  revenue,  DENSE_RANK() OVER (PARTITION BY category_id ORDER BY revenue DESC) AS posFROM product_category_revenue;

Passo 2 — Filtrar apenas o Top 3

SELECT *FROM (  SELECT    category_id,    product_id,    revenue,    DENSE_RANK() OVER (PARTITION BY category_id ORDER BY revenue DESC) AS pos  FROM product_category_revenue) tWHERE pos <= 3ORDER BY category_id, pos, revenue DESC;

Por que funciona bem? Porque o ranking é calculado “por categoria” (partição) e você filtra depois. Isso evita subconsultas complexas por categoria e mantém o resultado legível.

2.3) NTILE para faixas (quartis, decis)

NTILE(n) divide as linhas ordenadas em n grupos com tamanhos o mais equilibrados possível. É útil para segmentar clientes por gasto (ex.: quartis) ou produtos por volume.

SELECT  customer_id,  total_spent,  NTILE(4) OVER (ORDER BY total_spent DESC) AS quartil_gastoFROM customer_spend;

Se você quiser quartis por região, basta particionar:

NTILE(4) OVER (PARTITION BY region ORDER BY total_spent DESC)

3) Comparações linha a linha: variação, diferença e crescimento

Comparar a linha atual com uma linha anterior (ou posterior) é uma necessidade recorrente em análises: crescimento mês a mês, diferença para o dia anterior, comparação com o último pedido do cliente, etc. Para isso, as funções LAG e LEAD são as mais usadas.

3.1) LAG e LEAD: trazendo valores vizinhos

LAG(coluna) acessa o valor de uma linha anterior dentro da mesma partição e ordem. LEAD acessa uma linha posterior.

Exemplo: receita diária e variação vs dia anterior.

SELECT  dt,  revenue,  LAG(revenue) OVER (ORDER BY dt) AS revenue_prev_day,  revenue - LAG(revenue) OVER (ORDER BY dt) AS diff_abs,  (revenue - LAG(revenue) OVER (ORDER BY dt)) / NULLIF(LAG(revenue) OVER (ORDER BY dt), 0) AS diff_pctFROM daily_revenueORDER BY dt;

• NULLIF(...,0) evita divisão por zero ao calcular percentual.
• Se o primeiro dia não tem “dia anterior”, LAG retorna NULL (ou um valor padrão se você informar).

Você pode definir um valor padrão:

LAG(revenue, 1, 0) OVER (ORDER BY dt)

Aqui, quando não existir linha anterior, retorna 0.

3.2) Passo a passo: crescimento mês a mês por produto

Objetivo: para cada produto, calcular a receita do mês, a receita do mês anterior e a variação percentual.

Passo 1 — Garantir ordenação temporal dentro do produto

SELECT  product_id,  month_ref,  revenue,  LAG(revenue) OVER (PARTITION BY product_id ORDER BY month_ref) AS revenue_prevFROM product_monthly_revenue;

Passo 2 — Calcular variações

SELECT  product_id,  month_ref,  revenue,  revenue_prev,  revenue - revenue_prev AS delta_abs,  (revenue - revenue_prev) / NULLIF(revenue_prev, 0) AS delta_pctFROM (  SELECT    product_id,    month_ref,    revenue,    LAG(revenue) OVER (PARTITION BY product_id ORDER BY month_ref) AS revenue_prev  FROM product_monthly_revenue) tORDER BY product_id, month_ref;

Interpretação prática: delta_abs mostra ganho/perda em valor; delta_pct mostra crescimento relativo. Em relatórios, é comum formatar delta_pct como porcentagem na camada de visualização.

3.3) Comparação com “melhor anterior” e não apenas o imediatamente anterior

Às vezes você quer comparar a linha atual com o máximo histórico até então (por exemplo, “estamos acima do recorde anterior?”). Isso pode ser feito com agregação em janela com frame acumulado.

SELECT  dt,  revenue,  MAX(revenue) OVER (ORDER BY dt ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS max_before,  revenue - MAX(revenue) OVER (ORDER BY dt ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS diff_vs_recordFROM daily_revenueORDER BY dt;

Note o frame: “do início até a linha anterior”. Assim, o recorde não inclui o valor do dia atual.

4) Análise de tendência: acumulados, médias móveis e participação

Tendência geralmente envolve suavizar ruído (média móvel), acompanhar evolução (acumulado) e contextualizar (participação no total). Window functions permitem fazer isso diretamente no SQL.

4.1) Acumulado (running total)

Um acumulado soma valores ao longo do tempo. Exemplo: receita acumulada no mês.

SELECT  dt,  revenue,  SUM(revenue) OVER (PARTITION BY month_ref ORDER BY dt ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS revenue_mtdFROM daily_revenueORDER BY dt;

Pontos de atenção:

• Sem PARTITION BY month_ref, o acumulado atravessa meses.
• O frame explícito (ROWS BETWEEN ...) deixa claro que é acumulado até a linha atual.

4.2) Média móvel (moving average) para suavizar variações

Média móvel é útil para enxergar tendência quando há sazonalidade ou ruído diário. Exemplo: média móvel de 7 dias.

SELECT  dt,  revenue,  AVG(revenue) OVER (ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma_7dFROM daily_revenueORDER BY dt;

Interpretação: para cada dia, calcula a média do dia atual e dos 6 dias anteriores (janela de 7 dias). Nos primeiros dias, a janela terá menos linhas, então a média será calculada com o que existir.

Você também pode fazer média móvel por loja (ou outra dimensão):

AVG(revenue) OVER (PARTITION BY store_id ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

4.3) Participação no total (share) sem perder detalhe

Um uso muito comum de SUM(...) OVER é calcular o total do grupo e depois a participação de cada linha nesse total.

Exemplo: participação de cada produto na receita da categoria.

SELECT  category_id,  product_id,  revenue,  SUM(revenue) OVER (PARTITION BY category_id) AS category_total,  revenue / NULLIF(SUM(revenue) OVER (PARTITION BY category_id), 0) AS share_in_categoryFROM product_category_revenue;

Note que aqui não há ORDER BY porque o total da categoria independe de ordem; a janela é “toda a partição”.

5) “Primeiro”, “último” e comparações com extremos

Para análises como “primeira compra do cliente”, “último preço praticado”, “melhor dia do mês”, você pode usar FIRST_VALUE e LAST_VALUE. O cuidado principal é que LAST_VALUE depende do frame: se o frame terminar na linha atual, o “último” será a própria linha, não o último da partição.

5.1) FIRST_VALUE para referência inicial

Exemplo: para cada cliente, pegar a data da primeira compra e comparar com a compra atual.

SELECT  customer_id,  order_id,  order_dt,  FIRST_VALUE(order_dt) OVER (PARTITION BY customer_id ORDER BY order_dt) AS first_order_dtFROM orders;

5.2) LAST_VALUE com frame explícito

Exemplo: para cada cliente, trazer a data do último pedido em todas as linhas do cliente.

SELECT  customer_id,  order_id,  order_dt,  LAST_VALUE(order_dt) OVER (    PARTITION BY customer_id    ORDER BY order_dt    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING  ) AS last_order_dtFROM orders;

Sem ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, muitos bancos retornariam como “último” apenas até a linha atual, o que muda o significado.

6) Padrões práticos para relatórios: receitas, rankings e alertas

6.1) Ranking + participação + variação no mesmo dataset

Um padrão poderoso é montar um dataset que já traga: rank do item, participação no total e variação vs período anterior. Isso reduz trabalho na ferramenta de BI e padroniza a lógica.

Exemplo: por mês e categoria, ranquear produtos por receita, calcular share e variação vs mês anterior do mesmo produto.

SELECT  month_ref,  category_id,  product_id,  revenue,  DENSE_RANK() OVER (PARTITION BY month_ref, category_id ORDER BY revenue DESC) AS rank_in_cat_month,  revenue / NULLIF(SUM(revenue) OVER (PARTITION BY month_ref, category_id), 0) AS share_in_cat_month,  LAG(revenue) OVER (PARTITION BY category_id, product_id ORDER BY month_ref) AS revenue_prev_month,  (revenue - LAG(revenue) OVER (PARTITION BY category_id, product_id ORDER BY month_ref)) / NULLIF(LAG(revenue) OVER (PARTITION BY category_id, product_id ORDER BY month_ref), 0) AS mom_growth_pctFROM product_category_monthly_revenue;

Leitura do resultado:

• rank_in_cat_month: posição do produto dentro da categoria naquele mês.
• share_in_cat_month: quanto o produto representa da receita da categoria naquele mês.
• mom_growth_pct: crescimento vs mês anterior do mesmo produto na mesma categoria.

6.2) Detectar quedas ou picos com base em média móvel

Você pode criar um “alerta” simples comparando o valor do dia com a média móvel recente. Exemplo: sinalizar quando a receita do dia está 30% abaixo da média móvel de 7 dias.

SELECT  dt,  revenue,  AVG(revenue) OVER (ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma_7d,  CASE    WHEN revenue < 0.7 * AVG(revenue) OVER (ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)    THEN 1 ELSE 0  END AS is_drop_alertFROM daily_revenueORDER BY dt;

Esse padrão é útil para monitoramento operacional: você gera uma coluna binária e filtra os dias com alerta em um painel.

7) Armadilhas comuns e como evitar

7.1) Esquecer o PARTITION BY (ou particionar errado)

Se você quer ranking por categoria e esquece o PARTITION BY category_id, você vai ranquear no dataset inteiro. O resultado pode parecer “plausível” e passar despercebido, então vale validar com um filtro em uma categoria específica e conferir se os ranks reiniciam em 1.

7.2) Ordenação insuficiente e resultados não determinísticos

Se houver empates no critério de ordenação e você usar ROW_NUMBER, a ordem entre empatados pode variar. Para relatórios reprodutíveis, inclua colunas de desempate no ORDER BY da janela (por exemplo, um identificador).

7.3) Confundir ROWS e RANGE

ROWS define a janela por contagem de linhas (ex.: 6 linhas anteriores). RANGE define por intervalo de valores no ORDER BY (ex.: todos com a mesma data/valor). Para médias móveis por “últimos 7 registros”, use ROWS. Para janelas por intervalo de tempo (quando suportado e bem definido), RANGE pode fazer sentido, mas exige cuidado com duplicidades no valor de ordenação.

7.4) LAST_VALUE retornando a linha atual

Se você usar LAST_VALUE com ORDER BY e não ajustar o frame, é comum obter o valor da própria linha como “último”. Quando a intenção for “último da partição”, use frame até UNBOUNDED FOLLOWING.

7.5) Repetir a mesma window expression muitas vezes

Em consultas grandes, repetir LAG(revenue) OVER (...) várias vezes pode prejudicar legibilidade. Um padrão é calcular uma vez e reutilizar em uma camada externa.

SELECT  dt,  revenue,  revenue_prev,  revenue - revenue_prev AS diff_absFROM (  SELECT    dt,    revenue,    LAG(revenue) OVER (ORDER BY dt) AS revenue_prev  FROM daily_revenue) tORDER BY dt;

8) Checklist rápido: escolhendo a window function certa

• Quer Top N por grupo? Use DENSE_RANK (ou ROW_NUMBER com desempate) e filtre <= N.
• Quer segmentar em faixas (quartis/decis)? Use NTILE.
• Quer comparar com período anterior? Use LAG (ou LEAD para olhar adiante).
• Quer acumulado? Use SUM(...) OVER com ORDER BY e frame ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
• Quer média móvel? Use AVG(...) OVER com frame de linhas anteriores.
• Quer participação no total? Use SUM(...) OVER (PARTITION BY ...) e divida o valor pela soma.
• Quer primeiro/último valor do grupo? Use FIRST_VALUE e LAST_VALUE com frame explícito para o último.