Addition de valeurs sous conditions

Version imprimable

15/04/2019, 10h26
Naminei

1 pièce(s) jointe(s)

Addition de valeurs sous conditions

Bonjour,

Je travaille sur un e-learning:
J'ai une base de données où ma clé primaire est l'id_stats, puis le niveau qui correspond à un chapitre de l'e-learning et le code_partie est un sous-chapitre.
L'objectif est d'afficher le tableau ci-joint mais avec une condition en plus: celle d'additionner les dureeS si les id_stats se suivent ET si elles ont le même niveau et le même code_partie.

Pièce jointe 466836

Dans l'image, les encadrés rouges correspondent aux niveaux et code_partie similaires qui se suivent, il faut donc additionner leur duréeS.

Merci d'avance pour votre aide :)

Natacha
15/04/2019, 14h00
escartefigue

Qui se suivent mais non contigüs dans le premier encadré rouge contrairement aux 3 autres cas, est-ce volontaire ?
Peut il y avoir plus de deux lignes à additioner ?

Quelle est la règle ?
15/04/2019, 14h04
Naminei

Si ils se suivent, même si les numéros ne sont pas contigus, oui. C'est justement cela que j'ai du mal à faire: faire une comparaison sur id et next id et faire une addition ou non en fonction de cette comparaison.

Il peut y avoir plus de 2 lignes à additionner, oui.
15/04/2019, 14h30
tatayo
Bonjour,
Si je comprends bien, il faut prendre toutes les lignes
- Pour lesquelles on ne peut pas trouver de ligne avec un id inférieur pour laquelle il existe une ligne dont l'id est entre celui des deux lignes, et dont les deux autres colonnes ont la même valeur que celles de la ligne "principal"
- En faisant une jointure qui ont un id supérieur, les mêmes valeurs pour les deux colonnes, et pour lesquelles il n'existe pas de ligne dont l'id est entre les deux et qui aurait une valeur différente pour l'une des deux colonnes
Le premier point permet de trouver chaque début de "séquence", et le deuxième les lignes à cumuler.
Il ne reste plus qu'à traduire tout ça en SQL !

Tatayo.
15/04/2019, 15h44
Naminei

Bonjour, je n'ai rien compris à ce que tu as écrit. Qu'appelles-tu id inférieur? Celui qui est au dessus ou celui qui est le plus petit? "une ligne dont l'id est entre deux", entre deux quoi? Ligne principale est la ligne étudiée?
-> mon problème est que je n'arrive pas à récupérer "id étudié" ainsi que "id suivant" et dire "si niveau et code_partie sont les mêmes pour ces deux différentes lignes, alors je fais la somme du décompte en temps"
La partie qui me pose le plus problème c'est de comparer des datas de lignes qui se suivent. En effet, je veux comparer le niveau et le code_partie de deux lignes qui se suivent. Pour le reste, je vais me débrouiller mais comment dit-on "Je récupère la 1ere ligne ainsi que la deuxième et je compare leur colonne niveau respective ainsi que leur colonne code_partie".

Trouver chaque début de séquence? Tu veux dire les niveaux les plus bas ainsi que le code_partie le plus bas? Oui mais dans ce cas je change l'ordre et les id_stats ne se suivent plus dans l'ordre dans lequel je veux comparer....

Ce que je veux c'est:
- récupérer la 1ere ligne ainsi que la 2eme
- regarder si niveau1 et niveau2 sont les mêmes, si oui:
- regarder si code_partie1 et code_partie2 sont les mêmes, si oui,
- réunir en une ligne et additionner la durée de connexion
- comparer la 1ere et 2eme ligne (si il y a eu matching du niveau et code_partie précédemment) sinon c'est la 2eme et 3eme ligne si il n'y a pas eu de matching
15/04/2019, 16h17
tatayo

