Somme cumulative avec remise à zéro

**tanaka59** · 28/06/2019, 17h34

Bonjour,

Je souhaite faire une requête SQL en MySQL qui me calcule automatiquement la somme cumulative par mois et catégorie avec comme règle de gestion, de reprendre la comptage à "au premier jour du mois trouvé" pour chaque couple de début de mois / premier objectif.

Voici une requête qui me sort partiellement ce que je veux :

Code sql mysql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
SELECT T1.*, (
	SELECT SUM(obj_max)
	FROM latable T
	WHERE obj_date<=T1.obj_date
	) as somme_cumulee
FROM latable T1
GROUP BY obj_famille_technique , YEAR(obj_date) , MONTH (obj_date) , DAY(obj_date)  ;

Une illustration sous excel vaut mieux qu'un long discours pour comprendre l'attendu (colonne H et I) :

fichier_test.xlsx

Peut être dois je aussi avoir un filtre sur le min de la date ?

J'ai zieuté ce poste pour m'inspirer de ce que je veux faire : https://www.developpez.net/forums/d1...es-croissants/

Merci de m'aiguiller

**aieeeuuuuu** · 28/06/2019, 17h45

bonjour,

Oui, il faut restreindre la date min, pour ne pas comptabiliser les mois précédent, ou encore vérifier l'égalité de l'année et du mois dans la sous requête.

Quelle est votre version de MySQL. Le support des fonctions fenêtrées à été ajouté en version 8, vous pourriez alors profiter de la somme cumulée avec un SUM(...) OVER(ORDER BY...).

**tanaka59** · 28/06/2019, 23h02

Bonsoir,

Je viens de tester la requête suivante :

Code sql mysql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
create temporary table datemax
 
select t2.`OBJ_FAMILLE_TECHNIQUE` as OBJ_FAMILLE_TECHNIQUE_max , t2.`OBJ_Date` as OBJ_Date_max 
from obj_powerbi t2 
where t2.OBJ_Date=(select max(t.OBJ_Date) 
from obj_powerbi t  
where t.OBJ_FAMILLE_TECHNIQUE=t2.OBJ_FAMILLE_TECHNIQUE 
and year(t.`OBJ_Date`)=year(t2.OBJ_Date)
and month(t.OBJ_Date)=month(t2.OBJ_Date)) ;
 
create temporary table datemin
 
select t2.`OBJ_FAMILLE_TECHNIQUE` as OBJ_FAMILLE_TECHNIQUE_min , t2.`OBJ_Date` as OBJ_Date_min
from obj_powerbi t2 
where t2.OBJ_Date=(select min(t.OBJ_Date) 
from obj_powerbi t  
where t.OBJ_FAMILLE_TECHNIQUE=t2.OBJ_FAMILLE_TECHNIQUE 
and year(t.`OBJ_Date`)=year(t2.OBJ_Date)
and month(t.OBJ_Date)=month(t2.OBJ_Date)) ;
 
create temporary table obj_powerbi2
 
select obj_powerbi.OBJ_TYPE , obj_powerbi.OBJ_MAX , obj_powerbi.OBJ_min , obj_powerbi.OBJ_FAMILLE_TECHNIQUE , obj_powerbi.OBJ_Date , obj_powerbi.OBJ_commentaire , datemax.OBJ_Date_max , datemin.OBJ_Date_min 
from obj_powerbi
inner join datemin on datemin.OBJ_FAMILLE_TECHNIQUE_min = obj_powerbi.OBJ_FAMILLE_TECHNIQUE and year(obj_powerbi.OBJ_Date)=year(datemin.OBJ_Date_min) and month(obj_powerbi.OBJ_Date)=month(datemin.OBJ_Date_min)
inner join datemax on datemax.OBJ_FAMILLE_TECHNIQUE_max = obj_powerbi.OBJ_FAMILLE_TECHNIQUE and year(obj_powerbi.OBJ_Date)=year(datemax.OBJ_Date_max) and month(obj_powerbi.OBJ_Date)=month(datemax.OBJ_Date_max);
 
create temporary table obj_powerbi3
 
SELECT T1.*, ( SELECT SUM(obj_max) 
                    FROM obj_powerbi2 T 
		   WHERE t.obj_date<=T1.obj_date 
                   and t.obj_date between t1.OBJ_Date_min and t1.OBJ_Date_max ) as somme_cumulee 
FROM obj_powerbi2 T1 
GROUP BY obj_famille_technique , YEAR(obj_date) , MONTH (obj_date) ;

