SQL Funções de Janela

Uma função de janela faz uma conta olhando para outras linhas do resultado, sem juntar as linhas numa só como o GROUP BY faz: numerar os pedidos de cada cliente, dar a posição de cada produto no ranking de preços, pegar o frete do pedido anterior, somar o frete acumulado. Toda função de janela tem um OVER (…) depois dela. Esta página reúne todas as do SQLite 3.50; as explicações passo a passo estão em Funções de Janela (OVER), ROW_NUMBER, RANK e DENSE_RANK, LAG e LEAD e PARTITION BY.

A anatomia do OVER

função(…) OVER (
    PARTITION BY coluna      -- divide as linhas em grupos (opcional)
    ORDER BY coluna          -- a ordem dentro de cada grupo (opcional)
    ROWS BETWEEN … AND …     -- a "moldura": quais linhas entram na conta (opcional)
)

Numerar e classificar

Os três números lado a lado, nos fretes de seis pedidos. Os pedidos 4, 12 e 20 tiveram frete grátis (empate em 0.0):

SELECT id, frete,
       row_number() OVER w AS row_number,
       rank()       OVER w AS rank,
       dense_rank() OVER w AS dense_rank
FROM pedidos
WHERE id IN (1, 3, 4, 12, 20, 5)
WINDOW w AS (ORDER BY frete);

O row_number deu 1, 2, 3 para os três empatados; o rank deu 1, 1, 1 e pulou para 4; o dense_rank deu 1, 1, 1 e seguiu com 2. O WINDOW w AS (…) no fim dá um nome à janela, para não repetir o OVER (ORDER BY frete) três vezes.

E as outras três, nos 6 produtos de papelaria (categoria 1), do mais caro para o mais barato:

SELECT nome, preco,
       round(percent_rank() OVER w, 3) AS percent_rank,
       round(cume_dist() OVER w, 3)    AS cume_dist,
       ntile(3) OVER w                 AS terco
FROM produtos
WHERE categoria_id = 1
WINDOW w AS (ORDER BY preco DESC);

Pegar o valor de outra linha

SELECT id, data, frete,
       lag(frete)        OVER (ORDER BY id) AS anterior,
       lead(frete)       OVER (ORDER BY id) AS proximo,
       lag(frete, 2, 0)  OVER (ORDER BY id) AS dois_antes
FROM pedidos
WHERE id <= 5;

A armadilha do last_value: com só ORDER BY, a moldura vai do começo até a linha atual, então o "último" é sempre a própria linha (coluna ultimo_padrao). Para o último da partição inteira, estique a moldura até o fim (UNBOUNDED FOLLOWING):

SELECT nome, preco,
       first_value(nome) OVER w AS mais_barato,
       last_value(nome)  OVER w AS ultimo_padrao,
       last_value(nome)  OVER (ORDER BY preco
                               ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS mais_caro,
       nth_value(nome, 2) OVER w AS segundo
FROM produtos
WHERE categoria_id = 1
WINDOW w AS (ORDER BY preco);

Agregadas como janela

Qualquer função agregada (sum, avg, count, min, max, total, group_concat…) vira função de janela quando ganha um OVER. Ela continua calculando sobre várias linhas, mas devolve o resultado em cada linha.

SELECT id, frete,
       sum(frete) OVER (ORDER BY id) AS acumulado,
       round(avg(frete) OVER (ORDER BY id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS media_3,
       count(*) OVER () AS total
FROM pedidos
WHERE id <= 5;

A porcentagem de cada salário na folha da loja:

SELECT nome, salario,
       round(salario * 100.0 / sum(salario) OVER (), 1) AS pct
FROM funcionarios;

A moldura (frame)

A moldura diz quais linhas, em volta da atual, entram na conta. Ela vale para as agregadas e para first_value, last_value e nth_value; as funções de ranking e o lag/lead a ignoram.

A diferença entre RANGE (o padrão) e ROWS só aparece nos empates. Os pedidos 7 e 15 têm o mesmo frete (7.25), e os pedidos 1 e 9 também (9.9). Com RANGE, os empatados entram juntos no acumulado (os dois de 7,25 já mostram 14.5); com ROWS, entram um de cada vez (7.25 e depois 14.5). A coluna grupos mostra, com GROUPS, quais pedidos entram na moldura "o grupo de empatados anterior e o atual":

SELECT id, frete,
       sum(frete) OVER (ORDER BY frete) AS range_padrao,
       sum(frete) OVER (ORDER BY frete ROWS UNBOUNDED PRECEDING) AS rows_ac,
       group_concat(id) OVER (ORDER BY frete GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW) AS grupos
FROM pedidos
WHERE id IN (4, 7, 15, 1, 9);

O padrão "o primeiro de cada grupo"

Funções de janela não podem ir no WHERE (elas são calculadas depois dele). Para filtrar pelo número, calcule numa subconsulta e filtre por fora. O produto mais caro de cada categoria:

SELECT nome, categoria_id, preco
FROM (
    SELECT nome, categoria_id, preco,
           row_number() OVER (PARTITION BY categoria_id ORDER BY preco DESC) AS n
    FROM produtos
)
WHERE n = 1;

Troque n = 1 por n <= 3 para os 3 mais caros de cada categoria. O mesmo dá para escrever com uma CTE (WITH).

Quando dá errado

SELECT nome
FROM produtos
WHERE rank() OVER (ORDER BY preco) = 1;

Nos outros bancos

Experimente

SELECT cliente_id, id, data
FROM pedidos
ORDER BY cliente_id, data;

SELECT cliente_id, id, data,
       row_number() OVER (PARTITION BY cliente_id ORDER BY data) AS n
FROM pedidos;

Resumo

• Função de janela = função + OVER (PARTITION BY … ORDER BY … moldura). Ela calcula olhando outras linhas, mas não junta as linhas.
• Numerar e classificar: row_number, rank (pula depois do empate), dense_rank (não pula), percent_rank, cume_dist, ntile(n).
• Outra linha: lag, lead, first_value, last_value (cuidado com a moldura padrão), nth_value.
• Qualquer agregada com OVER: total acumulado, média móvel, porcentagem do total.
• Moldura: ROWS (linhas), RANGE (valores), GROUPS (grupos de empatados), com EXCLUDE opcional.
• Não vão no WHERE: filtre numa subconsulta.