Window Functions no SQLite: OVER e PARTITION BY

Window function: agrega uma coluna sem colapsar as linhas

O GROUP BY reduz várias linhas em uma só. Já uma window function faz algo diferente: ela calcula um valor sobre um conjunto de linhas relacionadas, mas mantém cada linha original no resultado. Você fica com o detalhe linha a linha e o valor agregado, lado a lado.

A estrutura é sempre a mesma: uma função, seguida de OVER (...).

CREATE TABLE sales (
    employee TEXT,
    region   TEXT,
    amount   INTEGER
);

INSERT INTO sales VALUES
    ('Ada',   'EU', 100),
    ('Boris', 'EU', 250),
    ('Cleo',  'EU', 175),
    ('Dan',   'US', 300),
    ('Eve',   'US', 150);

SELECT employee, region, amount,
       SUM(amount) OVER () AS total_all
FROM sales;

A coluna total_all mostra o total geral de todas as linhas, repetido em cada uma. As linhas originais continuam intactas. Compare com SELECT SUM(amount) FROM sales — o número é o mesmo, mas a consulta devolve apenas uma linha. As window functions do SQLite te dão as duas visões ao mesmo tempo.

PARTITION BY: agregando dentro de grupos

Um OVER () vazio agrega sobre a tabela inteira. Adicionando PARTITION BY, você passa a agregar dentro de grupos, parecido com o GROUP BY — só que, de novo, sem colapsar as linhas.

CREATE TABLE sales (employee TEXT, region TEXT, amount INTEGER);
INSERT INTO sales VALUES
    ('Ada','EU',100),('Boris','EU',250),('Cleo','EU',175),
    ('Dan','US',300),('Eve','US',150);

SELECT employee, region, amount,
       SUM(amount) OVER (PARTITION BY region) AS region_total,
       amount * 100.0 / SUM(amount) OVER (PARTITION BY region) AS pct_of_region
FROM sales;

Cada linha recebe o total da sua região e a fatia que representa nesse total. Com um GROUP BY tradicional, você perderia o detalhe por funcionário. Esse é o grande trunfo das window functions: detalhe e agregado na mesma query.

Ranking no SQLite: ROW_NUMBER, RANK e DENSE_RANK

A família de funções de ranking numera as linhas seguindo o ORDER BY que você define dentro do OVER. As três variações se diferenciam pela forma como lidam com empates.

CREATE TABLE scores (player TEXT, points INTEGER);
INSERT INTO scores VALUES
    ('Ada', 90), ('Boris', 85), ('Cleo', 90),
    ('Dan', 70), ('Eve', 85);

SELECT player, points,
       ROW_NUMBER() OVER (ORDER BY points DESC) AS rn,
       RANK()       OVER (ORDER BY points DESC) AS rk,
       DENSE_RANK() OVER (ORDER BY points DESC) AS dr
FROM scores;

Lendo o resultado:

• ROW_NUMBER() é sempre único — empates são desfeitos de forma arbitrária. Use quando precisar de um número estável e distinto para cada linha.
• RANK() atribui o mesmo rank a linhas empatadas e depois pula os números seguintes. Se dois jogadores empatam em 1, o próximo vem como rank 3.
• DENSE_RANK() também trata empates, mas não pula. O próximo rank é 2.

Para fazer "top N por grupo", combine a função de ranking com PARTITION BY e filtre numa query externa — o WHERE não consegue referenciar window functions diretamente:

CREATE TABLE sales (employee TEXT, region TEXT, amount INTEGER);
INSERT INTO sales VALUES
    ('Ada','EU',100),('Boris','EU',250),('Cleo','EU',175),
    ('Dan','US',300),('Eve','US',150),('Finn','US',220);

SELECT *
FROM (
    SELECT employee, region, amount,
           ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn
    FROM sales
)
WHERE rn <= 2;

Os dois maiores faturamentos por região.

LAG e LEAD: olhando para as linhas vizinhas

LAG(col) devolve o valor de col da linha anterior dentro da window. Já o LEAD(col) faz o caminho contrário e olha para a linha seguinte. Os dois caem como uma luva quando você precisa comparar valores ao longo do tempo.

CREATE TABLE temps (day INTEGER, celsius INTEGER);
INSERT INTO temps VALUES (1, 18), (2, 21), (3, 19), (4, 24), (5, 22);

SELECT day, celsius,
       LAG(celsius)  OVER (ORDER BY day) AS yesterday,
       celsius - LAG(celsius) OVER (ORDER BY day) AS delta
FROM temps;

A primeira linha tem yesterday como NULL — afinal, não existe nada antes dela. Dá pra definir um valor padrão: LAG(celsius, 1, celsius) OVER (ORDER BY day) usa o valor de hoje quando não há linha anterior.

LEAD é o espelho do LAG. Juntando os dois com PARTITION BY, você consegue sequências por grupo — por exemplo, comparar as vendas deste mês com as do mês anterior dentro de cada região.