Cela coince sur la requête de fin avec le message d'erreur suivant :

Envoyé par erreur mysql

#1137 - Can't reopen table: 'T'

Une idée ?

Merci de l'aiguillage

**tanaka59** · 01/07/2019, 14h46

Bonjour,

Je viens de refaire des tests :

Solution qui fonctionne mais non optimal ... Je me balade avec du alter table c'est particulièrement lourd syntaxiquement. Le but est d'avoir une "vue" au final.

Code sql mysql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
 
/* script pour calcul cumulative */
 
ALTER TABLE obj_powerbi
DROP COLUMN OBJ_Date_min ;
 
ALTER TABLE obj_powerbi
DROP COLUMN OBJ_Date_max ;
 
create temporary table datemax
 
select t2.OBJ_FAMILLE_TECHNIQUE as OBJ_FAMILLE_TECHNIQUE_max , t2.OBJ_Date as OBJ_Date_max 
from obj_powerbi t2 
where t2.OBJ_Date=(select max(t.OBJ_Date) 
from obj_powerbi t  
where t.OBJ_FAMILLE_TECHNIQUE=t2.OBJ_FAMILLE_TECHNIQUE 
and year(t.OBJ_Date)=year(t2.OBJ_Date)
and month(t.OBJ_Date)=month(t2.OBJ_Date));
 
ALTER TABLE obj_powerbi
ADD OBJ_Date_max date ;
 
create temporary table datemin
 
select t2.OBJ_FAMILLE_TECHNIQUE as OBJ_FAMILLE_TECHNIQUE_min , t2.OBJ_Date as OBJ_Date_min
from obj_powerbi t2 
where t2.OBJ_Date=(select min(t.OBJ_Date) 
from obj_powerbi t  
where t.OBJ_FAMILLE_TECHNIQUE=t2.OBJ_FAMILLE_TECHNIQUE 
and year(t.OBJ_Date)=year(t2.OBJ_Date)
and month(t.OBJ_Date)=month(t2.OBJ_Date)) ;
 
ALTER TABLE obj_powerbi
ADD OBJ_Date_min date ;
 
create temporary table obj_powerbi2
 
select obj_powerbi.OBJ_TYPE , obj_powerbi.OBJ_MAX , obj_powerbi.OBJ_min , obj_powerbi.OBJ_FAMILLE_TECHNIQUE , 
obj_powerbi.OBJ_Date , obj_powerbi.OBJ_commentaire , datemax.OBJ_Date_max , datemin.OBJ_Date_min 
from OBJ_PowerBi
inner join datemin on datemin.OBJ_FAMILLE_TECHNIQUE_min = obj_powerbi.OBJ_FAMILLE_TECHNIQUE 
and year(obj_powerbi.OBJ_Date)=year(datemin.OBJ_Date_min) 
and month(obj_powerbi.OBJ_Date)=month(datemin.OBJ_Date_min)
inner join datemax on datemax.OBJ_FAMILLE_TECHNIQUE_max = obj_powerbi.OBJ_FAMILLE_TECHNIQUE 
and year(obj_powerbi.OBJ_Date)=year(datemax.OBJ_Date_max) 
and month(obj_powerbi.OBJ_Date)=month(datemax.OBJ_Date_max);
 
/* pour OBJ_Date_min */
 
update obj_powerbi t1
set t1.OBJ_Date_min = (select obj_powerbi2.OBJ_Date_min
                   from obj_powerbi2
				   where year(t1.OBJ_Date) = year(obj_powerbi2.OBJ_Date) 
				   and month(t1.OBJ_Date) = month(obj_powerbi2.OBJ_Date)
				   and day(t1.OBJ_Date) = day(obj_powerbi2.OBJ_Date)
				   and t1.OBJ_FAMILLE_TECHNIQUE = obj_powerbi2.OBJ_FAMILLE_TECHNIQUE);
 
/* pour OBJ_Date_max */
 
update obj_powerbi t1
set t1.OBJ_Date_max = (select obj_powerbi2.OBJ_Date_max
                   from obj_powerbi2
				   where year(t1.OBJ_Date) = year(obj_powerbi2.OBJ_Date) 
				   and month(t1.OBJ_Date) = month(obj_powerbi2.OBJ_Date)
				   and day(t1.OBJ_Date) = day(obj_powerbi2.OBJ_Date)
				   and t1.OBJ_FAMILLE_TECHNIQUE = obj_powerbi2.OBJ_FAMILLE_TECHNIQUE);
 
