SQL para análise de dados · Guias · 30 de outubro de 2026 · 12 min de leitura

Funções de janela SQL: ROW_NUMBER, RANK, LAG, PARTITION BY e total acumulado

Uma função de janela calcula, para cada linha, um valor a partir de um conjunto de linhas relacionadas (a “janela”) sem juntar essas linhas numa só. O GROUP BY transforma 33.121 voos em seis linhas de status; COUNT(*) OVER (PARTITION BY status) mantém os 33.121 voos e escreve ao lado de cada um a contagem do seu status. Essa ideia simples dá rankings, top N por grupo, totais acumulados, médias móveis, fatias de um total e comparações com a linha anterior.

A sintaxe é sempre a mesma:

função(...) OVER (
  PARTITION BY ...   -- opcional: reinicia o cálculo para cada grupo
  ORDER BY ...       -- opcional: ordena as linhas dentro da janela
  ROWS/RANGE ...     -- opcional: quais linhas em volta da atual entram no cálculo
)

Todos os exemplos rodam no banco de dados de uma companhia aérea do curso de SQL da Nomto (104 aeroportos, 9 modelos de avião, 33.121 voos entre julho e setembro de 2026, 262.788 reservas; valores em dólares americanos, textos dos dados em inglês), na sintaxe do PostgreSQL. Cada saída abaixo é real, e você pode executar qualquer consulta na sala de SQL da Nomto para conferir.

OVER (): uma agregação ao lado de cada linha

A janela mais simples é o resultado inteiro. Compare o alcance de cada modelo com a média da frota:

SELECT model, range,
       ROUND(AVG(range) OVER ()) AS fleet_avg,
       range - ROUND(AVG(range) OVER ()) AS diff
FROM aircrafts
ORDER BY range DESC
LIMIT 5;

Sem janela, você precisaria de uma subconsulta ou de um JOIN com uma tabela de uma linha só para colocar fleet_avg em cada linha.

Fatia do total

As funções de janela são calculadas depois do GROUP BY, então dá para envolver uma agregação numa janela. SUM(COUNT(*)) OVER () é a soma de todas as contagens dos grupos:

SELECT status, COUNT(*) AS flights,
       ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct
FROM flights
GROUP BY status
ORDER BY flights DESC;

O 100.0 faz diferença: com inteiros, o PostgreSQL faria divisão inteira e a maioria das fatias sairia como 0.

PARTITION BY: uma janela separada para cada grupo

PARTITION BY divide as linhas em grupos e calcula a janela dentro de cada um, como o GROUP BY, mas sem juntar as linhas. Fatia da receita por classe dentro de cada modelo de avião:

SELECT a.model, tf.fare_conditions,
       SUM(tf.amount) AS revenue,
       ROUND(100.0 * SUM(tf.amount) / SUM(SUM(tf.amount)) OVER (PARTITION BY a.model), 1) AS pct_of_model
FROM ticket_flights tf
JOIN flights f ON f.flight_id = tf.flight_id
JOIN aircrafts a ON a.aircraft_code = f.aircraft_code
WHERE a.aircraft_code IN ('773', 'SU9')
GROUP BY a.model, tf.fare_conditions
ORDER BY a.model, revenue DESC;

As porcentagens somam 100 dentro de cada modelo, não na tabela inteira.

ROW_NUMBER, RANK e DENSE_RANK

As três numeram as linhas na ordem dada pelo ORDER BY dentro do OVER. A diferença aparece só nos empates:

• ROW_NUMBER() dá a cada linha um número único; os empates são resolvidos de forma arbitrária, a menos que você acrescente um critério de desempate.
• RANK() dá o mesmo número às linhas empatadas e depois pula: 1, 2, 2, 4.
• DENSE_RANK() dá o mesmo número às linhas empatadas e não pula: 1, 2, 2, 3.

Vamos ordenar os aeroportos pelo número de destinos distintos. A parte interessante fica perto do décimo lugar, onde cinco aeroportos empatam com 9 destinos:

WITH d AS (
  SELECT departure_airport, COUNT(DISTINCT arrival_airport) AS destinations
  FROM flights
  GROUP BY departure_airport
), r AS (
  SELECT departure_airport, destinations,
         ROW_NUMBER() OVER (ORDER BY destinations DESC, departure_airport) AS row_num,
         RANK()       OVER (ORDER BY destinations DESC) AS rnk,
         DENSE_RANK() OVER (ORDER BY destinations DESC) AS dense_rnk
  FROM d
)
SELECT * FROM r
WHERE row_num BETWEEN 8 AND 15
ORDER BY row_num;

