Como analisar uma série cronológica em SQL

Os valores ordenados pelo tempo são chamados de série cronológica. Neste artigo, vou lhe mostrar os padrões mais freqüentes e lhe ensinar como escrever consultas para séries cronológicas em SQL com a ajuda das funções de janela.

Talvez você tenha tido a oportunidade de analisar algumas variáveis onde cada valor estava associado a um valor de tempo. Tais dados - onde os valores são ordenados pelo tempo - são chamados de dados de séries cronológicas. Por ser tão freqüentemente usado nos negócios, é importante aprender os padrões comuns usados para analisar este tipo de dados.

Exemplos de dados de séries cronológicas

Dados de séries temporais são variáveis com um componente adicional - tempo. Isto significa que cada valor de um atributo está associado a um valor de data ou hora. Aqui estão alguns exemplos de dados de séries cronológicas:

• O preço diário das ações de uma determinada corporação no ano passado. (Cada preço de ação está associado a um dia específico).
• A média diária do índice Dow Jones para os últimos 10 anos. (Cada valor do índice está associado a um dia específico).
• Visitas únicas a um website durante um mês. (A cada mês, o número de visitas é diferente).
• Os usuários registrados de um website para cada dia.
• Números de vendas semanais.
• Receitas e despesas anuais da empresa ao longo de uma década. (O ano é o valor do tempo).
• Logins diários ao longo de dois meses. (O dia é o valor de tempo).

Neste artigo, analisaremos a popularidade de dois sites fictícios através de uma medida chamada "número total diário de visitas". As consultas SQL que discutiremos podem ser utilizadas para outras análises de séries temporais; ou seja, são aplicáveis a outros conjuntos de dados de séries temporais.

Observaremos o período de 01 de julho de 2019 até 31 de dezembro de 2019.

Aqui estão os dados:

tabela_de_visitas diárias

Esta tabela é denominada daily_visits e contém os seguintes atributos:

• data - Qualquer dia entre 01 de julho e 31 de dezembro de 2019.
• visitas - O número total de visitas ao site em uma data específica.
• fim de semana - Este valor é 'N' se a data for um dia da semana e 'Y' se for um sábado ou domingo.
• website - O nome de domínio do website ('www.sqlanalysts.com' ou 'www.sqldevelopers.com').

Como você pode notar, cada fileira em nosso daily_visits tabela (ou seja, cada valor do atributo visitas ) está associado a um dia (o atributo data ). Este é um exemplo de dados de séries cronológicas.

Totais de execução com SQL Funções de Janela (Window Functions) em SQL

Iniciaremos nossa análise utilizando um padrão chamado total em execução. Um total corrente é a soma cumulativa de todos os números anteriores em uma coluna.

Abaixo está um total corrente para o número de visitas de 01 de julho a um dia específico. Note que isto é calculado para cada site:

Na imagem acima, você pode ver como em 01 de julho o total corrido é de 2.805. (É igual ao número de visitas naquele dia.) Isto porque não há dados para as datas anteriores a 01 de julho; estamos iniciando o cálculo a partir desta data.

No dia seguinte (02 de julho), o valor da linha anterior é adicionado ao número atual de visitas. O total atual para este dia é 7.203 - o número de visitas em 01 de julho mais o número de visitas em 02 de julho. No dia seguinte, 03 de julho, adicionamos esse número (6.744) ao total anterior (7.203) e obtemos 13.947. E assim por diante.

Este é apenas um exemplo de negócio onde o padrão total atual é usado; alguns outros exemplos incluem o:

• Número total de itens vendidos desde o primeiro dia de um mês até o dia observado no mesmo mês.
• Total corrente (soma acumulada) das transações de débito/crédito de uma conta bancária no último trimestre ou ano.
• Total da receita acumulada desde janeiro até o mês observado no mesmo ano

Agora que sabemos o que é um total em execução, vamos escrever uma consulta SQL que calcula um. Um total em execução (ou soma cumulativa) pode ser calculado em SQL usando as funções apropriadas da janela.

