Discussion :

[skazu]

Bonjour,

Je vous sollicite car je n'arrive pas à construire une requête sql.

Je possède un automate qui enregistre dans une table (vout1) le changement d'état d'un contacteur (vout22). Ce dernier prend la valeur 0 ou 1.
Je souhaite connaître la durée entre 2 changements de valeur de vout22.

Ci-dessous un extrait du contenu de la table :

+---------------------+--------+
| ts | vout22 |
+---------------------+--------+
| 2023-01-04 09:47:57 | 1 |
| 2023-01-04 09:47:58 | 1 |
| 2023-01-04 09:47:59 | 1 |
| 2023-01-04 09:48:00 | 1 |
| 2023-01-04 09:48:01 | 1 |
| 2023-01-04 09:48:02 | 1 |
...
| 2023-01-04 09:57:21 | 1 |
| 2023-01-04 09:57:22 | 1 |
| 2023-01-04 09:57:23 | 1 |
| 2023-01-04 09:57:24 | 1 |
| 2023-01-04 09:57:25 | 1 |
| 2023-01-04 09:57:26 | 0 |
| 2023-01-04 10:07:10 | 0 |
| 2023-01-04 10:07:29 | 0 |
| 2023-01-04 10:17:20 | 0 |
| 2023-01-04 10:17:34 | 0 |
| 2023-01-04 10:17:35 | 0 |
| 2023-01-04 10:17:36 | 0 |
| 2023-01-04 10:17:37 | 0 |
| 2023-01-04 10:17:38 | 0 |
| 2023-01-04 10:17:39 | 0 |
| 2023-01-04 10:17:40 | 1 |
| 2023-01-04 10:17:41 | 1 |
| 2023-01-04 10:17:42 | 1 |
| 2023-01-04 10:17:43 | 1 |
| 2023-01-04 10:17:44 | 1 |
| 2023-01-04 10:17:45 | 1 |
| 2023-01-04 10:17:46 | 1 |
| 2023-01-04 10:17:47 | 1 |
| 2023-01-04 10:17:49 | 1 |
| 2023-01-04 10:17:50 | 0 |
| 2023-01-04 10:17:51 | 0 |
...

Le contacteur est à 1 le 2023-01-04 à 9h47 et repasse à zéro le 2023-01-04 09:57 soit 10mn, puis
le contacteur est à 0 le 2023-01-04 09:57:26 jusqu'au 2023-01-04 10:17:39 soit 20mn
etc.

Est-il possible de créer une requête sql qui calcule ces durées 10mn et 20mn ou faut-il passer par un autre outil ?

Je vous remercie pour vos réponses.

[binarygirl]

Il faudrait un peu plus de précisions.

• De quel système de DB parle-t-on ?
• Quelle est la structure de cette table ?
• Donner un exemple du résultat attendu.

Un indice: faites une recherche sur les "window functions" en SQL.
Mais pour faire une requête il faut savoir quel est le type de DB utilisé, puisque la syntaxe et les fonctions disponibles varient d'un système à un autre, et il vaudrait mieux que le champ soit bien de type datetime (j'ose espérer que oui mais on ne peut pas être sûr).

[skazu]

Merci pour votre réponse, oui effectivement je peux apporter quelques précisions.

La DB est MariaDB, la table comporte un champ ts de type datetime et les champs voutxx sont du type varchar(1).



J'ai fait des recherches mais j'ai un peu de mal à formuler ma demande.

Je souhaite parcourir la table, et à chaque changement de la valeur du champ vout22 (0 ou 1) faire la soustraction du champ ts (min) et ts (max) afin d'obtenir une durée pour une valeur (0 ou 1).

ex :
vout 22 le 2023-01-04 à 9h47 est à 1 et repasse à 0 le 2023-01-04 09:57 soit 10mn.

[escartefigue]

