SQL Interview Prep · Leçon

LAG et LEAD pour les lignes adjacentes

Accéder aux valeurs des lignes précédente et suivante sans jointure réflexive

LAG et LEAD pour les lignes adjacentes est une leçon SQL Interview Prep gratuite sur CoddyKit. Ceci est la leçon 1 sur 4. Tu peux lire la leçon complète ci-dessous gratuitement — puis la pratiquer en direct dans le navigateur avec un éditeur de code intégré et un tuteur IA 24/7. Elle fait partie du parcours d'apprentissage SQL Interview Prep, et ta progression se synchronise sur le web et l'application CoddyKit. Le cours SQL Interview Prep comprend 4 leçons au total.

La question posée par les recruteurs

L'une des questions les plus fréquentes lors d'un entretien d'analyste est la suivante : « Comparez chaque ligne à celle qui la précède sans jointure réflexive. » Pensez aux revenus d'un mois sur l'autre, à la connexion précédente d'un utilisateur ou à l'événement suivant dans une séquence.

La réponse élégante repose sur les fonctions de fenêtre LAG et LEAD. Elles permettent à une ligne d'examiner la valeur d'une ligne voisine tout en conservant chaque ligne détaillée. Dans cette leçon, vous allez construire un modèle mental précis de leur navigation entre les lignes adjacentes.

Fonctionnement de LAG et LEAD

LAG(col) renvoie la valeur de col de la ligne précédente. LEAD(col) renvoie la valeur de la ligne suivante. « Précédente » et « suivante » sont entièrement définies par le ORDER BY présent dans la clause OVER.

LAG regarde en arrière.
LEAD regarde en avant.

Les deux sont des fonctions de fenêtre avec décalage : elles ne regroupent jamais les lignes, mais ajoutent simplement la valeur d'une ligne voisine à la ligne courante.

Syntaxe de base de LAG

Voici la forme canonique. Nous disposons d'une table sales contenant un month et un revenue. Nous voulons également afficher le chiffre d'affaires du mois précédent sur chaque ligne.

Le OVER (ORDER BY month) indique au moteur comment définir la notion de « précédent ». La première ligne n'a pas de prédécesseur : prev_revenue vaut donc NULL à cet endroit.

SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_revenue
FROM sales
ORDER BY month;

Lire le résultat

Pour les données 2024-01 = 100, 2024-02 = 130, 2024-03 = 120, la requête renvoie :

Janv. : chiffre d'affaires 100, prev_revenue NULL
Févr. : chiffre d'affaires 130, prev_revenue 100
Mars : chiffre d'affaires 120, prev_revenue 130

Chaque ligne a récupéré la valeur de la ligne située juste au-dessus dans l'ensemble ordonné. Pas de jointure réflexive, pas de sous-requête, aucune ligne perdue.

LEAD regarde en avant

LEAD est l'image miroir de LAG. Utilisez-le lorsqu'une ligne doit connaître ce qui vient ensuite, par exemple la date du prochain achat pour calculer le délai entre les commandes.

La dernière ligne de l'ensemble ordonné n'a pas de successeur : son résultat LEAD vaut donc NULL.

SELECT
  month,
  revenue,
  LEAD(revenue) OVER (ORDER BY month) AS next_revenue
FROM sales
ORDER BY month;

L'argument de décalage

Les deux fonctions acceptent un deuxième argument facultatif : le nombre de lignes à franchir. LAG(col, 2) recule de deux lignes et LEAD(col, 3) avance de trois lignes.

Les recruteurs s'en servent pour demander, par exemple, « le chiffre d'affaires d'il y a deux mois » ou « la valeur située trois lignes plus bas ». Le décalage par défaut est 1.

SELECT
  month,
  revenue,
  LAG(revenue, 2) OVER (ORDER BY month) AS revenue_2_months_ago
FROM sales
ORDER BY month;

L'argument de valeur par défaut

Un troisième argument fournit une valeur de remplacement lorsqu'il n'existe aucune ligne voisine, au lieu d'obtenir NULL. La signature est LAG(col, offset, default).

C'est utile lorsqu'un calcul ultérieur ne peut pas accepter NULL, par exemple en considérant que la valeur précédente manquante vaut 0 afin de pouvoir calculer une différence.

SELECT
  month,
  revenue,
  LAG(revenue, 1, 0) OVER (ORDER BY month) AS prev_revenue
FROM sales
ORDER BY month;

PARTITION BY réinitialise la fenêtre

Les données réelles comportent rarement une seule série globale. Vous comparez généralement les lignes au sein de chaque client, produit ou région. PARTITION BY recommence le calcul de LAG/LEAD au début de chaque partition.

