Comment calculer les différences d'une année sur l'autre en SQL

Comment votre entreprise s'est-elle comportée cette année par rapport à l'année dernière ? L'année précédente ? Découvrez comment utiliser SQL pour calculer les différences d'une année sur l'autre et d'un mois sur l'autre.

Les comparaisons d'une année sur l'autre (YOY) sont un moyen populaire et efficace d'évaluer les performances de plusieurs types d'organisations. Les différences d'une année sur l'autre sont faciles à comprendre - par exemple, il est facile de comprendre que la croissance de vos revenus est de 35 % d'une année sur l'autre. Cependant, le calcul de cette métrique en SQL n'est pas si facile !

Dans cet article, nous allons apprendre à calculer les différences d'une année sur l'autre et d'un mois sur l'autre en utilisant SQL.

Êtes-vous prêt à investir dans vos compétences en SQL ? C'est parti !

Que sont les mesures d'une année sur l'autre d'un point de vue commercial ?

Pour savoir si une organisation commerciale est performante, nous devons généralement effectuer une comparaison basée sur des indicateurs commerciaux. Nous pouvons parfois nous comparer à des entreprises similaires ou à la moyenne de plusieurs entreprises du même marché. Cependant, un indicateur de performance clé est la croissance périodique : la comparaison des résultats actuels de votre entreprise par rapport à la même mesure d'une période précédente. C'est la raison d'être des comparaisons d'année en année : Vous pouvez facilement voir si votre entreprise fait mieux (ou moins bien) que l'année dernière. En outre, les comparaisons d'une année sur l'autre peuvent être appliquées à différentes mesures (par exemple, les ventes, les bénéfices, le nombre de clients) pour mieux comprendre l'évolution des différents indicateurs commerciaux.

Dans le tableau suivant, nous pouvons voir les résultats pour 2019 et 2020 :

Si nous ajoutons une troisième colonne appelée YOY, nous pouvons facilement voir la variation d'une année à l'autre. Cela nous permet de mieux comprendre les performances de notre organisation au cours de la dernière année et les domaines qui peuvent être améliorés :

Dans cet article, nous allons expliquer comment calculer les valeurs de la colonne YOY. Commençons par montrer le tableau de base, où nous avons les métriques pour 2019 et 2020. Dans l'image suivante, nous pouvons voir ces valeurs dans la table yearly_metrics tableau.

Dans la section suivante, nous allons expliquer comment interroger la table yearly_metrics tableau en utilisant SQL pour obtenir les valeurs de la colonne YOY. Avant cela, j'aimerais vous suggérer le cours LearnSQL.fr's Fonctions de fenêtrage où vous pourrez apprendre les bases des fonctions de la fenêtre SQL. En outre, l'article Quand dois je utiliser les SQL Fonctions de fenêtrage fournit de nombreux exemples de requêtes qui peuvent vous être utiles.

Comment calculer les métriques YOY avec SQL

Les fonctions de fenêtre sont une fonctionnalité SQL très puissante. Elles renvoient le résultat de l'application d'une fonction (comme MAX(), AVG() ou COUNT()) à un ensemble d'enregistrements (appelé "fenêtre") dans une table. Le jeu d'enregistrements est défini par la clause OVER(); cette clause est obligatoire pour la fonction fenêtre. Vous pouvez également classer les enregistrements de la fenêtre selon différents critères, puis utiliser des fonctions telles que FIRST_VALUE(), LAST_VALUE(), LEAD() ou LAG() pour renvoyer la valeur d'enregistrements spécifiques par rapport à l'enregistrement actuel.

Si vous souhaitez revoir les bases des fonctions de fenêtre, je vous suggère l'article Qu'est-ce que la clause OVER ? La compréhension des fonctions de fenêtre SQL et de la clause OVER vous aidera à maîtriser les concepts que nous allons aborder dans cet article.

Voyons maintenant un exemple de requête SQL qui renvoie les recettes pour chaque année et l'année précédente :

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