SELECT t1.* , (
	SELECT SUM(obj_powerbi.OBJ_MAX)
	FROM obj_powerbi
	WHERE obj_powerbi.OBJ_Date<=T1.OBJ_Date
	and obj_powerbi.OBJ_Date between T1.OBJ_Date_min and T1.OBJ_Date_max 
	and obj_powerbi.OBJ_FAMILLE_TECHNIQUE = t1.OBJ_FAMILLE_TECHNIQUE ) as somme_cumulee
FROM obj_powerbi T1
group by t1.OBJ_FAMILLE_TECHNIQUE ;

J'ai fait ce 2ème script qui lui ne fonctionne pas. Si je fais ma requête de cumulative sur des tables temporaires impossibles .... Je dois utiliser une CTE et je me retrouve dans un cul de sac. J'ai une version 5 de sql , pas 8 .

Code sql mysql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
create temporary table datemax
 
select t2.OBJ_FAMILLE_TECHNIQUE as OBJ_FAMILLE_TECHNIQUE_max , t2.OBJ_Date as OBJ_Date_max 
from obj_powerbi t2 
where t2.OBJ_Date=(select max(t.OBJ_Date) 
from obj_powerbi t  
where t.OBJ_FAMILLE_TECHNIQUE=t2.OBJ_FAMILLE_TECHNIQUE 
and year(t.OBJ_Date)=year(t2.OBJ_Date)
and month(t.OBJ_Date)=month(t2.OBJ_Date));
 
create temporary table datemin
 
select t2.OBJ_FAMILLE_TECHNIQUE as OBJ_FAMILLE_TECHNIQUE_min , t2.OBJ_Date as OBJ_Date_min
from obj_powerbi t2 
where t2.OBJ_Date=(select min(t.OBJ_Date) 
from obj_powerbi t  
where t.OBJ_FAMILLE_TECHNIQUE=t2.OBJ_FAMILLE_TECHNIQUE 
and year(t.OBJ_Date)=year(t2.OBJ_Date)
and month(t.OBJ_Date)=month(t2.OBJ_Date)) ;
 
create temporary table obj_powerbi2
 
select obj_powerbi.OBJ_TYPE , obj_powerbi.OBJ_MAX , obj_powerbi.OBJ_min , obj_powerbi.OBJ_FAMILLE_TECHNIQUE , obj_powerbi.OBJ_Date , obj_powerbi.OBJ_commentaire , datemax.OBJ_Date_max , datemin.OBJ_Date_min 
from OBJ_PowerBi
inner join datemin on datemin.OBJ_FAMILLE_TECHNIQUE_min = obj_powerbi.OBJ_FAMILLE_TECHNIQUE and year(obj_powerbi.OBJ_Date)=year(datemin.OBJ_Date_min) and month(obj_powerbi.OBJ_Date)=month(datemin.OBJ_Date_min)
inner join datemax on datemax.OBJ_FAMILLE_TECHNIQUE_max = obj_powerbi.OBJ_FAMILLE_TECHNIQUE and year(obj_powerbi.OBJ_Date)=year(datemax.OBJ_Date_max) and month(obj_powerbi.OBJ_Date)=month(datemax.OBJ_Date_max);
 
/* problematique du cte impossible car v5 au lieu de v8 
 
WITH CTE1 AS ( SELECT * FROM obj_powerbi2 )
 
SELECT t1.* , (
	SELECT SUM(CTE1.OBJ_MAX)
	FROM CTE1
	WHERE CTE1.OBJ_Date<=T1.OBJ_Date
	and CTE1.OBJ_Date between T1.OBJ_Date_min and T1.OBJ_Date_max 
	and CTE1.OBJ_FAMILLE_TECHNIQUE = t1.OBJ_FAMILLE_TECHNIQUE ) as somme_cumulee
FROM CTE1 T1
group by t1.OBJ_FAMILLE_TECHNIQUE ; */
 
/* problematique d une aliasisation impossible sur une table temporaire 
 
SELECT t1.* , (
	SELECT SUM(obj_powerbi2.OBJ_MAX)
	FROM obj_powerbi2
	WHERE obj_powerbi2.OBJ_Date<=T1.OBJ_Date
	and obj_powerbi2.OBJ_Date between T1.OBJ_Date_min and T1.OBJ_Date_max 
	and obj_powerbi2.OBJ_FAMILLE_TECHNIQUE = t1.OBJ_FAMILLE_TECHNIQUE ) as somme_cumulee
FROM obj_powerbi2 T1
group by t1.OBJ_FAMILLE_TECHNIQUE ; */

