O que é uma média móvel e como calculá-la em SQL

Quer mergulhar profundamente nos dados das séries temporais e examinar as tendências de longo prazo? Deseja saber o que é uma média móvel e como calculá-la em SQL? Então este artigo é para você. Vou explicar uma característica poderosa em SQL chamada funções de janela e mostrar como você pode calcular médias móveis usando-as.

A melhor maneira de aprender sobre as funções da janela SQL é a janela interativa Funções de Janela (Window Functions) em SQL curso em LearnSQL.com.br. Ele contém mais de 200 exercícios práticos para ajudá-lo a ganhar confiança em suas habilidades SQL. E este curso é apenas um dos mais de 30 cursos interativos de SQL em vários níveis de dificuldade que oferecemos. Inscreva-se agora de graça!

O que é uma média móvel?

A média móvel é uma técnica de série temporal para analisar e determinar as tendências dos dados. Às vezes chamadas de médias móveis, médias móveis, ou médias de execução, são calculadas como a média da corrente e um número especificado de valores imediatamente anteriores para cada ponto no tempo. A idéia principal é examinar como essas médias se comportam ao longo do tempo em vez de examinar o comportamento dos pontos de dados originais ou brutos.

Trabalhar com médias móveis nos dá uma melhor representação das séries temporais, uma vez que as tendências de longo prazo são muito mais fáceis de ver com médias móveis do que com pontos de dados brutos. Como as médias móveis são freqüentemente usadas na análise financeira, usarei os dados para os preços diários das ações de uma determinada empresa como um exemplo para explicar o que são.

Abaixo está a tabela com o nome stock_price que usaremos neste artigo:

Nesta tabela, temos duas colunas (date e price). Trata-se de um conjunto de dados de séries temporais, uma vez que cada preço de ação está associado a um ponto específico no tempo; ou seja, o preço para cada dia é armazenado nesta tabela.

O gráfico de linhas destes preços ao longo do tempo se parece com isto:

Podemos colher algumas coisas da tabela e do gráfico antes mesmo de fazer qualquer cálculo:

• Os preços em nossa tabela stock_price são para o período entre 7 de janeiro de 2020 e 30 de junho de 2020, inclusive.
• Os preços variam aproximadamente de 1.000 a 1.400 (ver o eixo y).
• Há flutuações nos preços; há alguns picos em fevereiro, vários picos em junho, etc.

Vamos calcular uma média móvel para a coluna price e gerar um gráfico de linhas das médias para ver o que acontece. Para este exemplo, vamos trabalhar com uma média móvel de três dias. Para isso, calculamos a média dos preços das ações de três dias consecutivos - o dia em questão e os dois dias anteriores - e depois repetimos o mesmo para cada dia no conjunto de dados. Esta é uma média móvel de três dias, porque calculamos a média ao longo de um período de três dias.

Eis como é calculada uma média móvel de três dias para o dia 9 de janeiro de 2020:

Para 9 de janeiro de 2020, a média móvel de três dias é calculada como a média dos preços daquele dia (1.300) e dos dois dias anteriores: 8 de janeiro (1.300) e 7 de janeiro (1.320). Portanto, a média móvel para 9 de janeiro de 2020 é a média desses três valores, ou 1.306,66, como mostrado na imagem acima.

A média móvel é calculada da mesma forma para cada uma das datas restantes, totalizando os três preços das ações da data em questão e os dois dias anteriores, dividindo este total por 3. Para 30 de junho, a média móvel de três dias é 1.070, a média dos preços das datas de 30 de junho (1.067), 29 de junho (1.067), e 28 de junho (1.076).

Se traçarmos os pontos de dados originais e a média móvel em um gráfico de linha, obtemos o seguinte:

A linha vermelha representa a média móvel, e a linha azul representa os pontos de dados originais. Você pode notar que a linha vermelha é mais suave e não tem picos vistos na linha azul. Este alisamento é o objetivo principal da técnica de média móvel?-?é usado para remover o ruído dos dados. Com menos ruído, as tendências reais dos dados das séries temporais são mais fáceis de ver.

Neste exemplo, calculamos a média móvel de três dias. Entretanto, podemos calculá-la em qualquer período que quisermos, como a média móvel de sete dias, a média móvel de dez dias, e assim por diante.