As funções de janela são funções SQL especiais que funcionam através de um conjunto de linhas. Elas são semelhantes à cláusula GROUP BY, mas o resultado é exibido de forma diferente no final. Em um simples GROUP BY, as linhas são colapsadas (cada grupo é mostrado como uma linha). Com funções de janela, as linhas não são colapsadas; cada linha é retornada e o valor do cálculo é atribuído a cada linha na tabela. Isto é exatamente o que precisamos ao exibir o total em execução - um valor adicional em uma tabela existente.

Abaixo está uma consulta SQL que calcula o total em execução sobre o daily_visit dados :

SELECT
   *, 
  SUM(visits) OVER (PARTITION BY website ORDER BY date) AS running_total 
FROM daily_visits;

Quando você executa esta consulta, todas as linhas são exibidas e uma coluna adicional, run_total, é criada. Aqui está uma breve explicação do que está ocorrendo:

• A cláusula OVER, com PARTITION BY, diz ao SQL que esta será uma função de janela.
• A PARTITION BY divide as linhas em grupos lógicos. Em nosso exemplo, as linhas são agrupadas no nível do site.
• O ORDER BY estabelece a ordem das linhas. Em nosso exemplo, todas as linhas são ordenadas pela coluna de datas.
• ROW/RANGE não está explicitamente definido neste exemplo. Estas palavras-chave opcionais significam que cada quadro de janela dentro de uma partição compreende todas as linhas desde o início da partição até a linha atual. Em outras palavras, para cada linha, a soma total é calculada como a soma dos valores desde a primeira linha na partição até a linha atual.

Com esta instrução SQL (OVER em combinação com PARTITION BY e ORDER BY), configuramos um total de visitas em funcionamento no nível do site.

Não vou mergulhar fundo nesta explicação e sintaxe aqui. Se você quiser aprender as funções da janela de uma maneira mais detalhada, recomendo a LearnSQL.com.br curso sobre funções de janela. Confira o artigo que acompanha, Curso SQL do Mês - Funções de Janela (Window Functions) em SQL; ele responde perguntas como por que você deve aprender funções de janela, quais são, e por que este curso é uma grande escolha.

As funções de janela são usadas em outros cálculos de padrões. Na próxima seção, vou dar uma olhada na folha de consultas de janela e mostrar como escrever consultas SQL para mudança percentual e médias móveis.

Porcentagem de mudança nas visitas diárias ao site

Você precisará muitas vezes descrever como seu negócio muda com o tempo. Há outro padrão muito comum usado na análise de séries temporais chamado "porcentagem de mudança" (ou porcentagem/percentagem de mudança). Ele responde perguntas como:

• Como a receita deste mês se compara à do mês passado? Ela aumentou ou diminuiu?
• O número de usuários registrados em nosso site aumentou ou diminuiu neste trimestre?
• Eu vendi mais itens hoje do que há uma semana atrás?
• Nossas vendas estão aumentando ou diminuindo em comparação com o ano passado?

A seguir, vou mostrar como usar SQL para calcular as mudanças percentuais. No exemplo a seguir, encontraremos o percentual de mudança no número total de visitas ao site (hoje em comparação com ontem e hoje em comparação com uma semana atrás). Uma vez que você aprenda como fazer mudanças percentuais com este exemplo, você pode aplicá-lo a qualquer outro caso de negócios. A estrutura da consulta é a mesma; apenas os nomes das tabelas e colunas - e talvez um argumento LAG(), que explicarei em um segundo - serão diferentes.

Usando a função LAG() para recuperar o valor de uma linha anterior

O primeiro passo para calcular o percentual de mudança é recuperar um valor de uma linha anterior. Por que precisamos disto? Porque a porcentagem de mudança é calculada pela fórmula:

(current_value - previous_value)/previous value * 100.

Esta fórmula significa que para calcular o aumento ou a diminuição do número de visitas, é necessário ter os dois valores apresentados na mesma linha.

Portanto, nossa primeira tarefa ao calcular este padrão é recuperar um valor de uma fileira anterior. Esta tarefa pode ser feita com a ajuda da função da janela LAG(). Ela fornece acesso a um valor em uma determinada linha que vem antes da linha atual. Abaixo está uma declaração SELECT que atribui o valor das_visitas_diárias da linha anterior a uma nova coluna(visitas_diárias_anteriores) na linha atual:

SELECT
  *,
  LAG(visits) OVER(PARTITION BY website ORDER BY date) AS previous_day_visits