Dans cet exemple, nous utilisons la fonction de la fenêtre LAG() pour obtenir la valeur de la colonne recettes pour l'enregistrement précédent. En d'autres termes, si l'enregistrement actuel concerne l'année 2020, LAG(revenue) renverra la valeur de la colonne des recettes pour l'année 2019. Les résultats de cette requête sont les suivants :

L'étape suivante pour obtenir la valeur YOY des recettes est simple : il suffit de calculer la différence entre les recettes de 2020 et celles de 2019. Voici comment nous allons procéder :

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

Nous avons calculé le YOY Difference en faisant une différence arithmétique entre les recettes de 2020 et celles de 2019. Dans le résultat suivant, nous pouvons voir que l'enregistrement de 2019 n'a pas de valeur YOY Difference parce que nous n'avons pas de données pour 2018. De même, l'enregistrement pour 2021 n'a pas de valeur cohérente dans YOY Difference parce que les données pour 2021 ne sont pas complètes.

Avant de passer à la section suivante, je vous suggère l'article Comment calculer la différence entre deux lignes en SQL, qui approfondit le calcul des différences à l'aide de LAG() et LEAD().

Calcul des différences de mois à mois et de trimestre à trimestre

Dans la section précédente, nous n'avons pas pu calculer une valeur cohérente pour YOY Difference pour 2021 car nous ne disposons pas de résultats complets pour 2021. Mais pour voir comment une entreprise se comporte pendant l'année en cours, nous devons disposer de certains paramètres décrivant ses performances. Les bons indicateurs clés de performance peuvent être des comparaisons d'un mois à l'autre ou d'un trimestre à l'autre (c'est-à-dire comparer le premier trimestre de 2021 au premier trimestre de 2020).

Avant de calculer les différences d'un mois à l'autre ou d'un trimestre à l'autre, examinons le tableau daily_metrics table. Il contient un enregistrement pour chaque jour qui décrit les recettes, les coûts et le nombre de nouveaux clients pour ce jour.

Ensuite, nous allons calculer le CTE monthly_metrics ( un CTE est similaire à une vue, mais il est créé pendant l'exécution de la requête). Le schéma de cette table est similaire à celui de la table yearly_metrics utilisée précédemment. Le SELECT de création de monthly_metrics est indiqué en rouge ; en bleu, nous pouvons voir la requête SQL qui utilise ce CTE comme une table ordinaire.

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;

Ci-dessus, nous avons utilisé la fonction LAG() avec le paramètre optionnel offset, qui nous permet d'obtenir une valeur de colonne à partir d'un enregistrement N positions avant l'enregistrement actuel. Si nous utilisons un décalage de 12, nous obtiendrons l'enregistrement du même mois mais de l'année précédente. Ci-dessous, nous pouvons voir le résultat :

Et c'est tout ! Vous savez maintenant comment trouver des différences d'un mois à l'autre, d'un trimestre à l'autre et d'une année à l'autre avec les fonctions de fenêtre SQL. Je vous conseille vivement le cours Fonctions de fenêtrage , qui constitue une bonne introduction à l'utilisation de ces fonctions. Si vous souhaitez en savoir plus, consultez cet article décrivant le cours Fonctions de fenêtrage .

Quelle est la suite ?

Les fonctions de fenêtre sont une ressource SQL essentielle. Dans cet article, nous avons utilisé la fonction fenêtre LAG() pour calculer les différences entre les années et les mois. En fait, nous pouvons l'utiliser pour calculer la différence entre n'importe quelle période de temps - trimestres, semestres, mois ou semaines.

Dans cet article, nous nous sommes concentrés sur les différences entre les périodes de temps, mais les fonctions de fenêtre peuvent être appliquées pour résoudre de nombreux types de problèmes de données. Enfin, je vous propose notre aide-mémoire Fonctions de fenêtrage, que j'ai collé sur un tableau en liège en face de mon bureau. Investissez en vous, et développez vos compétences en SQL !