Niveau 19 : Fonctions de fenêtre : LAG, LEAD et cadres

Leçon

Comparer une ligne à la précédente est une question fréquente : la température a-t-elle monté depuis la veille ? Ce mois est-il meilleur que le précédent ? LAG(colonne) OVER (ORDER BY …) renvoie la valeur de la ligne précédente dans l'ordre indiqué ; LEAD renvoie celle de la ligne suivante. temp_max - LAG(temp_max) OVER (ORDER BY day) donne l'évolution par rapport à la veille.

La première ligne n'a pas de précédente : LAG y vaut NULL, et l'évolution aussi. De même, LEAD vaut NULL sur la dernière ligne. LAG(x, 1, 0) renvoie 0 au lieu de NULL, et LAG(x, 2) remonte de deux lignes.

Avec PARTITION BY, LAG et LEAD restent dans le groupe : la première vente de chaque vendeur n'a pas de précédente, même si la vente d'un autre vendeur la précède dans la table.

On peut aussi choisir précisément les lignes qui entrent dans le calcul : c'est le cadre (frame). ROWS BETWEEN 2 PRECEDING AND CURRENT ROW prend la ligne en cours et les deux précédentes. AVG(temp_max) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) donne une moyenne glissante sur 3 jours, qui lisse les variations. Les deux premières lignes ont moins de voisins : leur moyenne porte sur 1, puis 2 valeurs.

Il existe deux façons de délimiter un cadre. ROWS compte des lignes. RANGE raisonne sur les valeurs de tri : les lignes à égalité entrent ou sortent ensemble. Sans cadre écrit, un OVER avec ORDER BY utilise RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW : c'est ce qui explique les cumuls à égalité du niveau 18. Pour une fenêtre glissante, écris toujours ROWS.

Comme pour les rangs, on ne filtre pas sur LAG dans le WHERE de la même requête : on le calcule dans une CTE, puis on filtre dessus, par exemple pour trouver les mois en hausse.

Syntaxe

SELECT d,
       x,
       x - LAG(x) OVER (ORDER BY d) AS evolution,
       AVG(x) OVER (ORDER BY d ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moy3
FROM ma_table;

Exemple commenté

SELECT month, amount, LAG(amount) OVER (ORDER BY month) AS previous
FROM sales
WHERE seller = 'Ana';

Chaque mois d'Ana affiche le montant du mois précédent ; janvier n'en a pas (NULL).

Résultat de l’exemple

À retenir

• LAG = ligne précédente, LEAD = ligne suivante.
• La première (ou dernière) ligne donne NULL.
• ROWS BETWEEN n PRECEDING AND CURRENT ROW = fenêtre glissante.

Pièges fréquents

• Sans ORDER BY dans OVER, « précédente » n'a pas de sens.
• Filtrer WHERE amount > LAG(amount) … dans la même requête est interdit : passe par une CTE.

Pour aller plus loin

FIRST_VALUE(x) et LAST_VALUE(x) renvoient la première et la dernière valeur du cadre.

Les 5 exercices du niveau

1. Guidé · Pour Paris, affiche chaque jour, la température maximale et son évolution par rapport à la veille (change). (Table : readings)
2. Entraînement · Pour chaque écoute, affiche l'utilisateur, l'heure d'écoute et l'heure de son écoute suivante (next_play). (Table : plays)
3. Entraînement · Pour chaque ville et chaque jour, affiche la température maximale et sa moyenne sur les 3 derniers jours (le jour même et les 2 précédents), arrondie à une décimale (avg3). (Table : readings)
4. Entraînement · Toutes villes confondues, calcule le cumul de pluie jour après jour de deux façons : cum_range avec le cadre par défaut (OVER (ORDER BY day)), et cum_rows ligne par ligne, avec ROWS et un tri par jour puis par ville. Affiche la ville, le jour, la pluie et les deux cumuls, triés par jour puis par ville. (Table : readings)
5. Défi · Affiche les mois où un vendeur a fait mieux que le mois précédent : vendeur, mois et hausse (growth). (Table : sales)

Dans SpeedQL, chaque requête est corrigée tout de suite : le résultat est comparé à celui attendu, puis la requête est relancée sur une base de contrôle cachée. Chaque exercice a ses indices écrits, à afficher seulement si tu bloques.

Voir aussi : la fiche de l’aide-mémoire · les exercices SQL corrigés sur cette notion