SQL PARTITION BY

Nas últimas três aulas, a janela era sempre o resultado inteiro: a média de todos os produtos, o ranking de todos, o pedido anterior da loja. Para comparar um produto só com os da categoria dele, ou um pedido só com os do mesmo cliente, a Ana precisou de um WHERE que deixava um grupo de cada vez. Com PARTITION BY ("dividir por"), a janela se divide em grupos sozinha e recomeça em cada um: a média de cada categoria, o ranking dentro de cada categoria, o intervalo entre os pedidos de cada cliente, tudo numa consulta só.

Esta aula fecha a série das funções de janela: Funções de Janela (OVER), ROW_NUMBER, RANK e DENSE_RANK e LAG e LEAD. Todos os exemplos rodam de verdade no seu navegador, no banco da Loja Aurora.

A ideia: as turmas da escola

Uma escola tem três turmas do 6º ano. No boletim de cada aluno, a coluna "média" é a média da turma dele, e não da escola inteira. E o "1º lugar" é o 1º da turma: cada turma tem o seu. A secretaria não junta os alunos nem os separa em planilhas diferentes; ela só faz cada conta dentro da turma.

1. A média da categoria ao lado de cada produto

Os produtos de Livros e de Papelaria, cada um com a média de preço da sua categoria:

SELECT c.nome AS categoria,
       p.nome,
       p.preco,
       ROUND(AVG(p.preco) OVER (PARTITION BY p.categoria_id), 2) AS media_categoria
FROM produtos AS p
JOIN categorias AS c ON c.id = p.categoria_id
WHERE p.categoria_id IN (1, 4)
ORDER BY c.nome, p.preco DESC;

AVG(p.preco) OVER (…)

Uma média como função de janela, como na aula Funções de Janela.

PARTITION BY p.categoria_id

"Divida por categoria". Em vez de uma janela com todas as linhas, cada linha enxerga só as linhas da mesma categoria que ela. Cada categoria é uma partição.

WHERE p.categoria_id IN (1, 4)

Só para o quadro ficar curto: duas categorias, duas partições. Tire o WHERE e as 8 categorias ganham, cada uma, a sua média.

Os 5 livros mostram 42.06, a média dos livros; os 6 itens de papelaria mostram 21.78, a média da papelaria. Nenhuma linha sumiu.

GROUP BY × PARTITION BY

Compare com o GROUP BY da aula GROUP BY, que calcula as mesmas médias:

SELECT c.nome AS categoria,
       ROUND(AVG(p.preco), 2) AS media_categoria
FROM produtos AS p
JOIN categorias AS c ON c.id = p.categoria_id
WHERE p.categoria_id IN (1, 4)
GROUP BY p.categoria_id
ORDER BY c.nome;

Comparar cada um com o seu grupo

Com a média da categoria na linha, dá para medir "caro para a categoria dele". A Mochila Escolar Azul custa R$ 89,99: barata para um eletrônico, caríssima para a papelaria.

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

2. PARTITION BY + ORDER BY: o ranking dentro de cada grupo

Dentro do OVER, o PARTITION BY vem primeiro e o ORDER BY depois. A numeração (ou o acumulado) anda na ordem pedida e volta a 1 em cada partição:

SELECT categoria_id,
       nome,
       preco,
       ROW_NUMBER() OVER (PARTITION BY categoria_id ORDER BY preco DESC) AS posicao
FROM produtos
WHERE categoria_id IN (1, 4, 8)
ORDER BY categoria_id, posicao;

PARTITION BY categoria_id

Uma numeração separada para cada categoria.

ORDER BY preco DESC

Dentro de cada categoria, do mais caro para o mais barato.

ROW_NUMBER()

1, 2, 3… e de novo 1 quando a categoria muda (aula ROW_NUMBER, RANK e DENSE_RANK).

ORDER BY categoria_id, posicao (no fim)

Só a arrumação do resultado: categoria por categoria, em ordem de posição.

A papelaria vai de 1 a 6, os livros recomeçam em 1 e vão até 5, a jardinagem também de 1 a 5. A Mochila, o Atlas Geográfico e a Tesoura de Poda são os mais caros das suas categorias.

3. O melhor de cada grupo

Este é o uso mais famoso do PARTITION BY: ficar com uma linha por grupo, mas a linha inteira (e não só um MAX). Numere dentro de cada grupo numa CTE e fique com o número 1.

O último pedido de cada cliente

WITH numerados AS (
  SELECT cliente_id, id, data,
         ROW_NUMBER() OVER (PARTITION BY cliente_id ORDER BY data DESC) AS n
  FROM pedidos
)
SELECT c.nome, u.id AS pedido, u.data
FROM numerados AS u
JOIN clientes AS c ON c.id = u.cliente_id
WHERE u.n = 1
ORDER BY u.data
LIMIT 5;

PARTITION BY cliente_id ORDER BY data DESC

Para cada cliente, os pedidos do mais recente para o mais antigo. O mais recente ganha 1.

WHERE u.n = 1

Fica só o mais recente de cada cliente. O filtro está fora da CTE porque uma janela não pode ir no WHERE.

