SQL Funções de Janela (OVER)

Com as funções agregadas e o GROUP BY, a Ana aprendeu a resumir: "a média de preço é tal", "cada categoria vendeu tanto". Só que o resumo engole as linhas: 7 produtos viram uma linha com a média. E se ela quiser ver cada produto e, ao lado, a média do grupo? Ou cada mês de vendas e, ao lado, quanto já foi vendido no ano até ali? Para isso existem as funções de janela: a mesma SUM, AVG ou COUNT de sempre, seguida da palavra OVER. Elas fazem a conta olhando para outras linhas, mas devolvem uma resposta para cada linha, sem juntar nada.

Todos os exemplos rodam de verdade no seu navegador, no banco da Loja Aurora. O SQLite tem funções de janela desde a versão 3.25 (2018); o editor e o lab usam a 3.50.

A ideia: o boletim com a média da turma

Pense no boletim da escola. A secretaria pode fazer duas coisas com as notas da turma:

• Um cartaz no mural: "média da turma: 7,2". Uma linha só. Os alunos sumiram; sobrou o resumo. Isso é a agregação (AVG com ou sem GROUP BY).
• O boletim de cada aluno, com a nota dele e, numa coluna ao lado, "média da turma: 7,2". Todo aluno continua lá, e cada um consegue se comparar com a turma. Isso é a janela.

O nome "janela" vem daí: cada linha olha pela janela e enxerga um conjunto de outras linhas (a turma inteira, ou só as anteriores a ela). A função calcula sobre o que a linha enxerga, e o resultado fica na própria linha.

1. Agregação × janela: as linhas não somem

Primeiro a agregação que você já conhece: a média de preço dos eletrônicos (categoria 2).

SELECT COUNT(*) AS produtos,
       ROUND(AVG(preco), 2) AS media
FROM produtos
WHERE categoria_id = 2;

Uma linha: 7 produtos, média de R$ 82,71. Agora a mesma média como função de janela:

SELECT nome,
       preco,
       ROUND(AVG(preco) OVER (), 2) AS media
FROM produtos
WHERE categoria_id = 2;

AVG(preco)

A mesma função de média de sempre (aula AVG()).

OVER

"Sobre". Transforma a agregação numa função de janela: "calcule a média sobre esta janela, mas sem juntar as linhas".

()

A definição da janela. Parênteses vazios querem dizer "a janela é todas as linhas do resultado". Os parênteses são obrigatórios, mesmo vazios.

WHERE categoria_id = 2

O WHERE roda antes da janela. Por isso a janela enxerga só os 7 eletrônicos, e a média é a deles, não a dos 45 produtos.

As 7 linhas continuaram lá, cada uma com o seu nome e preço, e a média (82.71) repetida ao lado de todas. Repare que não tem GROUP BY: com janela, você pode misturar a coluna nome com uma conta sobre o grupo, coisa que a agregação não deixa fazer direito.

Fazendo contas com a janela

Com a média ao lado de cada linha, dá para comparar. Quanto cada eletrônico está acima ou abaixo da média, e que fatia ele é da soma dos preços:

SELECT nome,
       preco,
       ROUND(preco - AVG(preco) OVER (), 2) AS diferenca,
       ROUND(100.0 * preco / SUM(preco) OVER (), 1) AS porcentagem
FROM produtos
WHERE categoria_id = 2
ORDER BY preco DESC;

preco - AVG(preco) OVER ()

O preço da linha menos a média de todas. Positivo: acima da média; negativo: abaixo.

SUM(preco) OVER ()

A soma dos 7 preços, repetida em cada linha.

100.0 * preco / SUM(…)

A porcentagem. O 100.0, com ponto, garante conta com decimais.

ORDER BY preco DESC

A ordem do resultado final. Ela não muda a conta da janela, que continua sendo sobre as 7 linhas.

A Caixa de Som Portátil está R$ 107,28 acima da média e sozinha é 32,8% da soma dos preços.

2. OVER (ORDER BY): o total acumulado

Agora o relatório que a Ana mais queria: as vendas de cada mês de 2025 e, ao lado, quanto já foi vendido no ano até aquele mês. Primeiro uma CTE (aula CTE (WITH)) monta as vendas de cada mês; depois a janela acumula:

WITH mensal AS (
  SELECT strftime('%Y-%m', p.data) AS mes,
         ROUND(SUM(i.quantidade * i.preco_unitario), 2) AS vendas
  FROM pedidos AS p
  JOIN itens_pedido AS i ON i.pedido_id = p.id
  WHERE p.data LIKE '2025-%'
  GROUP BY mes
)
SELECT mes,
       vendas,
       ROUND(SUM(vendas) OVER (ORDER BY mes), 2) AS acumulado
FROM mensal
ORDER BY mes;

mensal

A CTE: uma linha por mês de 2025, com o total vendido em produtos. strftime('%Y-%m', data) corta a data em "ano-mês" (aula Funções de Data).

SUM(vendas) OVER (ORDER BY mes)

Com um ORDER BY dentro do OVER, a janela de cada linha deixa de ser "todas as linhas" e passa a ser "da primeira até esta", na ordem dos meses. A soma dessa janela é o acumulado.

ROUND(…, 2)

Garante o resultado em centavos. Somar muitos decimais pode deixar caudas de ponto flutuante (aula Funções Numéricas), e o arredondamento evita surpresas.

ORDER BY mes (no fim)

A ordem de exibição. O ORDER BY de dentro do OVER diz como a janela anda; o de fora diz como o resultado aparece. São coisas diferentes: escreva os dois.

Leia a coluna acumulado de cima para baixo: em janeiro, 865.78 (só janeiro); em fevereiro, 865.78 + 401.7 = 1267.48; e assim por diante, até 5464.52 em dezembro, o total do ano. Cada linha enxerga só o passado e ela mesma.

SELECT data, frete
FROM pedidos
WHERE data >= '2026-01-01'
ORDER BY data;

SELECT data,
       frete,
       SUM(frete) OVER (ORDER BY data) AS frete_acumulado
FROM pedidos
WHERE data >= '2026-01-01'
ORDER BY data;

3. A moldura: ROWS BETWEEN e a média móvel

Abril de 2025 foi um mês fraco (só R$ 25,13). Olhando mês a mês, os números pulam muito, e fica difícil ver a tendência. Uma média móvel resolve: em cada mês, a média dele e dos dois anteriores. Para isso você diz à janela exatamente quais linhas ela enxerga, com uma moldura (em inglês, frame):

WITH mensal AS (
  SELECT strftime('%Y-%m', p.data) AS mes,
         ROUND(SUM(i.quantidade * i.preco_unitario), 2) AS vendas
  FROM pedidos AS p
  JOIN itens_pedido AS i ON i.pedido_id = p.id
  WHERE p.data LIKE '2025-%'
  GROUP BY mes
)
SELECT mes,
       vendas,
       ROUND(AVG(vendas) OVER (
         ORDER BY mes
         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ), 2) AS media_3_meses
FROM mensal
ORDER BY mes;

ORDER BY mes

Em que ordem as linhas ficam dentro da janela. Sem ordem, "as duas anteriores" não teria sentido.

ROWS

"Linhas". A moldura vai ser contada em linhas, uma a uma.

BETWEEN 2 PRECEDING

"Entre 2 anteriores": começa duas linhas antes da linha atual.

AND CURRENT ROW

"E a linha atual": termina nela. São três linhas: as duas de antes e a atual.

Repare nas primeiras linhas: em janeiro não há meses anteriores (dentro de 2025), então a média é só de janeiro (865.78); em fevereiro, de janeiro e fevereiro (633.74). Do março em diante, sempre três meses. E veja como a média móvel suaviza abril: 335.3, em vez de despencar para 25.13.

As peças da moldura

Combinações comuns: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (acumulado), ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING (o mês, o anterior e o seguinte), ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING (o que falta até o fim do ano). Com uma ponta só, como ROWS 2 PRECEDING, a outra ponta é CURRENT ROW.

WITH mensal AS (
  SELECT strftime('%Y-%m', p.data) AS mes,
         ROUND(SUM(i.quantidade * i.preco_unitario), 2) AS vendas
  FROM pedidos AS p
  JOIN itens_pedido AS i ON i.pedido_id = p.id
  WHERE p.data LIKE '2025-%'
  GROUP BY mes
)
SELECT mes,
       vendas,
       ROUND(AVG(vendas) OVER (
         ORDER BY mes
         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ), 2) AS janela
