7 Situações da vida real quando você precisa de um total em execução e como calculá-lo em SQL

Você quer aprender o que é um total em execução e como calculá-lo em SQL? Neste artigo, descreverei diferentes cenários de negócios nos quais um total em execução é necessário. Também lhe ensinarei como calcular um total em SQL com a ajuda das funções de janela. Pronto? Vamos lá!

Definição de total em execução

Um total corrente é a soma cumulativa de um valor e de todos os valores anteriores na coluna.

Por exemplo, imagine que você esteja em vendas e armazenando informações sobre o número de itens vendidos em um determinado dia. Talvez você queira calcular um total corrente, o número total de itens vendidos até uma data específica.

Abaixo está uma tabela com o número de itens vendidos em um determinado dia:

Número de itens vendidos e total corrente

A terceira coluna é o total em execução calculado. Tenha em mente que o valor atual da linha está sempre incluído no total corrido.

Por exemplo, em 05 de janeiro de 2021, o total corrido é 66. Este é o número total de itens vendidos de 01 de janeiro de 2021 a 05 de janeiro de 2021 (inclusive em 05 de janeiro de 2021). Especificamente, o cálculo é 10 + 12 + 15 + 15 + 9 + 20 = 66.

Executando o cálculo total em SQL

Em SQL, o total em execução é calculado com a ajuda de funções de janela. Esta característica SQL especial pode fazer cálculos complexos com apenas algumas linhas de código SQL.

Aqui está a função de janela para nosso exemplo acima:

SELECT *,
      SUM(no_of_items) OVER(ORDER BY date) AS running_total 
FROM sales

E aqui está uma explicação do código:

• ?Esta é uma função de janela, denotada com a cláusula OVER.
• Dentro da cláusula OVER, há um ORDER BY Isto nos diz como as linhas são ordenadas antes do cálculo ser feito. Em nosso caso, as filas são ordenadas pela coluna de datas.
• Ao utilizar funções de janela, cada registro recebe seu próprio cálculo. Portanto, o resultado é exibido com uma coluna adicional. Não há linhas colapsadas quando se trabalha com funções de janela. Esta é a principal diferença entre as funções de janela. GROUP BY cláusula e funções de janela em SQL.
• Dentro da declaração OVER, você também pode encontrar a palavra-chave PARTITION BY. PARTITION BY agrupa linhas em partições nas quais cada cálculo de partição é feito separadamente. Em nosso exemplo, as filas não estão organizadas em partições. Portanto, todo o conjunto de dados é tratado como um grupo.
• Ao trabalhar com funções de janela, para cada registro em uma tabela, definimos uma estrutura de janela dentro da qual é feito um cálculo específico. Isto é denotado pela palavra-chave ROW/RANGE dentro da declaração OVER. Se esta palavra-chave não for definida (como em nosso exemplo), é tomado um valor padrão para a moldura da janela. Uma moldura de janela padrão da linha atual inclui a linha atual e todas as linhas anteriores.
• Para nosso exemplo, cada registro na soma total da tabela é calculado pela soma de todos os valores anteriores mais o atual. Por exemplo, o total corrente para 05 de janeiro de 2021 é 66. ?Esta é a soma dos quatro valores anteriores (10, 12, 15 e 9) mais o valor na linha atual (20).

Exemplos de Total Corrente

Muito bem, vamos dar uma olhada em cenários da vida real nos quais são utilizados totais em execução.

1. mantendo o controle de cotas planejadas versus cotas realizadas

Em vendas, cada vendedor deve cumprir metas para satisfazer exigências específicas do trabalho. Da mesma forma, nos setores de telecomunicações e bancário, cada divisão deve adquirir um número específico de novos clientes em cada trimestre e vender um número específico de produtos aos clientes.

A gerência acompanha o desempenho usando totais em execução. A cada dia, o total em execução é atualizado com novos dados. Esses números são geralmente avaliados mensalmente, trimestralmente ou anualmente.

Aqui está uma tabela que rastreia o número de novos empréstimos em 2021 mensalmente:

Número de novos empréstimos, planejados vs. realizados

Para a primeira metade de 2021, foi decidido que 535 empréstimos deveriam ser vendidos aos clientes neste período. Os planos são feitos para cada mês separadamente, e 535 é a meta total em execução para estes seis meses.

A administração acompanha as cotas planejadas versus realizadas. Por enquanto, há 112 novos empréstimos vendidos. A tabela continuará sendo atualizada à medida que o tempo passar.

Abaixo está o código SQL que calcula os totais em execução:

SELECT 
      date,
      plan,
      SUM(plan) OVER(order by Date) AS plan_running_total,
      realization,
      SUM(realization) OVER(order by Date) AS realization_running_total
FROM sales;

Como no exemplo anterior, uma função de janela é usada para calcular o total em execução. A função de janela é indicada com OVER, e dentro dos parênteses, a ordem das linhas é definida.

A função de agregação utilizada é SUM. Temos dois totais em execução: ?quotas planejadas e realizadas. Portanto, temos duas funções de janela, e duas colunas são adicionadas à tabela.

2. Cálculos de Saldo

Os totais de execução também são usados no cálculo de um saldo. Cada vez que uma nova transação é feita (um pagamento ou saque de uma conta), a soma acumulada é atualizada, e o saldo atual é exibido.

Abaixo está uma tabela de saldos:

Valor da transação e saldo atual

