Temps de marche simultanée de deux pompes

Version imprimable

02/02/2017, 15h35
jupiter8

Temps de marche simultanée de deux pompes

Bonjour, J'ai un table qui se fait peupler automatiquement par les temps de marche de deux pompes, elles fonctionnent en alternance mais parfois ensemble, selon la demande. Ex:

colonnes: id, pompe, départ, arrêt.

J'aimerais savoir s'il est possible au moyen d'une requête ou d'un trigger de connaître le temps de marche simultané de ces deux pompes.

Merci.
02/02/2017, 16h01
SQLpro

OUI.

A +
02/02/2017, 17h43
jupiter8

Parfait c'est déjà ça de pris, ça va m'éviter de bricoler une sortie supplémentaire de mon micro-contrôleur.

Maintenant pour plus de précision sur mon projet (Je viens de débuter sous les bases de données, soyez indulgents. :mrgreen:)

J'ai cette table:

+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| poste12 | int(11) | NO | | NULL | |
| pompe | varchar(8) | YES | | | |
| etat | varchar(2) | NO | | NULL | |
| time_on | timestamp | YES | | NULL | |
| time_off | timestamp | YES | | NULL | |
| duration | varchar(24) | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+

ici aux lines 306 et 307 on voit que les pompes ont démarré et arrêté chacun leur tour successivement, les deux dernières lignes montrent un temps de marche simultané.

+-----+---------+-------+------+---------------------+---------------------+----------+
| id | poste12 | pompe | etat | time_on | time_off | duration |
+-----+---------+-------+------+---------------------+---------------------+----------+
| 306 | 0 | p1 | 1 | 2017-02-02 10:53:10 | 2017-02-02 10:53:41 | 00:00:31 |
| 307 | 0 | p2 | 1 | 2017-02-02 10:54:00 | 2017-02-02 10:54:18 | 00:00:18 |
| 308 | 0 | p1 | 1 | 2017-02-02 10:54:35 | 2017-02-02 10:54:55 | 00:00:20 |
| 309 | 0 | p2 | 1 | 2017-02-02 10:54:40 | 2017-02-02 10:54:55 | 00:00:15 |
+-----+---------+-------+------+---------------------+---------------------+----------+

Pour insérer les données automatiquement je passe par Node Red qui reçoit les messages et fait une requête INSERT aux démarrages et un UPDATE aux arrêts des pompes pour tout avoir sur une même ligne, ensuite j'ai un trigger BEFORE pour le calcul de temps de marche. Ce n'est peut-être pas la meilleure marche à suivre mais ça fonctionne nickel.

Maintenant j'aimerais avoir une table pour mes totaux journaliers et mensuels qui comprendrais les colonnes d'heures pour chaque pompes et une autre où j'aurais le temps de marche des deux pompes en simultané. C'est là que j'ai besoin d'être éclairé sur la bonne façon de le faire.

Merci de votre aide.

Citation:

Envoyé par jupiter8

Parfait c'est déjà ça de pris, ça va m'éviter de bricoler une sortie supplémentaire de mon micro-contrôleur.

ici aux lines 306 et 307 on voit que les pompes ont démarré et arrêté chacun leur tour successivement, les deux dernières lignes montrent un temps de marche simultané.

+-----+---------+-------+------+---------------------+---------------------+----------+
| id | poste12 | pompe | etat | time_on | time_off | duration |
+-----+---------+-------+------+---------------------+---------------------+----------+
| 306 | 0 | p1 | 1 | 2017-02-02 10:53:10 | 2017-02-02 10:53:41 | 00:00:31 |
| 307 | 0 | p2 | 1 | 2017-02-02 10:54:00 | 2017-02-02 10:54:18 | 00:00:18 |
| 308 | 0 | p1 | 1 | 2017-02-02 10:54:35 | 2017-02-02 10:54:55 | 00:00:20 |
| 309 | 0 | p2 | 1 | 2017-02-02 10:54:40 | 2017-02-02 10:54:55 | 00:00:15 |
+-----+---------+-------+------+---------------------+---------------------+----------+

Pour insérer les données automatiquement je passe par Node Red qui reçoit les messages et fait une requête INSERT aux démarrages et un UPDATE aux arrêts des pompes pour tout avoir sur une même ligne, ensuite j'ai un trigger BEFORE pour le calcul de temps de marche. Ce n'est peut-être pas la meilleure marche à suivre mais ça fonctionne nickel.

