76 votes

Créer une colonne de somme cumulée dans MySQL

J'ai un tableau qui ressemble à ceci :

id   count
1    100
2    50
3    10

Je veux ajouter une nouvelle colonne appelée cumulative_sum, de sorte que le tableau ressemblerait à ceci :

id   count  cumulative_sum
1    100    100
2    50     150
3    10     160

Existe-t-il une instruction de mise à jour MySQL permettant de faire cela facilement ? Quelle est la meilleure façon d'y parvenir ?

110voto

OMG Ponies Points 144785

Utilisation d'une requête corrélée :


  SELECT t.id,
         t.count,
         (SELECT SUM(x.count)
            FROM TABLE x
           WHERE x.id <= t.id) AS cumulative_sum
    FROM TABLE t
ORDER BY t.id

Utilisation des variables MySQL :


  SELECT t.id,
         t.count,
         @running_total := @running_total + t.count AS cumulative_sum
    FROM TABLE t
    JOIN (SELECT @running_total := 0) r
ORDER BY t.id

Nota:

  • El JOIN (SELECT @running_total := 0) r est une jointure croisée, et permet la déclaration de variables sans nécessiter un fichier séparé SET commandement.
  • L'alias de la table, r est requis par MySQL pour toute sous-requête, table dérivée ou vue en ligne.

Mises en garde :

  • Spécifique à MySQL ; non portable à d'autres bases de données
  • El ORDER BY est important ; il garantit que l'ordre correspond au PO et peut avoir des implications plus importantes pour l'utilisation de variables plus compliquées (IE : psuedo ROW_NUMBER/RANK fonctionnalité, dont MySQL est dépourvu).

93voto

Andomar Points 115404

Si les performances sont un problème, vous pouvez utiliser une variable MySQL :

set @csum := 0;
update YourTable
set cumulative_sum = (@csum := @csum + count)
order by id;

Vous pouvez également supprimer le cumulative_sum et le calculer pour chaque requête :

set @csum := 0;
select id, count, (@csum := @csum + count) as cumulative_sum
from YourTable
order by id;

Cela calcule la somme courante d'une manière courante :)

7 votes

Utiliser une jointure croisée pour définir la variable sans avoir besoin d'utiliser SET .

0 votes

Ma table contient 36 millions d'enregistrements, ce qui m'a permis d'accélérer les choses !

0 votes

Notez que le classement par cumulative_sum peut forcer le balayage complet de la table.

21voto

lad2025 Points 38168

MySQL 8.0/MariaDB supporte le mode fenêtré SUM(col) OVER() :

SELECT *, SUM(cnt) OVER(ORDER BY id) AS cumulative_sum
FROM tab;

Sortie :

 id   cnt   cumulative_sum 

  1   100              100 
  2    50              150 
  3    10              160 

db<>fidèle

3voto

Dercsár Points 779
UPDATE t
SET cumulative_sum = (
 SELECT SUM(x.count)
 FROM t x
 WHERE x.id <= t.id
)

3 votes

Bien que le PO ait demandé une mise à jour, celle-ci est dénormalisée et il sera probablement difficile de la maintenir correctement.

2voto

Jazz Points 26

Exemple de requête

SET @runtot:=0;
SELECT
   q1.d,
   q1.c,
   (@runtot := @runtot + q1.c) AS rt
FROM
   (SELECT
       DAYOFYEAR(date) AS d,
       COUNT(*) AS c
    FROM  orders
    WHERE  hasPaid > 0
    GROUP  BY d
    ORDER  BY d) AS q1

Prograide.com

Prograide est une communauté de développeurs qui cherche à élargir la connaissance de la programmation au-delà de l'anglais.
Pour cela nous avons les plus grands doutes résolus en français et vous pouvez aussi poser vos propres questions ou résoudre celles des autres.

Powered by:

X