Quelque chose comme ceci :
T1 étant votre table avec T1.TS=le time_stamp et T1.FL=le flag d'état valant zéro ou un

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
with T2 as (select T1.TS
                 , T1.FL
                 , row_number() over (order by T1.TS)
                 - row_number() over (partition by T1.FL order by T1.TS) as X
            from T1 as T1
           )
select min(TS)
     , max(TS)
     , timestampdiff(second, min(TS), max(TS)) as ec
from T2
group by X
order by 1

Ce qui avec votre jeu d'essai donne ceci :

min(TS)	max(TS)	ec
2023-01-04 09:47:57	2023-01-04 09:57:25	568
2023-01-04 09:57:26	2023-01-04 10:17:39	1213
2023-01-04 10:17:40	2023-01-04 10:17:44	4

EDIT précisions :

• les fonctions fenêtrées telles que ROW_NUMBER que j'ai utilisée dans ma solution ne sont disponibles dans MARIADB que depuis la V10
• dans une base de données relationnelle, il n'y a pas de champs, il y a des colonnes.
  
  Les champs ce sont les zones des formulaires ou des états
• une colonne définie en varchar(1) est une hérésie, c'est plus long que du char(1) puisque le varchar ajoute 1 à 3 octets pour la longueur effective !
  Pour des zones char de petite taille, moins de 20 octets environ, préférez du char fixe

[skazu]

Oui merci c'est quelque chose comme cela, mais je suis sous MariaDB et cette syntaxe n'est pas reconnue par mon système.

Mais en créant la table T2 (ou une vue) au préalable, j'obtiens le résultat escompté.

Merci beaucoup car à la lecture de votre réponse, je constate que j'ai encore pas mal de progrès à faire en sql

Et bien sûr je vais tenir compte de vos remarques sur la structure de la table ce qui m'amène à poser une question supplémentaire.
Puis-je modifier les colonnes en char(1) ou est-il préférable de reconstruire la table ?

[binarygirl]

J'étais en train de tester ma propre implémentation:

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
SELECT min(ts) AS min_ts,
MAX(previous_ts) AS max_ts,
vout22, SUM(diff_seconds) AS total_seconds
 
FROM (
    SELECT ts, vout22,
    LAG(ts)        OVER w AS previous_ts,
    LEAD(ts)        OVER w AS next_ts,
    COALESCE(LAG(vout22)        OVER w, '1') AS previous_vout22,
    LEAD(vout22) OVER w AS next_vout22,
    COALESCE(TIMESTAMPDIFF(SECOND, LAG(ts) OVER w, ts), 0) as diff_seconds
    FROM samples
    WINDOW w AS (ORDER BY ts)
) r
GROUP BY next_vout22
ORDER BY min_ts, max_ts
;

J'arrive à des résultats un peu différents, mais sans doute que je prend en compte la mauvaise ligne pour déterminer le changement d'état.

Code :

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

1
2
3
4
min_ts 	max_ts 	vout22 	total_seconds
2023-01-04 09:47:57 	2023-01-04 10:17:46 	1 	576
2023-01-04 09:57:25 	2023-01-04 10:17:49 	1 	1217
2023-01-04 10:17:51 	2023-01-04 10:17:50 	0 	1

J'ai un dbfiddle ici

NB: je pense que ces fonctions nécessiteront aussi MariaDB V10 au moins.

[escartefigue]

Oui merci c'est quelque chose comme cela, mais je suis sous MariaDB et cette syntaxe n'est pas reconnue par mon système.
Mais en créant la table T2 (ou une vue) au préalable, j'obtiens le résultat escompté.

Pourtant j'ai testé sur MariaDB V10, curieux...

Merci beaucoup car à la lecture de votre réponse, je constate que j'ai encore pas mal de progrès à faire en sql

Le forum developpez.net pourra vous y aider

Et bien sûr je vais tenir compte de vos remarques sur la structure de la table ce qui m'amène à poser une question supplémentaire.
Puis-je modifier les colonnes en char(1) ou est-il préférable de reconstruire la table ?