Total acumulado com window frames no SQLite

Coloque um ORDER BY dentro do OVER e funções de agregação como SUM, AVG e COUNT passam a calcular de forma cumulativa:

CREATE TABLE deposits (day INTEGER, amount INTEGER);
INSERT INTO deposits VALUES (1, 50), (2, 30), (3, 70), (4, 20), (5, 40);

SELECT day, amount,
       SUM(amount) OVER (ORDER BY day) AS running_total,
       AVG(amount) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS avg_3day
FROM deposits;

Dois detalhes importantes para reparar:

• SUM(amount) OVER (ORDER BY day) calcula um total acumulado. Quando você escreve ORDER BY sem definir um frame explícito, o padrão é "do início da janela até a linha atual".
• Já a segunda coluna usa um frame explícito: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW. Isso é uma janela deslizante de 3 linhas — ou seja, uma média móvel.

O modelo mental para entender frames é o seguinte: toda window function é avaliada sobre um frame de linhas, definido em relação à linha atual. Os frames mais comuns são:

• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — total acumulado (é o padrão implícito).
• ROWS BETWEEN N PRECEDING AND CURRENT ROW — janela retroativa (trailing).
• ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING — a partição inteira.

ROWS conta linhas físicas. Existe também o RANGE, que agrupa por valor — útil quando há empates na coluna do ORDER BY e você quer que esses empates sejam tratados como um único passo.

FIRST_VALUE, LAST_VALUE e NTILE

Vale conhecer mais algumas window functions:

CREATE TABLE sales (employee TEXT, region TEXT, amount INTEGER);
INSERT INTO sales VALUES
    ('Ada','EU',100),('Boris','EU',250),('Cleo','EU',175),
    ('Dan','US',300),('Eve','US',150),('Finn','US',220);

SELECT employee, region, amount,
       FIRST_VALUE(employee) OVER (PARTITION BY region ORDER BY amount DESC) AS top_earner,
       NTILE(2) OVER (ORDER BY amount DESC) AS half
FROM sales;

• FIRST_VALUE e LAST_VALUE retornam o primeiro ou o último valor dentro do frame. No caso do LAST_VALUE, fique de olho no frame: por padrão ele termina em CURRENT ROW, então normalmente você vai querer usar ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING para pegar de fato o último valor da partição.
• NTILE(n) divide as linhas em n grupos de tamanho aproximadamente igual — ótimo para quartis, percentis e divisões no estilo teste A/B.

Dando nome a uma janela com WINDOW

Quando várias colunas usam a mesma cláusula OVER (...), fica chato repetir tudo. O SQLite permite nomear uma janela uma única vez e reaproveitá-la:

CREATE TABLE sales (employee TEXT, region TEXT, amount INTEGER);
INSERT INTO sales VALUES
    ('Ada','EU',100),('Boris','EU',250),('Cleo','EU',175),
    ('Dan','US',300),('Eve','US',150);

SELECT employee, region, amount,
       SUM(amount)   OVER region_window AS region_total,
       AVG(amount)   OVER region_window AS region_avg,
       RANK()        OVER region_window AS region_rank
FROM sales
WINDOW region_window AS (PARTITION BY region ORDER BY amount DESC);

Mesma consulta, com bem menos ruído. A cláusula WINDOW entra depois de WHERE/GROUP BY/HAVING e antes do ORDER BY.

Window function vs GROUP BY no SQLite

As duas envolvem agregação, mas resolvem problemas diferentes:

• O GROUP BY reduz: gera uma linha por grupo. Use quando você só quer o resumo.
• As window functions preservam: cada linha original continua ali, agora com colunas calculadas a mais ao lado.

Se você se pegar fazendo um GROUP BY e depois um join dos agregados de volta na tabela original, isso é um sinal claro de que uma window function resolveria tudo numa consulta só.

Algumas pegadinhas

• O WHERE não enxerga window functions. Os filtros são aplicados antes do cálculo das janelas. A saída é envolver a consulta numa subquery ou CTE e filtrar no nível de fora.
• Frames implícitos pegam todo mundo de surpresa. SUM(x) OVER (ORDER BY y) vira um total acumulado porque o frame padrão é RANGE UNBOUNDED PRECEDING. Se você quer a soma da partição inteira, escreva OVER (PARTITION BY ...) sem ORDER BY, ou defina o frame de forma explícita.
• O LAST_VALUE confunde geral na primeira vez. Como o frame padrão termina na linha atual, ele devolve o valor da linha atual — e não o último da partição. Tem que sobrescrever o frame.
• Window functions exigem SQLite 3.25+ (lançado em 2018). Qualquer instalação minimamente atual já tem suporte, mas alguns ambientes embarcados ficam para trás.

A seguir: colunas geradas

Window functions são computação na hora da consulta. A próxima página fala de computação na hora do armazenamento: as generated columns, em que o valor da coluna é definido por uma expressão e atualizado automaticamente conforme os dados mudam.