Pas bien !!! En effet vous forcez des calculs inutiles au pire moment (mise à jour) alors que ceci peut être fait dans une vue à l'affichage; Abandonnez votre couteur trigger et faites une vue qui présente le même calcul en sus des données de la table. Lisez moi : http://sqlpro.developpez.com/cours/s...age=partie2#L8

Citation:

Maintenant j'aimerais avoir une table pour mes totaux journaliers et mensuels qui comprendrais les colonnes d'heures pour chaque pompes et une autre où j'aurais le temps de marche des deux pompes en simultané. C'est là que j'ai besoin d'être éclairé sur la bonne façon de le faire.

À nouveau une vue serait top... J'en reprends le principe :
Code:

1 2 3 4 5 6 7 8 CREATE TABLE MesPompes (id INT, poste12 int, pompe CHAR(2), etat INT, time_on DATETIME2, time_off DATETIME2) INSERT INTO MesPompes VALUES (306 , 0 , 'p1' , 1 , '2017-02-02 10:53:10' , '2017-02-02 10:53:41'), (307 , 0 , 'p2' , 1 , '2017-02-02 10:54:00' , '2017-02-02 10:54:18'), (308 , 0 , 'p1' , 1 , '2017-02-02 10:54:35' , '2017-02-02 10:54:55'), (309 , 0 , 'p2' , 1 , '2017-02-02 10:54:40' , '2017-02-02 10:54:55');
C'est faux, j'ai pas le temps de terminer, mais c'est la base :
Code:

1 2 3 4 5 6 7 SELECT *, CASE WHEN (P1.time_on >= P2.time_on AND P1.time_on > P2.time_off) THEN P1.time_on ELSE P2.time_on END AS STARTS, CASE WHEN (P1.time_off > P2.time_on AND P1.time_off >= P2.time_off) THEN P1.time_off ELSE P2.time_off END AS ENDS FROM MesPompes AS P1 JOIN MesPompes AS P2 ON P1.pompe < P2.pompe AND ((P1.time_on >= P2.time_on AND P1.time_on > P2.time_off) OR (P1.time_off > P2.time_on AND P1.time_off >= P2.time_off))
A +

03/02/2017, 04h02
jupiter8

Ouaf, merci pour la piste, moi qui croyais avoir trouvé un bon plan, retour à la planche à dessin, Je vais étudier le truc des vues en profondeur.

Salut Jupiter8.

Il y a plusieurs points qui ne vont pas du tout :

1) faites attention aux types que vous utilisez.

1-a) "Pompe" contient le nom de la pompe, soit "P1" ou "P2".
Ce sont des chaînes de caractères sur deux caractères, que vous pouvez mettre à "char(02)".
Si ce sont des pompes que vous numérotez, autant mettre 1, 2. Le type devient alors "tinyint" qui lui va occuper un octet.

1-b) "Etat" est soit à marche ou soit à arrêt, il y a donc deux possibilités.
Le type est "tinyint" qui va occuper un octet.

1-c) comme le millionième de seconde ne vous intéresse pas, autant mettre le type datetime.
Le format de la date est "YYYY-MM-DD HH:MM:SS".

2) il est inutile de calculer la durée et de la stocker dans la table.
Vous pouvez utiliser les view (vue_1) et refaire le calcul de la durée.

3) pour le jeu d'essai, j'ai créé deux autres lignes, qui sont à cheval sur deux jours.
De ce fait, pour avoir le bon calcul de la durée, il a été nécessaire de faire la décomposition selon le jour.

4) pour éviter de répéter la même sous-requête, j'ai créé la view vue_2.

5) j'ai créé trois requêtes à partir de la vue_2, pour cumuler la durée en fonction :
--> du jour
--> du mois
--> de l'année

6) pour le calcul de la durée lorsque les pompes fonctionnent en simultanée, j'ai crée une view vue_3.
Afin de déterminer l'intervalle de temps commun aux deux pompes, j'ai calcul :
--> la date de début, en prenant la plus grande date entre les "time_on" de la pompe 1 et 2.
--> la date de fin, en prenant la plus petite date entre les "time_off" de la pompe 1 et 2.
Puis ensuite, on fait le calcul de la durée, comme on l'a déjà fait pour la vue_1 et vue_2.

En ce qui concerne le test de sélection, j'ai fait en sorte que la date_off de la pompe 2 soit plus petite que la date_on de la pompe 1.
C'est pour déterminer le chevauchement des deux périodes.