Une idée ?

Merci de l'aiguillage

**SQLpro** · 01/07/2019, 19h30

Plus simple avec une fonction de fenêtrage :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
SELECT catégorie, YEAR(obj_date) , MONTH (obj_date), SUM(obj_max) OVER(PARTITION BY catégorie, YEAR(obj_date) , MONTH (obj_date) ORDER BY DAY(obj_date)) AS CUMUL
FROM latable T

Et c'est tout !

À me lire : https://sqlpro.developpez.com/articl...clause-window/

A +

**aieeeuuuuu** · 02/07/2019, 10h36

sauf qu'il est en version 5 de mysql, donc pas de fonctions fenêtrées...

**tanaka59** · 02/07/2019, 10h43

Bonjour,

Envoyé par SQLpro

Plus simple avec une fonction de fenêtrage :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
SELECT catégorie, YEAR(obj_date) , MONTH (obj_date), SUM(obj_max) OVER(PARTITION BY catégorie, YEAR(obj_date) , MONTH (obj_date) ORDER BY DAY(obj_date)) AS CUMUL
FROM latable T

Et c'est tout !

À me lire : https://sqlpro.developpez.com/articl...clause-window/

A +

Je viens de tester votre requête (avec mes champs qui vont bien) :

Code sql mysql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
SELECT T.OBJ_FAMILLE_TECHNIQUE, YEAR(T.obj_date) , MONTH (T.obj_date), SUM(T.obj_max) OVER(PARTITION BY T.OBJ_FAMILLE_TECHNIQUE, YEAR(T.obj_date) , MONTH (T.obj_date) ORDER BY DAY(T.obj_date)) AS CUMUL
FROM obj_powerbi T

J'ai ce message d'erreur depuis phpmyadmin :

Code erreur mysql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
Erreur
 
Analyse statique :
 
1 erreurs trouvées lors de l'analyse.
 
    Un alias a été constaté précédemment. (near "CUMUL" at position 196)
 
Requête SQL : Documentation
 
SELECT T.OBJ_FAMILLE_TECHNIQUE, YEAR(T.obj_date) , MONTH (T.obj_date), SUM(T.obj_max) OVER(PARTITION BY T.OBJ_FAMILLE_TECHNIQUE, YEAR(T.obj_date) , MONTH (T.obj_date) ORDER BY DAY(T.obj_date)) AS CUMUL FROM obj_powerbi T LIMIT 0, 25
 
MySQL a répondu: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(PARTITION BY T.OBJ_FAMILLE_TECHNIQUE, YEAR(T.obj_date) , MONTH (T.obj_date) ORD' at line 1

Je pense que ma version est trop ancienne (5.7.11) :/

**CinePhil** · 02/07/2019, 14h27

Construisons la requête petit à petit... Vérifiez chaque étape intermédiaire !

1) Calcul du total par mois

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SELECT obj_famille_technique, YEAR(obj_date) AS annee, MONTH(obj_date) AS mois,
	SUM(obj_max) AS sum_obj_max
FROM la_table
GROUP BY obj_famille_technique, YEAR(obj_date), MONTH(obj_date)
ORDER BY obj_famille_technique, YEAR(obj_date), MONTH(obj_date);

2) Calcul du cumul par jour pour chaque mois

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
SELECT t1.obj_famille_technique, t1.obj_date,
	SUM(t2.obj_max) AS cumul_obj_max
FROM la_table t1
INNER JOIN la table t2 
	ON t2.obj_famille_technique = t1.obj_famille_technique
	AND YEAR(t2.obj_date) = YEAR(t1.obj_date)
	AND MONTH(t2.obj_date) = MONTH(t1.obj_date)
WHERE t2.obj_date <= t1.obj_date
GROUP BY t1.obj_famille_technique, t1.obj_date;

