Splitter un enregistrement en N enregistrements en fonction de 2 dates

Version imprimable

23/09/2021, 10h16
bart64

Splitter un enregistrement en N enregistrements en fonction de 2 dates

Bonjour à tous,

Je bloque sur un problème SQL : j'ai un enregistrement ayant 3 champs :
- start date (datetime) : 2021-01-01 13:01:30
- end date (datetime) : 2021-01-01 13:05:15
- nombre : 500

je voudrais splitter cet enregistrement en autant d'enregistrement que l'on trouve de minutes entre start_date et end_date, et diviser le nombre par le nombre d'enregistrements possibles (ici : 5 sous-enregistrements) :

Enregistrement 1 :
- start date (datetime) : 2021-01-01 13:01:00
- end date (datetime) : 2021-01-01 13:01:59
- nombre : 500/5 = 100

Enregistrement 2 :
- start date (datetime) : 2021-01-01 13:02:00
- end date (datetime) : 2021-01-01 13:02:59
- nombre : 500/5 = 100

Enregistrement 3 :
- start date (datetime) : 2021-01-01 13:03:00
- end date (datetime) : 2021-01-01 13:03:59
- nombre : 500/5 = 100

Enregistrement 4 :
- start date (datetime) : 2021-01-01 13:04:00
- end date (datetime) : 2021-01-01 13:04:59
- nombre : 500/5 = 100

Enregistrement 5 :
- start date (datetime) : 2021-01-01 13:05:00
- end date (datetime) : 2021-01-01 13:05:15
- nombre : 500/5 = 100

Ceci n'est qu'un exemple, je voudrais que cela se fasse automatiquement entre start_date et end_date, quelles que soient leurs valeurs.
J'ai bien cherché sur internet mais je n'ai pas trouvé de manière dynamique de splitter ces enregistrements.

Je vous remercie pour vos lumières !
24/09/2021, 09h10
SQLpro
Il faut faire une CTE récursive qui présentera autant de lignes que de segment à créer, diviser l'intervalle de temps par la mesure demandée et faire un produit cartésien final pour réajuster le temps initial à la quantité d'intervalle de temps.

Néanmoins, je remarque que vos données sont anormales... En effet lorsque l'on travaille avec des intervalles, il faut définir que l'intervalle
- soit fermé à gauche et ouverte à droite ([...[)
- soit ouvert à gauche et fermé à droite (]...])
Sinon tous vos calculs seront faux.

Pour de plus amples explication, merci de respecter la règle de postage et donner le DDL de vos tables ainsi qu'un jeu d'essais sous forme INSERT.

A +

Bonjour SQLPro et merci pour votre réponse !

Voici les éléments demandés :

La table :
Code:

1 2 3 4 5 6 7 8 9 10 11 CREATE TABLE `STEPS` ( `start_date` datetime NOT NULL, `end_date` datetime NOT NULL, `steps` int DEFAULT NULL, `year` int DEFAULT NULL, `day` int DEFAULT NULL, `month` int DEFAULT NULL, `week_number` int DEFAULT NULL, `week_day` int DEFAULT NULL, PRIMARY KEY (`start_date`,`end_date`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Un jeu de données sous forme d'INSERT :
Code:

1 2 3 4 5 6 INSERT INTO STEPS VALUES( ('2021-08-11 19:04:40','2021-08-11 19:04:45',11,2021,11,8,32,3) ,('2021-08-11 19:04:45','2021-08-11 19:04:50',10,2021,11,8,32,3) ,('2021-08-11 19:04:50','2021-08-11 19:04:55',10,2021,11,8,32,3) ,('2021-08-11 19:04:55','2021-08-11 19:05:01',11,2021,11,8,32,3) );
C'est pour un projet personnel. Je fais un export des données santé de l'iphone, qui est disponible au format XML, je l'importe dans un site et je fais des statistiques sur les pas effectués.

Je pose ma question car je voudrais faire un graphique avec une journée type. Donc je dois lisser les informations (par demi-heure) pour connaître le nombre de pas moyen pour chaque demi-heure de la journée.

Je vais faire des recherches sur la CTE récursive, je ne connais pas du tout.

Je suis vraiment désolé si je n'ai pas respecté les règles de postage, je ne pensais pas avoir commis d'erreur en posant ma question :( Qu'ai-je dit ou fait de non conforme ?

Un grand merci et une excellente soirée.

Salut à tous.

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
--------------
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 IF NOT EXISTS `test`
( `id`          integer unsigned  NOT NULL auto_increment primary key,
  `start_date`  datetime          NOT NULL,
  `end_date`    datetime          NOT NULL,
  `steps`       integer unsigned  NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
DROP TABLE IF EXISTS `split`
--------------
 
--------------
CREATE TABLE `split` LIKE `test`
--------------
 
--------------
describe `split`
--------------
 
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int unsigned | NO   | PRI | NULL    | auto_increment |
| start_date | datetime     | NO   |     | NULL    |                |
| end_date   | datetime     | NO   |     | NULL    |                |
| steps      | int unsigned | NO   |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+
--------------
drop procedure if exists `trait`
--------------
 
--------------
create procedure `trait` ()
DETERMINISTIC
NO SQL
BEGIN
  DECLARE _start  DATETIME DEFAULT NULL;
  DECLARE _end    DATETIME DEFAULT NULL;
  DECLARE _step   INTEGER  DEFAULT NULL;
 
  DECLARE _inter  DATETIME DEFAULT NULL;
  DECLARE _nbre   INTEGER  DEFAULT NULL;
  DECLARE _loop   INTEGER  DEFAULT NULL;
 
  DECLARE _fin    INTEGER  DEFAULT 1;
 
  DECLARE _tab  CURSOR FOR SELECT `start_date`,`end_date`,`steps` FROM `test`;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET _fin = 0;
 
  OPEN  _tab;
  FETCH _tab INTO _start, _end, _step;
 
  SET _inter = date_format(_start,'%y-%m-%d %H:%i:59');
 
  WHILE (_fin)
  DO
    SET _nbre  = TIMESTAMPDIFF(MINUTE,date_format(_start,'%y-%m-%d %H:%i:00'),date_format(_end ,'%y-%m-%d %H:%i:00')) + 1;
    SET _loop  = _nbre;
 
    WHILE (_loop > 0)
    DO
      IF (_loop = 1) THEN SET _inter = _end;
      ELSE                SET _inter = date_format(_start,'%y-%m-%d %H:%i:59');
      END IF;
 
      INSERT INTO `split` (`start_date`,`end_date`,`steps`) VALUE (_start,_inter,_step/_nbre);
 
      SET _start = DATE_ADD(DATE_FORMAT(_start,'%y-%m-%d %H:%i:00'), INTERVAL 1 MINUTE);
      SET _loop  = _loop - 1;
    END WHILE;
 
    FETCH _tab INTO _start, _end, _step;
  END WHILE;
 
  CLOSE _tab;
END
--------------
 
--------------
INSERT INTO `test` (`start_date`,`end_date`,`steps`) VALUES
  ('2021-01-01 13:01:30','2021-01-01 13:05:15',500)
--------------
 
--------------
CALL trait()
--------------
 
--------------
select * from `test`
--------------
 
+----+---------------------+---------------------+-------+
| id | start_date          | end_date            | steps |
+----+---------------------+---------------------+-------+
|  1 | 2021-01-01 13:01:30 | 2021-01-01 13:05:15 |   500 |
+----+---------------------+---------------------+-------+
--------------
select * from `split`
--------------
 
+----+---------------------+---------------------+-------+
| id | start_date          | end_date            | steps |
+----+---------------------+---------------------+-------+
|  1 | 2021-01-01 13:01:30 | 2021-01-01 13:01:59 |   100 |
|  2 | 2021-01-01 13:02:00 | 2021-01-01 13:02:59 |   100 |
|  3 | 2021-01-01 13:03:00 | 2021-01-01 13:03:59 |   100 |
|  4 | 2021-01-01 13:04:00 | 2021-01-01 13:04:59 |   100 |
|  5 | 2021-01-01 13:05:00 | 2021-01-01 13:05:15 |   100 |
+----+---------------------+---------------------+-------+
--------------
truncate `test`
--------------
 
--------------
truncate `split`
--------------
 
--------------
INSERT INTO `test` (`start_date`,`end_date`,`steps`) VALUES
  ('2021-08-11 19:04:40','2021-08-11 19:04:45',11),
  ('2021-08-11 19:04:45','2021-08-11 19:04:50',10),
  ('2021-08-11 19:04:50','2021-08-11 19:04:55',10),
  ('2021-08-11 19:04:55','2021-08-11 19:05:01',11)
--------------
 
--------------
CALL trait()
--------------
 
--------------
select * from `test`
--------------
 
+----+---------------------+---------------------+-------+
| id | start_date          | end_date            | steps |
+----+---------------------+---------------------+-------+
|  1 | 2021-08-11 19:04:40 | 2021-08-11 19:04:45 |    11 |
|  2 | 2021-08-11 19:04:45 | 2021-08-11 19:04:50 |    10 |
|  3 | 2021-08-11 19:04:50 | 2021-08-11 19:04:55 |    10 |
|  4 | 2021-08-11 19:04:55 | 2021-08-11 19:05:01 |    11 |
+----+---------------------+---------------------+-------+
--------------
select * from `split`
--------------
 
+----+---------------------+---------------------+-------+
| id | start_date          | end_date            | steps |
+----+---------------------+---------------------+-------+
|  1 | 2021-08-11 19:04:40 | 2021-08-11 19:04:45 |    11 |
|  2 | 2021-08-11 19:04:45 | 2021-08-11 19:04:50 |    10 |
|  3 | 2021-08-11 19:04:50 | 2021-08-11 19:04:55 |    10 |
|  4 | 2021-08-11 19:04:55 | 2021-08-11 19:04:59 |     6 |
|  5 | 2021-08-11 19:05:00 | 2021-08-11 19:05:01 |     6 |
+----+---------------------+---------------------+-------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

28/09/2021, 12h30
bart64

Bonjour Artémus,

Un grand merci pour ce code, je ne m'imaginais pas recevoir une réponse aussi claire et précise !
Je trouve des erreurs à l'exécution du code mais je crois que je dois l'adapter pour que ça fonctionne chez moi.

Code:

Error Code: 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 '' at line 5

Etant donné que je n'ai pas pratiqué les procédures mysql, je dois chercher de mon côté pourquoi j'ai ces erreurs. Je reviendrai vers vous pour vous dire ce qu'il en est.

Encore merci et à très vite !
04/10/2021, 20h36
bart64

Bonjour Artémus,

Votre code fonctionnement parfaitement, conformément à ce que j'avais demandé. Je vous remercie énormément pour tout le travail que vous avez accompli pour pouvoir m'aider.

Un grand merci à vous.

A++