Depois do empate de cinco, o RANK salta para 15 (quatro posições são “gastas”), enquanto o DENSE_RANK continua com 11. O desempate por departure_airport no ROW_NUMBER deixa o resultado reproduzível; sem ele, IKT, KZN, PEE, SGC e TJM poderiam sair em qualquer ordem em outra execução.

Qual usar:

• “Quero exatamente N linhas” ou “remover duplicados”: ROW_NUMBER.
• Ranking “olímpico”, em que empatados dividem a posição e a seguinte é pulada: RANK.
• “Os 3 maiores valores distintos”, por exemplo os três preços mais altos, não importa quantas linhas tenham cada um: DENSE_RANK.

Top N por grupo

O uso clássico de ROW_NUMBER com PARTITION BY: os dois destinos mais frequentes a partir de cada aeroporto de Moscou.

WITH r AS (
  SELECT departure_airport, arrival_airport, COUNT(*) AS flights,
         ROW_NUMBER() OVER (PARTITION BY departure_airport
                            ORDER BY COUNT(*) DESC, arrival_airport) AS rn
  FROM flights
  WHERE departure_airport IN ('SVO', 'DME', 'VKO')
  GROUP BY departure_airport, arrival_airport
)
SELECT departure_airport, arrival_airport, flights, rn
FROM r
WHERE rn <= 2
ORDER BY departure_airport, rn;

Duas coisas para notar:

1. Não dá para filtrar uma função de janela no WHERE. O WHERE roda antes de as janelas serem calculadas, então WHERE ROW_NUMBER() OVER (...) <= 2 dá erro. Calcule o número numa CTE ou subconsulta e filtre por fora. (Alguns bancos oferecem a cláusula QUALIFY para isso; o PostgreSQL não.)
2. Verifique os empates. A partir de Vnukovo, BZK e LED têm 183 voos cada; o ROW_NUMBER colocou BZK em primeiro só por causa do desempate alfabético. Se as duas linhas empatadas devem aparecer, use RANK() ... <= 2; isso pode devolver mais de duas linhas por grupo.

Deduplicação: a linha mais recente por chave

O mesmo padrão mantém uma linha por chave. A última partida concluída de cada número de voo saindo de Kazan:

WITH ranked AS (
  SELECT flight_no, arrival_airport, scheduled_departure,
         ROW_NUMBER() OVER (PARTITION BY flight_no ORDER BY scheduled_departure DESC) AS rn
  FROM flights
  WHERE departure_airport = 'KZN' AND status = 'Arrived'
)
SELECT flight_no, arrival_airport, scheduled_departure
FROM ranked
WHERE rn = 1
ORDER BY flight_no
LIMIT 5;

Total acumulado

Coloque ORDER BY dentro do OVER de uma agregação e ela vira cumulativa. Reservas diárias na primeira semana de agosto, com total acumulado:

WITH daily AS (
  SELECT CAST(book_date AS date) AS day, COUNT(*) AS bookings
  FROM bookings
  WHERE book_date >= '2026-08-01' AND book_date < '2026-08-08'
  GROUP BY CAST(book_date AS date)
)
SELECT day, bookings,
       SUM(bookings) OVER (ORDER BY day) AS running_total,
       LAG(bookings) OVER (ORDER BY day) AS prev_day,
       bookings - LAG(bookings) OVER (ORDER BY day) AS change
FROM daily
ORDER BY day;

Acrescente PARTITION BY para reiniciar o total acumulado por grupo, por exemplo por aeroporto ou por mês.

LAG e LEAD: a linha anterior e a seguinte

LAG(x) devolve o x da linha anterior na ordem da janela, LEAD(x) o da linha seguinte. A tabela acima usa LAG para a variação dia a dia. Na primeira linha não existe linha anterior, então o resultado é NULL; LAG(bookings, 1, 0) devolveria 0 no lugar, e LAG(bookings, 7) olharia uma semana para trás.

LEAD responde “o que vem depois”. Com que frequência voa o PG0579, e quando é a sua última partida?

SELECT flight_no,
       CAST(scheduled_departure AS date) AS day,
       LEAD(CAST(scheduled_departure AS date)) OVER (ORDER BY scheduled_departure)
         - CAST(scheduled_departure AS date) AS days_to_next
FROM flights
WHERE flight_no = 'PG0579'
ORDER BY scheduled_departure DESC
LIMIT 3;

É um voo semanal; a última partida da programação não tem uma seguinte. Repare que o ORDER BY da janela (crescente) é independente do ORDER BY da consulta (decrescente): primeiro a janela é calculada, depois a saída é ordenada.

