Comment calculer la différence entre deux lignes en SQL ?

Calculer la différence entre deux lignes en SQL peut être une tâche difficile. C'est possible - et il y a plus d'une façon de le faire. Dans cet article, je vais vous expliquer comment utiliser les fonctions de fenêtrage SQL LEAD() et LAG() pour trouver la différence entre deux lignes d'une même table.

J'aime à penser que cet article est un article "happy hour" - vous pouvez apprendre sur deux sujets (le calcul de la différence entre deux lignes et les fonctions de fenêtrage SQL) en lisant un seul article. Entrons dans le vif du sujet !

Si vous souhaitez apprendre les fonctions de fenêtrage, consultez notre cours interactif Fonctions de fenêtrage. Si vous les connaissez déjà et que vous voulez vous entraîner à écrire vos requêtes de fonctions de fenêtrage, je vous recommande notre jeu d'exercices Fonctions de fenêtrage, qui contient 100 exercices pratiques.

Trouver la différence entre deux valeurs dans la même ligne

Pour calculer une différence quelconque, vous avez besoin de deux éléments ; pour calculer une différence en SQL, vous avez besoin de deux enregistrements. Vous pouvez calculer la différence entre deux colonnes dans le même enregistrement, comme je vais vous le montrer dans un instant. C'est très simple. Cependant, je vais surtout me concentrer sur la recherche de la différence entre deux valeurs de la même colonne dans des enregistrements différents.

Tout d'abord, parlons de nos données. Nous utiliserons une base de données contenant deux tables, telles qu'utilisées par le gouvernement d'une province imaginaire pour définir certains programmes sociaux. La première table est housing, qui contient des données sur le nombre de personnes qui louent, possèdent ou ont besoin d'une maison. Jetez-y un coup d'œil :

Table housing

Pour concevoir un plan de construction de maisons pour les personnes qui en ont besoin, le gouvernement souhaite obtenir des données sur les problèmes de logement dans différentes villes. Supposons que le gouvernement veuille savoir combien de personnes dans chaque ville ne possèdent pas de maison ; la question sera la suivante :

SELECT
    city,
    total_population,
    total_population - population_owning_house AS people_not_owning_house
FROM housing

Dans cette requête, il est clair que vous calculez une différence en utilisant deux colonnes différentes dans le même enregistrement. Ensuite, vous allez calculer des différences en utilisant deux enregistrements.

Calcul de la différence entre deux valeurs dans la même colonne

Habituellement, toutes les opérations que nous effectuons dans une requête SQL sont liées à l'enregistrement actuel. Mais dans ce cas, nous avons besoin d'un deuxième enregistrement. C'est la partie délicate. Nous allons utiliser une technique qui fait appel aux fonctions de fenêtrage LAG() et LEAD() pour obtenir les données d'un autre enregistrement. Si vous voulez aller plus loin, je vous suggère de lire cet article sur les fonctions de fenêtrage qui donne des explications claires sur les fonctions de fenêtrage avec de nombreux exemples.

Supposons que, pour une ville spécifique ("Bronzepolis"), vous souhaitiez obtenir la variation du nombre de personnes ayant besoin d'un logement par rapport à l'année précédente. Voyons la requête :

SELECT
  city,
  year,
  population_needing_house,
  LAG(population_needing_house) AS previous_year,
  population_needing_house - LAG(population_needing_house)
    OVER (ORDER BY year ) AS difference_previous_year
FROM housing
WHERE city = ‘Bronzepolis’
ORDER BY year

Dans le texte en bleu, vous pouvez voir le calcul du delta SQL entre deux lignes. Pour calculer une différence, vous avez besoin d'une paire d'enregistrements ; ces deux enregistrements sont "l'enregistrement actuel" et "l'enregistrement de l'année précédente". Vous obtenez cet enregistrement à l'aide de la fonction de fenêtrage LAG(). Cette fonction vous permet d'obtenir les données de l'enregistrement précédent (sur la base d'un critère d'ordre, qui est ici "ORDER BY year").

