LAG Oracle SQL : Fonction Analytique Expliquée

LAG Oracle SQL : Maîtriser la Fonction Analytique de Décalage

La fonction LAG Oracle SQL est une fonction analytique puissante qui permet d’accéder à la valeur d’une ligne précédente dans un ensemble de résultats, sans avoir recours à une auto-jointure. Incontournable dans le domaine de l’analyse de données, LAG est particulièrement utilisée pour comparer des valeurs entre périodes, calculer des évolutions ou détecter des tendances directement dans vos requêtes SQL Oracle.

Définition et utilisation de LAG en Oracle SQL

La fonction LAG fait partie de la famille des fonctions de fenêtrage analytiques introduites par Oracle. Elle permet de récupérer la valeur d’une expression sur une ligne située avant la ligne courante, selon un ordre défini. Son pendant inverse est la fonction LEAD, qui, elle, accède aux lignes suivantes.

Cas d’usage en entreprise

Dans un contexte professionnel, LAG est utilisée dans de nombreux scénarios :

Analyse financière : comparer le chiffre d’affaires mensuel avec celui du mois précédent pour calculer une croissance.
Ressources humaines : suivre l’évolution du salaire d’un employé d’une année sur l’autre.
Logistique : détecter des anomalies dans les délais de livraison en comparant chaque livraison à la précédente.
E-commerce : mesurer l’évolution du nombre de commandes d’un client entre deux achats consécutifs.

L’un des atouts majeurs de LAG est qu’elle évite l’écriture de requêtes complexes avec des auto-jointures (self-joins), rendant le code plus lisible, plus performant et plus maintenable.

Syntaxe complète de la fonction LAG

Voici la syntaxe officielle de la fonction LAG en Oracle SQL :

LAG (expression [, décalage [, valeur_par_défaut]])
OVER (
    [PARTITION BY partition_expression]
    ORDER BY ordre_expression [ASC | DESC]
)

Explication des paramètres

Paramètre	Obligatoire	Description
`expression`	Oui	La colonne ou l’expression dont on veut récupérer la valeur précédente.
`décalage`	Non (défaut : 1)	Le nombre de lignes en arrière à parcourir. Par défaut, Oracle remonte d’une seule ligne.
`valeur_par_défaut`	Non (défaut : NULL)	La valeur retournée si la ligne précédente n’existe pas (première ligne de la partition).
`PARTITION BY`	Non	Divise le résultat en groupes indépendants sur lesquels LAG s’applique séparément.
`ORDER BY`	Oui	Définit l’ordre de traitement des lignes au sein de chaque partition.

Remarque Oracle : La clause ORDER BY est obligatoire dans la clause OVER() pour que LAG soit utilisée de manière déterministe. Sans ordre défini, les résultats seraient imprévisibles.

Exemples pratiques de LAG en Oracle SQL

Exemple 1 – Comparer le chiffre d’affaires mensuel

Contexte : une entreprise souhaite analyser l’évolution de ses ventes mensuelles pour l’année 2024. La table VENTES_MENSUELLES contient le chiffre d’affaires par mois.

-- Table de référence : VENTES_MENSUELLES (MOIS, CA)
-- Objectif : afficher le CA du mois courant, celui du mois précédent
-- et calculer l'écart entre les deux

SELECT
    MOIS,
    CA AS ca_courant,
    LAG(CA, 1, 0) OVER (ORDER BY MOIS) AS ca_mois_precedent,
    CA - LAG(CA, 1, 0) OVER (ORDER BY MOIS) AS ecart
FROM VENTES_MENSUELLES
ORDER BY MOIS;

Résultat attendu (exemple) :

MOIS	CA_COURANT	CA_MOIS_PRECEDENT	ECART
2024-01	50 000	0	50 000
2024-02	62 000	50 000	12 000
2024-03	58 000	62 000	-4 000

Ici, le troisième paramètre 0 permet d’éviter un NULL sur la première ligne, en lui attribuant une valeur par défaut de zéro.

Exemple 2 – Évolution des salaires par département