7) et voici le résultat en mysql :

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
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
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
    DEFAULT CHARACTER SET `latin1`
    DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `test`
--------------
 
--------------
CREATE TABLE `test`
( `id`        integer  unsigned not null auto_increment primary key,
  `poste12`   smallint unsigned not null,
  `pompe`     char(02)          not null,
  `etat`      tinyint unsigned  not null,
  `time_on`   datetime          not null,
  `time_off`  datetime          not null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `test` (`id`,`poste12`,`pompe`,`etat`,`time_on`,`time_off`) values
  (306, 0, 'p1', 1, '2017-02-02 10:53:10', '2017-02-02 10:53:41'),
  (307, 0, 'p2', 1, '2017-02-02 10:54:00', '2017-02-02 10:54:18'),
  (308, 0, 'p1', 1, '2017-02-02 10:54:35', '2017-02-02 10:54:55'),
  (309, 0, 'p2', 1, '2017-02-02 10:54:40', '2017-02-02 10:54:55'),
 
  (310, 0, 'p1', 1, '2017-02-02 23:59:40', '2017-02-03 00:00:25'),
  (311, 0, 'p2', 1, '2017-02-02 23:58:40', '2017-02-03 00:01:10')
--------------
 
--------------
select * from `test`
--------------
 
+-----+---------+-------+------+---------------------+---------------------+
| id  | poste12 | pompe | etat | time_on             | time_off            |
+-----+---------+-------+------+---------------------+---------------------+
| 306 |       0 | p1    |    1 | 2017-02-02 10:53:10 | 2017-02-02 10:53:41 |
| 307 |       0 | p2    |    1 | 2017-02-02 10:54:00 | 2017-02-02 10:54:18 |
| 308 |       0 | p1    |    1 | 2017-02-02 10:54:35 | 2017-02-02 10:54:55 |
| 309 |       0 | p2    |    1 | 2017-02-02 10:54:40 | 2017-02-02 10:54:55 |
| 310 |       0 | p1    |    1 | 2017-02-02 23:59:40 | 2017-02-03 00:00:25 |
| 311 |       0 | p2    |    1 | 2017-02-02 23:58:40 | 2017-02-03 00:01:10 |
+-----+---------+-------+------+---------------------+---------------------+
--------------
drop view if exists `vue_1`
--------------
 
--------------
create view `vue_1` as
  select  *, cast(TIME_TO_SEC(TIMEDIFF(time_off, time_on)) as unsigned) as duration
    from  test
--------------
 
--------------
select * from `vue_1`
--------------
 
+-----+---------+-------+------+---------------------+---------------------+----------+
| id  | poste12 | pompe | etat | time_on             | time_off            | duration |
+-----+---------+-------+------+---------------------+---------------------+----------+
| 306 |       0 | p1    |    1 | 2017-02-02 10:53:10 | 2017-02-02 10:53:41 |       31 |
| 307 |       0 | p2    |    1 | 2017-02-02 10:54:00 | 2017-02-02 10:54:18 |       18 |
| 308 |       0 | p1    |    1 | 2017-02-02 10:54:35 | 2017-02-02 10:54:55 |       20 |
| 309 |       0 | p2    |    1 | 2017-02-02 10:54:40 | 2017-02-02 10:54:55 |       15 |
| 310 |       0 | p1    |    1 | 2017-02-02 23:59:40 | 2017-02-03 00:00:25 |       45 |
| 311 |       0 | p2    |    1 | 2017-02-02 23:58:40 | 2017-02-03 00:01:10 |      150 |
+-----+---------+-------+------+---------------------+---------------------+----------+
--------------
select sum(duration) as total from `vue_1`
--------------
 
+-------+
| total |
+-------+
|   279 |
+-------+
--------------
drop view if exists `vue_2`
--------------
 
--------------
create view `vue_2` as
 
select  pompe, time_on, time_off, cast(TIME_TO_SEC(TIMEDIFF(time_off, time_on)) as unsigned) as duration
  from  (  select  pompe,
                   time_on,
                   case when date(time_off) > date(time_on) then cast(concat(date(time_off), ' 00:00:00') as datetime) else time_off end as time_off
             from  test
 
            union
 
           select  pompe,
                   cast(concat(date(time_off), ' 00:00:00') as datetime) as time_on,
                   time_off
             from  test
            where  date(time_off) > date(time_on)
        ) as x
--------------
 
--------------
select * from `vue_2`
--------------
 
+-------+---------------------+---------------------+----------+
| pompe | time_on             | time_off            | duration |
+-------+---------------------+---------------------+----------+
| p1    | 2017-02-02 10:53:10 | 2017-02-02 10:53:41 |       31 |
| p2    | 2017-02-02 10:54:00 | 2017-02-02 10:54:18 |       18 |
| p1    | 2017-02-02 10:54:35 | 2017-02-02 10:54:55 |       20 |
| p2    | 2017-02-02 10:54:40 | 2017-02-02 10:54:55 |       15 |
| p1    | 2017-02-02 23:59:40 | 2017-02-03 00:00:00 |       20 |
| p2    | 2017-02-02 23:58:40 | 2017-02-03 00:00:00 |       80 |
| p1    | 2017-02-03 00:00:00 | 2017-02-03 00:00:25 |       25 |
| p2    | 2017-02-03 00:00:00 | 2017-02-03 00:01:10 |       70 |
+-------+---------------------+---------------------+----------+
--------------
select    date(time_on) as jour,
          pompe,
          sum(duration) as duration
    from  vue_2
   where  time_on is not null
group by  date(time_on), pompe
order by  date(time_on), pompe
--------------
 
+------------+-------+----------+
| jour       | pompe | duration |
+------------+-------+----------+
| 2017-02-02 | p1    |       71 |
| 2017-02-02 | p2    |      113 |
| 2017-02-03 | p1    |       25 |
| 2017-02-03 | p2    |       70 |
+------------+-------+----------+
--------------
select    date_format(time_on, '%Y-%m') as mois,
          pompe,
          sum(duration) as duration
    from  vue_2
   where  time_on is not null
group by  date_format(time_on, '%Y-%m'), pompe
order by  date_format(time_on, '%Y-%m'), pompe
--------------
 
+---------+-------+----------+
| mois    | pompe | duration |
+---------+-------+----------+
| 2017-02 | p1    |       96 |
| 2017-02 | p2    |      183 |
+---------+-------+----------+
--------------
select    year(time_on) as annee,
          pompe,
          sum(duration) as duration
    from  vue_2
   where  time_on is not null
group by  year(time_on), pompe
order by  year(time_on), pompe
--------------
 
+-------+-------+----------+
| annee | pompe | duration |
+-------+-------+----------+
|  2017 | p1    |       96 |
|  2017 | p2    |      183 |
+-------+-------+----------+
--------------
drop view if exists `vue_3`
--------------
 
--------------
create view `vue_3` as
  select  *, case when fin > deb then cast(TIME_TO_SEC(TIMEDIFF(fin, deb)) as unsigned) else 0 end as duration
    from  (  select  t1.id                              as p1_id,
                     t1.pompe                           as p1_pompe,
                     t1.time_on                         as p1_time_on,
                     t1.time_off                        as p1_time_off,
                     t2.id                              as p2_id,
                     t2.pompe                           as p2_pompe,
                     t2.time_on                         as p2_time_on,
                     t2.time_off                        as p2_time_off,
                     greatest(t1.time_on,  t2.time_on)  as deb,
                     least(t1.time_off, t2.time_off)    as fin
               from  test as t1
         inner join  test as t2
                 on  t2.pompe    = 'p2'
                and  t2.time_on <= t1.time_off
              where  t1.pompe    = 'p1'
           order by  t1.time_on, t2.time_on
          ) as x
--------------
 
--------------
select * from `vue_3`
--------------
 
+-------+----------+---------------------+---------------------+-------+----------+---------------------+---------------------+---------------------+---------------------+----------+
| p1_id | p1_pompe | p1_time_on          | p1_time_off         | p2_id | p2_pompe | p2_time_on          | p2_time_off         | deb                 | fin                 | duration |
+-------+----------+---------------------+---------------------+-------+----------+---------------------+---------------------+---------------------+---------------------+----------+
|   308 | p1       | 2017-02-02 10:54:35 | 2017-02-02 10:54:55 |   307 | p2       | 2017-02-02 10:54:00 | 2017-02-02 10:54:18 | 2017-02-02 10:54:35 | 2017-02-02 10:54:18 |        0 |
|   308 | p1       | 2017-02-02 10:54:35 | 2017-02-02 10:54:55 |   309 | p2       | 2017-02-02 10:54:40 | 2017-02-02 10:54:55 | 2017-02-02 10:54:40 | 2017-02-02 10:54:55 |       15 |
|   310 | p1       | 2017-02-02 23:59:40 | 2017-02-03 00:00:25 |   307 | p2       | 2017-02-02 10:54:00 | 2017-02-02 10:54:18 | 2017-02-02 23:59:40 | 2017-02-02 10:54:18 |        0 |
|   310 | p1       | 2017-02-02 23:59:40 | 2017-02-03 00:00:25 |   309 | p2       | 2017-02-02 10:54:40 | 2017-02-02 10:54:55 | 2017-02-02 23:59:40 | 2017-02-02 10:54:55 |        0 |
|   310 | p1       | 2017-02-02 23:59:40 | 2017-02-03 00:00:25 |   311 | p2       | 2017-02-02 23:58:40 | 2017-02-03 00:01:10 | 2017-02-02 23:59:40 | 2017-02-03 00:00:25 |       45 |
+-------+----------+---------------------+---------------------+-------+----------+---------------------+---------------------+---------------------+---------------------+----------+
--------------
select  sum(duration) as duration
  from  vue_3
--------------
 
+----------+
| duration |
+----------+
|       60 |
+----------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

J'espère que cela correspond à votre attente.

P.S.: En France, on dit durée et non duration qui est une mesure de la durée de vie moyenne d'une obligation.

@+

1 pièce(s) jointe(s)

Je n'avais pas eu le temps de finaliser ma requête, mais voici la bonne :

Code:

1
2
3
4
5
6
7
8
9
SELECT *, CASE WHEN (P1.time_on >= P2.time_on AND P1.time_on > P2.time_off) THEN P1.time_on 
                    ELSE P2.time_on END AS STARTS,
          CASE WHEN (P1.time_off > P2.time_on AND P1.time_off <= P2.time_off) THEN P1.time_off 
ELSE P2.time_off END AS ENDS
FROM   MesPompes AS P1
       JOIN MesPompes AS P2
            ON P1.pompe < P2.pompe
            AND ((P1.time_on >= P2.time_on AND P1.time_on < P2.time_off)
                 OR (P1.time_off > P2.time_on AND P1.time_off <= P2.time_off))

Pour en faire des vues...

1) pour la durée :