Il n'y a pas de notion d'ordre dans une base de données, donc il n'y a pas de "première ligne", ni de ligne suivante.
Il faut donc identifier d'une part les lignes "à garder", et d'autre part les lignes à additionner, le tout en imaginant que les lignes sont "bazar" dans un sac.

Je prends une ligne au hasard, que j'appelle l1. Est-ce que je dois garder l1 ? Oui, si quelque soit la ligne l2 avec l1.niveau = l2.niveau et l1.code_partie = l2.code_partie, je ne peux pas trouver de ligne l3 avec l3.id dans l'intervalle ]l1.id..l2.id[.
Ensuite quelles lignes dois-je cumuler avec l1 ? Toutes les lignes l4 donc l4.id > l1.id et l4.niveau = l1.niveau et l4.code_partie = l1.code_partie telles qu'il n'existe pas de ligne l5 dont l5.id est dans l'intervalle ]l1.id..l4.id[ et (l5.niveau <> l1.niveau ou l5.code_partie <> l1.code_partie).

Je pars de l'hypothèse que les id s'incrémentent dans le temps (un tri sur l'id est strictement équivalent à un tri chronologique). Si ce n'est pas le cas il faut remplacer les intervalles dans les conditions ci-dessus par la colonne d'horodatage.

Tatayo.
16/04/2019, 09h04
escartefigue

Citation:

Envoyé par tatayo

Je pars de l'hypothèse que les id s'incrémentent dans le temps (un tri sur l'id est strictement équivalent à un tri chronologique). Si ce n'est pas le cas il faut remplacer les intervalles dans les conditions ci-dessus par la colonne d'horodatage.

Attention : si l'identifiant est de type AUTO_INCREMENT il n'y a aucune garantie que ce soit le cas, une colonne d'horodatage est nettement préférable

Salut à tous.

@ Naminei : il y a deux points que je n'ai pas compris dans la présentation du résultat final ?

1) Doit-on fusionner vos deux lignes en une seule et si oui laquelle ?
Que fait-on pour les lignes 42598 & 42600 ?

2) Doit-on aussi afficher les autres lignes, celles qui ne sont pas en correspondances ?

Dans l'exemple, ci-après, au niveau de la présentation, j'ai juste ajouté une nouvelle colonne de nom "somme".
Si la mise en correspondance ne s'est pas faite, son contenu est à NULL, sinon c'est l'addition des deux lignes.

Code:

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
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE IF NOT EXISTS `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `test`
--------------
 
--------------
CREATE TABLE `test`
( `id_stats`     integer  unsigned NOT NULL primary key,
  `niveau`       smallint unsigned NOT NULL,
  `code_partie`  smallint unsigned NOT NULL,
  `duree`        integer  unsigned NOT NULL,
  `hdebut`       datetime          NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `test` (`id_stats`,`niveau`,`code_partie`,`duree`,`hdebut`) values
  (42576, 1,  8,  2249, '2010-01-01 09:57:25'),(42577, 2,  5,  1680, '2010-01-01 10:16:41'),
  (42578, 1,  4,  5351, '2010-01-01 12:26:24'),(42579, 2,  7,  1573, '2010-01-01 14:59:42'),
  (42580, 3, 11,  2495, '2010-01-01 16:02:26'),(42581, 1,  3, 12548, '2010-01-01 16:32:34'),
  (42582, 3,  5,  3934, '2010-01-01 16:38:28'),(42583, 2,  4,  3336, '2010-01-01 16:41:06'),
  (42584, 3, 11,  3720, '2010-01-01 18:19:25'),(42598, 1,  2,  5936, '2010-01-01 19:01:11'),
  (42600, 1,  2,    98, '2010-01-01 19:42:12'),(42601, 1,  3,  1801, '2010-01-01 19:43:57'),
  (42603, 2,  6,    22, '2010-01-01 19:46:16'),(42606, 2,  9,  8890, '2010-01-01 21:57:14'),
  (42607, 2,  8,  4265, '2010-01-02 00:25:49'),(42608, 1,  8,  1060, '2010-01-02 07:32:06'),
  (42609, 1,  8,  1908, '2010-01-02 07:57:38'),(42610, 1,  3,   433, '2010-01-02 08:44:50'),
  (42611, 1,  3,    56, '2010-01-02 08:52:11'),(42612, 1,  4,  1801, '2010-01-02 08:54:57'),
  (42613, 1,  8,  1432, '2010-01-02 09:40:27'),(42649, 2,  6,  3960, '2010-01-02 10:29:43'),
  (42650, 4,  1,  7935, '2010-01-02 10:32:52'),(42651, 2,  5,  1693, '2010-01-02 10:36:30'),
  (42652, 3,  2,   247, '2010-01-02 11:19:38'),(42653, 3,  2,     4, '2010-01-02 11:23:46')
--------------
 
--------------
select * from `test`
--------------
 
+----------+--------+-------------+-------+---------------------+
| id_stats | niveau | code_partie | duree | hdebut              |
+----------+--------+-------------+-------+---------------------+
|    42576 |      1 |           8 |  2249 | 2010-01-01 09:57:25 |
|    42577 |      2 |           5 |  1680 | 2010-01-01 10:16:41 |
|    42578 |      1 |           4 |  5351 | 2010-01-01 12:26:24 |
|    42579 |      2 |           7 |  1573 | 2010-01-01 14:59:42 |
|    42580 |      3 |          11 |  2495 | 2010-01-01 16:02:26 |
|    42581 |      1 |           3 | 12548 | 2010-01-01 16:32:34 |
|    42582 |      3 |           5 |  3934 | 2010-01-01 16:38:28 |
|    42583 |      2 |           4 |  3336 | 2010-01-01 16:41:06 |
|    42584 |      3 |          11 |  3720 | 2010-01-01 18:19:25 |
|    42598 |      1 |           2 |  5936 | 2010-01-01 19:01:11 |
|    42600 |      1 |           2 |    98 | 2010-01-01 19:42:12 |
|    42601 |      1 |           3 |  1801 | 2010-01-01 19:43:57 |
|    42603 |      2 |           6 |    22 | 2010-01-01 19:46:16 |
|    42606 |      2 |           9 |  8890 | 2010-01-01 21:57:14 |
|    42607 |      2 |           8 |  4265 | 2010-01-02 00:25:49 |
|    42608 |      1 |           8 |  1060 | 2010-01-02 07:32:06 |
|    42609 |      1 |           8 |  1908 | 2010-01-02 07:57:38 |
|    42610 |      1 |           3 |   433 | 2010-01-02 08:44:50 |
|    42611 |      1 |           3 |    56 | 2010-01-02 08:52:11 |
|    42612 |      1 |           4 |  1801 | 2010-01-02 08:54:57 |
|    42613 |      1 |           8 |  1432 | 2010-01-02 09:40:27 |
|    42649 |      2 |           6 |  3960 | 2010-01-02 10:29:43 |
|    42650 |      4 |           1 |  7935 | 2010-01-02 10:32:52 |
|    42651 |      2 |           5 |  1693 | 2010-01-02 10:36:30 |
|    42652 |      3 |           2 |   247 | 2010-01-02 11:19:38 |
|    42653 |      3 |           2 |     4 | 2010-01-02 11:23:46 |
+----------+--------+-------------+-------+---------------------+
--------------
select           t1.*,
                 t2.somme
 
           from  `test` as t1
 
left outer join (  select  ta.id_stats,
                           ta.duree + tb.duree as somme
 
                     from  `test` as ta
 
          left outer join  `test` as tb
                       on  tb.hdebut = (  select  min(tc.hdebut)
                                            from  `test` as tc
                                           where  tc.hdebut > ta.hdebut
                                       )
 
                    where  tb.niveau      = ta.niveau
                      and  tb.code_partie = ta.code_partie
                ) as t2
             on  t2.id_stats = t1.id_stats
--------------
 
+----------+--------+-------------+-------+---------------------+-------+
| id_stats | niveau | code_partie | duree | hdebut              | somme |
+----------+--------+-------------+-------+---------------------+-------+
|    42576 |      1 |           8 |  2249 | 2010-01-01 09:57:25 |  NULL |
|    42577 |      2 |           5 |  1680 | 2010-01-01 10:16:41 |  NULL |
|    42578 |      1 |           4 |  5351 | 2010-01-01 12:26:24 |  NULL |
|    42579 |      2 |           7 |  1573 | 2010-01-01 14:59:42 |  NULL |
|    42580 |      3 |          11 |  2495 | 2010-01-01 16:02:26 |  NULL |
|    42581 |      1 |           3 | 12548 | 2010-01-01 16:32:34 |  NULL |
|    42582 |      3 |           5 |  3934 | 2010-01-01 16:38:28 |  NULL |
|    42583 |      2 |           4 |  3336 | 2010-01-01 16:41:06 |  NULL |
|    42584 |      3 |          11 |  3720 | 2010-01-01 18:19:25 |  NULL |
|    42598 |      1 |           2 |  5936 | 2010-01-01 19:01:11 |  6034 |
|    42600 |      1 |           2 |    98 | 2010-01-01 19:42:12 |  NULL |
|    42601 |      1 |           3 |  1801 | 2010-01-01 19:43:57 |  NULL |
|    42603 |      2 |           6 |    22 | 2010-01-01 19:46:16 |  NULL |
|    42606 |      2 |           9 |  8890 | 2010-01-01 21:57:14 |  NULL |
|    42607 |      2 |           8 |  4265 | 2010-01-02 00:25:49 |  NULL |
|    42608 |      1 |           8 |  1060 | 2010-01-02 07:32:06 |  2968 |
|    42609 |      1 |           8 |  1908 | 2010-01-02 07:57:38 |  NULL |
|    42610 |      1 |           3 |   433 | 2010-01-02 08:44:50 |   489 |
|    42611 |      1 |           3 |    56 | 2010-01-02 08:52:11 |  NULL |
|    42612 |      1 |           4 |  1801 | 2010-01-02 08:54:57 |  NULL |
|    42613 |      1 |           8 |  1432 | 2010-01-02 09:40:27 |  NULL |
|    42649 |      2 |           6 |  3960 | 2010-01-02 10:29:43 |  NULL |
|    42650 |      4 |           1 |  7935 | 2010-01-02 10:32:52 |  NULL |
|    42651 |      2 |           5 |  1693 | 2010-01-02 10:36:30 |  NULL |
|    42652 |      3 |           2 |   247 | 2010-01-02 11:19:38 |   251 |
|    42653 |      3 |           2 |     4 | 2010-01-02 11:23:46 |  NULL |
+----------+--------+-------------+-------+---------------------+-------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

16/04/2019, 15h32
Naminei

1 pièce(s) jointe(s)

Alors, suite au message laissé par Tatayo, j'ai remis en question ce que je voulais faire ou plutôt ce que j'allais faire et comment.

Du coup, je vais laisser tomber la variable id_stats (qui est en auto-inc sur les différentes connexions dans l'ordre de celles-ci).
Voici la même base de données (triée différemment en triant selon 4 variables: l'id_client, le niveau, le code_partie puis l'hdebut).
Ce que je veux faire c'est combiner les lignes ayant le même id_client, le même niveau, le même code_partie sur la même date. Je veux également faire apparaitre les autres lignes qui n'ont pas été combinées.

Pièce jointe 467278

Dans cet exemple, je souhaite avoir:

id_client niveau code_partie dureeS hdebut

0 4 6 7320 2011-05-25

0 4 6 2766 2011-05-27

0 4 6 5893 2011-05-28

0 4 6 2349 2011-05-29

0 4 6 2006 2011-06-01

A noter que dans la table, il y a d'autres id-client, d'autre niveaux et d'autres code_partie également. Je ne veux additionner les dureeS que si:
- meme id_client
- meme niveau
- meme code_partie
- meme jour

Si ces 4 conditions ne sont pas remplies, je n'effectue pas de somme de durée S et j'affiche seulement la ligne telle qu'elle était à la base.

Merci pour votre aide, vraiment. C'est tout neuf pour moi de m'attaquer à cela et je sens que ça va vraiment me débloquer pour la suite alors merci <3
16/04/2019, 16h16
tatayo

Là c'est bien plus simple:
Tu veux faire la somme (SUM) des durées, en regroupant (GROUP BY) sur id_client, niveau, code_partie, et la partie date de hdebut.
Un petit coup d'œil sur les focntions de MySQL concernant le type dateheure (pour en extraire la date), et tout roule.

Tatayo.
16/04/2019, 16h21
Naminei

D'accord, super, c'est donc GROUP BY qu'il faut que j'utilise, merci! Désolée, j'avais mal attaqué le problème dès le début mais merci de me l'avoir fait remarquer!! :)
Je comprend maintenant mieux les possibilités et limites de MySQL. Je vais faire la requête et je vous en montre le résultat ensuite.
Merci pour votre aide!!
16/04/2019, 17h55
Naminei

Code:

SELECT `id_client`, `niveau`, `code_partie`, SUM(dureeS) , DATE_FORMAT(hdebut, "%Y/%m/%d") as TIMEFORMAT from stats group by id_client, niveau, code_partie, TIMEFORMAT order by id_client, TIMEFORMAT ASC

Merci !!!
17/04/2019, 12h02
Naminei

Bonjour,

Je reviens vers vous avec une question un peu trickie...

En fait, dans un tableau de ce style:

date et heure nombre

15 Janvier 10h 10

15 Janvier 11h 20

18 Janvier 8h 10

18 Janvier 10h 15

Mon problème est que je cherche à avoir un tableau avec:
15 Janvier 10h --- 30
18 Janvier 8h --- 25

Je cherche à faire la somme sur les nombres selon si on est sur la même date (ça c'est bon, pas de soucis) mais je souhaite également afficher l'heure la plus vieille.
C'est cette partie là qui me pose problème car quand j'essaie d'afficher l'heure, il ne sait pas quoi afficher vu que je viens de combiner 2 différentes date en une, et c'est possible quand je compare des deux en changeant leur format en jour/mois/année mais pas quand j'essaie d'afficher l'heure car il ne sait pas quoi afficher.

Ma requête actuelle est la suivante (au dessus, ce n'est qu'une application de mon problème pour que celui ci soit plus clair):

Code:

SELECT `id_client`, `niveau`, `code_partie`, SUM(dureeS) , DATE_FORMAT(hdebut, "%Y/%m/%d") as TIMEFORMAT from stats group by id_client, niveau, code_partie, TIMEFORMAT order by id_client, TIMEFORMAT ASC
17/04/2019, 13h18
skuatamad

Il suffir de rajouter un MIN(HDEBUT) dans le SELECT
17/04/2019, 14h16
Naminei

Citation:

Envoyé par skuatamad

Il suffir de rajouter un MIN(HDEBUT) dans le SELECT

Merci !!!! Tu gères la fougère!!! :chin::yaisse3::chin::chin:

id_client	niveau	code_partie	dureeS	hdebut
0	4	6	7320	2011-05-25

0	4	6	2766	2011-05-27
0	4	6	5893	2011-05-28
0	4	6	2349	2011-05-29
0	4	6	2006	2011-06-01

date et heure	nombre
15 Janvier 10h	10
15 Janvier 11h	20
18 Janvier 8h	10
18 Janvier 10h	15