Comment obtenir la somme cumulée ?

**Zakaroove** · 17/01/2017, 03h10

Bonjour,
Je veux calculer la somme cumulée du temps.

Voilà la table :

+-----------------+-------------------------------+-----------------------------+
|------nom--------|------------temps-------------|EXTRACT(YEAR FROM date)|
+-----------------+-------------------------------+-----------------------------+
|----dodo---------|------------20-----------------|--------------2015-----------|
+-----------------+-------------------------------+-----------------------------+
|-----dodo--------|--------------15 --------------|--------------2015-----------|
+-----------------+-------------------------------+-----------------------------+
|------didi--------|--------------201--------------| --------------2015----------|
+-----------------+-------------------------------+-----------------------------+
|------didi--------|--------------26----------------|-------------2015-----------|
+-----------------+-------------------------------+-----------------------------+
|------dada-------|-------------215---------------|-------------2015-----------|
+-----------------+-------------------------------+-----------------------------+

Voilà le résultat souhaité :

+-----------------+---------------------------+----------------------+--------------------------+-----------------------------+
| -----nom -------| -----sum(temps)--------|----pourcentage(%)-|---pourcentagecumulé---|EXTRACT(YEAR FROM date)|
+-----------------+---------------------------+----------------------+--------------------------+-----------------------------+
|---dodo----------|-----------35--------------|------- 0.0733-------| --------- 0.0733---------| ------------2015------------ |
+-----------------+---------------------------+----------------------+--------------------------+-----------------------------+
|-----didi---------|------------227------------|----------0,4759 -----|------------0.5492-------| ------------ 2015 ----------|
+-----------------+---------------------------+----------------------+--------------------------+-----------------------------+
|-----dada--------|-------------215-----------| ----------0.451------|-------------1------------|--------------2015-----------|
+-----------------+---------------------------+----------------------+--------------------------+-----------------------------+

En fait je suis encore débutant dans sql et j'ai effectué pas mal de recherches mais je n'arrive pas à obtenir ce résultat.

Quelle est la requête sql (MySql) permettant d'obtenir ce résultat ?

Merci d'avance.

**al1_24** · 17/01/2017, 10h01

Peux-tu nous donner la structure de ta table, ainsi que la requête qui t'a permis d'afficher la première liste.
Qu'as-tu déjà essayé comme requêtes qui ne fonctionnent pas ?

**isabelle.letrong** · 17/01/2017, 11h02

Envoyé par Zakaroove

Bonjour,
Je veux calculer la somme cumulée du temps.

Bonjour Zakaroove,

Il faudrait un peu plus de précision dans l'expression du besoin :
Voulez-vous calculer la somme du temps pour chaque 'nom' toutes années confondues où pour chaque 'nom' et par années distinctes ?

**Zakaroove** · 17/01/2017, 16h47

La table que j'ai donnée c'est juste un exemple de ce que j'ai :
En fait j'ai trois tables : la table qui contient l'id_nom', 'nom' et d'autres informations,
la table 'rapport' qui contient l'id_rapport', 'date' sous fomat(yyyy-MM-dd),et 'temps'(durée) sous format(HH:mm:ss) et la clé étrangère 'id_intervention' de la troisième table intervention qui contient la clé primaire 'id_intervention', le clé étrangère 'id_nom';

La requête que j'ai utilisée est :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT nom.nom, SEC_TO_TIME( SUM( TIME_TO_SEC( rapport.`TEMPS` ) ) ) , SUM( TIME_TO_SEC( rapport.`TEMPS` ) ) / ( 
SELECT SUM( TIME_TO_SEC( rapport.`TEMPS` ) ) 
FROM  `rapport` 
WHERE  `TEMPS` <>  '00:00:00'
AND EXTRACT( YEAR
FROM  `DATE` ) =  '2015' ) , EXTRACT( YEAR
FROM  `DATE` ) 
FROM  `rapport` , `nom`, intervention
WHERE nom.id_nom = intervention.id_nom
AND rapport.`ID_INTERVENTION` = intervention.`ID_INTERVENTION` 
AND  `TEMPS` <>  '00:00:00'
AND EXTRACT( YEAR
FROM  `DATE` ) =  '2015'
GROUP BY nom.nom
ORDER BY SEC_TO_TIME( SUM( TIME_TO_SEC( rapport.`TEMPS` ) ) ) DESC

