Cours MariaDB / Calculer un cumul et comparer les lignes avec LAG et LEAD
Dans MariaDB, SUM(...) OVER(...) calcule un total cumulé tout en conservant les lignes, tandis que LAG et LEAD lisent la valeur d’une ligne précédente ou suivante dans l’ordre choisi. Pour obtenir un cumul reproductible, précisez cet ordre, y compris en cas d’horaires identiques, et le cadre des lignes à additionner.
Définir l’unité du cumul et l’ordre chronologique
Un cumul par commande et un cumul par jour ne représentent pas le même calcul. Ici, nous additionnons d’abord les quantités multipliées par les prix unitaires enregistrés dans chaque commande payée, puis nous cumulons ces montants par date et identifiant de commande. Les frais de livraison sont exclus ; le traitement des annulations et des remboursements relève d’une règle métier distincte. Seules les commandes dont le statut vaut paid participent à cet exemple.
Une fonction de fenêtre ajoute un calcul aux lignes du résultat sans les fusionner. Le comparatif de ROW_NUMBER, RANK et DENSE_RANK explique cette différence avec le regroupement. Pour distinguer une ligne précédente d’une durée écoulée, consultez les calculs de périodes et de différences entre dates.
| Calcul | Lignes consultées | Comportement aux limites |
|---|---|---|
| SUM avec une fenêtre cumulative | De la première ligne à la ligne courante | Le premier cumul est le montant de la première ligne |
| LAG | Une ligne antérieure dans l’ordre défini | NULL si aucune ligne précédente n’existe |
| LEAD | Une ligne ultérieure dans l’ordre défini | NULL si aucune ligne suivante n’existe |
Le cadre de fenêtre est la partie de la séquence utilisée pour le calcul de la ligne courante. Il est utile pour le SUM cumulé. LAG et LEAD se définissent ici par la position de la ligne voisine dans leur clause OVER ; nous ne leur ajoutons pas le cadre du SUM. La documentation des fonctions de fenêtre de MariaDB présente les clauses d’ordre, de partition et de cadre.
Préparer des commandes dont l’ordre est déterminé
Connectez-vous au serveur d’apprentissage avec course_user et sélectionnez shopcourse. Le compte doit pouvoir créer, remplir, lire et supprimer les tables d’exercice. Le guide du client MariaDB et des fichiers SQL UTF-8 présente la connexion. Enregistrez le bloc complet ci-dessous dans un fichier UTF-8, par exemple C:/mariadb-lab/c33-cumul.sql, après avoir créé le dossier. Dans le client MariaDB, lancez SOURCE C:/mariadb-lab/c33-cumul.sql; en adaptant le chemin réel. Ces instructions SQL ne s’exécutent pas directement dans l’invite du système.
Avant de créer les tables, contrôlez le serveur et la base avec SELECT VERSION(), DATABASE();, puis les noms existants avec SHOW TABLES;. Les noms c33_customers, c33_products, c33_orders et c33_items appartiennent à cet exercice. S’ils existent déjà, examinez leur définition et leurs lignes avant toute action. Ne les supprimez que s’il s’agit bien de données d’exercice sans rien à conserver ; ne remplacez pas des tables d’application. Si le serveur ne reconnaît pas les fonctions de fenêtre, vérifiez sa version et sa documentation avant de poursuivre.
USE shopcourse;
CREATE TABLE c33_customers (
customer_id INT NOT NULL PRIMARY KEY,
customer_name VARCHAR(50) NOT NULL,
city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c33_products (
product_id INT NOT NULL PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
category VARCHAR(30) NOT NULL,
price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c33_orders (
order_id INT NOT NULL PRIMARY KEY,
customer_id INT NOT NULL,
ordered_at DATETIME NOT NULL,
status VARCHAR(20) NOT NULL,
shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
CONSTRAINT fk_c33_order_customer FOREIGN KEY (customer_id)
REFERENCES c33_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c33_items (
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL CHECK (quantity > 0),
unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
PRIMARY KEY (order_id, product_id),
CONSTRAINT fk_c33_item_order FOREIGN KEY (order_id)
REFERENCES c33_orders(order_id) ON DELETE RESTRICT,
CONSTRAINT fk_c33_item_product FOREIGN KEY (product_id)
REFERENCES c33_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c33_customers VALUES
(1,'김민수','서울'),(2,'이서연','부산'),
(3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c33_products VALUES
(1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
(3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c33_orders
(order_id,customer_id,ordered_at,status,shipping_fee) VALUES
(101,1,'2026-01-10 10:00:00','paid',1000),
(102,1,'2026-01-20 11:00:00','paid',1000),
(103,2,'2026-02-05 12:00:00','paid',1000),
(104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c33_items VALUES
(101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
(103,1,1,3500),(103,3,2,8000),(104,2,1,1500);
Vous obtenez quatre clients, quatre produits, quatre commandes et six lignes de détail. Les chaînes coréennes restent identiques entre les traductions afin de reproduire les résultats. Les produits 1 à 4 correspondent à un carnet, un stylo, une tasse et une lampe. Les clients 1 et 3 ont la valeur de ville 서울 (Séoul), les clients 2 et 4 respectivement 부산 (Busan) et 대전 (Daejeon). Conservez ces littéraux en UTF-8 et suivez les identifiants.
La séquence payée comprend la commande 101 du 10 janvier, la commande 102 du 20 janvier et la commande 103 du 5 février. Leurs montants de produits sont respectivement 11500, 8000 et 19500. La commande 104 est annulée et n’entre pas dans le calcul. En cas d’horaires identiques, order_id sert de critère secondaire unique. Cela détermine l’ordre des ex æquo sans supposer que les identifiants représentent toujours l’ordre chronologique dans un système réel.
Calculer le cumul avec un cadre ROWS explicite
Exécutez la requête suivante dans shopcourse. L’expression de table commune, ou CTE (Common Table Expression), appelée order_totals produit d’abord une ligne par commande payée. Le SELECT extérieur calcule ensuite le cumul et les montants voisins sur ces mêmes lignes.
WITH order_totals AS (
SELECT o.order_id,o.ordered_at,SUM(i.quantity*i.unit_price) AS goods_total
FROM c33_orders o JOIN c33_items i ON i.order_id=o.order_id
WHERE o.status='paid' GROUP BY o.order_id,o.ordered_at
)
SELECT order_id,goods_total,
SUM(goods_total) OVER(ORDER BY ordered_at,order_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
LAG(goods_total) OVER(ORDER BY ordered_at,order_id) AS previous_total,
LEAD(goods_total) OVER(ORDER BY ordered_at,order_id) AS next_total
FROM order_totals ORDER BY ordered_at,order_id;
| Commande | Montant | Cumul | Précédent | Suivant |
|---|---|---|---|---|
| 101 | 11500.00 | 11500.00 | NULL | 8000.00 |
| 102 | 8000.00 | 19500.00 | 11500.00 | 19500.00 |
| 103 | 19500.00 | 39000.00 | 8000.00 | NULL |
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW additionne les lignes depuis la première jusqu’à la ligne courante incluse. Le cumul passe donc de 11500 à 19500, puis à 39000. Le ORDER BY dans OVER définit le calcul ; le ORDER BY final définit l’affichage. Conservez les deux quand les deux ordres comptent.
Préciser le cadre évite de laisser une intention de cumul ligne par ligne dépendre du cadre par défaut. Avec des valeurs de tri égales, ROWS et RANGE peuvent inclure des ensembles différents : RANGE rassemble les lignes partageant les mêmes valeurs de tri, tandis que ROWS suit les positions de lignes. Un critère secondaire unique rend aussi l’ordre des positions explicite.
Le petit exemple autonome suivant compare ces cas sans modifier les tables. Les commandes 501 et 502 ont le même horaire, mais des montants différents.
WITH sample AS (
SELECT 501 AS order_id,CAST('2026-01-10 10:00:00' AS DATETIME) AS ordered_at,10 AS amount
UNION ALL SELECT 502,'2026-01-10 10:00:00',20
UNION ALL SELECT 503,'2026-01-11 10:00:00',5
)
SELECT order_id,amount,
SUM(amount) OVER (ORDER BY ordered_at,order_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_unique,
SUM(amount) OVER (ORDER BY ordered_at,order_id
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS range_unique,
SUM(amount) OVER (ORDER BY ordered_at
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS range_time
FROM sample ORDER BY ordered_at,order_id;
| order_id | amount | rows_unique | range_unique | range_time |
|---|---|---|---|---|
| 501 | 10 | 10 | 10 | 30 |
| 502 | 20 | 30 | 30 | 30 |
| 503 | 5 | 35 | 35 | 35 |
Les colonnes rows_unique et range_unique gardent exactement le même tri par horaire et identifiant ; seul le type de cadre change. L’identifiant unique supprime les ex æquo et les deux colonnes donnent 10, 30 et 35. Dans range_time, seul l’identifiant est retiré du tri de RANGE : les deux commandes de même horaire entrent ensemble dans le cadre, ce qui donne 30, 30 et 35. Un cadre ROWS avec l’horaire seul ne garantirait pas l’ordre entre les deux commandes à égalité.
Interpréter les lignes voisines et les valeurs absentes
D’après les syntaxes de LAG dans MariaDB et de LEAD dans MariaDB, le décalage par défaut est d’une ligne. LAG lit donc ici le montant de la commande précédente, et LEAD celui de la suivante. « Précédente » ne signifie pas « la veille » : entre les commandes 101 et 102, dix jours s’écoulent, mais elles restent voisines dans la séquence payée.
LAG ne crée pas de lignes pour les jours sans commande. Une comparaison quotidienne exige d’abord une agrégation par jour et une règle pour les dates absentes. Une valeur non observée et un montant mesuré égal à zéro ne transmettent pas la même information.
Soustraire le montant précédent au montant courant donne l’écart entre deux commandes. La première ligne n’a pas de prédécesseur : son écart est donc NULL. Remplacer systématiquement cette valeur par zéro avec COALESCE reviendrait à choisir une référence initiale de zéro, et à définir l’écart de la première commande comme son montant entier. Choisissez cette convention selon le rapport, plutôt que pour modifier seulement son apparence.
Appliquer le filtre avant ou après le cumul
Un filtre WHERE qui conserve février avant le calcul de fenêtre fait commencer le cumul à l’intérieur de février. Pour afficher les lignes de février avec un cumul qui inclut janvier, terminez le calcul dans une CTE, puis appliquez le filtre de date à l’extérieur. Ces deux requêtes sont valides, mais elles répondent à deux questions différentes.
Vérifiez aussi l’unité d’entrée : si les détails ne sont pas regroupés par commande, le cumul avance par ligne de détail. Pour définir le rapport, indiquez l’unité, les statuts retenus, les clés de tri, le cadre et le moment du filtrage. Dans les deux requêtes complètes suivantes, les tables c33 sont seulement lues.
La première requête cumule toutes les commandes payées, puis affiche février :
WITH order_totals AS (
SELECT o.order_id,o.ordered_at,SUM(i.quantity*i.unit_price) AS goods_total
FROM c33_orders o JOIN c33_items i ON i.order_id=o.order_id
WHERE o.status='paid' GROUP BY o.order_id,o.ordered_at
), running AS (
SELECT order_id,ordered_at,goods_total,
SUM(goods_total) OVER (ORDER BY ordered_at,order_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM order_totals
)
SELECT order_id,goods_total,running_total FROM running
WHERE ordered_at >= '2026-02-01' AND ordered_at < '2026-03-01'
ORDER BY ordered_at,order_id;
La seconde conserve le même intervalle de dates, mais le filtre précède le calcul de fenêtre :
WITH order_totals AS (
SELECT o.order_id,o.ordered_at,SUM(i.quantity*i.unit_price) AS goods_total
FROM c33_orders o JOIN c33_items i ON i.order_id=o.order_id
WHERE o.status='paid' GROUP BY o.order_id,o.ordered_at
)
SELECT order_id,goods_total,
SUM(goods_total) OVER (ORDER BY ordered_at,order_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM order_totals
WHERE ordered_at >= '2026-02-01' AND ordered_at < '2026-03-01'
ORDER BY ordered_at,order_id;
| Position du filtre | order_id | goods_total | running_total |
|---|---|---|---|
| Après le cumul | 103 | 19500.00 | 39000.00 |
| Avant le cumul | 103 | 19500.00 | 19500.00 |
Les deux résultats contiennent la seule commande 103. Le premier conserve le cumul depuis janvier ; le second commence au montant de février. L’intervalle du 1er février inclus au 1er mars exclu couvre aussi les heures de la dernière journée du mois. Pour comparer le comportement, déplacez seulement ce filtre entre les deux niveaux et gardez les autres critères inchangés ; revenez à la première requête pour retrouver le cumul de 39000.
Vérifier les limites et nettoyer les objets d’exercice
Recalculez à la main les cumuls 11500, 19500 et 39000, puis contrôlez NULL dans le premier LAG et dans le dernier LEAD. Le cas autonome des horaires égaux permet de vérifier le rôle du critère secondaire sans insérer de nouvelles commandes. Gardez le ORDER BY final explicite et ne dépendez pas de l’ordre accidentel d’un index.
Avant de recommencer, contrôlez la base sélectionnée, les définitions et les lignes réelles, puis sauvegardez tout élément à conserver. Supprimez uniquement les objets jetables de cet exercice, dans l’ordre suivant : les tables enfants avant les tables qu’elles référencent. DROP TABLE supprime les définitions et les données ; un ROLLBACK ordinaire ne les rétablit pas. Séparez donc ce nettoyage des essais de transactions.
DROP TABLE shopcourse.c33_items;
DROP TABLE shopcourse.c33_orders;
DROP TABLE shopcourse.c33_products;
DROP TABLE shopcourse.c33_customers;