3) Requête complète :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
SELECT t3.obj_max, t3.obj_famille_technique, t3.obj_date, 
	CONCAT(t3.obj_famille_technique, '_', MONTH(t3.obj_date), '/', YEAR(t3.obj_date) AS obj_famille_technique_mois_annee,
	t4.sum_obj_max AS par_mois_et_fam_tech,
	t5.cumul_obj_max AS par_jour_et_fam_tech
FROM la_table t3
INNER JOIN 
(
	-- Calcul du total par mois
	SELECT obj_famille_technique, YEAR(obj_date) AS annee, MONTH(obj_date) AS mois,
		SUM(obj_max) AS sum_obj_max
	FROM la_table
	GROUP BY obj_famille_technique, YEAR(obj_date), MONTH(obj_date)
) t4
	ON t4.obj_famille_technique = t3.obj_famille_technique
	AND t4.annee = YEAR(t3.obj_date)
	AND t4.mois = MONTH(t3.obj_date)
INNER JOIN
(
	-- Calcul du cumul par jour pour chaque mois
	SELECT t1.obj_famille_technique, t1.obj_date,
		SUM(t2.obj_max) AS cumul_obj_max
	FROM la_table t1
	INNER JOIN la table t2 
		ON t2.obj_famille_technique = t1.obj_famille_technique
		AND YEAR(t2.obj_date) = YEAR(t1.obj_date)
		AND MONTH(t2.obj_date) = MONTH(t1.obj_date)
	WHERE t2.obj_date <= t1.obj_date
	GROUP BY t1.obj_famille_technique, t1.obj_date
) t5
	ON t5.obj_famille_technique = t3.obj_famille_technique
	AND t5.obj_date = t3.obj_date
ORDER BY t3.obj_famille_technique, t3.obj_date

**tanaka59** · 02/07/2019, 15h50

Bonjour,

Je viens de tester vos propositions 1) et 2). J'obtiens un résultat satisfaisant et conforme a ce que je recherche.

Envoyé par CinePhil

1) Calcul du total par mois

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SELECT obj_famille_technique, YEAR(obj_date) AS annee, MONTH(obj_date) AS mois,
	SUM(obj_max) AS sum_obj_max
FROM la_table
GROUP BY obj_famille_technique, YEAR(obj_date), MONTH(obj_date)
ORDER BY obj_famille_technique, YEAR(obj_date), MONTH(obj_date);

Envoyé par CinePhil

2) Calcul du cumul par jour pour chaque mois

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
SELECT t1.obj_famille_technique, t1.obj_date,
	SUM(t2.obj_max) AS cumul_obj_max
FROM la_table t1
INNER JOIN la table t2 
	ON t2.obj_famille_technique = t1.obj_famille_technique
	AND YEAR(t2.obj_date) = YEAR(t1.obj_date)
	AND MONTH(t2.obj_date) = MONTH(t1.obj_date)
WHERE t2.obj_date <= t1.obj_date
GROUP BY t1.obj_famille_technique, t1.obj_date;

Ici cela se corse un peu . Je procède à des vérifications supplémentaires lié à ma version mysql.

Envoyé par CinePhil

3) Requête complète :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
SELECT t3.obj_max, t3.obj_famille_technique, t3.obj_date, 
	CONCAT(t3.obj_famille_technique, '_', MONTH(t3.obj_date), '/', YEAR(t3.obj_date) AS obj_famille_technique_mois_annee,
	t4.sum_obj_max AS par_mois_et_fam_tech,
	t5.cumul_obj_max AS par_jour_et_fam_tech
FROM la_table t3
INNER JOIN 
(
	-- Calcul du total par mois
	SELECT obj_famille_technique, YEAR(obj_date) AS annee, MONTH(obj_date) AS mois,
		SUM(obj_max) AS sum_obj_max
	FROM la_table
	GROUP BY obj_famille_technique, YEAR(obj_date), MONTH(obj_date)
) t4
	ON t4.obj_famille_technique = t3.obj_famille_technique
	AND t4.annee = YEAR(t3.obj_date)
	AND t4.mois = MONTH(t3.obj_date)
INNER JOIN
(
	-- Calcul du cumul par jour pour chaque mois
	SELECT t1.obj_famille_technique, t1.obj_date,
		SUM(t2.obj_max) AS cumul_obj_max
	FROM la_table t1
	INNER JOIN la table t2 
		ON t2.obj_famille_technique = t1.obj_famille_technique
		AND YEAR(t2.obj_date) = YEAR(t1.obj_date)
		AND MONTH(t2.obj_date) = MONTH(t1.obj_date)
	WHERE t2.obj_date <= t1.obj_date
	GROUP BY t1.obj_famille_technique, t1.obj_date
) t5
	ON t5.obj_famille_technique = t3.obj_famille_technique
	AND t5.obj_date = t3.obj_date
ORDER BY t3.obj_famille_technique, t3.obj_date

Merci à vous.

Autre piste , j'étudie un parallèle entre votre requête 2) une vue de la requête. Le résultat de la vue doit me servir pour l'appel dans un logiciel de reporting.

Somme cumulative avec remise à zéro [MySQL-5.7]

Requêtes MySQL

Discussions similaires

Partager

Partager