Se você já se sente confortável com SELECT, WHERE e JOIN, mas trava em perguntas como "encontre os 3 produtos principais de cada categoria" ou "qual é a variação percentual de receita de um dia para o outro", seja bem-vindo às funções de janela. Depois de JOIN, esta é a ferramenta que um analista mais usa no SQL.
Vou mostrar tudo sobre uma tabela de pedidos simples:
CREATE TABLE orders (
id INT PRIMARY KEY,
category VARCHAR(50),
product VARCHAR(100),
amount NUMERIC(10, 2),
created_at DATE
);
Funções de janela versus GROUP BY
A diferença essencial cabe em uma palavra: GROUP BY colapsa as linhas. As funções de janela não.
Com GROUP BY você perde o detalhe. Entra um milhão de pedidos, com GROUP BY category saem cinco linhas, uma por categoria. Ótimo para um relatório, mas você não consegue ver ao mesmo tempo o pedido individual e o ticket médio da sua categoria.
Com uma função de janela cada linha permanece no lugar e um valor calculado aparece ao seu lado:
SELECT
id,
category,
amount,
AVG(amount) OVER (PARTITION BY category) AS category_avg
FROM orders;
Leia em voz alta: "valor médio sobre a janela, particionada por categoria". Para cada pedido, a média da sua categoria aparece ao lado — nenhuma outra linha é descartada.
OVER(): três botões
Dentro de OVER() vivem três configurações. Para a maioria das consultas do dia a dia você só precisa das duas primeiras.
1. PARTITION BY — como fatiar
Sem PARTITION BY a tabela inteira é uma única janela. Com ele, cada valor distinto da coluna ganha o seu próprio grupo:
AVG(amount) OVER ()
AVG(amount) OVER (PARTITION BY category)
2. ORDER BY — em qual ordem
Use quando a posição da linha dentro da janela importa: "primeira", "segunda", "a vizinha". Sem ORDER BY, ROW_NUMBER não faz sentido. Com ele, você obtém uma numeração:
ROW_NUMBER() OVER (PARTITION BY category ORDER BY amount DESC)
3. Cláusula de frame — o terceiro botão opcional
O frame controla "quais linhas vizinhas entram na agregação". Por enquanto, lembre-se de apenas uma regra: no momento em que ORDER BY aparece dentro de OVER(), uma agregação como SUM muda para o modo cumulativo — somando desde o início da janela até a linha atual.
SUM(amount) OVER (PARTITION BY category ORDER BY created_at)
Esta é a armadilha mais comum para iniciantes: você adiciona ORDER BY "para ficar mais bonito" e a agregação silenciosamente vira um total acumulado. Tatue isso.
ROW_NUMBER, RANK, DENSE_RANK — três maneiras de numerar
Elas se diferenciam em como tratam os empates na coluna de ORDER BY.
| Função | Com os valores 10, 10, 20 produz |
|---|
ROW_NUMBER() | 1, 2, 3 — sempre único |
RANK() | 1, 1, 3 — mesmo rank, depois um salto |
DENSE_RANK() | 1, 1, 2 — mesmo rank, sem salto |
Estudo de caso: os 3 produtos principais por categoria
Esta é provavelmente a tarefa com funções de janela mais comum em entrevistas de SQL. Não dá para escrevê-la de forma limpa com GROUP BY. Com ROW_NUMBER resolve-se em uma única passagem:
WITH ranked AS (
SELECT
category,
product,
SUM(amount) AS total,
ROW_NUMBER() OVER (
PARTITION BY category
ORDER BY SUM(amount) DESC
) AS rn
FROM orders
GROUP BY category, product
)
SELECT category, product, total
FROM ranked
WHERE rn <= 3
ORDER BY category, rn;
Passo a passo:
- Agrupe por
(category, product) e some a receita de cada produto. Você obtém uma lista de produtos com a sua receita.
- Dentro de cada categoria, numere os produtos por receita decrescente. O líder recebe
rn = 1, o segundo colocado rn = 2, e assim por diante.
- A consulta externa mantém apenas as linhas onde
rn <= 3.
ROW_NUMBER garante um 1, 2, 3 estrito mesmo quando dois produtos empatam em receita. Se você quiser "todos os do top três" — incluindo os empates no segundo lugar — use RANK no lugar de ROW_NUMBER.
LAG e LEAD — espiar uma linha vizinha
LAG(col) retorna col da linha anterior na janela; LEAD(col), da seguinte. Se não houver vizinho, você obtém NULL por padrão.
Estudo de caso: crescimento de receita de um dia para o outro
Uma das três principais consultas que se espera que um analista saiba escrever em uma entrevista. Dado um fluxo de receita diária, retorne a variação percentual em relação ao dia anterior.
Passo 1. Agregue os pedidos em um total diário:
WITH daily AS (
SELECT
DATE_TRUNC('day', created_at)::DATE AS day,
SUM(amount) AS revenue
FROM orders
GROUP BY 1
)
SELECT * FROM daily ORDER BY day;
Passo 2. Adicione uma coluna com a receita de ontem:
SELECT
day,
revenue,
LAG(revenue) OVER (ORDER BY day) AS prev_revenue
FROM daily
ORDER BY day;
O prev_revenue da primeira linha é NULL — isso é esperado, o primeiro dia não tem vizinho à sua esquerda.
Passo 3. Calcule o percentual:
SELECT
day,
revenue,
ROUND(
100.0 * (revenue - LAG(revenue) OVER (ORDER BY day))
/ NULLIF(LAG(revenue) OVER (ORDER BY day), 0),
2
) AS dod_growth_pct
FROM daily
ORDER BY day;
A proteção NULLIF(..., 0) salva você de uma divisão por zero se não houve pedidos no dia anterior.
Soma cumulativa
Um total acumulado é o caso mais comum em que o frame importa. Com ORDER BY e sem frame explícito, uma agregação já é executada de forma cumulativa:
SELECT
day,
revenue,
SUM(revenue) OVER (ORDER BY day) AS cumulative_revenue
FROM daily
ORDER BY day;
Isso é "a receita desde o início da história até hoje". Quer o acumulado do mês corrente? Adicione PARTITION BY por mês:
SELECT
day,
revenue,
SUM(revenue) OVER (
PARTITION BY DATE_TRUNC('month', day)
ORDER BY day
) AS month_to_date
FROM daily
ORDER BY day;
No primeiro dia de cada mês o contador é reiniciado e começa a acumular de novo.
Média móvel de 7 dias
Para que o seu gráfico de receita pare de tremer nos fins de semana — um truque de suavização básico em painéis:
SELECT
day,
revenue,
AVG(revenue) OVER (
ORDER BY day
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS revenue_7d_avg
FROM daily
ORDER BY day;
O frame ROWS BETWEEN 6 PRECEDING AND CURRENT ROW significa "a linha atual mais as seis anteriores". Durante os seis primeiros dias a janela não está completa — a média é calculada sobre as linhas que existirem até aquele momento.
Para onde ir a partir daqui
As funções de janela desbloqueiam toda uma classe de consultas que sem elas exigem subconsultas aninhadas ou são simplesmente impossíveis: top-N por grupo, métricas de um dia para o outro, somas acumuladas, médias móveis, atributos de defasagem para ML. Se você escreve SQL para análise, esta é a sua ferramenta de todo dia.
A forma mais rápida de internalizar isso é resolver tarefas específicas sobre o tema. Há uma categoria dedicada às funções de janela no treinador, com explicações passo a passo.
Se você já se sente confortável com
SELECT,WHEREeJOIN, mas trava em perguntas como "encontre os 3 produtos principais de cada categoria" ou "qual é a variação percentual de receita de um dia para o outro", seja bem-vindo às funções de janela. Depois deJOIN, esta é a ferramenta que um analista mais usa no SQL.Vou mostrar tudo sobre uma tabela de pedidos simples:
CREATE TABLE orders ( id INT PRIMARY KEY, category VARCHAR(50), product VARCHAR(100), amount NUMERIC(10, 2), created_at DATE );Funções de janela versus GROUP BY
A diferença essencial cabe em uma palavra:
GROUP BYcolapsa as linhas. As funções de janela não.Com
GROUP BYvocê perde o detalhe. Entra um milhão de pedidos, comGROUP BY categorysaem cinco linhas, uma por categoria. Ótimo para um relatório, mas você não consegue ver ao mesmo tempo o pedido individual e o ticket médio da sua categoria.Com uma função de janela cada linha permanece no lugar e um valor calculado aparece ao seu lado:
SELECT id, category, amount, AVG(amount) OVER (PARTITION BY category) AS category_avg FROM orders;Leia em voz alta: "valor médio sobre a janela, particionada por categoria". Para cada pedido, a média da sua categoria aparece ao lado — nenhuma outra linha é descartada.
OVER(): três botões
Dentro de
OVER()vivem três configurações. Para a maioria das consultas do dia a dia você só precisa das duas primeiras.1. PARTITION BY — como fatiar
Sem
PARTITION BYa tabela inteira é uma única janela. Com ele, cada valor distinto da coluna ganha o seu próprio grupo:-- Média de toda a tabela AVG(amount) OVER () -- Média por categoria, separadamente AVG(amount) OVER (PARTITION BY category)2. ORDER BY — em qual ordem
Use quando a posição da linha dentro da janela importa: "primeira", "segunda", "a vizinha". Sem
ORDER BY,ROW_NUMBERnão faz sentido. Com ele, você obtém uma numeração:-- Pedidos dentro de uma categoria, ordenados por valor decrescente: 1, 2, 3, ... ROW_NUMBER() OVER (PARTITION BY category ORDER BY amount DESC)3. Cláusula de frame — o terceiro botão opcional
O frame controla "quais linhas vizinhas entram na agregação". Por enquanto, lembre-se de apenas uma regra: no momento em que
ORDER BYaparece dentro deOVER(), uma agregação comoSUMmuda para o modo cumulativo — somando desde o início da janela até a linha atual.-- Total acumulado desde o início de cada categoria SUM(amount) OVER (PARTITION BY category ORDER BY created_at)Esta é a armadilha mais comum para iniciantes: você adiciona
ORDER BY"para ficar mais bonito" e a agregação silenciosamente vira um total acumulado. Tatue isso.ROW_NUMBER, RANK, DENSE_RANK — três maneiras de numerar
Elas se diferenciam em como tratam os empates na coluna de
ORDER BY.ROW_NUMBER()RANK()DENSE_RANK()Estudo de caso: os 3 produtos principais por categoria
Esta é provavelmente a tarefa com funções de janela mais comum em entrevistas de SQL. Não dá para escrevê-la de forma limpa com
GROUP BY. ComROW_NUMBERresolve-se em uma única passagem:WITH ranked AS ( SELECT category, product, SUM(amount) AS total, ROW_NUMBER() OVER ( PARTITION BY category ORDER BY SUM(amount) DESC ) AS rn FROM orders GROUP BY category, product ) SELECT category, product, total FROM ranked WHERE rn <= 3 ORDER BY category, rn;Passo a passo:
(category, product)e some a receita de cada produto. Você obtém uma lista de produtos com a sua receita.rn = 1, o segundo colocadorn = 2, e assim por diante.rn <= 3.ROW_NUMBERgarante um 1, 2, 3 estrito mesmo quando dois produtos empatam em receita. Se você quiser "todos os do top três" — incluindo os empates no segundo lugar — useRANKno lugar deROW_NUMBER.LAG e LEAD — espiar uma linha vizinha
LAG(col)retornacolda linha anterior na janela;LEAD(col), da seguinte. Se não houver vizinho, você obtémNULLpor padrão.Estudo de caso: crescimento de receita de um dia para o outro
Uma das três principais consultas que se espera que um analista saiba escrever em uma entrevista. Dado um fluxo de receita diária, retorne a variação percentual em relação ao dia anterior.
Passo 1. Agregue os pedidos em um total diário:
WITH daily AS ( SELECT DATE_TRUNC('day', created_at)::DATE AS day, SUM(amount) AS revenue FROM orders GROUP BY 1 ) SELECT * FROM daily ORDER BY day;Passo 2. Adicione uma coluna com a receita de ontem:
SELECT day, revenue, LAG(revenue) OVER (ORDER BY day) AS prev_revenue FROM daily ORDER BY day;O
prev_revenueda primeira linha éNULL— isso é esperado, o primeiro dia não tem vizinho à sua esquerda.Passo 3. Calcule o percentual:
SELECT day, revenue, ROUND( 100.0 * (revenue - LAG(revenue) OVER (ORDER BY day)) / NULLIF(LAG(revenue) OVER (ORDER BY day), 0), 2 ) AS dod_growth_pct FROM daily ORDER BY day;A proteção
NULLIF(..., 0)salva você de uma divisão por zero se não houve pedidos no dia anterior.Soma cumulativa
Um total acumulado é o caso mais comum em que o frame importa. Com
ORDER BYe sem frame explícito, uma agregação já é executada de forma cumulativa:SELECT day, revenue, SUM(revenue) OVER (ORDER BY day) AS cumulative_revenue FROM daily ORDER BY day;Isso é "a receita desde o início da história até hoje". Quer o acumulado do mês corrente? Adicione
PARTITION BYpor mês:SELECT day, revenue, SUM(revenue) OVER ( PARTITION BY DATE_TRUNC('month', day) ORDER BY day ) AS month_to_date FROM daily ORDER BY day;No primeiro dia de cada mês o contador é reiniciado e começa a acumular de novo.
Média móvel de 7 dias
Para que o seu gráfico de receita pare de tremer nos fins de semana — um truque de suavização básico em painéis:
SELECT day, revenue, AVG(revenue) OVER ( ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ) AS revenue_7d_avg FROM daily ORDER BY day;O frame
ROWS BETWEEN 6 PRECEDING AND CURRENT ROWsignifica "a linha atual mais as seis anteriores". Durante os seis primeiros dias a janela não está completa — a média é calculada sobre as linhas que existirem até aquele momento.Para onde ir a partir daqui
As funções de janela desbloqueiam toda uma classe de consultas que sem elas exigem subconsultas aninhadas ou são simplesmente impossíveis: top-N por grupo, métricas de um dia para o outro, somas acumuladas, médias móveis, atributos de defasagem para ML. Se você escreve SQL para análise, esta é a sua ferramenta de todo dia.
A forma mais rápida de internalizar isso é resolver tarefas específicas sobre o tema. Há uma categoria dedicada às funções de janela no treinador, com explicações passo a passo.