As funções de agregação tradicionais, como SUM(), AVG(), COUNT(), MAX() e MIN(), tornam-se ferramentas analíticas poderosas quando aplicadas como funções de janela (window functions). Elas permitem realizar cálculos complexos sobre um conjunto de linhas relacionadas à linha atual, sem a necessidade de agrupar o resultado final em uma única linha.

Agregações Móveis (Moving Aggregates)

Agregações móveis são úteis para suavizar flutuações em séries temporais ou observar tendências locais. Elas definem um quadro (frame) que se desloca conforme percorremos os registros.

Média Móvel

A função AVG() como window function pode calcular a média de um intervalo específico. Por exemplo, para calcular a média de vendas de um produto considerando o mês atual e os dois meses anteriores:

SELECT 
    v.item_nome, 
    v.mes_ref, 
    v.faturamento,
    AVG(v.faturamento) OVER (
        PARTITION BY v.item_nome
        ORDER BY v.mes_ref
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS media_movel_trimestral
FROM vendas_mensais v
ORDER BY v.item_nome, v.mes_ref;

Neste exemplo, a cláusula PARTITION BY isola os cálculos por produto. O ROWS BETWEEN 2 PRECEDING AND CURRENT ROW define que a janela de cálculo inclui apenas as duas linhas anteriores e a linha atual.

Soma Móvel

De forma análoga, podemos caluclar o faturamento acumulado em janelas deslizantes para identificar picos de desempenho de curto prazo:

SELECT 
    v.item_nome, 
    v.mes_ref, 
    v.faturamento,
    SUM(v.faturamento) OVER (
        PARTITION BY v.item_nome
        ORDER BY v.mes_ref
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS total_movel_trimestral
FROM vendas_mensais v
ORDER BY v.item_nome, v.mes_ref;

Agregações Cumulativas

Diferente das janelas móveis que possuem um tamanho fixo, as agregações cumulativas expandem a janela desde o início do conjunto de dados (ou partição) até o registro atual.

Soma Cmuulativa (Runing Total)

Para obter o total de vendas acumulado de um produto desde o primeiro mês registrado:

SELECT 
    v.item_nome, 
    v.mes_ref, 
    v.faturamento,
    SUM(v.faturamento) OVER (
        PARTITION BY v.item_nome
        ORDER BY v.mes_ref
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS total_acumulado
FROM vendas_mensais v;

O uso de UNBOUNDED PRECEDING instrui o banco de dados a iniciar o cálculo a partir da primeira linha da partição.

Comportamento Padrão do ORDER BY

Um detalhe crucial no SQL é que, ao utilizar a cláusula ORDER BY dentro de OVER() sem definir explicitamente um frame (ROWS ou RANGE), o banco de dados assume por padrão a janela do início da partição até a linha atual (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW).

Portanto, a consulta abaixo produz o mesmo resultado de soma cumulativa:

SELECT 
    v.item_nome, 
    v.mes_ref, 
    v.faturamento,
    SUM(v.faturamento) OVER (
        PARTITION BY v.item_nome
        ORDER BY v.mes_ref
    ) AS total_acumulado_simplificado
FROM vendas_mensais v;

Caso o ORDER BY seja omitido completamente, a função de agregação será aplicada a toda a partição, resultando no mesmo valor total para todas as linhas daquele grupo.

Definição de Janelas por Intervalo (RANGE)

Enquanto ROWS baseia-se na contagem física de linhas, a palavra-chave RANGE baseia-se na diferença de valores lógicos (como datas ou números). Isso é extremamente útil para análises baseadas em tempo real.

Considere um cenário de detecção de fraude onde precisamos identificar contas que transferiram mais de 1 milhão de reais em um intervalo de 5 dias:

SELECT 
    resultado.momento_operacao, 
    resultado.id_origem, 
    resultado.soma_periodo 
FROM (
    SELECT 
        ts_operacao AS momento_operacao,
        conta_id AS id_origem,
        valor_transferido,
        SUM(valor_transferido) OVER (
            PARTITION BY conta_id
            ORDER BY ts_operacao
            RANGE INTERVAL '5' DAY PRECEDING
        ) AS soma_periodo
    FROM log_transacoes 
    WHERE operacao_tipo = 'TRANSFERENCIA'
) resultado
WHERE resultado.soma_periodo >= 1000000;

Estrutura de Dados para Testes

Para reproduzir os exemplos acima, utilize as definições de tabela abaixo:

-- Tabela de Vendas Mensais
CREATE TABLE vendas_mensais (
    item_nome VARCHAR(50), 
    mes_ref VARCHAR(10), 
    faturamento NUMERIC(12, 2)
);

-- Tabela de Logs Financeiros
CREATE TABLE log_transacoes (
    id_log SERIAL PRIMARY KEY,
    ts_operacao TIMESTAMP NOT NULL,
    conta_id VARCHAR(30) NOT NULL,
    conta_destino VARCHAR(30),
    operacao_tipo VARCHAR(20) NOT NULL,
    valor_transferido NUMERIC(15, 2) NOT NULL
);