Le résultat :
Nom : Capture.JPG
Affichages : 598
Taille : 23,4 Ko

*****************************************************************
*Cette requête ne contient pas la partie somme cumulée.
*mais je n'arrive pas à calculer la somme cumulée du temps,
*j'ai plusieurs années et je veux faire le traitement pour une seule année.
*pour un seul nom j'ai plusieurs temps d’où Group By nom,

**escartefigue** · 17/01/2017, 17h36

Bonjour,

Votre requete présente de nombreux soucis :
- critères de jointures mélangés avec ceux de filtrage
- prédicats non sargable (non indexables) : extract, différent etc.. qui nuisent aux performances
- absence d'alias et requête mal formatée qui rendent la lecture difficile

A noter aussi que nommer des colonnes avec des noms réservés comme DATE est une mauvaise idée

Voici une écriture simplifiée qui devrait donner le résultat attendu, à tester

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
   select NOM.NOM
        , SUM(RAP.TEMPS)
        , SUM(RAP.TEMPS)*100 / TOT
   FROM  nom  AS NOM
   INNER JOIN intervention as INT
      ON IN.ID_NOM = NOM.ID_NOM
   INNER JOIN rapport AS RAP
      ON RAP.ID_INTERVENTION = INT.ID_INTERVENTION
   INNER JOIN
        (SELECT SUM(TEMPS) as TOT
         FROM rapport 
         WHERE `DATE` BETWEEN '2015-01-01' AND '2015-12-31')
      ON 1=1
   WHERE RAP.`DATE` BETWEEN '2015-01-01' AND '2015-12-31')
   GROUP BY NOM.NOM

Je vous laisse le soin d'ajouter le test de division par zéro, il suffit d'ajouter un CASE

**Zakaroove** · 17/01/2017, 20h36

Meric pour votre reponse

mais ca donne une erreur:

#1248 - Every derived table must have its own alias

et dans cette requétte vous n'avez pas mentionné la partie du somme cummulée.

**Artemus24** · 17/01/2017, 20h42

Salut à tous.

Je réponds à la question du premier message :

Code :

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
--------------
select * from test
--------------
 
+----+------+-------+-------+
| id | nom  | temps | annee |
+----+------+-------+-------+
|  1 | dodo |    20 |  2015 |
|  2 | dodo |    15 |  2015 |
|  3 | didi |   201 |  2015 |
|  4 | didi |    26 |  2015 |
|  5 | dada |   215 |  2015 |
+----+------+-------+-------+
--------------
select *, @cum:=@cum+moyenne as cumul
from (
    select      @rang:=@rang+1                                                          as rang,
                t1.nom,
                t1.annee,
                cast( sum(t1.temps)                                  as decimal(15,0))  as temps,
                cast((sum(t1.temps) / (select sum(temps) from test)) as decimal(15,6))  as moyenne
          from  test as t1, (select @rang:=0, @cum:=0) as x
      group by  t1.nom, t1.annee
     ) as x
order by rang
--------------
 
+------+------+-------+-------+----------+----------+
| rang | nom  | annee | temps | moyenne  | cumul    |
+------+------+-------+-------+----------+----------+
|    1 | dodo |  2015 |    35 | 0.073375 | 0.073375 |
|    2 | didi |  2015 |   227 | 0.475891 | 0.549266 |
|    3 | dada |  2015 |   215 | 0.450734 |        1 |
+------+------+-------+-------+----------+----------+

@+

**Zakaroove** · 17/01/2017, 22h52

ca marche pas

**Zakaroove** · 18/01/2017, 04h07

voila exactement les table que j'ai:
Nom : question.JPG
Affichages : 655
Taille : 56,6 Ko

et voila le resulta souhete:
Nom : resultat.JPG
Affichages : 491
Taille : 27,2 Ko

**escartefigue** · 18/01/2017, 08h55

Envoyé par Zakaroove

#1248 - Every derived table must have its own alias

Il suffit d'ajouter l'alias :

Code :

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

1
2
3
4
5
6
7
8
   select [. . .]
   INNER JOIN
        (SELECT SUM(TEMPS) as TOT
         FROM rapport 
         WHERE `DATE` BETWEEN '2015-01-01' AND '2015-12-31') as TOTO
      ON 1=1
   [. . .]