Ainsi, la première ligne de chaque partition reçoit NULL de la part de LAG : aucune valeur ne franchit la limite pour se retrouver dans les données d'un autre client.

SELECT
  customer_id,
  order_date,
  amount,
  LAG(amount) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
  ) AS prev_amount
FROM orders;

Exemple détaillé : nombre de jours entre les commandes

Une tâche fréquente consiste à mesurer l'écart entre les commandes consécutives d'un client. Récupérez la date de la commande précédente avec LAG, puis effectuez la soustraction.

La première commande de chaque client produit NULL, car aucune date antérieure n'est disponible pour effectuer la soustraction. C'est exactement le type de comparaison par client que les recruteurs attendent que les fonctions de fenêtre permettent de résoudre.

SELECT
  customer_id,
  order_date,
  order_date - LAG(order_date) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
  ) AS days_since_prev
FROM orders;

Pourquoi ne pas utiliser une auto-jointure ?

Avant l'apparition des fonctions de fenêtre, la solution consistait en une auto-jointure corrélée : joindre la table à elle-même sur « la ligne dont la date est la plus grande parmi les dates antérieures à celle-ci ». Cela fonctionne, mais la requête est verbeuse, sujette aux erreurs en cas d'égalité et souvent plus lente.

LAG/LEAD expriment l'intention sur une seule ligne.
Ils sont évalués en un seul parcours ordonné.
Les égalités sont résolues de manière déterministe par votre ORDER BY.

Dire « J'utiliserais LAG plutôt qu'une auto-jointure » signale votre aisance.

Piège courant : <code>ORDER BY</code> manquant

Sans ORDER BY dans la clause OVER, la « ligne précédente » n'est pas définie. Certains moteurs rejettent cette requête, tandis que d'autres renvoient des résultats imprévisibles. Ordonnez toujours la fenêtre.

N'oubliez pas non plus que le tri à l'intérieur de OVER est indépendant du ORDER BY externe de la requête. La fenêtre détermine la ligne voisine ; la clause externe détermine uniquement l'ordre d'affichage.

Vérification rapide

Évaluez votre compréhension des fonctions de fenêtre avec décalage.

Récapitulatif

Vous connaissez maintenant les fonctions de fenêtre avec décalage :

LAG(col) lit la ligne précédente et LEAD(col) la suivante, selon le ORDER BY de la fenêtre.
Arguments facultatifs : LAG(col, offset, default).
PARTITION BY réinitialise la navigation pour chaque groupe, donc les lignes aux limites valent NULL.
Elles remplacent les auto-jointures complexes pour comparer des lignes adjacentes.

Ensuite, nous appliquons cela à la question incontournable pour les analystes : l'évolution d'une période à l'autre.

Questions Fréquemment Posées

La leçon « LAG et LEAD pour les lignes adjacentes » est-elle gratuite ?

Oui — le texte complet de « LAG et LEAD pour les lignes adjacentes » est gratuit à lire ici sur le web. Pour la pratiquer de manière interactive (un éditeur de code intégré et un tuteur IA 24/7) et déverrouiller le reste du cours SQL Interview Prep, passe à CoddyKit PRO. Le cours SQL Interview Prep comprend 4 leçons au total.

Qu'est-ce que j'apprendrai dans « LAG et LEAD pour les lignes adjacentes » ?

Accéder aux valeurs des lignes précédente et suivante sans jointure réflexive Tu pratiques SQL Interview Prep avec du code pratique que tu exécutes directement dans le navigateur, et un tuteur IA 24/7 répond à tes questions au fur et à mesure que tu avances dans la leçon.

Dois-je avoir de l'expérience pour commencer SQL Interview Prep ?

Aucune expérience préalable n'est requise. SQL Interview Prep sur CoddyKit est structuré pour les débutants jusqu'aux apprenants avancés, donc tu peux commencer ici ou depuis le début et avancer à ton rythme. Ceci est la leçon 1 sur 4.

Combien de temps prend la leçon « LAG et LEAD pour les lignes adjacentes » ?

La plupart des leçons CoddyKit prennent environ 5–10 minutes. Chacune est courte et interactive, tu progresses régulièrement et tu repiques exactement où tu t'es arrêté sur le web et l'app.

Peux-tu écrire et exécuter du code dans cette leçon SQL Interview Prep ?

Oui. Chaque leçon SQL Interview Prep inclut un éditeur de code intégré, tu écris et exécutes du vrai code directement dans ton navigateur et tu reçois des retours IA instantanés — aucune configuration locale requise.

Toutes les leçons de ce cours

LAG et LEAD pour les lignes adjacentes
Évolution d’une période à l’autre
NTILE pour créer des tranches
FIRST_VALUE, LAST_VALUE et limites de fenêtre

← Retour à SQL Interview Prep