Vous trouverez ci-dessous le résultat de cette requête. Les flèches rouges montrent que la valeur de la fonction LAG() renvoie la même valeur de population_besoin_maison que l'enregistrement de l'année précédente. La colonne de droite montre le résultat de la différence entre l'année en cours et l'année précédente.

Pour la prochaine requête, nous allons étendre l'analyse des problèmes de logement à toutes les villes. Pour chaque ville, nous voulons la différence entre l'année en cours et les années précédentes pour la colonne population_needing_house.

Tout d'abord, vous allez supprimer la condition city = ‘Bronzepolis’. Vous voulez calculer des valeurs pour chaque ville, vous avez donc besoin d'un moyen de séparer les données en groupes. C'est pourquoi vous ajoutez la clause PARTITION BY city à LAG(). PARTITION BY city vous permet de traiter tous les enregistrements de la même ville dans la même fenêtre.

Vous allez à nouveau utiliser LAG() pour calculer la différence entre le nombre de personnes ayant besoin d'un logement entre cette année et l'année précédente. Voyons la requête :

SELECT
  city,
  year,
  population_needing_house,
  LAG(population_needing_house)
    OVER (PARTITION BY city ORDER BY year ) AS previous_year,
  population_needing_house - LAG(population_needing_house)
   OVER (PARTITION BY city ORDER BY year ) AS difference_previous_year
FROM housing
ORDER BY city, year

Dans le texte en bleu, vous pouvez voir comment la différence est calculée. Voici une version en clair de ce qui se passe :

difference_previous_year =
  population_needing_house in current record
  - population_needing_house in the previous year’s record

Où :

Population_besoin_maison dans l'enregistrement actuel

population_needing_house

et

Population_besoin_maison dans l'enregistrement de l'année précédente

LAG(population_needing_house) OVER (PARTITION BY city ORDER BY year )

Vous avez donc utilisé la clause PARTITION pour créer un ensemble contenant tous les enregistrements relatifs à la même ville. Ensuite, la clause ORDER BY est utilisée pour ordonner tous ces enregistrements par année. Enfin, la fonction LAG() est utilisée pour obtenir la valeur population_needing_house de l'enregistrement précédent.

Une autre fonction de fenêtrage SQL, LEAD(), est similaire à LAG(), mais elle renvoie l'enregistrement suivant du jeu (dans notre exemple, il s'agit de l'enregistrement de l'année suivante).

Si vous examinez les deux requêtes précédentes, vous constaterez que nous utilisons le même modèle pour calculer la différence : nous soustrayons la valeur de la colonne précédente de la valeur actuelle à l'aide de LAG() (ou de l'enregistrement suivant à l'aide de LEAD()).

Les fonctions de fenêtrage SQL sont très puissantes. Pour des exemples tirés de différents domaines d'activité, comme la finance, les ventes et le commerce, consultez cet article sur l'utilisation des fonctions de fenêtrage SQL.

Calcul de la différence entre des valeurs de date en SQL

Dans les exemples précédents, vous avez calculé le delta entre deux lignes en utilisant les valeurs numériques des colonnes. Je vais maintenant vous montrer comment calculer la différence entre deux valeurs de type date.

Tout d'abord, considérons le type de données du résultat. Lorsque vous calculez la différence entre deux valeurs de date, le résultat n'est pas une date. Il s'agit d'un intervalle qui représente le nombre de jours entre les deux dates.

Supposons qu'il existe une table appelée hospital_statistics qui stocke les statistiques des trois hôpitaux de la province. Vous trouverez ci-dessous un exemple de données de la table :

Table hospital_statistics

Supposons maintenant qu'il existe une maladie rare appelée MDLR. Le gouvernement veut étudier la fréquence à laquelle un patient atteint de MDLR est admis dans l'un des hôpitaux. Il a demandé un rapport avec les colonnes day, hospital name, number of cases et days_since_the_last_case. Nous allons utiliser SQL pour construire le rapport :