Si on prend le problème par le petit bout de la lorgnette, oui c'est possible de remplacer un type varchar par un type char.

Mais la bonne démarche est de réfléchir en premier lieu au modèle conceptuel des données, puis une fois celui-ci validé, d'en dériver le modèle logique dans lequel on trouve les tables.
Par exemple, le fait de répéter plein de fois une colonne VOUTnn est symptomatique d'une modélisation mal faite, donc peu évolutive, présentant des redondances, dont l'intégrité n'est pas garantie et sur lequel les requêtes sont peu performantes.
Passer par l'étape de modélisation conceptuelle évite ce genre d'écueils.

[skazu]

Bonjour;

Je remercie binarygirl pour sa solution que je ne manquerai pas d'étudier.

Pourtant j'ai testé sur MariaDB V10, curieux...

Oui, je vous confirme que votre solution fonctionne sur MariaDB, c'est phpmyadmin qui signale une erreur sur la clause with. La requête fonctionne correctement en ligne de commande et c'est le principal.

Si on prend le problème par le petit bout de la lorgnette, oui c'est possible de remplacer un type varchar par un type char.

Mais la bonne démarche est de réfléchir en premier lieu au modèle conceptuel des données, puis une fois celui-ci validé, d'en dériver le modèle logique dans lequel on trouve les tables.
Par exemple, le fait de répéter plein de fois une colonne VOUTnn est symptomatique d'une modélisation mal faite, donc peu évolutive, présentant des redondances, dont l'intégrité n'est pas garantie et sur lequel les requêtes sont peu performantes.
Passer par l'étape de modélisation conceptuelle évite ce genre d'écueils.

Oui, je comprends, malheureusement pour moi, je n'ai pas suffisamment de compétences dans ce domaine pour avoir cette approche.

Cependant ces données sont issues d'un automate. il est capable de gérer 56 entrées physiques, 56 sorties physiques, 128 entrées virtuelles et 128 sorties virtuelles. A chaque changement d'état d'entrée ou de sortie, l'automate envoie les 56 ou 128 états sans distinction.

J'ai créé respectivement 4 tables afin de gérer ces changements d'état. Comme par exemple la table vout1 et ses 128 sorties virtuelles.
A chaque changement d'état d'une sortie virtuelle, l'état des 128 est inséré dans la table. Ce qui explique qu'il existe plusieurs lignes pour une sortie virtuelle ayant le même statut.

Mon approche fût la simplicité de mise en œuvre. L'automate détecte un changement, envoie un push qui déclenche une insertion des données dans la base. C'est simple et efficace.
Je suis conscient que simplicité ne rime pas forcément avec performance d'autant plus que toutes les entrées ou sorties ne sont pas utilisées mais ce projet est en constante évolution et c'est pour cette raison que j'ai choisie cette approche.

La phase 1 a été d'alimenter ces tables, la phase 2 est de faire des statistiques. A la lueur de vos commentaires, je note des problèmes de performance à venir mais voilà, n'ayant pas une expérience approfondie sur ce sujet, je considère ce projet comme un cas d'école qui me permet d'aborder de manière concrète les bases de données.

Je vous remercie pour l'aide que vous m'avez apporté et grâce à votre requête, j'ai matière à réflexion

[pier.antoine]

Bonsoir

Je ne sais comment se présentent les données issues de l'automate.
J'aurais tendance à prévoir qu'une seule table

Code :

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

1
2
3
4
5
6
7
8
CREATE TABLE `table_donnees` (
	`ts` DATETIME ,
	`Type_Donnees` CHAR(50)  COMMENT 'Entrée physique, sortie physique, entrée virtuelle, sortie virtuelle' COLLATE 'latin1_general_ci',
	`Numéro` INT(11)  COMMENT 'Numéro de l\'entrée ou de la sortie',
	`Etat` INT(11)  COMMENT '0 ou 1'
)
 