Code:

1
2
3
4
CREATE VIEW V_POMPE_DUREE
AS
SELECT *, CAST(TIME_TO_SEC(TIMEDIFF(time_off, time_on)) AS unsigned) AS DUREE_SECONDE
FROM   MesPompes;

2) pour la simultanéité :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE VIEW V_POMPES_SIMULTANES
AS
SELECT P1.id AS ID_1, P2.id AS ID_2, P1.pompe AS NOM_POMPE_1, P2.pompe AS NOM_POMPE_2, 
       CASE WHEN (P1.time_on >= P2.time_on AND P1.time_on > P2.time_off) 
               THEN P1.time_on 
            ELSE P2.time_on 
       END AS DEBUT,
       CASE WHEN (P1.time_off > P2.time_on AND P1.time_off <= P2.time_off) 
               THEN P1.time_off 
            ELSE P2.time_off 
       END AS FIN
FROM   MesPompes AS P1
       JOIN MesPompes AS P2
            ON P1.pompe < P2.pompe
            AND ((P1.time_on >= P2.time_on AND P1.time_on < P2.time_off)
                 OR (P1.time_off > P2.time_on AND P1.time_off <= P2.time_off));

Vues que vous pouvez bien entendu joindre à toutes vos autres tables...

Et pour apprendre le SQL, lisez mon livre :
Pièce jointe 241108