FROM daily_visits;

Esta é uma função típica de janela: dentro da cláusula OVER, você define a partição e ordenação desejada. A função LAG() pega um argumento (o nome da coluna com os valores desejados) e atribui o valor da linha anterior a cada linha:

LAG() também pode ser usado para atribuir valores de n linhas de volta, não apenas a linha anterior. Por exemplo, suponha que você queira calcular a mudança percentual para o mesmo dia da semana passada. Nesse caso, você precisará atribuir um valor de sete dias atrás para cada linha.

Para fazer isso, usamos o parâmetro opcional de offset do LAG. Confira a seguinte consulta:

SELECT
  *,
  LAG(visits,7) OVER(PARTITION BY website ORDER BY date) AS previous_day_visits
FROM daily_visits;

O valor padrão de offset do LAG é 1 (a linha anterior), mas você pode alterá-lo para qualquer outro valor. Em nosso exemplo, usamos um valor de 7, que atribui a cada linha o número de visitas de 7 dias atrás:

aumento/diminuição de 1 dia no número total de visitas

Agora podemos facilmente calcular um aumento/diminuição de 1 dia com este código SQL:

WITH daily_visits_lag AS (
 SELECT
  *,
  LAG(visits) OVER(PARTITION BY website ORDER BY date) AS previous_day_visits
  FROM daily_visits
)
SELECT
    *,
   COALESCE(round((visits — previous_day_visits)/previous_day_visits *100),0) AS percent_change
FROM daily_visits_lag;

Esta afirmação parece complicada, mas realmente não é. Aqui está o que está acontecendo:

• O SELECT, que atribui a cada linha um valor da linha anterior, está escrito dentro de uma declaração WITH. Isto significa que estamos usando uma expressão de tabela comum ou CTE (ou seja, um conjunto de resultados temporários nomeado). Utilizamos então este resultado temporário no SELECT subseqüente.
• O conjunto de resultados temporários é chamado de daily_visits_lag. Ele contém valores necessários para o cálculo da mudança percentual (ou seja, o número de visitas para a linha atual e o número de visitas do dia anterior).
• Odesfasamento_diáriode_visitas é usado na_consulta principal. Uma coluna adicional, porcentagem_de_versão, é calculada em COALESCE() com a fórmula(visitas_visitas_do_dia_anteriores)/visitas_do_dia_anteriores * 100.
• Após esta declaração ser executada, o mecanismo SQL deixa de lado o conjunto de resultados temporários; ele não pode ser usado mais no código. (É assim que um CTE funciona).

Não há espaço suficiente para entrar em CTEs aqui, mas nosso Consultas Recursivas curso é um bom recurso para aprender mais sobre o uso e a sintaxe do CTE.

Após a execução desta consulta, os seguintes valores são exibidos:

aumento/diminuição de 7 dias no número total de visitas

Agora que você sabe como calcular uma mudança percentual de 1 dia, uma declaração muito semelhante pode ser usada para calcular uma diminuição/aumento de 7 dias no número total de visitas:

WITH daily_visits_lag AS (
 SELECT
  *,
  LAG(visits,7) OVER(PARTITION BY website ORDER BY date) AS previous_7day_visits
  FROM daily_visits
)
SELECT
    *,
   COALESCE(round((visits — previous_7day_visits)/previous_7day_visits *100),0) AS percent_change
FROM daily_visits_lag;

A única diferença aqui é que usamos LAG() com um parâmetro de compensação de 7 - estamos recuperando valores de visitas de 7 dias antes (uma semana atrás) e calculando o percentual de mudança com esses valores (dia atual vs. uma semana antes). Em outras palavras, estamos calculando o aumento ou diminuição do número de visitas de 7 dias:

Tenha em mente que a consulta que usamos aqui pode ser usada em outros exemplos de negócios. Basta ajustar os nomes da tabela e das colunas; o restante pode permanecer o mesmo.

Médias de movimentação simples: 7 dias

Outro padrão muito utilizado na análise de séries temporais é chamado de uma média móvel simples (SMA). Uma SMA é a média não ponderada dos valores n de linha anteriores; ela é calculada para cada valor em uma determinada coluna.

