Fonctions window pour les données temporelles : LEAD(), LAG()

Maintenant, notre mission — aller encore plus loin et apprendre à utiliser les fonctions window pour analyser des données temporelles. Prêt·e ? J’espère que t’as pris un café, parce que ça va être cool.

Alors, comme d’hab, on commence par la question principale : pourquoi on a besoin des fonctions window (LEAD(), LAG()) ? Imagine que tu bosses avec des données temporelles, genre des logs d’événements, des heures de travail, des séries temporelles ou n’importe quoi où l’ordre des événements compte.

Par exemple, tu veux :

• Savoir quand l’événement suivant a eu lieu après l’actuel.
• Calculer la différence de temps entre l’événement actuel et le précédent.
• Trier les données et calculer la différence entre les lignes.

C’est là que deux fonctions trop pratiques arrivent : LEAD() et LAG(). Elles te permettent de choper des données de la ligne précédente ou suivante dans une fenêtre définie. C’est comme si t’avais un livre magique où tu peux voir la page suivante sans tourner la page actuelle.

LEAD() et LAG() : syntaxe et principes de base

Les deux fonctions utilisent une syntaxe similaire :

LEAD(column_name, [offset], [default_value]) OVER (PARTITION BY column_name ORDER BY column_name)
LAG(column_name, [offset], [default_value]) OVER (PARTITION BY column_name ORDER BY column_name)

• column_name — la colonne d’où on veut prendre les données.
• offset (optionnel) — le décalage par rapport à la ligne actuelle. Par défaut c’est 1.
• default_value (optionnel) — la valeur retournée si la ligne avec le décalage demandé n’existe pas (genre si t’es sur la dernière ligne).
• OVER() — ici tu définis la "fenêtre" sur laquelle le calcul va se faire. Le plus souvent c’est ORDER BY, parfois tu utilises PARTITION BY pour séparer les données en groupes.

Exemple : Simple LEAD() et LAG()

On va créer une table simple events pour nos tests :

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    event_name TEXT NOT NULL,
    event_date TIMESTAMP NOT NULL
);

INSERT INTO events (event_name, event_date)
VALUES
    ('Événement A', '2023-10-01 10:00:00'),
    ('Événement B', '2023-10-01 11:00:00'),
    ('Événement C', '2023-10-01 12:00:00'),
    ('Événement D', '2023-10-01 13:00:00');

Maintenant, on veut voir quand les événements précédents et suivants ont eu lieu par rapport à chaque événement :

SELECT
    id,
    event_name,
    event_date,
    LAG(event_date) OVER (ORDER BY event_date) AS evenement_precedent,
    LEAD(event_date) OVER (ORDER BY event_date) AS evenement_suivant
FROM events;

Le résultat sera :

Ici, LAG() prend les données de la ligne précédente, et LEAD() — de la suivante. Le premier événement n’a rien avant lui, et le dernier n’a rien après, donc ils ont NULL.

Exemple : différence entre les événements

Parfois, on veut savoir combien de temps s’est écoulé entre les événements. Pour ça, on peut juste soustraire une date de l’autre :

SELECT
    id,
    event_name,
    event_date,
    event_date - LAG(event_date) OVER (ORDER BY event_date) AS temps_depuis_dernier_evenement
FROM events;

Résultat :

Exemple : utilisation de PARTITION BY

Imaginons qu’on a plusieurs utilisateurs, chacun avec ses propres événements. On veut trouver la différence entre les événements pour chaque utilisateur.

On met à jour la table et on ajoute une colonne user_id :

ALTER TABLE events ADD COLUMN user_id INT;

UPDATE events SET user_id = 1 WHERE id <= 2;
UPDATE events SET user_id = 2 WHERE id > 2;

Maintenant on a deux utilisateurs. On utilise PARTITION BY pour calculer à l’intérieur de chaque groupe :

SELECT
    user_id,
    event_name,
    event_date,
    event_date - LAG(event_date) OVER (PARTITION BY user_id ORDER BY event_date) AS temps_depuis_dernier_evenement
FROM events;

Résultat :

Exemples d’utilisation dans des cas réels

1. Logs d’événements : analyse du temps entre des événements, comme la connexion et la déconnexion d’un utilisateur.
2. Time-tracking : calcul du temps passé sur certaines tâches.
3. Analyse comportementale : analyse de la séquence d’actions des clients dans une boutique en ligne.
4. Calcul de métriques cumulatives : utilisation des fonctions window pour bosser avec des séries temporelles.

Erreurs classiques

Quand tu bosses avec LEAD() et LAG(), les problèmes principaux peuvent être :

• Oublier ORDER BY dans OVER(). Sans ça, la fonction ne saura pas dans quel ordre traiter les lignes.
• Problèmes avec les intervalles de temps ou les types de données (TIMESTAMP vs DATE).
• Ignorer les valeurs NULL qui peuvent apparaître au début ou à la fin de la fenêtre.

Pour éviter ces erreurs, vérifie toujours tes données et assure-toi d’avoir bien défini la fenêtre pour tes opérations.