Cumul glissant

Le cas d'école du traitement ligne à ligne : un cumul se calcule en un seul passage, à condition de bien définir la fenêtre.

≈ 3 min de lectureLecture

Sur cette page

Énoncé
Jeu de données
Procédural contre ensembliste
1. Procédural
2. Ensembliste
Résultat attendu
Pourquoi l'ensembliste est préférable
Pièges
Variantes
Vérifier l'équivalence
Versions de SQL Server

Énoncé#

Pour chaque vente, afficher le total cumulé des montants dans l'ordre chronologique (puis par identifiant en cas d'égalité de date).

Jeu de données#

CREATE TABLE dbo.Ventes
(
    VenteId   int           NOT NULL PRIMARY KEY,
    DateVente date          NOT NULL,
    Montant   decimal(18,2) NOT NULL
);

INSERT INTO dbo.Ventes (VenteId, DateVente, Montant)
VALUES (1, '2026-01-01', 100.00),
       (2, '2026-01-02',  50.00),
       (3, '2026-01-02',  25.50),   -- même date que la vente 2
       (4, '2026-01-05',  10.00);

Procédural contre ensembliste#

Procédural#

DECLARE @Resultat TABLE (VenteId int PRIMARY KEY, DateVente date, Montant decimal(18,2), Cumul decimal(18,2));
DECLARE @Id int, @Date date, @Montant decimal(18,2), @Cumul decimal(18,2) = 0;

DECLARE c CURSOR LOCAL FAST_FORWARD FOR
    SELECT VenteId, DateVente, Montant FROM dbo.Ventes ORDER BY DateVente, VenteId;
OPEN c;
FETCH NEXT FROM c INTO @Id, @Date, @Montant;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @Cumul += @Montant;
    INSERT INTO @Resultat (VenteId, DateVente, Montant, Cumul)
    VALUES (@Id, @Date, @Montant, @Cumul);
    FETCH NEXT FROM c INTO @Id, @Date, @Montant;
END;
CLOSE c;
DEALLOCATE c;

SELECT * FROM @Resultat ORDER BY DateVente, VenteId;

Ensembliste#

SELECT VenteId, DateVente, Montant,
       SUM(Montant) OVER (ORDER BY DateVente, VenteId
                          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Cumul
FROM   dbo.Ventes
ORDER BY DateVente, VenteId;

Résultat attendu#

Quatre lignes, dont le cumul vaut successivement 100,00 · 150,00 · 175,50 · 185,50.

Pourquoi l'ensembliste est préférable#

Le cumul est calculé en un seul passage sur les données triées : le moteur maintient la somme courante, sans relire la table.
La version procédurale fait, pour chaque ligne, une lecture du curseur, une affectation et une insertion.
Un index sur (DateVente, VenteId) avec Montant en colonne incluse évite un tri : CREATE INDEX IX_Ventes_Date ON dbo.Ventes (DateVente, VenteId) INCLUDE (Montant);

Pièges#

Cadre par défaut : avec ORDER BY seul, le cadre est RANGE UNBOUNDED PRECEDING : les ventes 2 et 3 (même date) reçoivent le même cumul (175,50). Avec ROWS mais un ordre non unique, l'ordre entre ex æquo est arbitraire. Pour un cumul ligne à ligne déterministe : ROWS et un ordre unique.
La jointure triangulaire est ensembliste en apparence, mais lit environ n²/2 lignes : elle s'effondre sur de gros volumes.

-- Comparer trois écritures sur les ventes 2 et 3 (même date)
SELECT VenteId, DateVente, Montant,
       SUM(Montant) OVER (ORDER BY DateVente)                                   AS Cumul_cadre_par_defaut,
       SUM(Montant) OVER (ORDER BY DateVente ROWS UNBOUNDED PRECEDING)          AS Cumul_ROWS_ordre_non_unique,
       SUM(Montant) OVER (ORDER BY DateVente, VenteId ROWS UNBOUNDED PRECEDING) AS Cumul_ROWS_ordre_unique
FROM   dbo.Ventes
ORDER BY DateVente, VenteId;

-- Ensembliste mais mal fait : jointure triangulaire (coût en n² / 2 lectures)
SELECT a.VenteId, SUM(b.Montant) AS Cumul
FROM   dbo.Ventes AS a
JOIN   dbo.Ventes AS b
       ON  b.DateVente < a.DateVente
       OR (b.DateVente = a.DateVente AND b.VenteId <= a.VenteId)
GROUP BY a.VenteId;

Variantes#

-- Si la table contenait un ClientId : cumul par client et par année
SUM(Montant) OVER (PARTITION BY ClientId, YEAR(DateVente)
                   ORDER BY DateVente, VenteId ROWS UNBOUNDED PRECEDING)

-- Moyenne mobile sur les 7 dernières lignes
AVG(Montant) OVER (ORDER BY DateVente, VenteId ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

Vérifier l'équivalence#

Un changement de méthode ne vaut que si le résultat est identique. Comparer dans les deux sens ; aucune ligne ne doit sortir.

-- À exécuter dans le même lot que la version procédurale (la variable @Resultat doit exister)
SELECT VenteId, Cumul FROM @Resultat
EXCEPT
SELECT VenteId, SUM(Montant) OVER (ORDER BY DateVente, VenteId ROWS UNBOUNDED PRECEDING) FROM dbo.Ventes;

SELECT VenteId, SUM(Montant) OVER (ORDER BY DateVente, VenteId ROWS UNBOUNDED PRECEDING) FROM dbo.Ventes
EXCEPT
SELECT VenteId, Cumul FROM @Resultat;
-- Résultat attendu : aucune ligne dans les deux requêtes.

Versions de SQL Server#

Fonctions de fenêtre avec cadre ROWS : SQL Server 2012 et ultérieur (Azure SQL Database inclus).