Fonctions lead() et lag() dans SQL Server

Les fonctions LEAD et LAG permettent, dans un jeu de données ordonnées de récupérer l’enregistrement qui précède (ou qui suit) la ligne actuelle sans avoir à faire de jointure réflexive. Tour d'horizon de deux fonctions disponibles dans SQL Server depuis sa version 2008...

1 – Syntaxe et arguments

Ci-dessous la syntaxe de la fonction LEAD qui permet de récupérer l’enregistrement suivant. La fonction LAG (enregistrement précèdent) a exactement la même syntaxe :

LEAD(champ, pas, valeur_par_défaut) OVER (PARTITION champ_ partition ORDER BY champ_order)

« champ » correspond à la valeur qui serra retournée, cet argument est obligatoire dans l’écriture de la fonction.

« pas » correspond au nombre de ligne suivant la ligne actuelle (précédent pour la fonction LAG). Cet argument est facultatif, par défaut le pas est de 1.

«valeur_par_défaut » permet de déterminer une valeur dans le cas où il n’y ait pas de valeur sur la ligne suivante. Cet argument est facultatif, par défaut la valeur est de Null.

« champ_partition » divise le résultat en partition sur lesquelles la fonction est appliquée. Si cet argument n’est pas renseigné la fonction s’applique sur l’ensemble des données.

« champ_order » est un argument obligatoire pour déterminer l’ordre des données dans les partition ou non.

2 – Exemple d’utilisation dans une clause « select »

Dans cet exemple simple nous disposons d’une table qui contient le salaire annuel d’employés par années. Nous cherchons à connaître l’évolution de salaire de chaque employé. Pour ce faire nous allons utiliser la fonction LAG (enregistrement précédent).

1 – Identifier le salaire de l’année précédente de l’employé 1

lag(salaire) over ( order by annee)

Dans son écriture la plus simple la fonction LAG retourne la valeur contenue sur l’enregistrement précédent avec un pas de 1 et la valeur null si aucun résultat n’est trouvé sur l’enregistrement précédent. Les données sont triées par année.

  select id_emp, 
         annee, 
         salaire, 
         lag(salaire) over ( order by annee) as [salaire_N-1]
  from [test].[dbo].[employe] 
  where id_emp = 1

Résultat :

2 - Calculer l’évolution de salaire sur 2 ans pour tous les employés

lag(salaire,2,0) over (partition by id_emp order by annee)

Dans ce cas, la fonction LAG retourne la valeur du deuxième enregistrement précédent (pas de 2) et affiche la valeur 0 lorsqu’il n’y a pas d’enregistrement précédent. Les données sont partitionnées par employé et triées par année.

select id_emp, 
         annee, 
         salaire, 
         lag(salaire,2,0) over (partition by id_emp order by annee) as [salaire_N-2],
         case when lag(salaire,2,0) over (partition by id_emp order by annee) = 0
			then 0
			ELSE salaire -lag(salaire,2,0) over (partition by id_emp order by annee) 
         end as evolution
  from [test].[dbo].[employe]

Résultat :

3 – Exemple d’utilisation dans une clause « update »

Les fonctions LEAD et LAG ne peuvent pas être utilisées dans une clause UPDATE. Le seul moyen de mettre à jour une table en utilisant ces fonctions est de faire une jointure sur une table ou les valeurs sont déjà calculées.

Dans cet exemple nous voulons mettre à jour le champ « evolution » avec l’évolution de salaire d’un employé d’une année sur l’autre.

  UPDATE E
  SET  E.evolution  = T.evolution
  FROM [test].[dbo].[employe] E  
  INNER JOIN (
	  select id_emp, 
         annee, 
         salaire, 
         lag(salaire,1,0) over (partition by id_emp order by annee) as [salaire_N-2],
         case when lag(salaire,1,0) over (partition by id_emp order by annee) = 0
			then 0
			ELSE salaire -lag(salaire,1,0) over (partition by id_emp order by annee) 
         end as evolution
       from [test].[dbo].[employe]
  )T ON E.id_emp = T.id_emp AND E.annee = T.annee

Résulat (select * from employe) :

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Captcha *