Total móvel em SQL: O que é e como calculá-lo

O cálculo de um total móvel no SQL é simples quando você usa as funções de janela. Essas métricas úteis são semelhantes aos totais em execução e às médias móveis, e o ajudarão a criar uma imagem clara dos seus dados. Neste artigo, explicaremos e exploraremos os totais móveis.

No SQL, um total móvel é uma métrica importante que ajuda você a ver como as tendências mudam ao longo do tempo, fornecendo uma visão clara dos dados. Ele calcula uma soma em uma "janela" ou subconjunto de linhas que se move pelo conjunto de resultados. Por exemplo, para rastrear visitas a sites, você pode consultar o total de visitas de cada dia e também somar os sete dias anteriores para identificar tendências. Ao usar as funções de janela do SQL, você pode ver essas tendências em cada linha dos resultados da consulta, sem precisar recorrer a ferramentas externas.

Para calcular facilmente os totais móveis no SQL, você precisará de funções de janela. Nosso Funções de Janela (Window Functions) em SQL curso é perfeito se você quiser realmente se aprofundar nesse tópico. Ao fazer este curso, você concluirá 218 exercícios que abrangem a sintaxe e a semântica das funções de janela. Você conhecerá todo o potencial das funções de janela e os casos de uso típicos. Além disso, obterá uma compreensão do SQL avançado que lhe permitirá realizar análises mais complexas e eficientes.

Um total móvel simples em SQL

Vamos ver uma situação comercial comum em que o total móvel demonstra sua utilidade. Considere uma tabela chamada daily_sales que registra os totais de vendas diárias de uma loja:

Se olharmos para esses dados, ou mesmo se criarmos um gráfico a partir deles, é isso que veremos:

Podemos analisar eventos específicos, como encontrar datas com vendas excepcionalmente baixas. Por exemplo, as vendas em 2024-08-03 são menores do que em outros dias. Entretanto, essa forma de visualizar as informações não é eficaz para detectar tendências na evolução das vendas. Para isso, precisamos analisar os totais contínuos. E, para isso, precisaremos das funções de janela do SQL.

Se você precisar de um guia de referência rápida para as funções de janela do SQL, tenha em mãos esta folha de referênciaFunções de Janela (Window Functions) em SQL . Ela agilizará suas tarefas diárias de análise de dados.

Cálculo de um total móvel em SQL

Vamos escrever uma consulta SQL que retorna as duas colunas de daily_sales e adiciona um total móvel de três dias ao conjunto de resultados:

SELECT
    purchase_date,
    total_sales,
    SUM(total_sales) OVER (
        	ORDER BY purchase_date
        	ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
     ) AS rolling_total
FROM daily_sales;

No exemplo acima, a cláusula OVER após a função SUM() define a janela (ou seja, o grupo de linhas) que queremos somar para cada linha. Nesse caso, ela informa ao SQL que a soma deve incluir os dados das duas linhas anteriores e da atual. A ordem é baseada na data de compra. Essa é a nossa janela de rolagem: o dia atual e dois dias anteriores.

A consulta produz esses dados:

Na primeira linha, observe que o total móvel apenas repete o valor da coluna total_sales - não há linhas anteriores para somar. Na segunda linha, ele soma o valor total_sales da primeira e da segunda linhas; da terceira linha em diante, ele retorna a soma dos valores de vendas das duas linhas anteriores e da atual.

Vamos criar um gráfico com os dados totais contínuos para entender como eles nos ajudam a analisar as tendências:

A primeira coisa que salta aos olhos quando olhamos para esse gráfico é que devemos descartar os dois primeiros pontos de dados (as duas primeiras datas), porque eles não representam um total de três dias como o restante das datas. De 2024-08-03 em diante, a linha se torna quase horizontal, indicando que os totais de vendas de três dias permanecem estáveis. Isso é verdade até a última data, que mostra um ligeiro aumento porque o total não é mais afetado pela "queda" nas vendas de 2024-08-03.

Com um conjunto de dados tão pequeno, até mesmo pequenas flutuações afetam a tendência geral; com um conjunto de dados grande, esse não seria o caso. Entretanto, podemos concluir que a combinação de funções agregadas e funções de janela "suaviza" o efeito de flutuações ou anomalias nos dados. Isso permite a visualização clara das tendências dos dados.

Total móvel, total em execução e média móvel

Além do total móvel, há dois outros casos de uso comuns para as funções de janela: o total em execução e a média móvel.

• Um total em execução é um cálculo cumulativo que percorre um conjunto de resultados. Para cada linha, ele calcula a soma de todos os valores em uma coluna, desde a primeira linha até a linha atual. É semelhante ao total móvel, mas sempre soma a partir da primeira linha do conjunto de resultados.
• Uma média móvel é semelhante a um total móvel, pois usa valores da janela definida em relação à linha atual. A diferença é que ela retorna a média desses valores em vez do total.

O cálculo dessas métricas é bastante semelhante ao cálculo de um total móvel. Vamos dar uma olhada em alguns exemplos.

