La fonction LAG et la fonction LEAD en SQL

LAG() et LEAD() sont des fonctions positionnelles. Une fonction positionnelle est un type de fonction fenêtrage. Si vous ne savez pas quand, et comment les utiliser, quelle est la syntaxe de chaque fonction, pourquoi vous pourriez vouloir les utiliser et quelles sont les différences, lisez la suite !

LAG() et LEAD() sont des fonctions positionnelles. Ce sont des fonctions de fenêtrage et elles sont très utiles pour créer des rapports, car elles peuvent faire référence à des données de lignes situées au-dessus ou au-dessous de la ligne actuelle. Dans cet article, nous allons examiner ces deux fonctions en détail.

La syntaxe de la fonction LAG

La fonction LAG() permet d'accéder à une valeur stockée dans une ligne différente au-dessus de la ligne actuelle. La ligne de dessus peut être adjacente ou se situer à un certain nombre de lignes au-dessus, triée selon une colonne ou un ensemble de colonnes spécifiées.

Voyons sa syntaxe :

LAG(expression [,offset[,valeur_par_defaut]]) OVER(ORDER BY colonnes)

LAG() prend trois arguments : le nom de la colonne ou d'une expression à partir de laquelle la valeur est obtenue, le nombre de lignes à sauter (décalage) au-dessus, et la valeur par défaut à renvoyer si la valeur stockée obtenue de la ligne au-dessus est vide. Seul le premier argument est obligatoire. Le troisième argument (valeur par défaut) n'est autorisé que si vous spécifiez le deuxième argument, le décalage.

Comme pour les autres fonctions de fenêtrage, LAG() nécessite la clause OVER. Elle peut prendre des paramètres optionnels, que nous expliquerons plus tard. Avec LAG(), vous devez spécifier un ORDER BY dans la clause OVER, avec une colonne ou une liste de colonnes par lesquelles les lignes doivent être triées.

Considérons la table suivante, vente:

Et la requête suivante avec une fonction LAG():

SELECT nom_du_vendeur, valeur_de_la_vente,
  LAG(valeur_de_la_vente) OVER(ORDER BY valeur_de_la_vente) as valeur_de_vente_precedente
FROM vente;

Voici le résultat :

Cette utilisation la plus simple de LAG() affiche la valeur de la ligne adjacente de dessus. Par exemple, le deuxième enregistrement affiche le montant de la vente d'Alice (12 000 €) et celui de Stef (7 000 €) de la ligne précédente, respectivement dans les colonnes valeur_de_la_vente et valeur_de_vente_precedente. Remarquez que la première ligne n'a pas de ligne adjacente au-dessus, et que par conséquent le champ valeur_de_vente_precedente est vide (NULL), puisque la ligne à partir de laquelle la valeur de valeur_de_la_vente devrait être obtenue n'existe pas.

Si vous ne spécifiez que l'argument requis (le nom de la colonne ou une autre expression) comme nous l'avons fait dans cet exemple, l'argument de décalage a la valeur 1 par défaut et le troisième argument a la valeur NULL par défaut. Dans notre exemple, la première ligne du jeu de résultats contient NULL dans valeur_de_vente_precedente et les autres lignes contiennent les valeurs des lignes respectives situées immédiatement au-dessus, car le décalage est de 1.

En utilisant LAG(), vous pouvez voir la valeur de la ligne actuelle ainsi que la valeur de la ligne adjacente au-dessus. Vous pouvez l'utiliser, par exemple, pour vérifier le montant de la vente d'une ligne donnée par rapport à celui de la ligne précédente, le montant de la vente étant trié de la plus petite à la plus grande.

L'illustration ci-dessous montre comment la valeur de la ligne adjacente de dessus est ajoutée à la ligne actuelle.

La fonction LAG() est incluse dans notre cours pratique " Astuces SQL Fonctions de fenêtrage ".

La syntaxe de la fonction LEAD