ORDER BY u.data LIMIT 5

Os 5 clientes que estão há mais tempo sem comprar: o último pedido do Tiago Fontes foi em 8 de janeiro de 2026.

Sem o LIMIT, saem 26 linhas: um último pedido para cada um dos 26 clientes que já compraram.

WITH numerados AS (
  SELECT cliente_id, id, data,
         ROW_NUMBER() OVER (ORDER BY data DESC) AS n
  FROM pedidos
)
SELECT c.nome, u.id AS pedido, u.data
FROM numerados AS u
JOIN clientes AS c ON c.id = u.cliente_id
WHERE u.n = 1
ORDER BY c.nome;

WITH numerados AS (
  SELECT cliente_id, id, data,
         ROW_NUMBER() OVER (PARTITION BY cliente_id ORDER BY data DESC) AS n
  FROM pedidos
)
SELECT c.nome, u.id AS pedido, u.data
FROM numerados AS u
JOIN clientes AS c ON c.id = u.cliente_id
WHERE u.n = 1
ORDER BY c.nome;

4. LAG por cliente: o intervalo entre pedidos de todos de uma vez

Na aula LAG e LEAD, a Ana calculou os dias entre os pedidos de um cliente, com WHERE cliente_id = 1. Com PARTITION BY cliente_id, o "pedido anterior" passa a ser o anterior do mesmo cliente, para todos ao mesmo tempo:

SELECT cliente_id,
       id,
       data,
       LAG(data) OVER (PARTITION BY cliente_id ORDER BY data) AS anterior,
       CAST(julianday(data)
            - julianday(LAG(data) OVER (PARTITION BY cliente_id ORDER BY data)) AS INTEGER) AS dias
FROM pedidos
WHERE cliente_id IN (1, 2, 13)
ORDER BY cliente_id, data;

Cada cliente começa com NULL (o primeiro pedido dele não tem anterior), e a conta nunca mistura um cliente com outro. O WHERE deixou só três clientes para o quadro ficar curto; a mesma janela, sem o WHERE, calcula os intervalos dos 26. Resumindo numa CTE:

WITH intervalos AS (
  SELECT cliente_id,
         julianday(data)
         - julianday(LAG(data) OVER (PARTITION BY cliente_id ORDER BY data)) AS dias
  FROM pedidos
)
SELECT COUNT(dias) AS intervalos,
       ROUND(AVG(dias), 1) AS media_de_dias,
       MIN(dias) AS menor,
       MAX(dias) AS maior
FROM intervalos;

64 intervalos (90 pedidos menos o primeiro de cada um dos 26 clientes), com média de 302,1 dias: os clientes da Aurora voltam, em média, uma vez a cada dez meses.

SELECT cliente_id,
       id,
       data,
       CAST(julianday(data)
            - julianday(LAG(data) OVER (PARTITION BY cliente_id ORDER BY data)) AS INTEGER) AS dias
FROM pedidos
ORDER BY cliente_id, data;

5. Particionar por uma expressão: o acumulado do ano

A partição não precisa ser uma coluna pronta: pode ser uma conta. A Ana quer o total vendido acumulado dentro de cada ano, recomeçando em janeiro. O ano são os 4 primeiros caracteres do mês (substr(mes, 1, 4), aula Funções de Texto):

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
  GROUP BY mes
),
acumulado AS (
  SELECT mes,
         vendas,
         ROUND(SUM(vendas) OVER (PARTITION BY substr(mes, 1, 4) ORDER BY mes), 2) AS acumulado_do_ano
  FROM mensal
)
SELECT mes, vendas, acumulado_do_ano
FROM acumulado
WHERE mes LIKE '%-12' OR mes = '2026-10';

PARTITION BY substr(mes, 1, 4)

Uma partição por ano: '2024', '2025', '2026'.

ORDER BY mes

Dentro de cada ano, o acumulado vai de janeiro em diante e recomeça no ano seguinte. Os meses não se repetem, então a moldura padrão não traz surpresa de empate (aula Funções de Janela, seção 4).

WHERE mes LIKE '%-12' OR …

Mostra só o fechamento de cada ano (dezembro, e outubro em 2026, o último mês com pedidos). O filtro está fora da CTE de propósito; você vai ver por quê em Quando dá errado.

2024 fechou com R$ 8.280,80 em produtos, 2025 com R$ 5.464,52 (o mesmo número do acumulado da aula de janelas), e 2026 está em R$ 4.792,08 até outubro.

Mais de uma coluna

PARTITION BY aceita várias colunas separadas por vírgula: a partição é cada combinação. Os eletrônicos e brinquedos, contando quantos produtos há em cada par (categoria, descontinuado):

SELECT categoria_id,
       descontinuado,
       nome,
       COUNT(*) OVER (PARTITION BY categoria_id, descontinuado) AS no_grupo
FROM produtos
WHERE categoria_id IN (2, 5)
ORDER BY categoria_id, descontinuado, nome;

Os eletrônicos se dividem em 6 ativos e 1 descontinuado (o Pen Drive 32GB); os brinquedos, em 4 ativos e 1 descontinuado (o Carrinho de Fricção).

