Janelas avançadas: LAG, LEAD, NTILE e quadros — SQL avançado: janelas, views e CASE

Olhar para os lados: LAG e LEAD

Às vezes uma linha precisa do valor de outra linha relacionada: a do dia anterior para calcular o crescimento, a seguinte para comparar, etc. Isso é justamente o que fazem LAG e LEAD sobre uma janela ordenada:

LAG(col) → o valor de col na linha anterior (segundo o ORDER BY).
LEAD(col) → o valor de col na linha seguinte.

SELECT id, valor,
  LAG(valor)  OVER (ORDER BY id) AS valor_anterior,
  LEAD(valor) OVER (ORDER BY id) AS valor_seguinte
FROM vendas;

Na primeira linha não há anterior e na última não há seguinte: aí o resultado é NULL. Você pode passar um segundo e terceiro argumento para pular mais linhas ou dar um valor padrão: LAG(valor, 1, 0) retorna 0 em vez de NULL na primeira linha.

Uma subtração clássica: valor - LAG(valor) OVER (ORDER BY id) te dá a variação em relação à linha anterior.

Repartir em grupos: NTILE(n)

NTILE(n) reparte as linhas (na ordem da janela) em n grupos o mais iguais possível e retorna a qual grupo (1..n) cada linha pertence. É ideal para quartis (n = 4), decis, percentis aproximados, etc.

SELECT nome, preco,
  NTILE(3) OVER (ORDER BY preco) AS terco
FROM produtos;

Se as linhas não se dividem de forma exata, os primeiros grupos recebem uma linha a mais.

Quadros de janela: ROWS BETWEEN

Por padrão, um agregado com ORDER BY dentro do OVER acumula desde a primeira linha até a atual. Mas você pode definir o quadro (a faixa de linhas que entra no cálculo) com ROWS BETWEEN ... AND ...:

SELECT id, valor,
  AVG(valor) OVER (
    ORDER BY id
    ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
  ) AS media_movel
FROM vendas;

N PRECEDING → N linhas antes da atual.
CURRENT ROW → a linha atual.
N FOLLOWING → N linhas depois.
UNBOUNDED PRECEDING / UNBOUNDED FOLLOWING → até o início / o fim.

Assim, ROWS BETWEEN 2 PRECEDING AND CURRENT ROW calcula sobre a linha atual e as duas anteriores: a base das médias e somas móveis.

O acumulado clássico (SUM(...) OVER (ORDER BY id)) é na verdade o quadro ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW escrito de forma implícita.

Exemplos

Comparar cada venda com a anterior (LAG)

SELECT id, valor,
  LAG(valor) OVER (ORDER BY id) AS anterior,
  valor - LAG(valor) OVER (ORDER BY id) AS variacao
FROM vendas
ORDER BY id;

Média móvel de 2 linhas com um quadro de janela

SELECT id, valor,
  AVG(valor) OVER (
    ORDER BY id
    ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
  ) AS media_movel
FROM vendas
ORDER BY id;