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 ?

[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.

[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.