Somme cumulative avec remise à zéro

Invité · 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...).

Invité · 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

Invité · 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...

Invité · 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) :/

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

Requêtes MySQL

Vue hybride

Discussions similaires

Partager

Partager