Leçon 7.4 · Temps de lecture: ~9 minutes

Cette leçon présente les fonctions de fenêtre LAG, LEAD, FIRST_VALUE et LAST_VALUE. Vous allez apprendre à récupérer une valeur précédente ou suivante sans JOIN, à obtenir la première et la dernière valeur dans une fenêtre, et à comprendre pourquoi LAST_VALUE demande souvent un frame explicite. À la fin de cette leçon, vous pourrez utiliser ces fonctions avec assurance pour comparer des lignes, analyser des évolutions et construire des rapports analytiques.

LAG, LEAD, FIRST_VALUE et LAST_VALUE

Dans la leçon précédente, nous avons étudié les frames de fenêtre et vu comment leurs limites influencent les calculs. Passons maintenant aux fonctions qui permettent de regarder en arrière, en avant, et d'obtenir les valeurs aux bornes de la fenêtre.

Ces fonctions sont particulièrement utiles en analyse : elles permettent de comparer les ventes par jour, d'identifier l'action précédente d'un client, de calculer un écart par rapport à une valeur antérieure, et de trouver la première ou la dernière ligne d'un groupe sans self join.

Ce que font ces fonctions

Les quatre fonctions sont des fonctions de fenêtre et s'utilisent avec OVER (...).

• LAG retourne une valeur provenant d'une ligne précédente dans la fenêtre.
• LEAD retourne une valeur provenant d'une ligne suivante dans la fenêtre.
• FIRST_VALUE retourne la première valeur dans la fenêtre courante.
• LAST_VALUE retourne la dernière valeur dans la fenêtre courante.

L'idée essentielle est simple : la ligne courante reste la même, mais elle peut accéder aux valeurs d'autres lignes de la même partition.

Syntaxe de base

LAG et LEAD

LAG(expression [, offset [, default_value]]) OVER (
    [PARTITION BY ...]
    ORDER BY ...
)

LEAD(expression [, offset [, default_value]]) OVER (
    [PARTITION BY ...]
    ORDER BY ...
)

• expression est la valeur à récupérer depuis une autre ligne.
• offset indique de combien de lignes aller en arrière ou en avant.
• default_value définit la valeur retournée si cette ligne n'existe pas.

FIRST_VALUE et LAST_VALUE

FIRST_VALUE(expression) OVER (
    [PARTITION BY ...]
    ORDER BY ...
    [frame_clause]
)

LAST_VALUE(expression) OVER (
    [PARTITION BY ...]
    ORDER BY ...
    [frame_clause]
)

Pour FIRST_VALUE et surtout LAST_VALUE, le frame de fenêtre est important. Sans frame explicite, LAST_VALUE donne souvent un résultat différent de celui attendu par les débutants.

Utiliser LAG

LAG est pratique lorsque vous devez comparer la ligne courante avec la précédente.

Paiement précédent d'un client

SELECT
    customer_id,
    payment_date,
    amount,
    LAG(amount) OVER (
        PARTITION BY customer_id
        ORDER BY payment_date
    ) AS previous_amount
FROM payment
WHERE customer_id = 1
ORDER BY payment_date;

Résultat : chaque ligne montre le paiement courant et le montant du paiement précédent du même client.

Écart par rapport au paiement précédent

SELECT
    customer_id,
    payment_date,
    amount,
    amount - LAG(amount, 1, 0) OVER (
        PARTITION BY customer_id
        ORDER BY payment_date
    ) AS amount_diff
FROM payment
WHERE customer_id = 1
ORDER BY payment_date;

Résultat : vous voyez de combien le paiement courant diffère du précédent. Pour la première ligne, 0 est utilisé par défaut.

Utiliser LEAD

LEAD fonctionne de façon symétrique, mais regarde vers l'avant.

Paiement suivant d'un client

SELECT
    customer_id,
    payment_date,
    amount,
    LEAD(amount) OVER (
        PARTITION BY customer_id
        ORDER BY payment_date
    ) AS next_amount
FROM payment
WHERE customer_id = 1
ORDER BY payment_date;

Résultat : chaque ligne affiche le montant du paiement suivant de ce client.

Date de location suivante

SELECT
    customer_id,
    rental_date,
    LEAD(rental_date) OVER (
        PARTITION BY customer_id
        ORDER BY rental_date
    ) AS next_rental_date
FROM rental
WHERE customer_id = 1
ORDER BY rental_date;

Résultat : la requête montre quand ce même client effectuera la location suivante.

Utiliser FIRST_VALUE

FIRST_VALUE retourne la première valeur dans la fenêtre. C'est utile lorsque vous voulez comparer la ligne courante à un point de départ.

Premier paiement d'un client

