O que é um total de execução de SQL e como calculá-lo?

O SQL running total é um padrão muito comum, usado com freqüência nas finanças e na análise de tendências. Neste artigo, você aprenderá o que é um total em execução e como escrever uma consulta SQL para calculá-lo.

A melhor maneira de aprender SQL é através da prática. LearnSQL.com.br oferece mais de 30 cursos interativos de SQL em vários níveis de dificuldade. Cada curso é interativo: há um pouco de leitura, seguido por um exercício para praticar o que você acabou de ler. Com cada exercício resolvido, você ganha confiança em suas habilidades SQL. Inscreva-se agora de graça!

O que é um SQL Running Total?

Em SQL, um total em execução é a soma cumulativa dos números anteriores em uma coluna. Veja o exemplo abaixo, que apresenta o registro diário dos usuários para uma loja on-line:

A primeira coluna mostra a data. A segunda coluna mostra o número de usuários que se registraram naquela data. A terceira coluna, total_utilizadores, soma o número total de usuários cadastrados naquele dia.

Por exemplo, no primeiro dia (2020-03-05), 32 usuários registrados e o valor total de usuários registrados era 32. No dia seguinte (2020-03-06), 15 usuários registrados; o valor total_de_usuários tornou-se 47 (32+15). No terceiro dia (2020-03-07), seis usuários registrados e o valor total_ de usuários foi 53. Em outras palavras, o valor total_de_usuários é um valor corrente que muda de dia para dia. É o número total de usuários em cada dia.

O exemplo seguinte utiliza a coluna total_correndo para lidar com a receita da empresa de forma semelhante. Veja a tabela abaixo:

Para cada dia, a coluna total_receita está calculando o valor da receita gerada até o dia determinado. Em 2020-04-04, a empresa alcançou uma receita total de $270.500 porque esta é a soma de todas as receitas de 2020-04-02 a 2020-04-04-04.

Bancos de dados relacionais (como SQL Server, Oracle, PostgreSQL e MySQL) e até mesmo motores não relacionais como Hive e Presto fornecem funções de janela que nos permitem calcular um total em execução. Para aprender sobre as funções de janela, recomendo as funções interativas Funções de Janela (Window Functions) em SQL curso. Contém mais de 200 exercícios para aprender as funções de janela, utilizando-as.

A seguir, falaremos sobre a consulta SQL que constrói tal soma e aprenderemos mais sobre as funções de janela.

Como calcular uma soma acumulada em SQL

Se você gostaria de calcular o total em SQL, você precisa estar familiarizado com as funções de janela fornecidas por seu banco de dados. As funções de janela operam em um conjunto de linhas e retornam um valor agregado para cada linha do conjunto de resultados.

A sintaxe da função de janela SQL que calcula uma soma cumulativa entre as linhas é:

window_function ( column ) 
OVER ( [ PARTITION BY partition_list ] [ ORDER BY order_list] )

É obrigatório o uso da cláusula OVER em uma função de janela, mas os argumentos nesta cláusula são opcionais. Iremos discuti-los nos próximos parágrafos deste artigo.

Exemplo 1

Neste exemplo, calcularemos a soma total dos usuários registrados a cada dia.

Esta consulta ...

SELECT registration_date,registred_users,
  SUM(registred_users) OVER (ORDER BY registration_date)
  AS total_users
FROM registration;

... seleciona a data de registro para todos os usuários. Também precisamos da soma de todos os usuários para cada dia, desde o primeiro dia dado (2020-03-05) até o dia naquela linha.

Este é o resultado definido:

Para calcular o total em execução, usamos a função agregada SUM() e colocamos a coluna registered_users como argumento; queremos obter a soma acumulada de usuários a partir desta coluna.

O próximo passo é usar a cláusula OVER. Em nosso exemplo, esta cláusula tem um argumento: ORDER BY registration_date. As linhas do conjunto de resultados são ordenadas de acordo com esta coluna (registration_date). Para cada valor na coluna registration_date, a soma total dos valores da coluna anterior é computada (ou seja, a soma do número de usuários antes da data na linha atual) e o valor atual (ou seja, usuários registrados no dia da linha atual) é adicionado a ela.