As SMAs são freqüentemente usadas ao determinar tendências no preço das ações ou na análise da moeda criptográfica. Estas informações nos ajudam a entender o comportamento de nossa variável: em vez de apenas um valor, obtemos melhores estimativas usando os valores médios de uma medida específica. Estamos suavizando as flutuações para obter uma visão geral.

Em nosso site exemplo, cada dia teremos dois valores de interesse:

• O número de visitas que aconteceram naquele dia específico
• O número médio de visitas durante os últimos 7 dias.

Mais uma vez, usaremos uma função de janela SQL para calcular nosso SMA. Ao contrário do exemplo anterior, onde usamos LAG() para recuperar valores de linhas anteriores, aqui usaremos o parâmetro ROW/RANGE dentro da cláusula OVER:

SELECT
   *, 
   AVG(visits) OVER(PARTITION BY website ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as SMA7
FROM daily_visits ;

Anteriormente no artigo, dissemos que ROW dentro de uma cláusula OVER define uma janela dentro de cada partição. Quando calculamos o total em execução, usamos os valores padrão para os parâmetros de linha/largura. Isto significa que o ponto inicial para cada janela dentro da partição era a primeira linha naquela partição e o ponto final era a linha atual.

Agora, cada janela é definida como 7 linhas (os 6 valores da linha anterior + o valor da linha atual). Fizemos isso com ROWS BETWEEN 6 PRECEDING AND CURRENT ROW. Em outras palavras, o ponto de partida não é a primeira linha na partição. Aqui, o valor médio(avg(visitas)) será calculado nas últimas 7 fileiras, incluindo a atual. Esta é a definição de um SMA.

Uma vez executada esta declaração, você obterá um valor médio associado a cada linha. Isto permitirá que você observe as tendências das visitas de uma maneira mais detalhada. Aqui está o resultado:

As funções de janela SQL são uma característica realmente poderosa para análise de tendências, e o SMA é apenas um dos indicadores que podem ser obtidos com as funções de janela. Se você gostaria de ver mais exemplos relacionados à análise de tendências, experimente nossa Análise de Tendências de Receita no curso SQL. Ele discute como você pode usar SQL para analisar qualquer tendência de série temporal.

Usando RANK() para encontrar o maior número de visitas

Nosso padrão de última análise é o ranking. Como você pode adivinhar, esta ordem resulta com base em uma determinada variável. Suponhamos que queremos ver quais datas tiveram o maior número de visitas para cada um de nossos websites. Para fazer isso, precisamos classificar nossas visitas diárias para cada website separadamente. Podemos fazer isso usando a função de janela RANK():

SELECT *,
  RANK() OVER (PARTITION by website ORDER BY visits DESC) AS rank 
FROM daily_visits;

Aqui está o que está acontecendo neste código:

• O ranking é determinado pela coluna de visitas. Isto é definido no ORDER BY, que está na cláusula OVER). A linha com o maior valor de visitas recebe o maior ranking.
• As linhas (visitas) são agrupadas separadamente para cada website, de modo que a classificação para cada website é determinada individualmente. Isto é definido em PARTITION BY, na cláusula OVER.
• Linhas com critérios de classificação iguais (ou seja, com o mesmo valor de visitas ) recebem a mesma classificação.

Uma vez executado este SELECT, o mecanismo SQL retorna um conjunto de resultados com uma coluna adicional chamada Rank. Agora podemos ver facilmente quais dias tiveram o maior número de visitas. O ótimo é que o ranking é definido para cada site, portanto, não estamos comparando os dois sites juntos.

A classificação mais alta para sqldevelopers.com

A mais alta posição para sqlanalysts.com

Saiba mais sobre a série de análise de tempo com SQL

Assim, agora você pode usar as funções de janela SQL para fazer algumas análises básicas de séries cronológicas. Você sabe o que são séries cronológicas e como você pode usar as funções de janela SQL para obter alguns insights muito bons. Você até já foi apresentado aos CTEs.

O que vem a seguir? Eu definitivamente recomendo uma abordagem organizada para aprender mais sobre a análise de séries temporais SQL. O Funções de Janela (Window Functions) em SQL e Análise de Tendências de Receita nos cursos de SQL que já mencionei são bons. Lembre-se, é importante colocar suas habilidades em prática, então certifique-se de que você está recebendo alguns exercícios do mundo real para resolver!