LAG, LEAD et totaux cumulés

LAG et LEAD sont deux fonctions de fenêtre spéciales qui vous donnent accès à autres lignes de la même partition : la ligne précédente (LAG) ou la suivant (LEAD) selon le ORDER BY de la fenêtre. Ce sont les outil incontournable pour les comparaisons ligne à ligne (deltas, variations, intervalles) et - combiné avec SUM/AVG/COUNT OVER - pour les totaux cumulés.

LAG : la ligne précédente

LAG(colonna)            OVER (PARTITION BY p ORDER BY o)
LAG(colonna, n)         OVER (PARTITION BY p ORDER BY o)   -- n righe indietro
LAG(colonna, n, default) OVER (PARTITION BY p ORDER BY o)  -- default se non c'è

Exemple : pour chaque commande, "jours depuis la commande précédente du même client » :

SELECT id,
       customer_id,
       ordered_at,
       ordered_at - LAG(ordered_at) OVER (
         PARTITION BY customer_id ORDER BY ordered_at
       ) AS since_previous_order
FROM   orders
ORDER BY customer_id, ordered_at;

La première commande de chaque client a since_previous_order = NULL car il n'y a pas de "précédent" dans la partition.

LEAD : la ligne suivante

Le miroir de LAG. Typique : "temps jusqu'au prochain événement", "différence avec la prochaine version".

SELECT id, ordered_at,
       LEAD(ordered_at) OVER (PARTITION BY customer_id ORDER BY ordered_at)
         - ordered_at AS to_next
FROM   orders;

Totaux cumulés : agrégats avec OVER

Les fonctions d'agrégation (SUM, AVG, COUNT, MAX, MIN) deviennent cumulatif en combinaison avec OVER (… ORDER BY …) :

SELECT order_id,
       total,
       SUM(total) OVER (
         PARTITION BY customer_id
         ORDER BY     ordered_at
       ) AS running_total
FROM   …;

Pour chaque ligne, SUM calcule la somme de toutes les lignes de la partition jusqu'à ce point, selon le ORDER BY de la fenêtre. Le résultat est le "balance courante" classique.

Essayez-le

Exercice de révision