Contexte : le service RH veut visualiser l’évolution des salaires des employés au fil du temps, département par département, en utilisant la table standard Oracle HR.EMPLOYEES.

-- Utilisation de PARTITION BY pour isoler chaque département
-- LAG remonte de 1 ligne dans l'ordre chronologique d'embauche

SELECT
    EMPLOYEE_ID,
    FIRST_NAME || ' ' || LAST_NAME AS nom_employe,
    DEPARTMENT_ID,
    HIRE_DATE,
    SALARY AS salaire_actuel,
    LAG(SALARY) OVER (
        PARTITION BY DEPARTMENT_ID
        ORDER BY HIRE_DATE
    ) AS salaire_employe_precedent,
    SALARY - LAG(SALARY) OVER (
        PARTITION BY DEPARTMENT_ID
        ORDER BY HIRE_DATE
    ) AS difference_salaire
FROM HR.EMPLOYEES
ORDER BY DEPARTMENT_ID, HIRE_DATE;

Dans cet exemple, grâce à PARTITION BY DEPARTMENT_ID, la fonction LAG est réinitialisée à chaque nouveau département. Cela signifie que le premier employé embauché dans chaque département retournera NULL pour salaire_employe_precedent, ce qui est un comportement attendu et cohérent.

Erreurs courantes avec LAG en Oracle SQL

Erreur : utiliser LAG sans clause ORDER BY dans OVER()

C’est l’erreur la plus fréquente chez les développeurs qui découvrent les fonctions analytiques Oracle. Voici un exemple de requête incorrecte :

-- INCORRECT : absence de ORDER BY dans OVER()
SELECT
    MOIS,
    CA,
    LAG(CA) OVER (PARTITION BY ANNEE) AS ca_precedent -- Erreur ORA-30485
FROM VENTES_MENSUELLES;

Oracle retourne l’erreur suivante :

ORA-30485: missing ORDER BY expression in the window specification

Solution : toujours inclure une clause ORDER BY dans la spécification de fenêtre :

-- CORRECT : ORDER BY présent dans OVER()
SELECT
    MOIS,
    CA,
    LAG(CA) OVER (PARTITION BY ANNEE ORDER BY MOIS) AS ca_precedent
FROM VENTES_MENSUELLES;

Résumé : points clés de la fonction LAG Oracle

Élément	Détail
Type de fonction	Analytique (fenêtrage)
Objectif principal	Accéder à la valeur d’une ligne précédente
Paramètre décalage	Entier positif, défaut = 1
Valeur par défaut	NULL si non spécifiée
Clause obligatoire	`ORDER BY` dans `OVER()`
Clause optionnelle	`PARTITION BY` pour segmenter les données
Fonction inverse	`LEAD` (accède aux lignes suivantes)
Compatible Oracle	À partir d’Oracle 8i

Bonnes pratiques Oracle

Toujours fournir une valeur par défaut explicite : utilisez le troisième paramètre de LAG (valeur_par_défaut) pour éviter les NULL inattendus sur la première ligne de chaque partition, notamment lorsque ces valeurs alimentent des calculs ou des comparaisons.
Préférer LAG aux auto-jointures : sur des volumes importants de données, LAG est généralement plus performant qu’une jointure d’une table sur elle-même. Oracle peut exploiter les index et optimiser le plan d’exécution plus efficacement avec les fonctions analytiques.

Aller plus loin avec les fonctions analytiques Oracle

Vous souhaitez approfondir votre maîtrise des fonctions de fenêtrage en Oracle SQL ? Voici trois sujets complémentaires à explorer :

LEAD Oracle SQL : l’opposé de LAG, pour accéder aux valeurs des lignes suivantes et anticiper des tendances.
ROW_NUMBER Oracle SQL : attribuez un numéro de ligne unique à chaque enregistrement au sein d’une partition, idéal pour la pagination et la déduplication.
Les fonctions analytiques Oracle SQL : tour d’horizon complet de toutes les fonctions de fenêtrage disponibles en Oracle (RANK, DENSE_RANK, NTILE, etc.) pour des analyses avancées.