Na tabela acima, vemos que a primeira transação ocorreu em 01 de dezembro de 2020-12-01, um influxo de $5.000. O saldo nesse dia foi de US$ 5.000. Depois disso, o cliente começou a gastar dinheiro.

Em 03 de dezembro de 2020, o cliente gastou $50 (esta transação é mostrada com um valor negativo). Assim, o saldo diminuiu para $4.950. No dia seguinte, o saldo diminuiu em mais $125 a $4.825, e assim por diante.

O saldo da conta é calculado como um total corrente. É a soma cumulativa de todas as transações associadas a essa conta. A cada nova transação, o saldo é atualizado, ou seja, o total em execução é recalculado.

Aqui está a função de janela para este exemplo:

SELECT date,
            transaction,
           SUM(transaction) OVER(order by date) AS balance_amount 
FROM balance;

3. Operações de caixa registradora

Os totais em execução também são utilizados em operações de caixa registradora.

Por exemplo, quando um cliente está comprando itens em uma loja, o caixa escaneia os itens na cesta. Com cada item escaneado recentemente, o total em execução atualizado é exibido na tela. Isto mostra quanto o cliente precisa pagar pelos itens escaneados até o momento.

Abaixo está um exemplo de uma cesta em processo de escaneamento. Fraldas são o item escaneado mais recentemente. Até agora, o cliente precisa pagar 19,70 EUR. O xampu ainda não foi processado, portanto seu preço ainda não foi calculado no total corrente:

Caixa registradora: produtos escaneados

Esta é quase a mesma função de janela que no exemplo anterior (apenas os nomes das tabelas e colunas diferem):

SELECT 
       *,
      SUM(price) OVER(order by date) as running_total 
FROM cash_register;

Assim, cada vez que o caixa escaneia um item, a soma acumulada é atualizada. Com cada varredura, o caixa sabe quanto o cliente precisa pagar até agora.

4. Contagem do Consumo Diário de Calorias

Se você quisesse perder algum peso, você poderia usar um total corrente para calcular sua ingestão diária de calorias. A cada dia, você iniciaria sua contagem calórica a zero e então atualizaria o total corrido com base no que e quanto você come.

Abaixo está uma contagem de calorias para um dia:

O total corrido é atualizado toda vez que você come alguma coisa. Assim, você sempre sabe o quanto já comeu e quanto mais pode comer nesse dia. Esta abordagem é chamada de dieta de contagem calórica, que é uma opção popular.

Aqui está o código que calcula o total de calorias:

SELECT calorie_intake.*,
      SUM(calories) OVER(ORDER BY hours) AS running_total
FROM calorie_intake;

Vamos passar por mais alguns cenários nos quais são usados totais corridos. Tenha em mente que as funções de janela que calculam os totais em execução permanecem praticamente as mesmas. Assim, de agora em diante, explicarei brevemente os exemplos sem incluir o código SQL.

5. casos confirmados de COVID-19

Cada país coleta informações sobre o número total de casos confirmados de COVID-19, mortes e casos curados a cada dia. Com esta nova informação, os totais em execução são atualizados para que cada país saiba quantos casos confirmados, mortes e casos curados teve até agora, desde o início da pandemia até agora.

Para mais detalhes, verifique como o total corrente é calculado usando os dados de John Hopkins: como analisar os dados da COVID-19.

6. usuários registrados de uma aplicação móvel

Os proprietários de aplicações móveis geralmente querem ver a soma acumulada de novos usuários registrados e instalações/desinstalações que foram feitas no último mês, trimestre e/ou ano. Aqui novamente, você pode usar os totais em execução para obter melhores percepções. Estas estatísticas dizem aos proprietários como a aplicação ou uma característica específica foi recebida e como desenvolver o produto no futuro.

Da mesma forma, se você administra um website, uma das métricas mais importantes a ser rastreada é o número total de page views ou visitas. Estas informações são normalmente coletadas diariamente. Calculando o total em execução, você pode ver como a soma acumulada se comporta ao longo do tempo e como se compara com os totais em execução no passado.

7. programas de fidelidade de companhias aéreas

Este conceito é provavelmente bem conhecido por aqueles que voam com freqüência. A cada vôo, você coleta pontos. Uma vez que você acumula um certo número de pontos, você recebe descontos e/ou recompensas.

Um total corrente é usado para calcular o número total de pontos que você tem em sua conta. Cada vez que você compra uma passagem, o total de pontos é atualizado, e seus pontos aumentam.

Resumo

Neste artigo, eu mostrei como os totais em execução são utilizados em diferentes cenários. Como você aprendeu, os totais em execução têm muitas aplicações.

As funções da janela SQL permitem que você calcule os totais em execução com relativa facilidade com apenas algumas linhas de código. As funções de janela são usadas quando se trata de cálculos complexos. Uma vez aprendida a sintaxe, você escreverá um código mais limpo e mais compreensível.

Embora eu só tenha explicado como usar as funções de janela para executar cálculos totais, as funções de janela são amplamente utilizadas para uma variedade de cálculos. Neste artigo com exemplos de funções de janela, você pode aprender sobre outras maneiras de usar as funções de janela.

Também oferecemos um ótimo curso interativo Funções de Janela (Window Functions) em SQL com muitos exemplos. Se você quiser mergulhar fundo nas funções de janela, recomendo vivamente que faça este curso. Ele fornece muitos exercícios para a prática, o que é importante ao adquirir novas habilidades SQL.