LEAD() est similaire à celle de LAG(). Alors que LAG() accède à une valeur stockée dans une ligne supérieure, LEAD() accède à une valeur stockée dans une ligne inférieure.

La syntaxe de LEAD() est identique à celle de LAG():

LEAD(expression [,offset[,valeur_par_defaut]]) OVER(ORDER BY colonnes)

Tout comme LAG(), la fonction LEAD() prend trois arguments : le nom d'une colonne ou d'une expression, le décalage à sauter en dessous et la valeur par défaut à renvoyer si la valeur stockée obtenue de la ligne en dessous est vide. Seul le premier argument est obligatoire. Le troisième argument, la valeur par défaut, ne peut être spécifié que si vous spécifiez le deuxième argument, le décalage.

Tout comme LAG(), LEAD() est une fonction de fenêtrage et nécessite une clause OVER. Et comme pour LAG(), LEAD() doit être accompagné d'un ORDER BY dans la clause OVER.

Nous regardons à nouveau la table, vente:

Voici une requête avec une fonction LEAD():

SELECT nom_du_vendeur, valeur_de_la_vente,
  LEAD(valeur_de_la_vente) OVER(ORDER BY valeur_de_la_vente) as valeur_de_vente_suivante
FROM vente;

Voici le jeu de résultats :

Les lignes sont triées par la colonne spécifiée dans ORDER BY (valeur_de_la_vente). La fonction LEAD() récupère le montant de la vente dans la ligne inférieure. Par exemple, le montant de la vente de Stef est de 7 000 € dans la colonne valeur_de_la_vente, et la colonne valeur_de_vente_suivante du même enregistrement contient 12 000 €. Ce dernier provient de la colonne valeur_de_la_vente pour Alice, le vendeur de la ligne suivante. Notez que la dernière ligne n'a pas de ligne suivante, le champ valeur_de_vente_suivante est donc vide (NULL) pour la dernière ligne.

Si vous ne spécifiez que l'argument requis, c'est-à-dire uniquement le nom de la colonne ou une autre expression, le décalage prend la valeur 1 par défaut et le troisième argument prend la valeur NULL. Dans notre exemple, la valeur de valeur_de_vente_suivante d'Alice provient de la colonne valeur_de_la_vente de la ligne adjacente inférieure, puisque le décalage par défaut est 1.

À l'aide de LEAD(), vous pouvez comparer les valeurs entre les lignes. L'illustration suivante montre comment le montant renvoyé par LEAD() est ajouté à la ligne actuelle.

Utilisation de LAG() et LEAD() pour comparer des valeurs

Une utilisation importante de LAG() et LEAD() dans les rapports est la comparaison des valeurs de la ligne actuelle avec les valeurs de la même colonne, mais d'une ligne supérieure ou inférieure.

Considérons la table vente_annuelle ci-dessous :

Comme vous pouvez le voir, cette table contient le montant total des ventes par année. En utilisant LAG() et LEAD(), nous pouvons comparer les montants des ventes annuelles d'une année à l'autre.

Examinons cette requête :

SELECT  annee, vente_totale_actuelle,
   LAG(vente_totale) OVER(ORDER BY annee) AS vente_totale_precedente,
   vente_totale - LAG(vente_totale) OVER(ORDER BY annee) AS difference
FROM vente_annuelle;

Voici l'ensemble des résultats :

Cette requête récupère le montant des ventes de l'année précédente et le place dans la colonne vente_totale_precedente en utilisant la fonction LAG(). La clause ORDER BY de la clause OVER ordonne les enregistrements par année, en veillant à ce que la ligne adjacente de dessus représente l'année précédente. Ensuite, elle prend le montant de la colonne vente_totale_actuelle dans la ligne précédente et le transfère dans la ligne actuelle.

Cette requête calcule également la différence du montant de la vente entre l'année en cours et l'année précédente. Cela peut nous aider à comprendre s'il y a eu une augmentation (différence positive) ou une diminution (différence négative) des ventes d'une année à l'autre.