;

Pierre

[Artemus24]

Salut à tous.

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
--------------
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`       integer unsigned not null auto_increment primary key,
  `periode`  datetime         not null,
  `flag`     tinyint          not null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `test` (`periode`,`flag`) values
 ('2023-01-04 09:47:57', 1), ('2023-01-04 09:47:58', 1), ('2023-01-04 09:47:59', 1), ('2023-01-04 09:48:00', 1),
 ('2023-01-04 09:48:01', 1), ('2023-01-04 09:48:02', 1), ('2023-01-04 09:57:21', 1), ('2023-01-04 09:57:22', 1),
 ('2023-01-04 09:57:23', 1), ('2023-01-04 09:57:24', 1), ('2023-01-04 09:57:25', 1), ('2023-01-04 09:57:26', 0),
 ('2023-01-04 10:07:10', 0), ('2023-01-04 10:07:29', 0), ('2023-01-04 10:17:20', 0), ('2023-01-04 10:17:34', 0),
 ('2023-01-04 10:17:35', 0), ('2023-01-04 10:17:36', 0), ('2023-01-04 10:17:37', 0), ('2023-01-04 10:17:38', 0),
 ('2023-01-04 10:17:39', 0), ('2023-01-04 10:17:40', 1), ('2023-01-04 10:17:41', 1), ('2023-01-04 10:17:42', 1),
 ('2023-01-04 10:17:43', 1), ('2023-01-04 10:17:44', 1), ('2023-01-04 10:17:45', 1), ('2023-01-04 10:17:46', 1),
 ('2023-01-04 10:17:47', 1), ('2023-01-04 10:17:49', 1), ('2023-01-04 10:17:50', 0), ('2023-01-04 10:17:51', 0)
--------------
 
--------------
select * from `test`
--------------
 
+----+---------------------+------+
| id | periode             | flag |
+----+---------------------+------+
|  1 | 2023-01-04 09:47:57 |    1 |
|  2 | 2023-01-04 09:47:58 |    1 |
|  3 | 2023-01-04 09:47:59 |    1 |
|  4 | 2023-01-04 09:48:00 |    1 |
|  5 | 2023-01-04 09:48:01 |    1 |
|  6 | 2023-01-04 09:48:02 |    1 |
|  7 | 2023-01-04 09:57:21 |    1 |
|  8 | 2023-01-04 09:57:22 |    1 |
|  9 | 2023-01-04 09:57:23 |    1 |
| 10 | 2023-01-04 09:57:24 |    1 |
| 11 | 2023-01-04 09:57:25 |    1 |
| 12 | 2023-01-04 09:57:26 |    0 |
| 13 | 2023-01-04 10:07:10 |    0 |
| 14 | 2023-01-04 10:07:29 |    0 |
| 15 | 2023-01-04 10:17:20 |    0 |
| 16 | 2023-01-04 10:17:34 |    0 |
| 17 | 2023-01-04 10:17:35 |    0 |
| 18 | 2023-01-04 10:17:36 |    0 |
| 19 | 2023-01-04 10:17:37 |    0 |
| 20 | 2023-01-04 10:17:38 |    0 |
| 21 | 2023-01-04 10:17:39 |    0 |
| 22 | 2023-01-04 10:17:40 |    1 |
| 23 | 2023-01-04 10:17:41 |    1 |
| 24 | 2023-01-04 10:17:42 |    1 |
| 25 | 2023-01-04 10:17:43 |    1 |
| 26 | 2023-01-04 10:17:44 |    1 |
| 27 | 2023-01-04 10:17:45 |    1 |
| 28 | 2023-01-04 10:17:46 |    1 |
| 29 | 2023-01-04 10:17:47 |    1 |
| 30 | 2023-01-04 10:17:49 |    1 |
| 31 | 2023-01-04 10:17:50 |    0 |
| 32 | 2023-01-04 10:17:51 |    0 |
+----+---------------------+------+
--------------
select *, timediff(t2_periode, t1_periode) as duree
  from (      select any_value(t1.id)       as t1_id,
                     any_value(t1.periode)  as t1_periode,
                     any_value(t1.flag)     as t1_flag,
                               t2.id        as t2_id,
                               t2.periode   as t2_periode,
                               t2.flag      as t2_flag
 
                from `test` as t1
 
          inner join `test` as t2
                  on t2.id = (select min(id) from `test` where periode >= t1.periode and flag != t1.flag)
 
            group by t2.id, t2.periode, t2.flag
       ) as x
--------------
 
+-------+---------------------+---------+-------+---------------------+---------+----------+
| t1_id | t1_periode          | t1_flag | t2_id | t2_periode          | t2_flag | duree    |
+-------+---------------------+---------+-------+---------------------+---------+----------+
|     1 | 2023-01-04 09:47:57 |       1 |    12 | 2023-01-04 09:57:26 |       0 | 00:09:29 |
|    12 | 2023-01-04 09:57:26 |       0 |    22 | 2023-01-04 10:17:40 |       1 | 00:20:14 |
|    22 | 2023-01-04 10:17:40 |       1 |    31 | 2023-01-04 10:17:50 |       0 | 00:00:10 |
+-------+---------------------+---------+-------+---------------------+---------+----------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

Cordialement.
Artemus24.
@+

[skazu]

Bonsoir à tous,

Je collecte les données depuis environ 3 mois. Ci-dessous le volume des tables.



Est-ce que ce volume de données vous paraît adapté à une seule table ?

A savoir que pour les entrées et sorties physiques il y a 56 colonnes et 128 pour les entrées et sorties virtuelles.

Qu'en pensez-vous ?

Cdt
patrick

[Artemus24]

Salut Skazu.

Je t'ai donné la solution à ton problème. L'as-tu regardé ?

Le problème est plutôt la modélisation de ta base de données, ainsi que l'optimisation des accès.
Ce que tu nous donnes est une petite volumétrie.

Cordialement.
Artemus24.
@+

[skazu]

Salut Artemus24,

Je t'ai donné la solution à ton problème. L'as-tu regardé ?

Oui, je l'ai regardée, j'ai créé la table mais je n'arrive pas à exécuter la requête j'ai une erreur de syntaxe.

cdt
patrick

[skazu]

Bonjour escartefigue,

Je vous sollicite une nouvelle fois car après avoir étudié votre requête et je pense l'avoir comprise : vous comptez le nombre de lignes tant que le flag d'état est à une valeur, puis vous faîtes la différence entre la date min et max.

çà fonctionne très bien si la table contient plusieurs lignes contiguës avec la même valeur de flag.

Si il n'y a qu'une seule ligne çà ne marche plus.

çà marche dans ce cas :

| 2023-01-04 09:47:57 | 0
| 2023-01-04 09:47:58 | 1
| 2023-01-04 09:47:59 | 1
| 2023-01-04 09:48:00 | 1
| 2023-01-04 09:48:01 | 1
| 2023-01-04 09:48:02 | 0

mais pas dans celui là :

| 2023-01-04 09:47:57 | 0
| 2023-01-04 09:47:58 | 1
| 2023-01-04 09:47:59 | 0

Qu'en pensez-vous ?

cdt

[Artemus24]

Salut skazu.

Je vous ai donné la solution et elle fonctionne dans n'importe quelle version.
Elle fonctionne avec une seule ligne comme dans votre dernier exemple.

Vous dites avoir un problème de syntaxe mais vous ne nous avez pas donné la requête en entier.
Si vous désirez que l'on vous aide, donnez nous la requête en entier.

Vous dites être sous MariaDB, mais dans quelle version ?

Cordialement.
Artemus24.
@+

[skazu]

Bonjour Artemus24,

J'ai reproduit votre exemple test à savoir :

Structure de la table :

J'ai lancé la commande insert telle quelle.

Code :

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

1
2
3
4
5
6
7
8
9
INSERT INTO `test` (`periode`,`flag`) values
 ('2023-01-04 09:47:57', 1), ('2023-01-04 09:47:58', 1), ('2023-01-04 09:47:59', 1), ('2023-01-04 09:48:00', 1),
 ('2023-01-04 09:48:01', 1), ('2023-01-04 09:48:02', 1), ('2023-01-04 09:57:21', 1), ('2023-01-04 09:57:22', 1),
 ('2023-01-04 09:57:23', 1), ('2023-01-04 09:57:24', 1), ('2023-01-04 09:57:25', 1), ('2023-01-04 09:57:26', 0),
 ('2023-01-04 10:07:10', 0), ('2023-01-04 10:07:29', 0), ('2023-01-04 10:17:20', 0), ('2023-01-04 10:17:34', 0),
 ('2023-01-04 10:17:35', 0), ('2023-01-04 10:17:36', 0), ('2023-01-04 10:17:37', 0), ('2023-01-04 10:17:38', 0),
 ('2023-01-04 10:17:39', 0), ('2023-01-04 10:17:40', 1), ('2023-01-04 10:17:41', 1), ('2023-01-04 10:17:42', 1),
 ('2023-01-04 10:17:43', 1), ('2023-01-04 10:17:44', 1), ('2023-01-04 10:17:45', 1), ('2023-01-04 10:17:46', 1),
 ('2023-01-04 10:17:47', 1), ('2023-01-04 10:17:49', 1), ('2023-01-04 10:17:50', 0), ('2023-01-04 10:17:51', 0)

Puis la requête select, qui malheureusement retourne une erreur de syntaxe.

La version de MariaDB que j'utilise :

MariaDB [(none)]>  show variables like '%version%';
+-----------------------------------+------------------------------------------+
| Variable_name                     | Value                                    |
+-----------------------------------+------------------------------------------+
| in_predicate_conversion_threshold | 1000                                     |
| innodb_version                    | 10.5.15                                  |
| protocol_version                  | 10                                       |
| slave_type_conversions            |                                          |
| system_versioning_alter_history   | ERROR                                    |
| system_versioning_asof            | DEFAULT                                  |
| tls_version                       | TLSv1.1,TLSv1.2,TLSv1.3                  |
| version                           | 10.5.15-MariaDB-0+deb11u1-log            |
| version_comment                   | Raspbian 11                              |
| version_compile_machine           | armv8l                                   |
| version_compile_os                | debian-linux-gnueabihf                   |
| version_malloc_library            | system                                   |
| version_source_revision           | 9aa3564e8a06c3d2027fc514213ecf42b049b06e |
| version_ssl_library               | OpenSSL 1.1.1n  15 Mar 2022              |
| wsrep_patch_version               | wsrep_26.22                              |
+-----------------------------------+------------------------------------------+

Cordialement
Patrick

[Artemus24]

Salut skazu.

Vous n'avez pas beaucoup cherché pour résoudre l'erreur de syntaxe.

Mon exemple a été testé sous mysql version 8.0.29.

Je viens de le tester sous mariadb version 10.5.8.
La différence est que mariadb ne supporte pas les any_value().
Vous les remplacez par min() et vous aurez une requête opérationnelle.

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
--------------
START TRANSACTION
--------------
 
--------------
show variables like '%version%'
--------------
 
+-----------------------------------+------------------------------------------+
| Variable_name                     | Value                                    |
+-----------------------------------+------------------------------------------+
| in_predicate_conversion_threshold | 1000                                     |
| innodb_version                    | 10.5.8                                   |
| protocol_version                  | 10                                       |
| slave_type_conversions            |                                          |
| system_versioning_alter_history   | ERROR                                    |
| system_versioning_asof            | DEFAULT                                  |
| tls_version                       | TLSv1.1,TLSv1.2,TLSv1.3                  |
| version                           | 10.5.8-MariaDB                           |
| version_comment                   | mariadb.org binary distribution          |
| version_compile_machine           | x64                                      |
| version_compile_os                | Win64                                    |
| version_malloc_library            | system                                   |
| version_source_revision           | 7da6353b1558adce73320c803f0413c9bbd81185 |
| version_ssl_library               | WolfSSL 4.4.0                            |
+-----------------------------------+------------------------------------------+
--------------
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`       integer unsigned not null auto_increment primary key,
  `periode`  datetime         not null,
  `flag`     tinyint          not null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `test` (`periode`,`flag`) values
 ('2023-01-04 09:47:57', 1), ('2023-01-04 09:47:58', 1), ('2023-01-04 09:47:59', 1), ('2023-01-04 09:48:00', 1),
 ('2023-01-04 09:48:01', 1), ('2023-01-04 09:48:02', 1), ('2023-01-04 09:57:21', 1), ('2023-01-04 09:57:22', 1),
 ('2023-01-04 09:57:23', 1), ('2023-01-04 09:57:24', 1), ('2023-01-04 09:57:25', 1), ('2023-01-04 09:57:26', 0),
 ('2023-01-04 10:07:10', 0), ('2023-01-04 10:07:29', 0), ('2023-01-04 10:17:20', 0), ('2023-01-04 10:17:34', 0),
 ('2023-01-04 10:17:35', 0), ('2023-01-04 10:17:36', 0), ('2023-01-04 10:17:37', 0), ('2023-01-04 10:17:38', 0),
 ('2023-01-04 10:17:39', 0), ('2023-01-04 10:17:40', 1), ('2023-01-04 10:17:41', 1), ('2023-01-04 10:17:42', 1),
 ('2023-01-04 10:17:43', 1), ('2023-01-04 10:17:44', 1), ('2023-01-04 10:17:45', 1), ('2023-01-04 10:17:46', 1),
 ('2023-01-04 10:17:47', 1), ('2023-01-04 10:17:49', 1), ('2023-01-04 10:17:50', 0), ('2023-01-04 10:17:51', 0),
 ('2023-01-04 10:17:53', 1), ('2023-01-04 10:17:55', 0)
