Lição 7.3 · Tempo de leitura: ~8 min

Uma moldura de janela decide que linhas à volta da atual uma função de janela de agregação usa realmente: todas as linhas desde o início, as três últimas linhas ou os últimos sete dias. Nesta lição, você vai aprender a sintaxe das molduras ROWS e RANGE, construir totais acumulados e médias móveis, ver por que "as últimas 3 linhas" e "os últimos 3 dias" dão respostas diferentes em dados reais, e evitar a armadilha da moldura padrão.

Molduras de Janela em SQL: ROWS vs RANGE

O PARTITION BY divide as linhas em grupos e o ORDER BY ordena-as. A moldura é a terceira parte do OVER: dentro da partição atual, seleciona um intervalo deslizante de linhas relativo à linha atual. As funções de agregação (SUM, AVG, COUNT, MIN, MAX) e de valor (FIRST_VALUE, LAST_VALUE) respeitam a moldura; as funções de ranking e LAG/LEAD ignoram-na.

Os exemplos são executados no banco de exemplo Sakila (MySQL 8.0).

Sintaxe da Moldura

function_name(expression) OVER (
    PARTITION BY partition_columns
    ORDER BY sort_column
    { ROWS | RANGE } BETWEEN frame_start AND frame_end
)

• ROWS conta linhas físicas: 2 PRECEDING são as duas linhas acima da atual, sejam quais forem os seus valores.
• RANGE trabalha com os valores da coluna do ORDER BY: INTERVAL 2 DAY PRECEDING são todas as linhas cuja data é no máximo dois dias anterior. Uma moldura RANGE com deslocamento exige exatamente uma coluna numérica ou de data no ORDER BY.

Os Dados: Receita Diária

Todos os exemplos usam a receita diária de 26 de julho a 23 de agosto de 2005. Repare na lacuna: depois de 2 de agosto, o dia seguinte com pagamentos é 16 de agosto.

WITH daily AS (
    SELECT DATE(payment_date) AS pay_day, SUM(amount) AS revenue
    FROM payment
    WHERE payment_date >= '2005-07-26'
      AND payment_date < '2005-08-24'
    GROUP BY DATE(payment_date)
)
SELECT pay_day, revenue
FROM daily
ORDER BY pay_day;

Resultado: 16 dias: de 26 de julho a 2 de agosto e de 16 a 23 de agosto.

Total Acumulado e Total Restante

UNBOUNDED PRECEDING AND CURRENT ROW acumula a partir da primeira linha; CURRENT ROW AND UNBOUNDED FOLLOWING soma o que ainda falta:

WITH daily AS (
    SELECT DATE(payment_date) AS pay_day, SUM(amount) AS revenue
    FROM payment
    WHERE payment_date >= '2005-07-26'
      AND payment_date < '2005-08-24'
    GROUP BY DATE(payment_date)
)
SELECT
    pay_day,
    revenue,
    SUM(revenue) OVER (ORDER BY pay_day
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
    SUM(revenue) OVER (ORDER BY pay_day
        ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS remaining_total
FROM daily
ORDER BY pay_day
LIMIT 3;

Resultado: em 26 de julho o total acumulado é 160.67 e o total restante é o período inteiro, 37971.56; em 28 de julho são 5464.98 e 35084.38.

O total acumulado é tratado com mais profundidade, incluindo totais por grupo e a solução sem funções de janela, na Lição 12.7.

Média Móvel: 3 Linhas Não São 3 Dias

Uma média móvel suaviza as oscilações diárias. A primeira tentativa costuma ser "a linha atual e as duas anteriores", uma moldura ROWS. Uma moldura RANGE com INTERVAL usa antes "o dia atual e os dois dias de calendário anteriores":

WITH daily AS (
    SELECT DATE(payment_date) AS pay_day, SUM(amount) AS revenue
    FROM payment
    WHERE payment_date >= '2005-07-26'
      AND payment_date < '2005-08-24'
    GROUP BY DATE(payment_date)
)
SELECT
    pay_day,
    revenue,
    ROUND(AVG(revenue) OVER (ORDER BY pay_day
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS avg_3_rows,
    ROUND(AVG(revenue) OVER (ORDER BY pay_day
        RANGE BETWEEN INTERVAL 2 DAY PRECEDING AND CURRENT ROW), 2) AS avg_3_days
FROM daily
ORDER BY pay_day;

Resultado: 16 linhas; as duas médias coincidem em todo o lado, exceto logo após a lacuna: em 16 de agosto a média de 3 linhas é 1885.21 e a de 3 dias é 111.77.

Em 16 de agosto, "as últimas 3 linhas" são 1 de agosto, 2 de agosto e 16 de agosto: duas delas têm duas semanas. "Os últimos 3 dias" contêm apenas o próprio 16 de agosto, porque não houve pagamentos em 14 e 15 de agosto. Quando as datas têm lacunas, use RANGE com INTERVAL para janelas de calendário, ou junte primeiro os dados a um calendário completo (Lição 6.6) para que cada dia tenha uma linha.

O MariaDB não aceita INTERVAL numa moldura. Aí, ordene pelo número do dia e use um deslocamento numérico, que dá o mesmo resultado: AVG(revenue) OVER (ORDER BY TO_DAYS(pay_day) RANGE BETWEEN 2 PRECEDING AND CURRENT ROW).

No início dos dados, as duas molduras simplesmente contêm menos linhas: o primeiro dia é a média de um valor, o segundo de dois.

A Moldura Padrão

Se o OVER tem ORDER BY mas não tem moldura, o banco de dados usa RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. É um cálculo acumulado e, por ser RANGE, as linhas com o mesmo valor de ordenação são somadas em conjunto, em bloco. Sem ORDER BY, a moldura padrão é a partição inteira.

Duas consequências práticas:

• SUM(x) OVER (ORDER BY day) é um total acumulado, não um total geral. Retire o ORDER BY para obter o total.
• Quando a coluna de ordenação tem repetidos, o total acumulado salta todo o grupo de linhas iguais de uma vez. A Lição 12.7 mostra isto com dois pagamentos feitos no mesmo segundo. Escreva ROWS BETWEEN ... explicitamente sempre que a moldura for importante.

Dar Nome a uma Janela com WINDOW

Quando várias funções usam a mesma janela, defina-a uma vez com a cláusula WINDOW e refira-a pelo nome:

WITH daily AS (
    SELECT DATE(payment_date) AS pay_day, SUM(amount) AS revenue
    FROM payment
    WHERE payment_date >= '2005-07-26'
      AND payment_date < '2005-08-24'
    GROUP BY DATE(payment_date)
)
SELECT
    pay_day,
    revenue,
    MIN(revenue) OVER last_3_days AS min_3_days,
    MAX(revenue) OVER last_3_days AS max_3_days,
    COUNT(*) OVER last_3_days AS days_in_window
FROM daily
WINDOW last_3_days AS (
    ORDER BY pay_day
    RANGE BETWEEN INTERVAL 2 DAY PRECEDING AND CURRENT ROW
)
ORDER BY pay_day;

Resultado: 16 linhas; em 16 de agosto days_in_window é 1, em 17 de agosto é 2 e a partir de 18 de agosto volta a ser 3.

A cláusula WINDOW fica depois do HAVING e antes do ORDER BY. O MySQL 8.0, MariaDB 10.2+, PostgreSQL e SQLite suportam-na; o SQL Server a partir da versão 2022.

O Que o MySQL Não Suporta

O padrão SQL tem também molduras GROUPS, que contam grupos de valores iguais, e uma opção EXCLUDE. O PostgreSQL e o SQLite suportam-nas; o MySQL e o MariaDB não.

Principais conclusões:

• Uma moldura seleciona as linhas à volta da atual que uma função de janela de agregação usa: ROWS ou RANGE BETWEEN start AND end.
• UNBOUNDED PRECEDING AND CURRENT ROW dá um total acumulado; n PRECEDING AND CURRENT ROW dá uma janela deslizante.
• ROWS conta linhas, RANGE compara valores: com lacunas nas datas, "3 linhas" e "3 dias" dão médias diferentes.
• Com ORDER BY e sem moldura, o padrão é RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW; escreva a moldura explicitamente quando for importante.
• A cláusula WINDOW dá nome a uma janela e permite que várias funções a reutilizem.

Perguntas Frequentes

Qual é a diferença entre ROWS e RANGE em SQL?

ROWS conta linhas físicas: 2 PRECEDING são as duas linhas acima da atual. RANGE compara valores da coluna do ORDER BY: INTERVAL 2 DAY PRECEDING são todas as linhas cuja data está nos dois dias anteriores à atual, quantas forem.

Como calcular uma média móvel em SQL?

Use AVG com uma moldura: AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) calcula a média da linha atual e das seis anteriores. Se alguns dias não têm linhas, use RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW ou preencha primeiro as lacunas com um calendário.

Qual é a moldura de janela padrão?

Com ORDER BY no OVER e sem cláusula de moldura, é RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, um cálculo acumulado em que as linhas com valores de ordenação iguais entram juntas. Sem ORDER BY, a moldura é a partição inteira.

As molduras de janela funcionam com ROW_NUMBER e RANK?

Não. As funções de ranking (ROW_NUMBER, RANK, DENSE_RANK, NTILE) e LAG/LEAD trabalham sempre sobre a partição inteira e ignoram a moldura. As molduras afetam as funções de agregação e FIRST_VALUE, LAST_VALUE e NTH_VALUE.