Pour 2015, nous n'avons aucune information sur l'année précédente. Par conséquent, la valeur renvoyée par la fonction LAG() est NULL, tout comme la différence. Maintenant, la vente totale en 2018 était de 32 000 €, mais elle était de 34 000 € en 2017 (l'année précédente) comme le montre la colonne vente_totale_precedente. La différence est de -2 000 €, ce qui indique qu'en 2018, il y a eu une diminution des ventes de 2 000 € par rapport à l'année 2017.

Utilisation de LAG() et LEAD() avec un décalage spécifique

Vous pouvez utiliser les fonctions LAG() et LEAD() avec deux arguments : le nom de la colonne et le décalage.

Considérons la table suivante, employe:

La requête ci-dessous sélectionne la prime de l'employé avec ID=1 pour chaque trimestre de chaque année. Elle identifie ensuite les primes pour le trimestre correspondant de l'année précédente et de l'année suivante.

SELECT annee, trimestre,
  LAG(bonus,4) OVER(ORDER BY annee,trimestre) AS bonus_precedent,
 bonus AS bonus_actuel,
  LEAD(bonus,4) OVER(ORDER BY annee,trimestre) AS prochain_bonus 
FROM employe
WHERE employe_id = 1;

Cette requête renvoie le jeu de résultats suivant :

Les lignes surlignées en vert correspondent aux enregistrements du premier trimestre de chaque année, les lignes en blanc au deuxième trimestre de chaque année, etc. Dans une ligne donnée, le montant de la prime précédente et celui de la prime suivante proviennent du trimestre correspondant de l'année précédente et de l'année suivante, ils sont affectés aux colonnes bonus_precedent et prochain_bonus, respectivement.

Par exemple, l'employé ID=1 a reçu une prime de 80 € au cours du premier trimestre de 2018. Pour le même employé, la prime du premier trimestre 2017 était de 100 €, et celle du premier trimestre 2019 était de 0 €. Le ORDER BY spécifie que les lignes doivent être triées par année et par trimestre. Un décalage de 4 indique à LEAD() et LAG() de sauter 4 lignes avant et après la ligne actuelle, respectivement. Avec ce décalage, vous pouvez comparer les valeurs du même trimestre de différentes années, car il y a 4 trimestres dans une année. L'image ci-dessous illustre cette idée.

Pour en savoir plus sur les fonctions LAG() et LEAD(), consultez nos articles " SQL courant Fonctions de fenêtrage : fonctions positionnelles " par Aldo Zelen et "Quand utiliser les Fonctions de fenêtrage en SQL ?" par Tihomir Babic.

Utilisation de LAG() et LEAD() avec une valeur par défaut

Dans la section précédente, nous avons vu comment utiliser l'argument offset dans LAG() et LEAD(). Nous examinons maintenant les cas avec un troisième argument : la valeur par défaut à attribuer lorsque la valeur obtenue est NULL. Pour spécifier cet argument, vous devez également spécifier le deuxième argument, le décalage. Le décalage par défaut est 1, donc spécifiez 1 pour conserver le décalage par défaut ou une autre valeur appropriée à votre cas.

Examinons un autre exemple. La table suivante, produit_vendu contient les ID des produits, le mois (1 = janvier, 2 = février, etc.) et le nombre de ventes par mois.

Voici les enregistrements pour lesquels l'ID du produit est 1.

La requête :

SELECT produit_id, mois,
  LAG(ventes_mensuelles,1,0) OVER(ORDER BY mois) AS ventes_mensuelles_precedentes,
  ventes_mensuelles AS ventes_actuelles,
  ventes_mensuelles - LAG(ventes_mensuelles,1,0) OVER(ORDER BY mois) AS difference
FROM produit_vendu
WHERE produit_id = 1;

renvoie le résultat :

Pour le produit dont l'ID est 1, nous sélectionnons le mois de vente, le nombre de ventes pour ce mois (ventes_actuelles) et le nombre de ventes du mois précédent (la valeur de la ligne précédente renvoyée par LAG()).

Nous souhaitons afficher zéro à la place de NULL lorsque LAG() essaie d'obtenir des valeurs de lignes autres que celles qui existent dans notre ensemble de données. Pour LAG() et LEAD(), ceci est fait en spécifiant un troisième argument, la valeur par défaut. Rappelez-vous que l'argument de décalage est nécessaire pour spécifier l'argument de valeur par défaut ; ici, nous spécifions un décalage de 1 pour regarder la ligne de dessus. Nous spécifions ensuite 0 comme troisième argument. Cela met à zéro toute tentative d'obtenir des valeurs à partir de lignes qui n'existent pas, comme c'est le cas ici pour la première ligne (il n'y a pas de ligne au-dessus de la première ligne).

Notez que la valeur par défaut de zéro n'est attribuée qu'aux lignes qui n'existent pas ; les lignes dont les lignes adjacentes de dessus existent, mais avec NULLs dans ventes_actuelles, sont laissées telles quelles en tant que NULLs au lieu d'être changées en 0. Vous pouvez le voir dans la ligne où mois=4 : bien que ventes_actuelles pour la ligne au-dessus (mois=3) soit NULL, il n'est pas remplacé par un zéro, puisque la ligne précédente existe et contient seulement un NULL dans ventes_actuelles.

Utilisation de LAG() et LEAD() avec des partitions

Continuons avec le même exemple, mais examinons maintenant un cas dans lequel nous avons besoin de PARTITION BY dans la clause OVER. Voici la partie suivante de la table produit_vendu avec un autre produit dont l'ID=2.

La requête :

SELECT produit_id, mois,
  LAG(ventes_mensuelles,1,0) OVER(PARTITION BY produit_id ORDER BY mois) AS ventes_mensuelles_precedentes,
  ventes_mensuelles AS ventes_actuelles,
  ventes_mensuelles - LAG(ventes_mensuelles,1,0) OVER(PARTITION BY produit_id ORDER BY mois) AS difference
FROM produit_vendu;

renvoie le résultat :

Nous avons plusieurs produits dans cette table. Pour calculer les différences entre les ventes actuelles et les ventes précédentes séparément pour chaque produit, nous spécifions PARTITION BY avant ORDER BY dans la clause OVER.

Vous pouvez nommer une colonne ou une liste de colonnes dans PARTITION BY. Ici, nous utilisons la colonne produit_id pour diviser les enregistrements en partitions et trier par mois dans chaque partition. Par conséquent, chaque partition commence par le mois 1 et se termine par le mois 4.

PARTITION BY est similaire à GROUP BY en ce sens qu'il regroupe les mêmes valeurs. Toutefois, contrairement à GROUP BY, PARTITION BY ne réduit pas les lignes d'origine en une seule ligne ; les lignes d'origine restent accessibles. Pour en savoir plus sur la différence entre PARTITION BY et ORDER BY, consultez l'article " Quelle est la différence entre un GROUP BY et un PARTITION BY ? ".

Les fonctions LAG et LEAD sont très utiles !

Les fonctions positionnelles telles que LAG() et LEAD() sont utiles dans de nombreuses situations. Elles sont souvent utilisées pour créer des rapports, car elles peuvent faire référence aux lignes situées au-dessus ou au-dessous, comme nous l'avons vu dans ces exemples. J'espère que cet article vous aidera à étendre vos connaissances SQL sur les fonctions de fenêtrage. Pour en savoir plus sur les fonctions positionnelles, consultez les articles " Fonctions courantes en SQL. Fonctions de fenêtrage : fonctions positionnelles " d'Aldo Zelen et " Quand utiliser les Fonctions de fenêtrage ? " de Tihomir Babic. Si vous souhaitez en savoir plus sur les fonctions de fenêtrage, essayez notre cours interactif " Fonctions de fenêtrage " sur la plate-forme LearnSQL.fr.