Por que e onde são utilizadas as médias móveis

As médias móveis são amplamente utilizadas no comércio financeiro e técnico, como na análise de preços de ações, para examinar as tendências de curto e longo prazo. Se o preço das ações permanecer acima da média móvel, então temos uma tendência de alta; se permanecer abaixo, então os comerciantes dizem que estamos em uma tendência de baixa. Sinais tais como tendências de alta e baixa informam os comerciantes ao tomar decisões sobre a compra ou venda de ações.

Dito isto, a análise do preço das ações não é o único caso de uso para a média móvel. Outras aplicações comerciais incluem:

• Análise de vendas: As médias móveis suavizam flutuações e picos nas vendas semanais ou diárias.
• Análise de casos confirmados de COVID-19: As médias móveis ajudam a mostrar como o número de casos confirmados está mudando ao longo do tempo.
• Análise do tráfego web: As médias móveis nos ajudam a ver as tendências de longo prazo no número de visitas e visualizações de páginas.
• Finanças pessoais: As médias móveis ajudam a mostrar tendências em nossos gastos (por exemplo, olhando para as médias de dez dias nos últimos 2 anos).

Médias móveis em SQL

Agora que você sabe o que é uma média móvel, vamos ver como calculá-la. Em SQL, é fácil com as funções de janela, um recurso especial de SQL que permite fazer agregações através das linhas.

Embora semelhante a GROUP BY, as funções de janela mantêm todas as linhas ao exibir o resultado; não há colapso de linhas. Em vez disso, com as funções de janela, definimos um quadro ou uma "janela" de linhas de um determinado tamanho ao redor da linha atual e, em seguida, fazemos alguns cálculos através daquela janela. Assim, é feita uma agregação para cada linha em uma tabela; cada linha tem sua própria janela sobre a qual é feito um cálculo.

Abaixo está como a coluna moving_average de nosso exemplo é calculada em SQL:

select *,
  avg(Price) OVER(ORDER BY Date 
     ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) 
     as moving_average 
from stock_price;

Para explicar o código em detalhes:

• Usamos uma função de janela, denotada com uma cláusula OVER. Como explicado anteriormente, as linhas não são colapsadas, e cada linha tem sua própria janela sobre a qual um cálculo é realizado.
• O tamanho da janela em nosso exemplo é três. Para cada linha dada, tomamos a própria linha e as duas linhas anteriores, e calculamos o preço médio a partir dessas três linhas. Isto é denotado pela palavra-chave ROW na declaração: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW. Esta declaração diz que, para cada linha da tabela, algo é calculado como uma agregação da linha atual e das duas linhas anteriores. Isto significa que a média móvel para cada linha é calculada como o preço médio do dia determinado e dos dois dias anteriores.
• Temos um quadro de janela diferente para cada dia. Abaixo, você pode ver uma ilustração do quadro de janela usado para a linha correspondente ao dia 9 de janeiro (em verde) e o quadro de janela usado para a linha correspondente ao dia 27 de junho (em azul):
• É importante que os dados não tenham nenhuma lacuna nas datas. Para cada dia, precisamos calcular a média dos preços daquele dia e dos dois dias anteriores. Se houver falta de datas nos dados, esta análise não fará sentido.
• A palavra-chave ORDER BY dentro da cláusula OVER define a ordem das filas sobre as quais a média móvel deve ser calculada. Em nosso exemplo, as linhas são primeiro ordenadas pela coluna de datas, depois é definida a moldura da janela, e o cálculo é realizado.
• Para este exemplo, não usamos a palavra-chave PARTITION BY na cláusula OVER. PARTITION BY agrupa as linhas em pedaços lógicos por alguma categoria, mas não estamos agrupando as linhas dessa forma aqui. Com efeito, todo o nosso conjunto de dados é apenas uma grande partição. Mais adiante neste artigo, veremos um exemplo com um PARTITION BY.

Então, agora você sabe como calcular as médias móveis em SQL! O código SQL acima pode ser usado em muitos outros cenários empresariais; basta substituir a tabela e os nomes das colunas e ajustar para o número de linhas para as quais você deseja calcular as médias. Tudo o resto pode permanecer o mesmo.

