Lição 12.7 · Tempo de leitura: ~6 min

Um total acumulado (soma cumulativa) soma cada valor à soma de todos os valores anteriores: receita até à data, saldo de uma conta após cada movimento, registos acumulados. É um básico dos dashboards e uma tarefa popular em entrevistas de SQL. Nesta lição, você vai calculá-lo com SUM() OVER (ORDER BY ...), recomeçá-lo para cada grupo, evitar a armadilha dos valores de ordenação repetidos e ver como se fazia antes das funções de janela.

Total Acumulado em SQL

Um SUM normal com GROUP BY reduz as linhas a um total. Um total acumulado mantém cada linha e acrescenta uma coluna com a soma até ali. É exatamente isso que faz uma função de janela: SUM(amount) OVER (ORDER BY day) soma todas as linhas desde a primeira até à atual, pela ordem de day. As funções de janela e as molduras foram vistas nas Lições 7.1 e 7.3; aqui resolvem uma tarefa prática.

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

Receita Acumulada por Dia

Primeiro agregue os pagamentos por dia; depois acrescente o total acumulado sobre as linhas diárias:

WITH daily AS (
    SELECT DATE(payment_date) AS pay_day,
           SUM(amount) AS daily_amount
    FROM payment
    WHERE payment_date >= '2005-08-01'
      AND payment_date < '2005-09-01'
    GROUP BY DATE(payment_date)
)
SELECT pay_day,
       daily_amount,
       SUM(daily_amount) OVER (ORDER BY pay_day) AS running_total
FROM daily
ORDER BY pay_day;

Resultado: 10 dias com pagamentos em agosto de 2005; o total acumulado cresce de 2817.29 para 24072.13, a receita do mês inteiro.

Agregar primeiro é importante: SUM(...) OVER sobre os pagamentos individuais devolveria uma linha por pagamento, não por dia. Os dias sem pagamentos simplesmente não aparecem; para os mostrar com 0, junte os dados a um calendário (Lição 6.6).

Recomeçar o Total para Cada Grupo

O PARTITION BY inicia um total acumulado separado para cada grupo. Aqui a receita mensal recebe duas colunas acumuladas: uma desde o início e outra que recomeça a cada ano:

WITH monthly AS (
    SELECT YEAR(payment_date) AS pay_year,
           MONTH(payment_date) AS pay_month,
           SUM(amount) AS monthly_amount
    FROM payment
    GROUP BY YEAR(payment_date), MONTH(payment_date)
)
SELECT pay_year, pay_month, monthly_amount,
       SUM(monthly_amount) OVER (ORDER BY pay_year, pay_month) AS running_total,
       SUM(monthly_amount) OVER (PARTITION BY pay_year ORDER BY pay_month) AS year_to_date
FROM monthly
ORDER BY pay_year, pay_month;

Resultado: 5 meses; o total acumulado geral chega a 67416.51, enquanto o acumulado do ano recomeça em 2006 com 514.18.

O mesmo padrão dá um total acumulado por cliente (PARTITION BY customer_id ORDER BY payment_date) ou por loja.

A Armadilha: Valores de Ordenação Iguais

Quando o ORDER BY da janela tem repetidos, o resultado surpreende muita gente. Dois pagamentos no Sakila têm exatamente a mesma hora:

SELECT payment_id, payment_date, amount,
       SUM(amount) OVER (ORDER BY payment_date) AS default_frame,
       SUM(amount) OVER (ORDER BY payment_date, payment_id
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_frame
FROM payment
WHERE payment_date >= '2005-05-30 14:40:00'
  AND payment_date < '2005-05-30 15:00:00'
ORDER BY payment_date, payment_id;

Resultado: os pagamentos 1258 e 8245 têm ambos default_frame = 5.98, enquanto rows_frame mostra 2.99 e depois 5.98.

Com ORDER BY e sem moldura explícita, a moldura padrão é RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: inclui todas as linhas com o mesmo valor de ordenação que a atual. As linhas empatadas recebem o mesmo total. Para somar as linhas estritamente uma a uma, use uma moldura ROWS e um critério de desempate único no ORDER BY, como a chave primária.

Sem Funções de Janela

Antes das funções de janela (MySQL antes da 8.0), um total acumulado era uma subconsulta correlacionada: para cada linha, somar todas as linhas até ela.

WITH daily AS (
    SELECT DATE(payment_date) AS pay_day,
           SUM(amount) AS daily_amount
    FROM payment
    WHERE payment_date >= '2005-08-01'
      AND payment_date < '2005-09-01'
    GROUP BY DATE(payment_date)
)
SELECT d1.pay_day,
       d1.daily_amount,
       (SELECT SUM(d2.daily_amount)
        FROM daily d2
        WHERE d2.pay_day <= d1.pay_day) AS running_total
FROM daily d1
ORDER BY d1.pay_day;

Resultado: as mesmas 10 linhas e totais da versão com função de janela.

Para cada linha, a subconsulta percorre todas as linhas anteriores, por isso o trabalho cresce de forma quadrática. Mencione-a como alternativa de recurso e use SUM() OVER na prática.

Prática

• Pagamentos Mensais e Acumulados
• Análise de Pagamentos Acumulados
• Valores dos Pagamentos de Agosto de 2005

Principais conclusões:

• SUM(x) OVER (ORDER BY key) devolve a soma acumulada até cada linha e mantém todas as linhas.
• Agregue primeiro ao nível pretendido (dia, mês) e depois acrescente o total acumulado.
• O PARTITION BY recomeça o total para cada grupo: cliente, loja, ano.
• A moldura padrão é RANGE, por isso linhas com valores de ordenação iguais partilham um total; use ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW e um critério de desempate único.
• Sem funções de janela, use uma subconsulta correlacionada, mas é muito mais lenta.

Perguntas Frequentes

Como calcular um total acumulado em SQL?

Use uma função de janela: SELECT day, amount, SUM(amount) OVER (ORDER BY day) AS running_total FROM daily;. Funciona no MySQL 8.0+, MariaDB 10.2+, PostgreSQL, SQL Server, Oracle e SQLite 3.25+.

Como calcular um total acumulado por grupo?

Acrescente PARTITION BY: SUM(amount) OVER (PARTITION BY customer_id ORDER BY payment_date). A soma recomeça na primeira linha de cada cliente.

Por que algumas linhas têm o mesmo total acumulado?

Porque têm o mesmo valor na coluna do ORDER BY da janela, e a moldura RANGE padrão inclui todas essas linhas empatadas. Indique ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW e acrescente uma coluna única, como o id, ao ORDER BY.

Como calcular uma média móvel em vez de um total acumulado?

Limite a moldura a um número fixo de linhas: AVG(amount) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) calcula a média do dia atual e dos seis anteriores. As molduras de janela são vistas na Lição 7.3.