A +

04/02/2017, 00h33
jupiter8

Un grand merci à vous deux, les deux solutions fonctionnent très bien, vous m'avez fait sauver un temps fou et fait comprendre qu'on ne fait pas de calculs sur un table mais bien sur des vues.

Maintenant pour sortir mes données et les rendre présentables sur une page web ou un classeur Libre Office par exemple il me faudra faire des requêtes sur ces vues j'imagine?

Aussi est-ce une bonne pratique d’effacer ou de détruire et recréer une table après un certain laps de temps de façon à ce qu'elle ne devienne pas trop volumineuse?

Salut jupiter8.

Je propose une autre solution, qui consiste à passer par une table où l'on va faire la mise en forme de la ligne.
Entre autre, décomposer la ligne lorsque celle-ci est à cheval sur deux jours.
Et du coup, la view "vue_1" n'a plus lieu d'être, et cela résout le fait que je faisais référence à cette view pour constituer les autres view.

Dans la table, j'ai créé un index sur le numéro de pompe, la date time_deb et time_fin.
Cela permet d'augmenter la performance, si la table vient à être imposante.

J'ai produit aussi un "explain" afin de se rendre compte de ce qui se passe dans chaque requête.
On constate que l'index "idx" est bien utilisé, et que le niveau d'imbrication n'est pas trop important.

