LAG LEAD
Récupérer dans la sélection des données d'un enregistrement, les données d'autres enregistrements
Fonction :
Les fonctions OLAP SQL : LAG et LEAD permettent de récupérer la valeur de données présentent sur d’autres enregistrements que l’enregistrement courant.
La fonction LAG : permet de récupérer les données des enregistrements précédents l’enregistrement courant.
La fonction LEAD : permet de récupérer les données des enregistrements suivants l’enregistrement courant.
Syntaxe :
Les fonctions LAG et LEAD disposent de 4 paramètres :
- La zone de données à extraire.
- Le nombre de décalages.
Indique le nombre d’enregistrements entre l’enregistrement courant et celui duquel doivent être extraites les données.Par défaut le décalage est à 1.
Un LAG de 1 retournera la valeur de l’enregistrement précédent.
Un LEAD de 3 retournera la valeurs du 3eme enregistrement suivant l’enregistrement courant. - La valeur par défaut à retourner si la valeurs à extraire n’est pas trouvée.
- ‘RESPECT NULLS‘ ou ‘IGNORE NULLS‘
Si la valeur à extraire est NULL, ‘IGNORE NULLS‘ permet de ne pas en tenir compte et d’extraire la valeur trouvée suivante.
La valeur pas défaut est ‘RESPECT NULLS‘;
Les fonctions LAG et LEAD sont suivies de la clause OVER() avec 2 les paramètres suivants :
- PARTITION BY + la liste des champs composant une partition de la table traitée.
Ce paramètre est facultatif.
Il permet de découper la table traitée en différentes partitions.
Ceci pour éviter qu’un LEAD sur le dernier élément d’une partition ne retourne la 1ère valeur de la partition suivante.
Ou pour éviter qu’un LAG sur le premier enregistrement d’une partition ne retourne la dernière valeur de la partition précédente. - ORDER BY + la liste des champs composant le chemin d’accès à utiliser pour déterminer l’ordre des enregistrements pris en compte par le LAG ou le LEAD.
Ce paramètre est obligatoire.
Et enfin les fonctions LAG et LEAD se terminent par le nom du nouveau champ qu’elles génèrent.
LAG( ma_zone_a_extraire, 1, ‘Non trouvé’, ‘RESPECT NULLS’) OVER(PARTITION BY ma_zone_de_partitionnement ORDER BY ma_zone_clef1, ma_zone_clef_2) mon_nouveau_champ_LAG
LEAD( ma_zone_a_extraire, 3, ‘Non trouvé’, ‘IGNORE NULLS’) OVER(ORDER BY ma_zone_clef1, ma_zone_clef_2) mon_nouveau_champ_LEAD
Exemples :
Exemple 1 : principes de base
La table des mois : TABLE_MOIS
SELECT MOIS,
NOM_MOIS,
LAG( NOM_MOIS,1) OVER(ORDER BY MOIS asc) LAG_1_MOIS_AVANT,
LEAD(NOM_MOIS,1) OVER(ORDER BY MOIS asc) LAG_1_MOIS_APRES
FROM QTEMP.TABLE_MOIS;
Résultat :
LAG_1_MOIS_AVANT du premier enregistrement est retourné à NULL car il n’y a pas d’enregistrement avant le mois 1.
LEAD_1_MOIS_APRES du dernier enregistrement est à NULL car il n’y a pas d’enregistrement après le mois 12.
Pour récupérer les données d’un autre enregistrement que le précédent ou le suivant, il faut modifier la valeur de décalage.
SELECT MOIS,
NOM_MOIS,
LAG( NOM_MOIS,2) OVER(ORDER BY MOIS asc) LAG_2_MOIS_AVANT,
LEAD(NOM_MOIS,2) OVER(ORDER BY MOIS asc) LAG_2_MOIS_APRES
FROM QTEMP.TABLE_MOIS;
Résultat :
LAG_2_MOIS_AVANT des 2 premiers enregistrements est retourné à NULL car il n’y a pas d’enregistrement avant le mois 1.
LEAD_2_MOIS_APRES des 2 derniers enregistrement est à NULL car il n’y a pas d’enregistrement après le mois 12.
Les fonctions LAG et LEAD sont des fonctions OLAP, elles renvoient donc un ensemble de données.
Ecrire la requête suivante n’a pas de sens car la clause WHERE limite la fonction OLAP à un seul enregistrement.
SELECT MOIS,
NOM_MOIS,
LAG(NOM_MOIS,1) OVER(ORDER BY MOIS asc) LAG_1_MOIS_AVANT,
LEAD(NOM_MOIS,2) OVER(ORDER BY MOIS asc) LEAD_1_MOIS_APRES
FROM QTEMP.TABLE_MOIS
WHERE MOIS = 5;
Résultat :
LAG_1_MOIS_AVANT et LEAD_1_MOIS_APRES sont à NULL car la clause WHERE limite la sélection à 1 seul enregistrement, les fonctions LAG et LEAD ne trouveront jamais de valeurs précédentes, ou de valeurs suivantes…
Pour récupérer les données d’un seul enregistrement avec les valeurs suivantes ou précédentes, on peut utiliser une CTE (COMMON TABLE EXPRESSIONS)
WITH CTE_TABLE_MOIS AS(
SELECT MOIS,
NOM_MOIS,
LAG( NOM_MOIS,1,0) OVER(ORDER BY MOIS asc) LAG_1_MOIS_AVANT,
LEAD(NOM_MOIS,1,0) OVER(ORDER BY MOIS asc) LEAD_1_MOIS_APRES
FROM QTEMP.TABLE_MOIS
)
SELECT MOIS,
NOM_MOIS,
LAG_1_MOIS_AVANT,
LEAD_1_MOIS_APRES
FROM CTE_TABLE_MOIS
WHERE MOIS = 5;
Résultat :
Exemple 2 : PARTITION BY
La table des mois des années 2024 et 2025 : TABLE_MOIS_ANNEE
SELECT ANNEE,
MOIS,
NOM_MOIS,
LAG(NOM_MOIS,2) OVER(ORDER BY ANNEE, MOIS asc) LAG_2_MOIS_AVANT,
LAG(ANNEE,2) OVER(ORDER BY ANNEE, MOIS asc) LAG_2_MOIS_AVANT,
LEAD(NOM_MOIS,1) OVER(ORDER BY ANNEE, MOIS asc) LEAD_1_MOIS_APRES,
LEAD(ANNEE,1) OVER(ORDER BY ANNEE, MOIS asc) LEAD_1_MOIS_APRES
FROM QTEMP.TABLE_MOIS_ANNEE
ORDER BY ANNEE, MOIS;
Résultat :
Les résultats sont extraits en fonction de l’intégralité de la table.
LAG_2_MOIS_AVANT des 2 premiers enregistrements est retourné à NULL car il n’y a pas d’enregistrement avant le mois 1 de l’année 2024.
LEAD_1_MOIS_APRES du dernier enregistrement est à NULL car il n’y a pas d’enregistrement après le mois 12 de l’année 2025.
Les résultats des mois Décembre 2024, Janvier 2025, Février 2025 correspondent aux données de l’année précédente ou de l’année suivante.
Pour avoir des résultats en fonction d’une partie de la table, il faut utiliser PARTITION BY:
SELECT ANNEE,
MOIS,
NOM_MOIS,
LAG(NOM_MOIS,2) OVER(PARTITION BY ANNEE ORDER BY ANNEE, MOIS asc) LAG_2_MOIS_AVANT,
LAG(ANNEE,2) OVER(PARTITION BY ANNEE ORDER BY ANNEE, MOIS asc) LAG_2_MOIS_AVANT,
LEAD(NOM_MOIS,1) OVER(PARTITION BY ANNEE ORDER BY ANNEE, MOIS asc) LEAD_1_MOIS_APRES,
LEAD(ANNEE,1) OVER(PARTITION BY ANNEE ORDER BY ANNEE, MOIS asc) LEAD_1_MOIS_APRES
FROM QTEMP.TABLE_MOIS_ANNEE
ORDER BY ANNEE, MOIS;
Résultat :
Les résultats sont extraits en fonction de chaque partition ANNEE de la table.
LAG_2_MOIS_AVANT des 2 premiers enregistrements est retourné à NULL car il n’y a pas d’enregistrement avant le mois 1 de l’année 2024.
LAG_2_MOIS_AVANT des 2 premiers mois de 2025 est également à NULL car il n’y a pas de mois en 2025 avant le mois 1.
LEAD_1_MOIS_APRES du dernier enregistrement est à NULL car il n’y a pas d’enregistrement après le mois 12 de l’année 2025.
LEAD_1_MOIS_APRES du dernier mois de 2024 est à NULL car il n’y a pas de mois en 2024 après le mois 12 de l’année 2024.
Les résultats des mois Décembre 2024, Janvier 2025, Février 2025 ne tiennent pas compte des données de l’année précédente ou de l’année suivante, mais uniquement des données de la partition définie par le PARTITION BY.
