LAG, LEAD et cumuls
LAG lit la valeur de la ligne précédente, LEAD celle de la suivante, et SUM() OVER (ORDER BY …) calcule un cumul. On compare ainsi une période à la précédente ou on suit un total qui grandit.
- LAG
- LEAD
- cumul
- running total
- moyenne mobile
- ROWS BETWEEN
Syntaxe
LAG(col [, decalage [, defaut]]) OVER (ORDER BY tri)LEAD(col [, decalage [, defaut]]) OVER (ORDER BY tri)SUM(col) OVER (ORDER BY tri ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)Exemple
WITH ventes AS ( SELECT o.id, o.order_date, ROUND(SUM(oi.quantity * p.price), 2) AS montant FROM orders o JOIN order_items oi ON oi.order_id = o.id JOIN products p ON p.id = oi.product_id GROUP BY o.id, o.order_date)SELECT order_date, montant, LAG(montant) OVER (ORDER BY order_date) AS montant_precedent, ROUND(SUM(montant) OVER ( ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ), 2) AS cumulFROM ventesORDER BY order_date;-- Jeu de données : boutique-- La commande annulée, sans ligne de commande, est absente.-- Résultat attendu :-- 2024-01-15 | 149.97 | NULL | 149.97-- 2024-02-20 | 179.98 | 149.97 | 329.95-- 2024-03-10 | 59.99 | 179.98 | 389.94-- 2024-03-25 | 109.98 | 59.99 | 499.92À retenir
- LAG(montant, 1, 0) renvoie 0 au lieu de NULL pour la première ligne. Évolution en pourcentage : ROUND(100.0 * (montant - LAG(montant) OVER w) / LAG(montant) OVER w, 1).
- Moyenne mobile sur trois lignes : AVG(montant) OVER (ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW).
- Piège : sans ROWS BETWEEN, le cadre par défaut est RANGE, qui regroupe les ex æquo du tri. Deux commandes à la même date reçoivent alors le même cumul, qui inclut déjà les deux. Écrivez ROWS pour un cumul ligne à ligne.
- Piège : LAG prend la ligne précédente du résultat, pas le mois précédent du calendrier. Si un mois n'a aucune vente, LAG compare à un mois plus ancien sans le signaler. Complétez d'abord les périodes avec une CTE récursive.
Voir aussi
- Fonctions de fenêtre (OVER)Une fonction de fenêtre calcule un agrégat sur un groupe de lignes tout en gardant chaque ligne, à l'inverse de GROUP BY. On compare ainsi chaque salaire à la moyenne de son service.
- ROW_NUMBER, RANK, DENSE_RANKLes fonctions de classement numérotent les lignes selon un tri, éventuellement par groupe. Elles répondent aux questions de type « top N par catégorie » qu'un simple LIMIT ne sait pas traiter.
- CTE récursive (WITH RECURSIVE)Une CTE récursive part d'un point de départ puis se rappelle elle-même pour avancer d'un niveau à chaque tour. Elle parcourt une hiérarchie complète ou génère une suite de dates.
- Fonctions date & heureSQLite stocke les dates en texte ISO et fournit date(), strftime() et julianday() pour les calculer, les formater et les découper. Base des indicateurs par mois, des délais et des échéances.