Cómo calcular las diferencias interanuales en SQL

¿Cómo le fue a su empresa este año en comparación con el año pasado? ¿El año anterior? Descubra cómo utilizar SQL para calcular las diferencias interanuales y mensuales.

Las comparaciones interanuales (YOY) son una forma popular y eficaz de evaluar el rendimiento de varios tipos de organizaciones. Las diferencias año a año son fáciles de entender - por ejemplo, es fácil entender que el crecimiento de sus ingresos es del 35% año a año. Sin embargo, calcular esta métrica en SQL no es tan fácil.

En este artículo, vamos a aprender a calcular las diferencias de año a año y de mes a mes utilizando SQL.

¿Estás preparado para invertir en tus conocimientos de SQL? ¡Vamos allá!

¿Qué son las métricas interanuales desde el punto de vista empresarial?

Para saber si una organización empresarial se está desempeñando bien, por lo general necesitamos hacer una comparación basada en métricas empresariales. A veces podemos comparar con empresas similares o con la media de varias empresas del mismo mercado. Sin embargo, un indicador clave de rendimiento es el crecimiento periódico: la comparación de los resultados actuales de la empresa con la misma métrica de un periodo anterior. Esta es la razón de las comparaciones interanuales: Puede ver fácilmente si su organización lo está haciendo mejor (o peor) que el año pasado. Además, las comparaciones interanuales pueden aplicarse a diferentes métricas (por ejemplo, ventas, beneficios, número de clientes) para comprender mejor la evolución de los distintos indicadores empresariales.

En la siguiente tabla, podemos ver los resultados de 2019 y 2020:

Si añadimos una tercera columna llamada YOY, podemos ver fácilmente la variación de un año a otro. Esto nos permite entender mejor cómo se ha comportado nuestra organización en el último año y qué áreas se pueden mejorar:

En este artículo, explicaremos cómo podemos calcular los valores de la columna YOY. Empecemos mostrando la tabla base, donde tenemos las métricas de 2019 y 2020. En la siguiente imagen, podemos ver estos valores en la yearly_metrics tabla.

En la siguiente sección, explicaremos cómo consultar la tabla yearly_metrics tabla mediante SQL para obtener los valores de la columna YOY. Antes de eso, me gustaría sugerir LearnSQL.es's Funciones de ventana donde podrás aprender los fundamentos de las funciones de ventana de SQL. Además, el artículo Cuándo debo utilizar las funciones de ventana de SQL ofrece muchos ejemplos de consultas que pueden resultarle útiles.

Cómo calcular las métricas YOY con SQL

Las funciones de ventana son una característica muy potente de SQL. Devuelven el resultado de aplicar una función (como MAX(), AVG() o COUNT()) a un conjunto de registros (que se denomina "ventana") de una tabla. El conjunto de registros se define mediante la cláusula OVER(); esta cláusula es obligatoria para la función de ventana. También puede ordenar los registros de la ventana por diferentes criterios y luego utilizar funciones como FIRST_VALUE(), LAST_VALUE(), LEAD(), o LAG() para devolver el valor de registros específicos en relación con el registro actual.

Si quieres repasar los fundamentos de las funciones de ventana, te sugiero el artículo ¿Qué es la cláusula Over? Entender las funciones ventana de SQL y la cláusula OVER le ayudará con los conceptos que discutiremos en este artículo.

Ahora, veamos un ejemplo de una consulta SQL que devuelve los ingresos de cada año y su año anterior:

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

En este ejemplo, estamos utilizando la función de ventana LAG() para obtener el valor de la columna ingresos para el registro anterior. En otras palabras, si el registro actual es del año 2020, LAG(revenue) devolverá el valor de la columna ingresos del año 2019. Los resultados de esta consulta son:

El siguiente paso para obtener el valor YOY de los ingresos es sencillo; sólo tenemos que calcular la diferencia entre los ingresos de 2020 y los de 2019. Así es como lo haríamos:

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 el YOY Difference haciendo una diferencia aritmética entre los ingresos de 2020 y los ingresos de 2019. En el siguiente resultado, podemos ver que el registro de 2019 no tiene un valor de YOY Difference porque no tenemos datos de 2018. Además, el registro de 2021 no tiene un valor consistente en YOY Difference porque los datos de 2021 no están completos.

Antes de pasar a la siguiente sección, me gustaría sugerir el artículo Cómo calcular la diferencia entre dos filas en SQL, que profundiza en el cálculo de las diferencias utilizando LAG() y LEAD().

Cálculo de las diferencias de mes a mes y de trimestre a trimestre

En la sección anterior, no pudimos calcular un valor consistente para el YOY Difference para 2021 porque no tenemos resultados completos para 2021. Pero para ver el rendimiento de una empresa en el año en curso, deberíamos tener algunas métricas que describan su rendimiento. Los buenos indicadores clave de rendimiento pueden ser comparaciones mes a mes o trimestre a trimestre (es decir, comparar el primer trimestre de 2021 con el primer trimestre de 2020).

Antes de calcular las diferencias de un mes a otro o de un trimestre a otro, veamos la daily_metrics tabla. Tiene un registro para cada día que describe los ingresos, los costes y el número de nuevos clientes para ese día.

A continuación, calcularemos el CTE monthly_metrics ( un CTE es similar a una vista, pero se crea durante la ejecución de la consulta). Este tiene un esquema similar al de la tabla yearly_metrics tabla utilizada anteriormente. El SELECT de creación de monthly_metrics se muestra en rojo; en azul, podemos ver la consulta SQL que utiliza este CTE como una tabla normal.

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;

Más arriba, utilizamos la función LAG() con el parámetro opcional offset, que nos permite obtener un valor de columna de un registro N posiciones antes del registro actual. Si utilizamos un offset de 12, obtendremos el registro del mismo mes pero del año anterior. A continuación, podemos ver el resultado:

¡Y ya está! Ahora ya sabes cómo encontrar diferencias mes a mes, trimestre a trimestre y año a año con las funciones de ventana de SQL. Realmente sugiero el curso Funciones de ventana como una buena introducción para trabajar con estas funciones. Si quieres aprender más, consulta este artículo que describe el curso Funciones de ventana .

¿Qué es lo que sigue?

Las funciones de ventana son un recurso clave de SQL. En este artículo, utilizamos la función de ventana LAG() para calcular las diferencias de año a año y de mes a mes. De hecho, podemos utilizarla para calcular la diferencia entre cualquier período de tiempo: trimestres, semestres, meses o semanas.

En este artículo nos hemos centrado en las diferencias entre periodos de tiempo, pero las funciones ventana pueden aplicarse para resolver muchos tipos diferentes de problemas de datos. Por último, pero no por ello menos importante, quiero sugerir nuestra hoja de trucos Funciones de ventana , que tengo pegada en una pizarra de corcho frente a mi escritorio. Invierte en ti y haz crecer tus conocimientos de SQL.