FROM mensal
ORDER BY mes;

4. A moldura padrão e a surpresa dos empates

No total acumulado da seção 2 você não escreveu moldura nenhuma, e funcionou. É que, quando o OVER tem ORDER BY e nenhuma moldura, vale uma moldura padrão:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Parece igual ao acumulado com ROWS, mas tem uma palavra diferente: RANGE ("faixa"). Com RANGE, a "linha atual" não é uma linha só: é a linha atual e todas as que empatam com ela no ORDER BY. As linhas empatadas se chamam pares (peers). Enquanto o ORDER BY não tem empate (cada mês aparece uma vez), RANGE e ROWS dão o mesmo resultado. Com empate, não.

Veja os pedidos do primeiro trimestre de 2026, numerados com um contador acumulado por mês:

SELECT id,
       data,
       strftime('%Y-%m', data) AS mes,
       COUNT(*) OVER (ORDER BY strftime('%Y-%m', data)) AS acumulado
FROM pedidos
WHERE data BETWEEN '2026-01-01' AND '2026-03-31'
ORDER BY data;

A Ana esperava 1, 2, 3, 4, 5, 6, 7. Veio 2, 2, 5, 5, 5, 7, 7. Os dois pedidos de janeiro empatam no ORDER BY (o mês é o mesmo), então são pares: a moldura de cada um vai "do começo até o último par", e os dois enxergam os dois. Por isso ambos mostram 2. Os três de fevereiro mostram 5 (2 de janeiro + 3 de fevereiro).

Isso não é um defeito: é a regra do padrão SQL, e às vezes é exatamente o que você quer ("quantos pedidos até o fim deste mês"). O problema é quando você não sabia. Há dois consertos:

SELECT id,
       strftime('%Y-%m', data) AS mes,
       COUNT(*) OVER (
         ORDER BY strftime('%Y-%m', data)
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS por_linha,
       COUNT(*) OVER (ORDER BY strftime('%Y-%m', data), id) AS com_desempate
FROM pedidos
WHERE data BETWEEN '2026-01-01' AND '2026-03-31'
ORDER BY id;

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Moldura em linhas: até a linha atual, e só ela, sem os pares. O problema: entre dois pedidos do mesmo mês, quem vem primeiro é o SQLite que decide. Funciona, mas a ordem dentro do empate não é garantida.

ORDER BY strftime(…), id

Um desempate: dentro do mesmo mês, ordena pelo id. Sem empates, não há pares, e a moldura padrão anda uma linha por vez. É o conserto mais seguro: o resultado é o mesmo toda vez.

5. Uma janela com nome: WINDOW

Quando várias colunas usam a mesma janela, repetir OVER (ORDER BY mes ROWS …) em cada uma cansa. A cláusula WINDOW dá um nome à janela, e cada coluna só diz OVER nome:

WITH mensal AS (
  SELECT strftime('%Y-%m', p.data) AS mes,
         ROUND(SUM(i.quantidade * i.preco_unitario), 2) AS vendas
  FROM pedidos AS p
  JOIN itens_pedido AS i ON i.pedido_id = p.id
  WHERE p.data LIKE '2025-%'
  GROUP BY mes
)
SELECT mes,
       vendas,
       ROUND(SUM(vendas) OVER ate_aqui, 2) AS acumulado,
       COUNT(*) OVER ate_aqui AS meses,
       MAX(vendas) OVER ate_aqui AS melhor_ate_aqui
FROM mensal
WINDOW ate_aqui AS (ORDER BY mes ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
ORDER BY mes;

WINDOW ate_aqui AS ( … )

Define a janela uma vez, com nome. Vem depois do FROM/WHERE/GROUP BY/HAVING e antes do ORDER BY final.

OVER ate_aqui

Usa a janela pelo nome, sem parênteses.

MAX(vendas) OVER ate_aqui

Qualquer agregada vira janela: aqui, o melhor mês do ano até aquele ponto. Janeiro (865.78) segura o recorde o ano todo.

6. Onde a janela pode aparecer

A ordem em que o SQLite trabalha explica tudo: primeiro FROM e WHERE escolhem as linhas, depois GROUP BY/HAVING agrupam, depois as janelas são calculadas, e só então vêm o ORDER BY e o LIMIT finais. Por isso uma função de janela só pode aparecer na lista do SELECT e no ORDER BY. No WHERE ela ainda não existe.

Então como mostrar só os meses acima da média do ano? Calcule a janela numa CTE e filtre fora dela:

WITH mensal AS (
  SELECT strftime('%Y-%m', p.data) AS mes,
         ROUND(SUM(i.quantidade * i.preco_unitario), 2) AS vendas
  FROM pedidos AS p
  JOIN itens_pedido AS i ON i.pedido_id = p.id
  WHERE p.data LIKE '2025-%'
  GROUP BY mes
),
com_media AS (
  SELECT mes, vendas, ROUND(AVG(vendas) OVER (), 2) AS media
  FROM mensal
)
SELECT mes, vendas, media
FROM com_media
WHERE vendas > media;

Cinco meses de 2025 ficaram acima da média de R$ 455,38. A CTE com_media já tem a média pronta como coluna comum, e aí o WHERE funciona.

7. Formas diferentes de fazer a mesma coisa

Antes das funções de janela, o total acumulado se fazia com uma subconsulta correlacionada (aula Subconsultas): para cada mês, somar os meses até ele.

WITH mensal AS (
  SELECT strftime('%Y-%m', p.data) AS mes,
         ROUND(SUM(i.quantidade * i.preco_unitario), 2) AS vendas
  FROM pedidos AS p
  JOIN itens_pedido AS i ON i.pedido_id = p.id
  WHERE p.data LIKE '2025-%'
  GROUP BY mes
)
SELECT m.mes,
       m.vendas,
       (SELECT ROUND(SUM(m2.vendas), 2)
        FROM mensal AS m2
        WHERE m2.mes <= m.mes) AS acumulado
FROM mensal AS m
ORDER BY m.mes;

O resultado é igual ao da seção 2. Mas a subconsulta roda uma vez para cada linha, e relê as linhas anteriores toda vez: com 12 meses não se nota; com 100 mil pedidos, faz diferença. E a média móvel de 3 meses, com subconsulta, fica bem mais difícil de escrever.

Quando dá errado

SELECT nome, preco
FROM produtos
WHERE preco > AVG(preco) OVER ();

SELECT categoria_id, AVG(preco) OVER () AS media
FROM produtos
GROUP BY categoria_id
HAVING media > 50;

SELECT nome, AVG(preco) OVER
FROM produtos;

SELECT nome,
       SUM(preco) OVER (ORDER BY preco
                        ROWS BETWEEN CURRENT ROW AND 2 PRECEDING) AS soma
FROM produtos;

SELECT nome,
       SUM(preco) OVER (ORDER BY categoria_id, preco
                        RANGE BETWEEN 2 PRECEDING AND CURRENT ROW) AS soma
FROM produtos;

Referência rápida

Exercícios

Missão final: quanto falta para o mais caro

SELECT nome, preco
FROM produtos
WHERE categoria_id = 5;

SELECT nome,
       preco,
       ROUND(MAX(preco) OVER () - preco, 2) AS falta
FROM produtos
WHERE categoria_id = 5
ORDER BY falta;

O Jogo de Tabuleiro Trilha Mística é o mais caro, então a falta dele é 0.0. Repare que o WHERE deixou só os brinquedos, e o MAX da janela foi o deles, não o da loja inteira.

Resumo

• Uma função de janela é uma conta sobre um conjunto de linhas que devolve a resposta em cada linha, sem juntar nada. Qualquer agregada vira janela com OVER.
• OVER (): a janela é o resultado inteiro (depois do WHERE). Bom para média, total e porcentagem ao lado de cada linha.
• OVER (ORDER BY …): a janela vai do começo até a linha atual, o total acumulado.
• ROWS BETWEEN … AND … escolhe a moldura linha a linha: 2 PRECEDING AND CURRENT ROW é a média móvel de 3.
• A moldura padrão é RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: linhas empatadas no ORDER BY entram juntas. Desempate (…, id) ou use ROWS.
• Janelas só na lista do SELECT e no ORDER BY. Para filtrar por elas, CTE primeiro. E não esqueça os parênteses: OVER ().
• Próxima aula: numerar e fazer rankings com ROW_NUMBER, RANK e DENSE_RANK.