As funções de janela têm uma sintaxe específica, e leva algum tempo e prática para se familiarizar com o uso. Para aprender e praticar mais, recomendo o curso de funções de janela em LearnSQL.com.br. É interativo com muitos exercícios, dando a você a oportunidade de praticar e aprender os novos materiais rapidamente, porque você aprende fazendo! Para mais informações sobre o conteúdo do curso e as próprias funções da janela, você pode ler o artigo Curso SQL do Mês: Funções de Janela (Window Functions) em SQL. Na mesma plataforma, você também pode encontrar grandes artigos sobre quando usar as funções de janela e explorar alguns exemplos.

Média móvel ao longo de um número específico de dias

No exemplo anterior, calculamos uma média móvel de três dias. Você também pode calcular outras médias, tomando qualquer número de valores anteriores que desejar. Quanto maior o número de valores anteriores, mais suave será a nossa curva. Quanto menor o número de linhas utilizadas para calcular as médias, mais próximo o gráfico da média móvel estará do dos valores originais. Há uma enorme diferença entre, por exemplo, médias móveis de dois dias e médias móveis de 30 dias.

Antes de criarmos um visual para provar isto, vamos calcular estas duas médias móveis:

select *,
  avg(Price) OVER(ORDER BY Date 
      ROWS BETWEEN 1 PRECEDING AND CURRENT ROW ) 
     as 2day_moving_average,
  avg(Price) OVER(ORDER BY Date 
      ROWS BETWEEN 29 PRECEDING AND CURRENT ROW ) 
      as 30day_moving_average
from stock_price;

E agora podemos visualizar como as duas linhas diferem:

A linha verde representa a média móvel de 30 dias (30 preços de ações são usados no cálculo de cada média), e a linha vermelha representa a média de dois dias. A linha vermelha se parece quase como a linha azul dos pontos de dados originais. A linha verde é mais suave e mais fácil de ver as tendências a longo prazo.

Agora você pode estar se perguntando, como você sabe o número certo de linhas a serem tomadas? Bem, eu não posso ajudá-lo nisso.... depende realmente das necessidades e da situação do próprio negócio e porque você está analisando a métrica em primeiro lugar.

Exemplo: Média móvel de sete dias de casos de COVID

Agora vamos praticar um pouco mais para recapitular o que aprendemos até agora. A COVID-19 ainda é muito real em nossas vidas, portanto calcularemos a média móvel de sete dias para o número total de casos confirmados por país. O número de casos confirmados para cada dia de cada país é armazenado em uma tabela chamada confirmed_covid, na coluna confirmed_day:

Para calcular a média móvel de sete dias do número de casos confirmados, podemos fazer o seguinte:

SELECT *,
      avg(confirmed_day) OVER(
          PARTITION BY country 
          ORDER BY date 
          ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) 
          AS 7day_moving_average
FROM confirmed_covid;

Você deve ter notado que este código se parece exatamente com o que escrevemos para a média móvel do preço das ações. A única diferença chave é que temos aqui partições?-?cada país é uma partição, porque estamos calculando as médias de sete dias separadamente para cada país. Caso contrário, apenas a tabela e os nomes das colunas são diferentes. Você pode facilmente adaptar este código a qualquer outro cenário de negócios.

Uma vez executado este código e calculada a média móvel, podemos gerar o gráfico com a linha mostrando a tendência para o país específico, desde o início da pandemia até o mês de julho. Abaixo está o gráfico para o país da Croácia:

Novamente, vemos como a média móvel se suaviza e reduz os picos e flutuações em relação aos pontos de dados originais.

Você pode usar outras funções de janela para obter insights poderosos a partir dos dados da COVID-19. Se você estiver interessado em mais, confira nosso recente artigo sobre como analisar os dados da COVID-19 usando funções de janela.

Médias móveis e Funções de Janela (Window Functions) em SQL em SQL

Neste artigo, vimos como você pode calcular médias móveis em SQL usando funções de janela. Estas médias nos ajudam a ver melhor as tendências reais, reduzindo a quantidade de ruído.

Se você gostaria de praticar e aprender mais sobre funções de janela, recomendo o curso interativo online sobre funções de janela publicado por LearnSQL.com.br. Este curso contém mais de 200 exercícios, portanto, você definitivamente colocará em prática sua habilidade recém aprendida. E acredite em mim - quando se trata de SQL e habilidades de codificação, é importante praticar - você aprende fazendo!