--------------
 
--------------
select * from `test`
--------------
 
+----+---------------------+------+
| id | periode             | flag |
+----+---------------------+------+
|  1 | 2023-01-04 09:47:57 |    1 |
|  2 | 2023-01-04 09:47:58 |    1 |
|  3 | 2023-01-04 09:47:59 |    1 |
|  4 | 2023-01-04 09:48:00 |    1 |
|  5 | 2023-01-04 09:48:01 |    1 |
|  6 | 2023-01-04 09:48:02 |    1 |
|  7 | 2023-01-04 09:57:21 |    1 |
|  8 | 2023-01-04 09:57:22 |    1 |
|  9 | 2023-01-04 09:57:23 |    1 |
| 10 | 2023-01-04 09:57:24 |    1 |
| 11 | 2023-01-04 09:57:25 |    1 |
| 12 | 2023-01-04 09:57:26 |    0 |
| 13 | 2023-01-04 10:07:10 |    0 |
| 14 | 2023-01-04 10:07:29 |    0 |
| 15 | 2023-01-04 10:17:20 |    0 |
| 16 | 2023-01-04 10:17:34 |    0 |
| 17 | 2023-01-04 10:17:35 |    0 |
| 18 | 2023-01-04 10:17:36 |    0 |
| 19 | 2023-01-04 10:17:37 |    0 |
| 20 | 2023-01-04 10:17:38 |    0 |
| 21 | 2023-01-04 10:17:39 |    0 |
| 22 | 2023-01-04 10:17:40 |    1 |
| 23 | 2023-01-04 10:17:41 |    1 |
| 24 | 2023-01-04 10:17:42 |    1 |
| 25 | 2023-01-04 10:17:43 |    1 |
| 26 | 2023-01-04 10:17:44 |    1 |
| 27 | 2023-01-04 10:17:45 |    1 |
| 28 | 2023-01-04 10:17:46 |    1 |
| 29 | 2023-01-04 10:17:47 |    1 |
| 30 | 2023-01-04 10:17:49 |    1 |
| 31 | 2023-01-04 10:17:50 |    0 |
| 32 | 2023-01-04 10:17:51 |    0 |
| 33 | 2023-01-04 10:17:53 |    1 |
| 34 | 2023-01-04 10:17:55 |    0 |
+----+---------------------+------+
--------------
select *, timediff(t2_periode, t1_periode) as duree
  from (      select min(t1.id)            as t1_id,
                     min(t1.periode)       as t1_periode,
                     min(t1.flag)          as t1_flag,
                         t2.id             as t2_id,
                         t2.periode        as t2_periode,
                         t2.flag           as t2_flag
 
                from `test` as t1
 
          inner join `test` as t2
                  on t2.id = (select min(id) from `test` where periode >= t1.periode and flag != t1.flag)
 
            group by t2.id, t2.periode, t2.flag
       ) as x
