Leçon 12.7 · Temps de lecture : ~6 min

Un total cumulé (somme cumulative) ajoute chaque valeur à la somme de toutes les valeurs précédentes : revenu depuis le début de la période, solde d'un compte après chaque opération, inscriptions cumulées. C'est un classique des tableaux de bord et un exercice fréquent en entretien SQL. Dans cette leçon, vous le calculerez avec SUM() OVER (ORDER BY ...), le redémarrerez pour chaque groupe, éviterez le piège des valeurs de tri égales et verrez comment on faisait avant les fonctions de fenêtrage.

Total cumulé en SQL

Un SUM ordinaire avec GROUP BY réduit les lignes à un seul total. Un total cumulé garde chaque ligne et ajoute une colonne avec la somme jusqu'ici. C'est exactement ce que fait une fonction de fenêtrage : SUM(amount) OVER (ORDER BY day) additionne toutes les lignes de la première jusqu'à la ligne courante, dans l'ordre de day. Les fonctions de fenêtrage et les cadres ont été vus dans les leçons 7.1 et 7.3 ; ici, ils résolvent un problème pratique.

Les exemples s'exécutent sur la base d'exemple Sakila (MySQL 8.0).

Revenu cumulé par jour

Agrégez d'abord les paiements par jour, puis ajoutez le total cumulé sur les lignes journalières :

WITH daily AS (
    SELECT DATE(payment_date) AS pay_day,
           SUM(amount) AS daily_amount
    FROM payment
    WHERE payment_date >= '2005-08-01'
      AND payment_date < '2005-09-01'
    GROUP BY DATE(payment_date)
)
SELECT pay_day,
       daily_amount,
       SUM(daily_amount) OVER (ORDER BY pay_day) AS running_total
FROM daily
ORDER BY pay_day;

Résultat : 10 jours avec des paiements en août 2005 ; le total cumulé passe de 2817.29 à 24072.13, le revenu du mois entier.

Agréger d'abord est important : SUM(...) OVER sur les paiements individuels renverrait une ligne par paiement, et non par jour. Les jours sans paiement sont simplement absents ; pour les afficher avec 0, joignez les données à un calendrier (leçon 6.6).

Redémarrer le total pour chaque groupe

PARTITION BY lance un total cumulé séparé pour chaque groupe. Ici, le revenu mensuel reçoit deux colonnes cumulatives : une depuis le début, et une qui redémarre chaque année :

WITH monthly AS (
    SELECT YEAR(payment_date) AS pay_year,
           MONTH(payment_date) AS pay_month,
           SUM(amount) AS monthly_amount
    FROM payment
    GROUP BY YEAR(payment_date), MONTH(payment_date)
)
SELECT pay_year, pay_month, monthly_amount,
       SUM(monthly_amount) OVER (ORDER BY pay_year, pay_month) AS running_total,
       SUM(monthly_amount) OVER (PARTITION BY pay_year ORDER BY pay_month) AS year_to_date
FROM monthly
ORDER BY pay_year, pay_month;

Résultat : 5 mois ; le total cumulé global atteint 67416.51, tandis que le cumul annuel redémarre en 2006 à 514.18.

Le même modèle donne un total cumulé par client (PARTITION BY customer_id ORDER BY payment_date) ou par magasin.

Le piège : des valeurs de tri égales

Quand l'ORDER BY de la fenêtre contient des doublons, le résultat surprend beaucoup de monde. Deux paiements de Sakila ont exactement la même heure :

SELECT payment_id, payment_date, amount,
       SUM(amount) OVER (ORDER BY payment_date) AS default_frame,
       SUM(amount) OVER (ORDER BY payment_date, payment_id
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_frame
FROM payment
WHERE payment_date >= '2005-05-30 14:40:00'
  AND payment_date < '2005-05-30 15:00:00'
ORDER BY payment_date, payment_id;

Résultat : les paiements 1258 et 8245 ont tous deux default_frame = 5.98, alors que rows_frame affiche 2.99 puis 5.98.

Avec un ORDER BY et sans cadre explicite, le cadre par défaut est RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW : il inclut toutes les lignes ayant la même valeur de tri que la ligne courante. Les lignes ex æquo obtiennent le même total. Pour ajouter les lignes strictement une par une, utilisez un cadre ROWS et un critère de départage unique dans ORDER BY, comme la clé primaire.

Sans fonctions de fenêtrage

Avant les fonctions de fenêtrage (MySQL avant 8.0), un total cumulé était une sous-requête corrélée : pour chaque ligne, additionner toutes les lignes jusqu'à elle.

WITH daily AS (
    SELECT DATE(payment_date) AS pay_day,
           SUM(amount) AS daily_amount
    FROM payment
    WHERE payment_date >= '2005-08-01'
      AND payment_date < '2005-09-01'
    GROUP BY DATE(payment_date)
)
SELECT d1.pay_day,
       d1.daily_amount,
       (SELECT SUM(d2.daily_amount)
        FROM daily d2
        WHERE d2.pay_day <= d1.pay_day) AS running_total
FROM daily d1
ORDER BY d1.pay_day;

Résultat : les mêmes 10 lignes et totaux que la version avec fonction de fenêtrage.

Pour chaque ligne, la sous-requête parcourt toutes les lignes précédentes : le travail croît de façon quadratique. Citez-la comme solution de repli et utilisez SUM() OVER en pratique.

Pratique

• Paiements mensuels et cumulés
• Analyse des paiements cumulés
• Montants des paiements d'août 2005

Points clés :

• SUM(x) OVER (ORDER BY key) renvoie la somme cumulée jusqu'à chaque ligne et conserve toutes les lignes.
• Agrégez d'abord au niveau voulu (jour, mois), puis ajoutez le total cumulé.
• PARTITION BY redémarre le total pour chaque groupe : client, magasin, année.
• Le cadre par défaut est RANGE, donc des lignes avec des valeurs de tri égales partagent un même total ; utilisez ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW et un critère de départage unique.
• Sans fonctions de fenêtrage, utilisez une sous-requête corrélée, bien plus lente.

Questions fréquentes

Comment calculer un total cumulé en SQL ?

Utilisez une fonction de fenêtrage : SELECT day, amount, SUM(amount) OVER (ORDER BY day) AS running_total FROM daily;. Cela fonctionne dans MySQL 8.0+, MariaDB 10.2+, PostgreSQL, SQL Server, Oracle et SQLite 3.25+.

Comment calculer un total cumulé par groupe ?

Ajoutez PARTITION BY : SUM(amount) OVER (PARTITION BY customer_id ORDER BY payment_date). La somme redémarre à la première ligne de chaque client.

Pourquoi certaines lignes ont-elles le même total cumulé ?

Parce qu'elles ont la même valeur dans la colonne de l'ORDER BY de la fenêtre, et que le cadre RANGE par défaut inclut toutes ces lignes ex æquo. Indiquez ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW et ajoutez une colonne unique, comme l'id, à l'ORDER BY.

Comment calculer une moyenne mobile au lieu d'un total cumulé ?

Limitez le cadre à un nombre fixe de lignes : AVG(amount) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) fait la moyenne du jour courant et des six précédents. Les cadres de fenêtre sont vus dans la leçon 7.3.