Quando você compara linhas de várias entidades de uma vez, sempre particione: LEAD(...) OVER (PARTITION BY flight_no ORDER BY scheduled_departure). Sem isso, a última partida de um voo seria comparada com a primeira partida do número de voo seguinte.

Frames da janela: ROWS ou RANGE

O frame define quais linhas em volta da atual a função enxerga. Duas coisas para guardar.

O frame padrão. Com ORDER BY e sem frame explícito, o frame é RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. O RANGE trata todas as linhas com o mesmo valor de ORDER BY como um único passo, então linhas empatadas recebem o mesmo total acumulado:

WITH d AS (
  SELECT departure_airport, COUNT(DISTINCT arrival_airport) AS destinations
  FROM flights
  GROUP BY departure_airport
), w AS (
  SELECT departure_airport, destinations,
         SUM(destinations) OVER (ORDER BY destinations DESC) AS range_total,
         SUM(destinations) OVER (ORDER BY destinations DESC, departure_airport
                                 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_total
  FROM d
)
SELECT * FROM w
WHERE destinations BETWEEN 9 AND 10
ORDER BY destinations DESC, departure_airport;

Com RANGE, os cinco aeroportos empatados pulam direto para 261. Com ROWS, o total cresce uma linha por vez. Se o seu total acumulado “dá degraus” em timestamps iguais, o motivo é este. Para um total acumulado linha a linha, use ROWS e uma ordenação única.

Médias móveis. Um frame como ROWS BETWEEN 2 PRECEDING AND CURRENT ROW é uma janela móvel de 3 linhas:

WITH daily AS (
  SELECT CAST(book_date AS date) AS day, COUNT(*) AS bookings
  FROM bookings
  WHERE book_date >= '2026-08-01' AND book_date < '2026-08-08'
  GROUP BY CAST(book_date AS date)
)
SELECT day, bookings,
       ROUND(AVG(bookings) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 1) AS avg_3d
FROM daily
ORDER BY day;

As duas primeiras linhas têm média de menos de três dias, porque nesta janela não há nada antes de 1º de agosto. ROWS conta linhas, não dias: se faltasse um dia em daily, a média “de 3 dias” cobriria em silêncio quatro dias do calendário. Preencha as lacunas antes com uma série de datas, ou use RANGE BETWEEN INTERVAL '2 days' PRECEDING AND CURRENT ROW sobre uma coluna de data.

Onde as funções de janela entram na ordem da consulta

Logicamente, as janelas são calculadas depois de FROM, WHERE, GROUP BY e HAVING, e antes de ORDER BY e LIMIT. Isso explica três regras:

• Uma janela só enxerga as linhas que sobreviveram ao WHERE; para ranquear todos os voos mas mostrar só alguns, ranqueie numa subconsulta e filtre por fora.
• Dá para usar agregações dentro de uma janela (SUM(COUNT(*)) OVER ()), porque o agrupamento já aconteceu.
• Não dá para usar uma função de janela no WHERE, no GROUP BY ou no HAVING; envolva a consulta em outra.

Para entender como WHERE, GROUP BY e HAVING interagem, veja GROUP BY e HAVING. Ranking e “top N por grupo” também estão entre as perguntas de entrevista SQL mais comuns.

Perguntas frequentes

Qual a diferença entre função de janela e GROUP BY?

O GROUP BY devolve uma linha por grupo. Uma função de janela devolve todas as linhas de entrada e acrescenta um valor calculado sobre um grupo de linhas. Dá para usar os dois na mesma consulta: a janela é aplicada sobre o resultado agrupado.

Qual a diferença entre RANK e DENSE_RANK?

Os dois dão o mesmo número às linhas empatadas. Depois de um empate, o RANK pula números (1, 2, 2, 4) e o DENSE_RANK não (1, 2, 2, 3). No exemplo dos aeroportos, cinco empatam no 10º lugar; o seguinte fica em 15º pelo RANK e em 11º pelo DENSE_RANK.

Por que o meu total acumulado se repete em várias linhas?

Porque o frame padrão é RANGE, que trata as linhas com valores iguais no ORDER BY como pares e inclui todas de uma vez. Especifique ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW e deixe a ordenação única.

Posso usar uma função de janela no WHERE?

Não. Calcule-a numa CTE ou subconsulta e filtre na consulta externa.

O ROW_NUMBER precisa de ORDER BY?

O PostgreSQL aceita ROW_NUMBER() OVER (), mas aí a numeração é arbitrária. Para qualquer resultado que você queira reproduzir, dê a ele um ORDER BY com um desempate único.

Outros idiomas: Tiếng Việt, English, Español, 简体中文