Cálculo de um total em execução no SQL

Aqui está um exemplo de um total em execução:

SELECT
    purchase_date,
    total_sales,
    SUM(total_sales) OVER (ORDER BY purchase_date) AS running_total
FROM
    daily_sales;

A diferença entre o total em execução e o total móvel no SQL é a definição da janela na cláusula OVER. Em vez de definir um subconjunto que abranja um número de linhas em relação à linha atual, basta especificar o critério de ordem; entende-se que o total abrange desde a primeira linha até a atual. Você pode encontrar mais informações sobre totais em execução em nosso artigo O que é um total em execução e como calculá-lo no SQL.

Cálculo de uma média móvel no SQL

O conceito de média móvel é muito semelhante ao de um total móvel. A função de agregação AVG() opera em uma janela que rola por um número definido de linhas. De fato, a consulta para criar uma média móvel em SQL é praticamente a mesma que usamos para o total móvel. Só precisamos alterar a função SUM() para AVG():

SELECT
    purchase_date,
    total_sales,
    AVG(total_sales) OVER (
        	ORDER BY purchase_date
     ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ) AS moving_average
FROM
    daily_sales;

Isso retornará a média de vendas em um período de três dias: o dia atual e os dois dias anteriores. Você pode encontrar mais detalhes sobre médias móveis neste artigo.

Outros casos de uso para totais móveis

Consumo semanal de energia

Há muitos outros cenários em que um total móvel pode ser aplicado. A maioria dos exemplos envolve tabelas de ordem cronológica, em que a janela de dados representa um intervalo de tempo (por exemplo, 7 dias, 30 dias, 3 meses etc.).

Um exemplo poderia ser a análise da evolução do consumo de energia de uma residência ao longo do tempo. Essas informações poderiam ser armazenadas em uma tabela (chamada daily_consumption_per_day) que registra o total de quilowatts-hora consumidos pela residência a cada dia.

Aqui está a consulta:

SELECT
    measure_date,
    daily_consumption,
    SUM(daily_consumption) OVER (
        ORDER BY measure_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ) AS weekly_consumption
FROM
    daily_consumption_per_day;

Para cada data, o resultado mostra a soma do consumo dos últimos 7 dias (incluindo o dia atual). Isso nos permite ver a variação das tendências no consumo de energia ao longo do tempo.

As funções de janela têm muito potencial. Saiba tudo o que você pode fazer com elas em nosso guia completo sobre as funções de janela do SQL.

Uso de totais móveis além da análise de séries temporais

A maioria dos casos de uso dos totais móveis está relacionada à análise de séries temporais. Entretanto, essas estatísticas podem ser aplicadas a qualquer conjunto de dados classificados por uma dimensão em que faça sentido fazer cálculos agregados (por exemplo, totais, médias) em janelas deslizantes que isolem subconjuntos dessa dimensão.

Um exemplo seria uma análise do custo de benefícios médicos por idade. Suponha que tenhamos uma tabela que registra o custo dos benefícios médicos recebidos por pessoas de várias idades. Se ordenarmos as linhas por idade, um total móvel nos permitirá ver como os custos médicos variam de acordo com a faixa etária.

Vamos supor que a tabela se chame medical_costs_by_age e que ela tenha duas colunas: idade e total_medical_cost. Para analisar essas informações, escreveremos uma consulta com um total móvel que abranja uma janela de cinco anos centrada na idade da linha atual (de dois anos abaixo a dois anos acima):

SELECT
    age,
    total_medical_cost,
    SUM(total_medical_cost) OVER (
        ORDER BY age
        ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING
        ) AS weighted_medial_cost
FROM
    medical_costs_by_age;

A partir desse exemplo, podemos obter uma compreensão clara e bastante granular da relação entre as faixas etárias e os custos de saúde.

Quer saber mais sobre totais móveis em SQL?

Ao longo deste artigo, vimos várias maneiras de usar um total móvel no SQL. Nós o comparamos com métricas semelhantes, principalmente o total em execução e a média móvel.

Se você não conhecia as funções de janela do SQL antes de ler este artigo, provavelmente está impressionado com o que elas podem fazer. Se quiser aprender sobre as funções de janela do zero, recomendo nosso Funções de Janela (Window Functions) em SQL curso. Se você já estiver familiarizado com elas, faça o Funções de Janela (Window Functions) em SQL Practice Set. Ele contém 100 exercícios interativos de funções de janela que usam três bancos de dados reais diferentes. E nossa plataforma de aprendizado fornece todas as ferramentas necessárias - você não precisa instalar nada. Basta usar seu navegador e uma conexão com a Internet e pronto!

Por fim, você pode manter sua compreensão dos totais contínuos em SQL afiada com este conjunto de exercícios práticos gratuitos de função de janela. E, se você deseja um emprego que exija o domínio do SQL e quer se preparar para a entrevista de emprego, não se esqueça de ler sobre essas perguntas de entrevista sobre a função de janela do SQL. Obrigado por ler este artigo e bom aprendizado!