SELECT
    customer_id,
    payment_date,
    amount,
    FIRST_VALUE(amount) OVER (
        PARTITION BY customer_id
        ORDER BY payment_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS first_payment_amount
FROM payment
WHERE customer_id = 1
ORDER BY payment_date;

Résultat : le montant du tout premier paiement du client est répété sur chaque ligne de la fenêtre.

Comparer le paiement courant au premier

SELECT
    customer_id,
    payment_date,
    amount,
    amount - FIRST_VALUE(amount) OVER (
        PARTITION BY customer_id
        ORDER BY payment_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS diff_from_first
FROM payment
WHERE customer_id = 1
ORDER BY payment_date;

Résultat : cela aide à mesurer l'écart entre les valeurs courantes et la première valeur de la séquence.

Utiliser LAST_VALUE

LAST_VALUE semble simple, mais c'est ici que les attentes sont souvent trompeuses.

Nuance importante : le frame par défaut

Si vous écrivez ceci :

SELECT
    customer_id,
    payment_date,
    amount,
    LAST_VALUE(amount) OVER (
        PARTITION BY customer_id
        ORDER BY payment_date
    ) AS last_amount_default
FROM payment
WHERE customer_id = 1
ORDER BY payment_date;

alors, dans de nombreux SGBD, le résultat n'est pas la dernière valeur de toute la partition, mais la valeur à la fin du frame courant. Très souvent, cela correspond simplement à la ligne courante.

Version correcte pour la dernière valeur de la partition

SELECT
    customer_id,
    payment_date,
    amount,
    LAST_VALUE(amount) OVER (
        PARTITION BY customer_id
        ORDER BY payment_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_payment_amount
FROM payment
WHERE customer_id = 1
ORDER BY payment_date;

Résultat : chaque ligne peut maintenant voir le montant du dernier paiement du client dans toute la partition.

Pourquoi c'est utile

Ce schéma est pratique lorsque vous devez comparer une valeur courante à la dernière valeur connue d'une série, au statut final d'une commande, ou au dernier paiement d'un client.

Comparaison de LAG, LEAD, FIRST_VALUE et LAST_VALUE

Si le besoin principal est de comparer des lignes voisines, LAG et LEAD sont en général les bons choix. Si vous avez besoin d'un point de référence au début ou à la fin de la fenêtre, utilisez FIRST_VALUE et LAST_VALUE.

Exemple pratique : chiffre d'affaires journalier et comparaison avec les jours voisins

Commençons par agréger les paiements par jour, puis appliquons les fonctions de fenêtre au résultat déjà agrégé :

SELECT
    pay_day,
    daily_total,
    LAG(daily_total) OVER (ORDER BY pay_day) AS previous_day_total,
    LEAD(daily_total) OVER (ORDER BY pay_day) AS next_day_total,
    FIRST_VALUE(daily_total) OVER (
        ORDER BY pay_day
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS first_day_total,
    LAST_VALUE(daily_total) OVER (
        ORDER BY pay_day
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_day_total
FROM (
    SELECT
        DATE(payment_date) AS pay_day,
        SUM(amount) AS daily_total
    FROM payment
    GROUP BY DATE(payment_date)
) AS daily_stats
ORDER BY pay_day;

Résultat : chaque date obtient le chiffre d'affaires du jour précédent, du jour suivant, ainsi que les première et dernière valeurs de la séquence complète.

Ce modèle est très utile pour l'analyse de séries temporelles, la préparation de tableaux de bord et la détection des variations de tendance.

Questions fréquentes

Quelle est la différence entre LAG et LEAD ?

LAG regarde en arrière et retourne une valeur issue d'une ligne précédente, tandis que LEAD regarde en avant et retourne une valeur issue d'une ligne suivante. Les deux fonctions opèrent dans la fenêtre définie et selon l'ordre de tri choisi.

Pourquoi LAST_VALUE retourne-t-il souvent la ligne courante ?

Parce que le résultat dépend du frame de fenêtre. Si vous gardez le frame par défaut, la dernière ligne du frame peut être la ligne courante. Pour obtenir la dernière valeur de toute la partition, il faut généralement écrire ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

Peut-on utiliser LAG et LEAD sans PARTITION BY ?

Oui. Dans ce cas, la fonction travaille sur l'ensemble du résultat comme sur une seule grande partition. C'est utile lorsque vous voulez analyser une séquence globale sans la séparer en groupes.

Questions d'entretien

Quand utiliser LAG et quand utiliser LEAD ?

Utilisez LAG lorsque vous devez comparer la ligne courante avec la précédente, par exemple pour trouver la variation par rapport au paiement précédent. Utilisez LEAD lorsque vous devez regarder vers l'avant, par exemple pour obtenir la date de l'événement suivant ou la prochaine valeur d'une métrique.

En quoi FIRST_VALUE diffère-t-il de MIN ?

MIN retourne la valeur minimale sur un ensemble de lignes, tandis que FIRST_VALUE retourne la valeur de la première ligne selon l'ordre défini. Si l'ordre de tri ne correspond pas à la valeur minimale, les résultats seront différents.

Pourquoi LAST_VALUE exige-t-il souvent un frame explicite ?

Parce que LAST_VALUE ne signifie pas "la dernière ligne de la partition dans tous les cas". Elle signifie la dernière ligne du frame courant. Si le frame par défaut se termine sur la ligne courante, la fonction retournera la valeur courante. Un frame explicite étend la fenêtre à toute la partition.

Points clés de cette leçon:

• LAG et LEAD permettent d'accéder aux lignes voisines sans self join.
• FIRST_VALUE et LAST_VALUE retournent les valeurs aux bornes de la fenêtre, et pas simplement le minimum ou le maximum.
• Pour toutes ces fonctions, la clause ORDER BY est essentielle car elle définit la séquence des lignes.
• LAST_VALUE exige souvent le frame explicite UNBOUNDED PRECEDING ... UNBOUNDED FOLLOWING.
• Ces fonctions sont particulièrement utiles pour l'analyse de séquences, les séries temporelles et la détection de changements entre lignes.