Observe que a soma total é mostrada na nova coluna, que denominamos total_users.

Na primeira etapa (a data de registro 2020-03-05), temos 57 usuários cadastrados. A soma dos usuários registrados neste dia é a mesma 57. No passo seguinte, adicionamos a este valor total (57). O que adicionamos? O número de usuários registrados na data atual (2020-03-06), que é 27; isto nos dá um total de 84 usuários registrados. Na última linha do resultado estabelecido (para a data do último registro, 2020-03-07), o total corrente é 100.

Graças às funções da janela SQL, é fácil encontrar o número total cumulativo de usuários durante um determinado período de tempo. Por exemplo, durante 2020-03-05 - 2020-03-06, o número total de usuários registrados foi 84.

Exemplo 2

No segundo exemplo, vamos entrar em mais detalhes sobre os usuários. Vamos mostrar os usuários com seus países. Veja a tabela abaixo:

Observe que para cada dia temos o número de usuários para cada país mostrado separadamente. Neste exemplo, calcularemos uma soma cumulativa separada de usuários registrados para cada país.

Esta consulta ...

SELECT country, registration_date,registred_users,
  SUM(registred_users) 
  OVER (PARTITION BY country ORDER BY registration_date)
  AS total_users
FROM registration;

... calcula a soma de usuários para cada dia, primeiro para os usuários da Inglaterra e depois para os usuários da Polônia.

Aqui está o resultado definido:

Para cada país, cada dia de cadastro recebe um total em execução. A cláusula PARTITION BY na cláusula OVER tem como argumento o país da coluna. Esta partição fila por país, permitindo a SQL calcular um total em execução apenas para aquele país (ao invés de ambos os países juntos). Assim, na Inglaterra, de 2020-03-05 a 2020-03-07, temos um total de 47 usuários. Para o mesmo período na Polônia, o total de usuários registrados foi de 53.

Exemplo 3

No último exemplo, analisaremos os dados no competition tabela, que armazena as colunas game_id, gamer_id, game_level, competition_date, e score.

Precisamos verificar a pontuação total acumulada de cada jogador para cada dia em dois jogos diferentes. Veja a consulta abaixo, que cria este total em execução:

SELECT game_id,game_level,gamer_id,competition_date,score,
  SUM(score)
  OVER (PARTITION BY game_id, gamer_id 
        ORDER BY competition_date)
  AS total_score
FROM competition;

O resultado:

Nesta tabela de resultados, podemos ler que o jogador com ID=4 começa com uma pontuação de 4 e termina com uma pontuação total de 11. O melhor foi o jogador com ID=7, que terminou com uma pontuação total de 12.

Mais uma vez, na cláusula OVER, usamos PARTITION BY. Desta vez, utilizamos uma lista de colunas (game_id, gamer_id). Isto nos permite criar duas partições: uma para o jogo 1 e uma para o jogo 2.

Em seguida, as linhas foram divididas por gamer_id para cada jogo. No jogo 1, temos os gamers 4 e 5; no jogo 2, temos os gamers 6, 7, e 8. Entre cada grupo (um determinado jogador joga em um determinado jogo), as linhas são ordenadas por data_de_concorrência e a pontuação de cada dia é somada. Em cada grupo, podemos observar a mudança de pontuação de cada jogador em um determinado jogo.

Como você utilizará os totais de execução SQL?

A utilização de um valor total em execução em relatórios SQL pode ser muito útil, especialmente para especialistas financeiros. Portanto, vale a pena saber o que é uma soma cumulativa e como usar as funções de janela SQL para criar uma. Este artigo apresentou alguns casos de uso selecionados. Para mais informações sobre funções de janela, veja nosso artigo Exemplo de funções de janela SQL com explicações ou o curso LearnSQL Funções de Janela (Window Functions) em SQL.