SELECT
  day,
  hospital_name,
  number_of_patients,
  day - LAG(day) OVER (ORDER BY day)
     AS days_since_last_case
FROM hospital_statistics
WHERE illness_name = 'MDLR'
ORDER BY day

Vous pouvez voir le même modèle que nous avons utilisé auparavant. Maintenant, il est utilisé pour calculer les jours depuis le dernier cas. La seule différence est que vous calculez une différence entre deux dates au lieu de valeurs numériques.

Comme vous le voyez dans les résultats, la colonne days_since_last_case est une valeur entière, représentant un nombre de jours.

SQL vous permet également de calculer les différences entre les valeurs de timestamp. Et il existe d'autres opérations arithmétiques intéressantes sur les dates que vous pouvez utiliser sur les types de données liés aux dates. Si vous voulez aller plus loin, je vous suggère l'article Comment analyser les données COVID 19 de séries chronologiques avec les fonctions de fenêtrage SQL.

Trouver la différence entre des enregistrements non consécutifs

Jusqu'à présent, vous avez calculé les différences entre des enregistrements contigus en fonction de critères d'ordre spécifiques. Dans certains cas, vous aurez besoin de calculer la différence entre des enregistrements non contigus.

À titre d'exemple, revenons à la première requête. Supposons que nous voulions ajouter une autre colonne indiquant le nombre de cas au cours des deux dernières années. Heureusement, LAG() et LEAD() possèdent un paramètre facultatif qui spécifie le nombre d'enregistrements à sauter avant/après l'enregistrement actuel. Par défaut, ce paramètre est égal à 1 (c'est-à-dire "utiliser l'enregistrement suivant/précédent"), mais vous pouvez le définir avec une autre valeur. Ainsi, avec ce nouveau paramètre, la requête sera :

SELECT
  city,
  year,
  population_needing_house,
  LAG(population_needing_house)
    OVER (PARTITION BY city ORDER BY year ) AS one_year_before,
  LAG(population_needing_house,2)
    OVER (PARTITION BY city ORDER BY year ) AS two_years_before,
  population_needing_house - LAG(population_needing_house)
    OVER (PARTITION BY city ORDER BY year ) AS difference_previous_year,
  population_needing_house - LAG(population_needing_house,2)
    OVER (PARTITION BY city ORDER BY year ) AS difference_last_two_years
FROM housing
ORDER BY 1, 2 DESC

En bleu, vous pouvez voir le calcul de la différence entre deux enregistrements non consécutifs. Dans l'image suivante, nous pouvons voir la nouvelle colonne Difference last two years seulement pour les valeurs de 2019 ; ceci parce que 2019 est la seule année qui a des données pour deux années précédentes. Les lignes rouges relient les résultats de la fonction LAG() (obtention des données pour 2017 et affichage de ces mêmes données dans l'enregistrement 2017). Vous pouvez voir que les deux lignes ont la même valeur.

Nous avons montré l'importance de LAG() et LEAD(), mais il existe de nombreuses autres fonctions de fenêtrage en SQL. Lisez cet article sur les fonctions de fenêtrage pour en savoir plus.

En savoir plus sur les calculs SQL et les fonctions de fenêtrage

Dans cet article, vous avez appris à calculer la différence entre des lignes consécutives en langage SQL. Vous avez également appris à utiliser LAG() et LEAD() pour trouver la différence entre des lignes non consécutives. Bien qu'il existe d'autres moyens de calculer cette différence, comme l'utilisation d'une jointure automatique, ces méthodes sont très utiles.

Si vous êtes amené à utiliser fréquemment les fonctions de fenêtrage (ou SQL), ou si vous souhaitez simplement améliorer vos connaissances, ce guide complet Fonctions de fenêtrage est une excellente ressource. Pour apprendre les fonctions de fenêtrage par la pratique, essayez notre cours Fonctions de fenêtrage. Vous trouverez des informations sur ce cours dans cet article.