6. Formas diferentes de fazer a mesma coisa

A média da categoria ao lado de cada produto (seção 1) também sai de dois jeitos sem janela:

a) GROUP BY numa CTE e JOIN de volta

WITH medias AS (
  SELECT categoria_id, ROUND(AVG(preco), 2) AS media_categoria
  FROM produtos
  GROUP BY categoria_id
)
SELECT p.categoria_id, p.nome, p.preco, m.media_categoria
FROM produtos AS p
JOIN medias AS m ON m.categoria_id = p.categoria_id
WHERE p.categoria_id = 4
ORDER BY p.preco DESC;

b) Subconsulta correlacionada

SELECT p.categoria_id,
       p.nome,
       p.preco,
       (SELECT ROUND(AVG(p2.preco), 2)
        FROM produtos AS p2
        WHERE p2.categoria_id = p.categoria_id) AS media_categoria
FROM produtos AS p
WHERE p.categoria_id = 4
ORDER BY p.preco DESC;

Quando dá errado

SELECT cliente_id,
       id,
       data,
       CAST(julianday(data)
            - julianday(LAG(data) OVER (ORDER BY cliente_id, data)) AS INTEGER) AS dias
FROM pedidos
WHERE cliente_id IN (1, 2)
ORDER BY cliente_id, data;

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
  GROUP BY mes
)
SELECT mes,
       vendas,
       ROUND(SUM(vendas) OVER (PARTITION BY substr(mes, 1, 4) ORDER BY mes), 2) AS acumulado_do_ano
FROM mensal
WHERE mes BETWEEN '2024-11' AND '2025-02'
ORDER BY mes;

SELECT nome,
       preco,
       ROW_NUMBER() OVER (PARTITION BY id ORDER BY preco DESC) AS posicao
FROM produtos
LIMIT 4;

SELECT categoria_id AS cat,
       nome,
       ROW_NUMBER() OVER (PARTITION BY cat ORDER BY preco DESC) AS posicao
FROM produtos;

SELECT nome,
       ROW_NUMBER() OVER (ORDER BY preco DESC PARTITION BY categoria_id) AS posicao
FROM produtos;

SELECT nome, AVG(preco) OVER (PARTITION categoria_id) AS media
FROM produtos;

Referência rápida

Exercícios

Missão final: o campeão de cada categoria

WITH vendas AS (
  SELECT pr.categoria_id, pr.nome,
         ROUND(SUM(i.quantidade * i.preco_unitario), 2) AS vendido
  FROM itens_pedido AS i
  JOIN produtos AS pr ON pr.id = i.produto_id
  GROUP BY pr.id
),
ranking AS (
  SELECT categoria_id, nome, vendido,
         RANK() OVER (ORDER BY vendido DESC) AS posicao
  FROM vendas
)
SELECT c.nome AS categoria, r.nome AS produto, r.vendido
FROM ranking AS r
JOIN categorias AS c ON c.id = r.categoria_id
WHERE r.posicao = 1
ORDER BY r.vendido DESC;

WITH vendas AS (
  SELECT pr.categoria_id, pr.nome,
         ROUND(SUM(i.quantidade * i.preco_unitario), 2) AS vendido
  FROM itens_pedido AS i
  JOIN produtos AS pr ON pr.id = i.produto_id
  GROUP BY pr.id
),
ranking AS (
  SELECT categoria_id, nome, vendido,
         RANK() OVER (PARTITION BY categoria_id ORDER BY vendido DESC) AS posicao
  FROM vendas
)
SELECT c.nome AS categoria, r.nome AS produto, r.vendido
FROM ranking AS r
JOIN categorias AS c ON c.id = r.categoria_id
WHERE r.posicao = 1
ORDER BY r.vendido DESC;

Do Jogo de Panelas (Casa e Cozinha, R$ 1.999,20) ao Regador Plástico 5L (Jardim, R$ 158,70): cada categoria tem o seu campeão. Troque vendido por unidades (SUM(i.quantidade)) e o RANK mostra empates: em Alimentos, Brinquedos e Livros, dois ou mais produtos dividem o 1º lugar.

Resumo

• PARTITION BY divide a janela em grupos (partições), e cada função de janela é calculada dentro do grupo de cada linha. Nenhuma linha some.
• GROUP BY resume (uma linha por grupo); PARTITION BY compara (cada linha com o seu grupo).
• Dentro do OVER: PARTITION BY, depois ORDER BY, depois a moldura. Com ORDER BY, a numeração, o ranking e o acumulado recomeçam em cada partição.
• "O melhor de cada grupo": ROW_NUMBER() ou RANK() com PARTITION BY numa CTE, e WHERE posicao = 1 fora.
• LAG e LEAD com PARTITION BY nunca cruzam de um grupo para outro; sem ele, o primeiro pedido de um cliente "herda" a data de outro.
• A partição pode ser uma expressão (substr(mes, 1, 4)) ou várias colunas. O WHERE da mesma consulta corta linhas antes da janela; para filtrar o resultado, use uma CTE.
• Próxima aula: guardar e consultar dados em JSON no SQL.