Et de reporter cet alias dans le select

**Zakaroove** · 18/01/2017, 13h50

merci Mr escartefigue ca marche

mais j'ai dux question:

*qu'est ce je peut ajouter a la requéte pour obtenir la somme cumulé?

*concernant la requete est ce que tu peut m'expliquer la partie

Code :

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

INNER JOIN[.....] ON 1=1

Merci a l'avance.

**escartefigue** · 18/01/2017, 14h04

Envoyé par Zakaroove

*qu'est ce je peut ajouter a la requéte pour obtenir la somme cumulé?

Oui mais ce n'est pas nécessaire, puisque la solution proposée par Artemus24 récupère le total selon une autre méthode

Envoyé par Zakaroove

*concernant la requete est ce que tu peut m'expliquer la partie

Code :

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

INNER JOIN[.....] ON 1=1

Il s'agit de forcer le critère de jointure, 1=1 étant toujours vrai, de sorte à obtenir le total annuel quoi qu'il arrive
Dans la requête imbriquée, j'ai préféré utiliser un BETWEEN sargable à la place de la fonction EXTRACT qui compromet toute utilisation d'index

**Zakaroove** · 18/01/2017, 15h23

mais j'arrive pas a lie entre votre reponse et la solution propose par Artemus24

**Zakaroove** · 18/01/2017, 16h26

c'est bon
apres beaucoup d'essais j'trouver une solution:

Code :

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
SET @cum:=0;
SELECT * , @cum := @cum + Pourcentage AS  'Somme cumulée'
FROM (
 
SELECT NOM.nom AS NOM, SEC_TO_TIME( SUM( TIME_TO_SEC( RAP.`TEMPS` ) ) ) AS  Temps, SUM( TIME_TO_SEC( RAP.`TEMPS` ) ) *100 / TOT AS Pourcentage
FROM nom AS NOM
INNER JOIN intervention AS INTE ON INTE.ID_nom= NOM.ID_nom
INNER JOIN rapport AS RAP ON RAP.`ID_INTERVENTION = INTE.`ID_INTERVENTION 
INNER JOIN (
 
SELECT SUM( TIME_TO_SEC(  `TEMPS` ) ) AS TOT
FROM rapport
WHERE EXTRACT( YEAR
FROM  `DATE_D`) =  '2015'
) AS TOTO ON 1 =1
WHERE EXTRACT( YEAR
FROM  `DATE_D` ) =  '2015'
GROUP BY NOM.nom
ORDER BY SUM( TIME_TO_SEC( RAP.`TEMPS` ) ) DESC
) AS TOTOT

Merci pour votre aide

**Artemus24** · 18/01/2017, 20h29

Salut Zakaroove.

Mais oui, ça fonctionne !

Code :

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
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
SET collation_connection = latin1_general_ci
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
    DEFAULT CHARACTER SET `latin1`
    DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `equipement`
--------------
 