--------------
 
+-------+---------------------+---------+-------+---------------------+---------+----------+
| t1_id | t1_periode          | t1_flag | t2_id | t2_periode          | t2_flag | duree    |
+-------+---------------------+---------+-------+---------------------+---------+----------+
|     1 | 2023-01-04 09:47:57 |       1 |    12 | 2023-01-04 09:57:26 |       0 | 00:09:29 |
|    12 | 2023-01-04 09:57:26 |       0 |    22 | 2023-01-04 10:17:40 |       1 | 00:20:14 |
|    22 | 2023-01-04 10:17:40 |       1 |    31 | 2023-01-04 10:17:50 |       0 | 00:00:10 |
|    31 | 2023-01-04 10:17:50 |       0 |    33 | 2023-01-04 10:17:53 |       1 | 00:00:03 |
|    33 | 2023-01-04 10:17:53 |       1 |    34 | 2023-01-04 10:17:55 |       0 | 00:00:02 |
+-------+---------------------+---------+-------+---------------------+---------+----------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

@+

[skazu]

Bonjour,

Je vous remercie pour votre réponse et je vous prie de pardonner mon incompétence car je n'aurai pas été capable de trouver l'erreur de syntaxe.

Cependant, au vu des volumétries annoncées dans mes précédents messages, l'exécution de la requête est trop longue. Plus de 20s pour la table test avec un total de 3000 lignes. A ce jour la table en production a plus de 60000 lignes.

Je vous remercie encore pour le temps que vous avez consacré à mon problème.

cdt

[skazu]

Bonjour à tous,

Aujourd'hui journée plus que maussade et encline à cogiter, n'en déplaise à Artemus24 j'ai fini par trouver une solution à mon problème.

Sur la base de la requête fournie par escartefigue ci-dessous le code modifié.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
with T2 as (
        select T1.periode, T1.flag,
                row_number() over (order by T1.periode) - row_number() over (partition by T1.flag order by T1.periode) as X
        from IPX_test.test as T1)
        select
                flag,
                min(periode) as date_start,
                nvl(lead(periode) over (order by periode), max(periode)) as date_end,
                sec_to_time(timestampdiff(second, min(periode), nvl(lead(periode) over (order by periode), max(periode)))) as elapse
        from T2
        group by X
        order by 2

Elle fonctionne dans tous les cas, le nvl ne sert pas à grand chose si ce n'est à avoir une valeur pour la dernière ligne.

Je vous renouvelle mes remerciements pour m'avoir aidé.

cdt