Como calcular as diferenças de ano para ano em SQL

Como sua empresa se saiu este ano em comparação com o ano passado? No ano anterior? Saiba como usar SQL para calcular as diferenças ano a ano e mês a mês.

Comparações ano-a-ano (YOY) são uma forma popular e eficaz de avaliar o desempenho de vários tipos de organizações. As diferenças ano a ano são fáceis de entender - por exemplo, é fácil entender que seu crescimento de receita é de 35% ano a ano. Entretanto, calcular esta métrica em SQL não é tão fácil!

Neste artigo, vamos aprender como calcular as diferenças ano a ano e mês a mês usando SQL.

Você está pronto para investir em suas habilidades em SQL? Vamos lá!

O que são métricas do ano-a-ano do ponto de vista empresarial?

Para saber se uma organização empresarial está tendo um bom desempenho, geralmente precisamos fazer uma comparação baseada em métricas empresariais. Às vezes podemos comparar com empresas similares ou com a média de várias empresas no mesmo mercado. Entretanto, um indicador-chave de desempenho é o crescimento periódico: a comparação dos resultados atuais de sua empresa com a mesma métrica de um período de tempo anterior. Esta é a razão das comparações ano a ano: Você pode facilmente ver se sua organização está indo melhor (ou pior) do que no ano passado. Além disso, as comparações ano a ano podem ser aplicadas a diferentes métricas (por exemplo, vendas, lucros, número de clientes) para entender melhor como diferentes indicadores comerciais estão evoluindo.

Na tabela a seguir, podemos ver os resultados para 2019 e 2020:

Se adicionarmos uma terceira coluna chamada YOY, podemos ver facilmente a variação de ano para ano. Isto nos permite entender melhor como nossa organização se comportou no último ano e que áreas podem ser melhoradas:

Neste artigo, explicaremos como podemos calcular os valores na coluna JUVENTUDE. Vamos começar mostrando a tabela base, onde temos as métricas para 2019 e 2020. Na próxima imagem, podemos ver estes valores na tabela de yearly_metrics mesa.

Na próxima seção, explicaremos como consultar a yearly_metrics tabela usando SQL para obter os valores na coluna YOY. Antes disso, eu gostaria de sugerir LearnSQL.com.br's Funções de Janela (Window Functions) em SQL curso, onde você pode aprender os conceitos básicos das funções da janela SQL. Além disso, o artigo Quando uso as SQL Funções de Janela (Window Functions) em SQL fornece muitos exemplos de consultas que você pode achar úteis.

Como calcular a métrica YOY com SQL

As funções de janela são uma característica SQL muito poderosa. Elas retornam o resultado da aplicação de uma função (como MAX(), AVG() ou COUNT()) a um conjunto de registros (que é chamado de "janela") em uma tabela. O conjunto de registros é definido pela cláusula OVER(); esta cláusula é obrigatória para a função window. Você também pode ordenar os registros na janela por critérios diferentes e depois usar funções como FIRST_VALUE(), LAST_VALUE(), LEAD(), ou LAG() para retornar o valor de registros específicos em relação ao registro atual.

Se você quiser rever os fundamentos das funções de janela, sugiro o artigo O que é a cláusula OVER? A compreensão das funções de janela SQL e da cláusula OVER o ajudará com os conceitos que discutiremos neste artigo.

Agora, vamos ver um exemplo de uma consulta SQL que retorna a receita para cada ano e seu ano anterior:

SELECT year, 
       revenue, 
       LAG(revenue) OVER ( ORDER BY year ) AS Revenue_Previous_Year
FROM   yearly_metrics

Neste exemplo, estamos usando a função de janela LAG() para obter o valor da receita da coluna para o registro anterior. Em outras palavras, se o registro atual for para 2020, LAG(revenue) retornará o valor da coluna de receita para o ano de 2019. Os resultados desta consulta são:

O próximo passo para obter o valor YOY da receita é simples; só precisamos calcular a diferença entre a receita para 2020 e a receita para 2019. Eis como faríamos isso:

SELECT year, 
       revenue, 
       LAG(revenue) OVER ( ORDER BY year ) AS Revenue_Previous_Year
       revenue - LAG(revenue) OVER ( ORDER BY year ) AS YOY_Difference
FROM   yearly_metrics

Calculamos o YOY Difference fazendo uma diferença aritmética entre a receita de 2020 e a receita de 2019. No resultado a seguir, podemos ver que o registro para 2019 não tem um valor YOY Difference porque não temos dados para 2018. Além disso, o registro para 2021 não tem um valor consistente em YOY Difference, porque os dados para 2021 não estão completos.