--------------
CREATE TABLE `equipement`
( `id_equipement`   integer  unsigned not null auto_increment primary key,
  `nom_equipement`  varchar(255)      not null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `equipement` (`nom_equipement`) values
  ('dodo'),('didi'),('dada')
--------------
 
--------------
select * from equipement
--------------
 
+---------------+----------------+
| id_equipement | nom_equipement |
+---------------+----------------+
|             1 | dodo           |
|             2 | didi           |
|             3 | dada           |
+---------------+----------------+
--------------
DROP TABLE IF EXISTS `intervention`
--------------
 
--------------
CREATE TABLE `intervention`
( `id_intervention`  integer  unsigned not null auto_increment primary key,
  `id_equipement`    integer  unsigned not null,
  CONSTRAINT `FK_EQUIPEMENT` FOREIGN KEY (`id_equipement`) REFERENCES `equipement` (`id_equipement`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `intervention` (`id_equipement`) values
  (1),(2),(3)
--------------
 
--------------
select * from intervention
--------------
 
+-----------------+---------------+
| id_intervention | id_equipement |
+-----------------+---------------+
|               1 |             1 |
|               2 |             2 |
|               3 |             3 |
+-----------------+---------------+
--------------
DROP TABLE IF EXISTS `rapport`
--------------
 
--------------
CREATE TABLE `rapport`
( `id_rapport`       integer  unsigned not null auto_increment primary key,
  `id_intervention`  integer  unsigned not null,
  `date_rapport`     date              not null,
  `duree_rapport`    integer unsigned  not null,
  CONSTRAINT `FK_INTERVENTION` FOREIGN KEY (`id_intervention`) REFERENCES `intervention` (`id_intervention`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `rapport` (`id_rapport`,`id_intervention`,`date_rapport`,`duree_rapport`) values
  ( 1, 1, '2017-01-18', 23),
  ( 2, 1, '2017-02-14', 17),
  ( 3, 2, '2017-02-18', 12),
  ( 6, 2, '2015-04-01', 18),
  ( 7, 2, '2017-01-02', 01),
  (32, 2, '2017-01-18', 30),
  (16, 3, '2017-03-12', 23)
--------------
 
--------------
select * from rapport
--------------
 
+------------+-----------------+--------------+---------------+
| id_rapport | id_intervention | date_rapport | duree_rapport |
+------------+-----------------+--------------+---------------+
|          1 |               1 | 2017-01-18   |            23 |
|          2 |               1 | 2017-02-14   |            17 |
|          3 |               2 | 2017-02-18   |            12 |
|          6 |               2 | 2015-04-01   |            18 |
|          7 |               2 | 2017-01-02   |             1 |
|         16 |               3 | 2017-03-12   |            23 |
|         32 |               2 | 2017-01-18   |            30 |
+------------+-----------------+--------------+---------------+
--------------
select e.id_equipement,
       e.nom_equipement,
       r.id_rapport,
       r.id_intervention,
       year(r.date_rapport) as annee,
       r.duree_rapport
 
      from  rapport      as r
 
inner join  intervention as i
        on  i.id_intervention = r.id_intervention
 
inner join  equipement   as e
        on  e.id_equipement   = i.id_equipement
--------------
 
+---------------+----------------+------------+-----------------+-------+---------------+
| id_equipement | nom_equipement | id_rapport | id_intervention | annee | duree_rapport |
+---------------+----------------+------------+-----------------+-------+---------------+
|             1 | dodo           |          1 |               1 |  2017 |            23 |
|             1 | dodo           |          2 |               1 |  2017 |            17 |
|             2 | didi           |          3 |               2 |  2017 |            12 |
|             2 | didi           |          6 |               2 |  2015 |            18 |
|             2 | didi           |          7 |               2 |  2017 |             1 |
|             2 | didi           |         32 |               2 |  2017 |            30 |
|             3 | dada           |         16 |               3 |  2017 |            23 |
+---------------+----------------+------------+-----------------+-------+---------------+
--------------
select    nom, temps, moyenne, @cum:=@cum+moyenne as cumul, annee
    from  (    select            t1.id_equipement                                                                 as rang,
                                 t1.nom_equipement                                                                as nom,
                       cast( sum(t1.duree_rapport)                                             as decimal(15,0))  as temps,
                       cast((sum(t1.duree_rapport) / (select sum(duree_rapport) from rapport)) as decimal(15,6))  as moyenne,
                             max(t1.annee)                                                                        as annee
                 from  (   select  e.id_equipement,
                                   e.nom_equipement,
                                   year(r.date_rapport) as annee,
                                   r.duree_rapport
 
                             from  rapport      as r
 
                       inner join  intervention as i
                               on  i.id_intervention = r.id_intervention
 
                       inner join  equipement   as e
                               on  e.id_equipement   = i.id_equipement
 
                       ) as t1, (select @cum:=0) as x
             group by  t1.id_equipement
          )  as x
   order by  rang
--------------
 
+------+-------+----------+----------+-------+
| nom  | temps | moyenne  | cumul    | annee |
+------+-------+----------+----------+-------+
| dodo |    40 | 0.322581 | 0.322581 |  2017 |
| didi |    61 | 0.491935 | 0.814516 |  2017 |
| dada |    23 | 0.185484 |        1 |  2017 |
+------+-------+----------+----------+-------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

@+

Comment obtenir la somme cumulée ?

Requêtes MySQL

Discussions similaires

Partager

Partager