C'est à l'usage que l'on va se rendre compte de la performance de chaque requête.

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
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
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
    DEFAULT CHARACTER SET `latin1`
    DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `test`
--------------
 
--------------
CREATE TABLE `test`
( `id`        integer unsigned not null auto_increment primary key,
  `pompe`     tinyint unsigned not null,
  `time_deb`  datetime         not null,
  `time_fin`  datetime         not null,
  unique index `idx` (`pompe`,`time_deb`,`time_fin`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
DROP TABLE IF EXISTS `test_bis`
--------------
 
--------------
CREATE TABLE `test_bis`
( `id`        integer  unsigned not null auto_increment primary key,
  `pompe`     tinyint  unsigned not null,
  `time_deb`  datetime          not null,
  `time_fin`  datetime          not null,
  unique index `idx` (`pompe`,`time_deb`,`time_fin`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
DROP TRIGGER IF EXISTS `intervalle`
--------------
 
--------------
CREATE TRIGGER `intervalle`
BEFORE INSERT ON `test`
FOR EACH ROW
BEGIN
  DECLARE _time_fin  datetime;
 
  IF date(new.time_fin) > date(new.time_deb) THEN
     SET _time_fin   = cast(concat(cast(date(new.time_fin) as char(10)), ' ', '00:00:00') as datetime);
 
     INSERT `test_bis` (`pompe`,`time_deb`,`time_fin`) VALUES (new.pompe, new.time_deb,    _time_fin);
     INSERT `test_bis` (`pompe`,`time_deb`,`time_fin`) VALUES (new.pompe,    _time_fin,   new.time_fin);
  ELSE
     INSERT `test_bis` (`pompe`,`time_deb`,`time_fin`) VALUES (new.pompe, new.time_deb, new.time_fin);
  END IF;
END
--------------
 
--------------
insert into `test` (`pompe`,`time_deb`,`time_fin`) values
  (1, '2017-02-02 10:53:10', '2017-02-02 10:53:41'),
  (2, '2017-02-02 10:54:00', '2017-02-02 10:54:18'),
  (1, '2017-02-02 10:54:35', '2017-02-02 10:54:55'),
  (2, '2017-02-02 10:54:40', '2017-02-02 10:54:55'),
  (1, '2017-02-02 23:59:40', '2017-02-03 00:00:25'),
  (2, '2017-02-02 23:58:40', '2017-02-03 00:01:10')
--------------
 
--------------
select * from `test`     order by id
--------------
 
+----+-------+---------------------+---------------------+
| id | pompe | time_deb            | time_fin            |
+----+-------+---------------------+---------------------+
|  1 |     1 | 2017-02-02 10:53:10 | 2017-02-02 10:53:41 |
|  2 |     2 | 2017-02-02 10:54:00 | 2017-02-02 10:54:18 |
|  3 |     1 | 2017-02-02 10:54:35 | 2017-02-02 10:54:55 |
|  4 |     2 | 2017-02-02 10:54:40 | 2017-02-02 10:54:55 |
|  5 |     1 | 2017-02-02 23:59:40 | 2017-02-03 00:00:25 |
|  6 |     2 | 2017-02-02 23:58:40 | 2017-02-03 00:01:10 |
+----+-------+---------------------+---------------------+
--------------
select * from `test_bis` order by id
--------------
 
+----+-------+---------------------+---------------------+
| id | pompe | time_deb            | time_fin            |
+----+-------+---------------------+---------------------+
|  1 |     1 | 2017-02-02 10:53:10 | 2017-02-02 10:53:41 |
|  2 |     2 | 2017-02-02 10:54:00 | 2017-02-02 10:54:18 |
|  3 |     1 | 2017-02-02 10:54:35 | 2017-02-02 10:54:55 |
|  4 |     2 | 2017-02-02 10:54:40 | 2017-02-02 10:54:55 |
|  5 |     1 | 2017-02-02 23:59:40 | 2017-02-03 00:00:00 |
|  6 |     1 | 2017-02-03 00:00:00 | 2017-02-03 00:00:25 |
|  7 |     2 | 2017-02-02 23:58:40 | 2017-02-03 00:00:00 |
|  8 |     2 | 2017-02-03 00:00:00 | 2017-02-03 00:01:10 |
+----+-------+---------------------+---------------------+
--------------
truncate `test`
--------------
 
--------------
drop view if exists `vue_jour`
--------------
 
--------------
create view `vue_jour` as
  select  date(time_deb) as jour, pompe, sum(cast(TIME_TO_SEC(TIMEDIFF(time_fin, time_deb)) as unsigned)) as duration
    from  test_bis
group by  date(time_deb), pompe
order by  date(time_deb), pompe
--------------
 
--------------
explain select * from `vue_jour`
--------------
 
+----+-------------+------------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------------+
| id | select_type | table      | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                        |
+----+-------------+------------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL   | NULL          | NULL | NULL    | NULL |    8 |   100.00 | NULL                                         |
|  2 | DERIVED     | test_bis   | NULL       | index | idx           | idx  | 11      | NULL |    8 |   100.00 | Using index; Using temporary; Using filesort |
+----+-------------+------------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------------+
--------------
select * from `vue_jour`
--------------
 
+------------+-------+----------+
| jour       | pompe | duration |
+------------+-------+----------+
| 2017-02-02 |     1 |       71 |
| 2017-02-02 |     2 |      113 |
| 2017-02-03 |     1 |       25 |
| 2017-02-03 |     2 |       70 |
+------------+-------+----------+
--------------
drop view if exists `vue_total`
--------------
 
--------------
create view `vue_total` as
  select  pompe, sum(cast(TIME_TO_SEC(TIMEDIFF(time_fin, time_deb)) as unsigned)) as total
    from  test_bis
group by  pompe
order by  pompe
--------------
 
--------------
explain select * from `vue_total`
--------------
 
+----+-------------+------------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL   | NULL          | NULL | NULL    | NULL |    8 |   100.00 | NULL        |
|  2 | DERIVED     | test_bis   | NULL       | index | idx           | idx  | 11      | NULL |    8 |   100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+------+---------+------+------+----------+-------------+
--------------
select * from `vue_total`
--------------
 
+-------+-------+
| pompe | total |
+-------+-------+
|     1 |    96 |
|     2 |   183 |
+-------+-------+
--------------
drop view if exists `vue_simul_jour`
--------------
 
--------------
create view `vue_simul_jour` as
  select  date(time_deb) as jour,
          sum(case when time_fin > time_deb then cast(TIME_TO_SEC(TIMEDIFF(time_fin, time_deb)) as unsigned) else 0 end) as duration
    from  (  select  greatest(ta.time_deb, tb.time_deb) as time_deb,
                        least(ta.time_fin, tb.time_fin) as time_fin
 
               from  test_bis as ta
 
         inner join  test_bis as tb
                 on  tb.pompe = 2
                and (tb.time_deb >= ta.time_deb and tb.time_deb <= ta.time_fin
                 or  ta.time_deb >= tb.time_deb and ta.time_deb <= tb.time_fin)
 
              where  ta.pompe = 1
          ) as x
group by  date(time_deb)
order by  date(time_deb)
--------------
 
--------------
explain select * from `vue_simul_jour`
--------------
 
+----+-------------+------------+------------+------+---------------+------+---------+-------+------+----------+----------------------------------------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra                                        |
+----+-------------+------------+------------+------+---------------+------+---------+-------+------+----------+----------------------------------------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL  |    3 |   100.00 | NULL                                         |
|  2 | DERIVED     | ta         | NULL       | ref  | idx           | idx  | 1       | const |    4 |   100.00 | Using index; Using temporary; Using filesort |
|  2 | DERIVED     | tb         | NULL       | ref  | idx           | idx  | 1       | const |    4 |    20.98 | Using where; Using index                     |
+----+-------------+------------+------------+------+---------------+------+---------+-------+------+----------+----------------------------------------------+
--------------
select * from `vue_simul_jour`
--------------
 
+------------+----------+
| jour       | duration |
+------------+----------+
| 2017-02-02 |       35 |
| 2017-02-03 |       25 |
+------------+----------+
--------------
drop view if exists `vue_simul_total`
--------------
 
--------------
create view `vue_simul_total` as
  select  sum(case when time_fin > time_deb then cast(TIME_TO_SEC(TIMEDIFF(time_fin, time_deb)) as unsigned) else 0 end) as total
    from  (  select  greatest(ta.time_deb, tb.time_deb) as time_deb,
                        least(ta.time_fin, tb.time_fin) as time_fin
 
               from  test_bis as ta
 
         inner join  test_bis as tb
                 on  tb.pompe = 2
                and (tb.time_deb >= ta.time_deb and tb.time_deb <= ta.time_fin
                 or  ta.time_deb >= tb.time_deb and ta.time_deb <= tb.time_fin)
 
              where  ta.pompe = 1
          ) as x
--------------
 
--------------
explain select * from `vue_simul_total`
--------------
 
+----+-------------+------------+------------+--------+---------------+------+---------+-------+------+----------+--------------------------+
| id | select_type | table      | partitions | type   | possible_keys | key  | key_len | ref   | rows | filtered | Extra                    |
+----+-------------+------------+------------+--------+---------------+------+---------+-------+------+----------+--------------------------+
|  1 | PRIMARY     | <derived2> | NULL       | system | NULL          | NULL | NULL    | NULL  |    1 |   100.00 | NULL                     |
|  2 | DERIVED     | ta         | NULL       | ref    | idx           | idx  | 1       | const |    4 |   100.00 | Using index              |
|  2 | DERIVED     | tb         | NULL       | ref    | idx           | idx  | 1       | const |    4 |    20.98 | Using where; Using index |
+----+-------------+------------+------------+--------+---------------+------+---------+-------+------+----------+--------------------------+
--------------
select * from `vue_simul_total`
--------------
 
+-------+
| total |
+-------+
|    60 |
+-------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

Ton pseudo Jupiter8, a-t-il un rapport avec "Roland Jupiter 8", un synthétiseur analogique ?

Citation:

Envoyé par Jupiter8

il me faudra faire des requêtes sur ces vues j'imagine?

Sur ces view, vous pouvez faire exactement la même chose que sur une table.

Citation:

Envoyé par Jupiter8

Aussi est-ce une bonne pratique d’effacer ou de détruire et recréer une table après un certain laps de temps de façon à ce qu'elle ne devienne pas trop volumineuse?

Dans mon exemple, la table "test" sert à réceptionner la ligne à l'état brut en provenance de vos pompes.
Une fois par jour, vous pouvez remettre cette table à zéro, comme je l'ai fait dans mon exemple (truncate test).
Pour ce faire, vous pouvez utiliser le "planificateur de tâches" de Windows pour déclencher à heure fixe, le nettoyage de la table.
Je ne vous conseille pas de détruire et de recréer la table à chaque fois que vous désirez la remettre à zéro.

En ce qui concerne l'autre table "test_bis", vous avez deux possibilités :

1) faire une suppression de toutes les lignes, une fois par mois, en faisant un "delete".
C'est long et il vous sera nécessaire de faire à chaque fois une optimisation de la table.

2) créer une partition pour chaque mois de l'année.
Il vous sera d'autant plus facile à la remettre à zéro, car l'impacte se fera que sur la partition que vous aurez sélectionner.
Et cela ne nécessite aucune optimisation sur la table.

Pour supprimer toutes les lignes d'une partition, faire par exemple : "ALTER TABLE t1 TRUNCATE PARTITION p5;" où p5 est le nom de la partition.
--> https://dev.mysql.com/doc/refman/5.7...perations.html

Ce "p5", vous pouvez le gérer comme vous voulez, par exemple concaténer "p" avec le mois précédent.
Cela peut se faire automatiquement et le déclencher une fois par mois en utilisant le "planificateur de tâches".

@+

04/02/2017, 17h16
jupiter8

Ouf, vous ne chômez pas vous alors, mille mercis pour tout ce travail, je vais avoir besoin de temps pour assimiler tout ça et faire plus de lecture. Je reviendrai avec quelques petites questions dans les prochains jour au fur et à mesure que tout ça va se mettre ensemble.

Citation:

Ton pseudo Jupiter8, a-t-il un rapport avec "Roland Jupiter 8", un synthétiseur analogique ?

En effet, c'est un monstre mythique que je rêve d'ajouter à mon arsenal de synthés. ;)

à bientôt.