%% nav-debut : généré par gen_navigation.py, ne pas modifier à la main %% > [!nav] > ↩ [[Code et Développement/Développement d'Applications/SQL IV/SQL IV|Accueil · SQL IV]] > > | [[Code et Développement/Développement d'Applications/SQL IV/11_Classer - RANK\|← Classer : RANK, DENSE_RANK, ROW_NUMBER]] | [[Code et Développement/Développement d'Applications/SQL IV/13_Répondre à une demande projet\|Répondre à une demande « projet » →]] | > |:---|---:| %% nav-fin %% [[Code et Développement/Développement d'Applications/SQL IV/11_Classer - RANK|Classer - RANK]] # Cumuls et ligne précédente : `SUM() OVER (ORDER BY …)`, `LAG`, `LEAD` ## Introduction Deux questions reviennent très souvent quand les données sont **datées** : - « Quel est le **cumul** depuis le début ? » (chiffre d'affaires cumulé, stock cumulé…) - « Comment la valeur a-t-elle **évolué par rapport à la fois précédente** ? » Les fonctions de fenêtre y répondent en ajoutant un `ORDER BY` dans le `OVER`. ### Objectifs du chapitre À la fin de ce chapitre, vous serez capable de : - Calculer un cumul avec `SUM(…) OVER (ORDER BY …)`, sur toute la table ou par groupe. - Récupérer la valeur de la ligne précédente avec `LAG`, et de la ligne suivante avec `LEAD`. - Calculer une évolution entre deux lignes consécutives. Nous utilisons la table *Commandes* présentée au chapitre [[Code et Développement/Développement d'Applications/SQL IV/01_Supprimer les doublons - DISTINCT|Supprimer les doublons : DISTINCT]], et la notion de fonction de fenêtre vue au chapitre [[Code et Développement/Développement d'Applications/SQL IV/10_Calculer sur un groupe - PARTITION BY|Calculer sur un groupe : PARTITION BY]]. --- ## 1. Le cumul On nous demande : ***le chiffre d'affaires cumulé au fil des commandes, de la plus ancienne à la plus récente.*** ### Ce que vous savez déjà faire (SQL I et SQL III) Avec une **auto-jointure** (SQL III), on associe à chaque commande `Co` toutes les commandes `Co2` passées **jusqu'à sa date** (incluse), puis on additionne leurs montants : ```sql SELECT Co.id_commande, Co.date_commande, Co.montant_total, SUM(Co2.montant_total) AS ca_cumule FROM Commandes Co INNER JOIN Commandes Co2 ON Co2.date_commande <= Co.date_commande GROUP BY Co.id_commande, Co.date_commande, Co.montant_total ORDER BY Co.date_commande; ``` | id_commande | date_commande | montant_total | ca_cumule | | ----------- | ------------- | ------------- | --------- | | 101 | 2023-01-10 | 250.00 | 250.00 | | 102 | 2023-02-18 | 120.00 | 370.00 | | 103 | 2023-03-05 | 560.00 | 930.00 | | 104 | 2023-04-22 | 80.00 | 1010.00 | | 105 | 2023-06-01 | 310.00 | 1320.00 | | 106 | 2023-07-14 | 150.00 | 1470.00 | | 107 | 2023-08-03 | 400.00 | 1870.00 | Pour la commande 103, par exemple, la jointure trouve les commandes 101, 102 et 103 : 250 + 120 + 560 = 930. ### Une autre solution : `SUM(…) OVER (ORDER BY …)` ```sql SELECT id_commande, date_commande, montant_total, SUM(montant_total) OVER (ORDER BY date_commande, id_commande) AS ca_cumule FROM Commandes ORDER BY date_commande; ``` Le résultat est le même. > [!important] `OVER` avec ou sans `ORDER BY` > - `SUM(montant_total) OVER ()` : la somme de **toutes** les commandes (1870 pour chaque ligne, chapitre 10). > - `SUM(montant_total) OVER (ORDER BY date_commande)` : la somme des commandes **depuis la première jusqu'à la ligne en cours**, dans l'ordre des dates. C'est un **cumul**. > > Le simple fait d'ajouter `ORDER BY` dans le `OVER` transforme le total en cumul. > [!tip] Un critère de tri unique > Si deux commandes avaient la même date, elles recevraient le même cumul (les deux montants ajoutés d'un coup). Pour que le cumul avance ligne par ligne, ajoutez un second critère de tri **unique**, comme l'identifiant : `ORDER BY date_commande, id_commande`. ### Le cumul par groupe En ajoutant `PARTITION BY`, le cumul **repart de zéro pour chaque groupe**. On nous demande : ***pour chaque client, le cumul de ses achats au fil de ses commandes.*** ```sql SELECT id_client, id_commande, date_commande, montant_total, SUM(montant_total) OVER (PARTITION BY id_client ORDER BY date_commande, id_commande) AS cumul_client FROM Commandes ORDER BY id_client, date_commande; ``` | id_client | id_commande | date_commande | montant_total | cumul_client | | --------- | ----------- | ------------- | ------------- | ------------ | | 1 | 101 | 2023-01-10 | 250.00 | 250.00 | | 1 | 102 | 2023-02-18 | 120.00 | 370.00 | | 2 | 103 | 2023-03-05 | 560.00 | 560.00 | | 2 | 104 | 2023-04-22 | 80.00 | 640.00 | | 2 | 105 | 2023-06-01 | 310.00 | 950.00 | | 3 | 106 | 2023-07-14 | 150.00 | 150.00 | | 4 | 107 | 2023-08-03 | 400.00 | 400.00 | La dernière ligne de chaque client donne son chiffre d'affaires total (370, 950, 150, 400). --- ## 2. La ligne précédente : `LAG` On nous demande : ***pour chaque commande, le montant de la commande précédente du même client et l'évolution entre les deux.*** ### Ce que vous savez déjà faire (chapitres 02 et 08) Une sous-requête corrélée dans le `SELECT` cherche, parmi les commandes **du même client** passées **avant** la commande examinée, la plus récente (`ORDER BY … DESC LIMIT 1`) : ```sql SELECT Co.id_client, Co.id_commande, Co.date_commande, Co.montant_total, (SELECT Co2.montant_total FROM Commandes Co2 WHERE Co2.id_client = Co.id_client AND Co2.date_commande < Co.date_commande ORDER BY Co2.date_commande DESC LIMIT 1) AS montant_precedent FROM Commandes Co ORDER BY Co.id_client, Co.date_commande; ``` C'est juste, mais long à écrire et à relire, et il faudrait répéter la sous-requête pour calculer l'évolution. ### Une autre solution : `LAG` `LAG(champ)` renvoie la valeur de `champ` sur **la ligne précédente** de la partition, dans l'ordre indiqué par le `ORDER BY` du `OVER`. ```sql SELECT id_client, id_commande, date_commande, montant_total, LAG(montant_total) OVER (PARTITION BY id_client ORDER BY date_commande) AS montant_precedent, montant_total - LAG(montant_total) OVER (PARTITION BY id_client ORDER BY date_commande) AS evolution FROM Commandes ORDER BY id_client, date_commande; ``` | id_client | id_commande | date_commande | montant_total | montant_precedent | evolution | | --------- | ----------- | ------------- | ------------- | ----------------- | --------- | | 1 | 101 | 2023-01-10 | 250.00 | NULL | NULL | | 1 | 102 | 2023-02-18 | 120.00 | 250.00 | -130.00 | | 2 | 103 | 2023-03-05 | 560.00 | NULL | NULL | | 2 | 104 | 2023-04-22 | 80.00 | 560.00 | -480.00 | | 2 | 105 | 2023-06-01 | 310.00 | 80.00 | 230.00 | | 3 | 106 | 2023-07-14 | 150.00 | NULL | NULL | | 4 | 107 | 2023-08-03 | 400.00 | NULL | NULL | Lisez `LAG(montant_total) OVER (PARTITION BY id_client ORDER BY date_commande)` ainsi : *« le montant de la commande précédente **du même client**, dans l'ordre des dates »*. > [!note] Pourquoi des `NULL` ? > La première commande de chaque client n'a **pas de commande précédente** : `LAG` renvoie `NULL`, et l'évolution (un calcul avec `NULL`) vaut aussi `NULL`. Sans `PARTITION BY`, la commande précédente pourrait être celle d'un autre client : pensez toujours à la partition. --- ## 3. La ligne suivante : `LEAD` `LEAD` fait l'inverse de `LAG` : il renvoie la valeur de **la ligne suivante**. On nous demande : ***pour chaque commande, la date de la commande suivante du même client.*** ```sql SELECT id_client, id_commande, date_commande, LEAD(date_commande) OVER (PARTITION BY id_client ORDER BY date_commande) AS commande_suivante FROM Commandes ORDER BY id_client, date_commande; ``` | id_client | id_commande | date_commande | commande_suivante | | --------- | ----------- | ------------- | ----------------- | | 1 | 101 | 2023-01-10 | 2023-02-18 | | 1 | 102 | 2023-02-18 | NULL | | 2 | 103 | 2023-03-05 | 2023-04-22 | | 2 | 104 | 2023-04-22 | 2023-06-01 | | 2 | 105 | 2023-06-01 | NULL | | 3 | 106 | 2023-07-14 | NULL | | 4 | 107 | 2023-08-03 | NULL | La dernière commande de chaque client n'a pas de suivante : `NULL`. --- ## 4. Récapitulatif : que mettre dans le `OVER` ? | Je veux… | J'écris… | | --- | --- | | un calcul sur toute la table, à côté de chaque ligne | `SUM(x) OVER ()` | | un calcul sur le groupe de la ligne | `SUM(x) OVER (PARTITION BY g)` | | un classement | `RANK() OVER (ORDER BY x DESC)` | | un classement dans chaque groupe | `RANK() OVER (PARTITION BY g ORDER BY x DESC)` | | un cumul | `SUM(x) OVER (ORDER BY date, id)` | | un cumul dans chaque groupe | `SUM(x) OVER (PARTITION BY g ORDER BY date, id)` | | la valeur précédente / suivante dans le groupe | `LAG(x) OVER (PARTITION BY g ORDER BY date)` / `LEAD(x) OVER (…)` | > [!tip] Deux questions à se poser > 1. **Sur quel groupe** le calcul porte-t-il ? → `PARTITION BY` (ou rien pour toute la table). > 2. **L'ordre compte-t-il ?** (rang, cumul, précédent, suivant) → `ORDER BY` dans le `OVER`. --- > [!important] Ce qu'il faut retenir > - `SUM(…) OVER (ORDER BY …)` calcule un **cumul** ; avec `PARTITION BY`, le cumul repart de zéro pour chaque groupe. > - Ajoutez un critère de tri unique (l'identifiant) pour un cumul qui avance ligne par ligne. > - `LAG(champ)` : la valeur de la ligne précédente ; `LEAD(champ)` : celle de la ligne suivante. La première (ou dernière) ligne de chaque partition reçoit `NULL`. > - Ce que vous saviez faire (auto-jointure, sous-requête corrélée) reste juste, mais devient vite long à écrire. Dernière étape, mettre tout cela en pratique sur des demandes « réelles » : [[Code et Développement/Développement d'Applications/SQL IV/13_Répondre à une demande projet|Répondre à une demande « projet »]] %% nav-debut : généré par gen_navigation.py, ne pas modifier à la main %% > [!nav] > ↩ [[Code et Développement/Développement d'Applications/SQL IV/SQL IV|Accueil · SQL IV]] > > | [[Code et Développement/Développement d'Applications/SQL IV/11_Classer - RANK\|← Classer : RANK, DENSE_RANK, ROW_NUMBER]] | [[Code et Développement/Développement d'Applications/SQL IV/13_Répondre à une demande projet\|Répondre à une demande « projet » →]] | > |:---|---:| %% nav-fin %% --- 🗓️ Historique Dernière mise à jour : `30 octobre 2026` Rédigé par : [[Vincent TELLIER]]