Antes de ir para a próxima seção, gostaria de sugerir o artigo Como calcular a diferença entre duas linhas em SQL, que vai mais fundo no cálculo das diferenças usando LAG() e LEAD().

Cálculo das diferenças de mês a mês e de trimestre a trimestre

Na seção anterior, não foi possível calcular um valor consistente para o YOY Difference para 2021 porque não temos resultados completos para 2021. Mas para ver como uma empresa está se saindo no ano corrente, deveríamos ter algumas métricas descrevendo seu desempenho. Bons indicadores-chave de desempenho podem ser comparações mês a mês ou trimestre a trimestre (ou seja, comparando o primeiro trimestre de 2021 com o primeiro trimestre de 2020).

Antes de calcular as diferenças mês a mês ou trimestre a trimestre, vejamos o daily_metrics mesa. Ela tem um registro para cada dia que descreve a receita, custos e número de novos clientes para aquele dia.

A seguir, vamos calcular o CTE monthly_metrics (um CTE é semelhante a uma visão, mas é criado durante a execução da consulta). Este tem um esquema semelhante ao yearly_metrics tabela utilizada anteriormente. A monthly_metrics criação SELECT é mostrada em vermelho; em azul, podemos ver a consulta SQL que utiliza este CTE como uma tabela regular.

WITH monthly_metrics AS (
 SELECT 
   extract(year from day) as year,
   extract(month from day) as month,
   SUM(revenue) as revenue
 FROM daily_metrics 
 GROUP BY year, month 
)
SELECT 
  year, month, revenue,
  LAG(revenue) OVER (ORDER BY year, month) as Revenue_previous_month,
  revenue - LAG(revenue) OVER (ORDER BY year, month) as Month_to_month_difference
FROM monthly_metrics
ORDER BY 1,2;

In the above query, we first generate the monthly_metrics CTE using a SELECT that extracts the year and month from the day column. Then grouping by year and month, we calculate the SUM of revenue for each month. The CTE monthly_metrics works like a regular table with the columns year, month, and revenue; when the query ends, the CTE is destroyed. You can compute the quarter-to-quarter difference in a similar way.

After that (in blue), we obtain the revenue for the previous month using the LAG() window function. Then we calculate the difference between each month and the previous month. Here’s a partial view of the result:

There is another way to calculate a month-over-month difference. Instead of comparing against the previous month, we can compare against the same month in the previous year. To make this comparison, we need to use the LAG() function’s optional offset parameter, as we can see in the following query:

WITH monthly_metrics AS (
SELECT EXTRACT(year from day) as year,
	 EXTRACT(month from day) as month,
       SUM(revenue) as revenue
  FROM daily_metrics 
  GROUP BY 1,2
)
SELECT year AS current_year, 
       month AS current_month, 
       revenue AS revenue_current_month, 
       LAG(year,12) OVER ( ORDER BY year, month) AS previous_year, 
       LAG(month,12) OVER ( ORDER BY year, month) AS month_comparing_with,
       LAG(revenue,12) OVER ( ORDER BY year, month) AS revenue_12_months_ago,
       revenue - LAG(revenue,12) OVER (ORDER BY year, month) AS month_to_month_difference
FROM monthly_metrics
ORDER BY 1,2;

Acima, usamos a função LAG() com o parâmetro de compensação opcional, o que nos permite obter um valor de coluna a partir de uma posição N recorde antes do registro atual. Se utilizarmos um offset de 12, obteremos o registro para o mesmo mês, mas no ano anterior. Abaixo, podemos ver o resultado:

E é isso! Agora você sabe como encontrar diferenças mês a mês, trimestre a trimestre, e ano a ano com funções de janela SQL. Eu realmente sugeriria o curso Funções de Janela (Window Functions) em SQL como uma boa introdução ao trabalho com estas funções. Se você quiser saber mais, confira este artigo descrevendo o curso Funções de Janela (Window Functions) em SQL .

Qual é o próximo?

As funções de janela são um recurso SQL chave. Neste artigo, utilizamos a função de janela LAG() para calcular as diferenças ano a ano e mês a mês. Na verdade, podemos usá-la para calcular a diferença entre qualquer período de tempo - trimestres, meio ano, meses, ou semanas.

Neste artigo, nos concentramos nas diferenças entre períodos de tempo, mas as funções de janela podem ser aplicadas para resolver muitos tipos diferentes de problemas de dados. Por último, mas não menos importante, gostaria de sugerir nossa folha de consulta Funções de Janela (Window Functions) em SQL , que coloquei em uma prancha de cortiça na frente de minha mesa. Invista em você, e aumente suas habilidades SQL!