Discussion :

[calou_33]

Bonjour,

J'ai la table suivante :

ID DEBUT CODE
-----------------------------------------------------------------
439559 01/04/2010 07:01:45 10
439558 01/04/2010 07:02:33 0
439533 01/04/2010 07:05:49 10
439532 01/04/2010 07:06:37 0
439557 01/04/2010 07:17:33 10
439556 01/04/2010 07:18:22 0

Je voudrais connaitre la durée et l'occurence des evenements 10 par exemple:

Durée= (07:02:33-07:01:45)+(07:06:37-07:05:49)+(07:18:22-07:17:33)
Occurence=3

Comment pourrais je faire ca en SQL?

Merci

[SergioMaster]

Occurrence :

Code :

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

SELECT COUNT(*) FROM TABLE WHERE CODE='10'

pour la durée cela semble beaucoup plus compliquée , la structure de la table est loin d'être idéale

[calou_33]

Bonjour SergioMaster,

Pour l'occurence pas de pb mais je suis aussi bloqué sur la durée

Amitiés

[makowski]

voilà le début de la solution :

Code :

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

SELECT COUNT(*),MIN(DEBUT),MAX(DEBUT) FROM TABLE WHERE CODE='10'

je te laisse deviner la suite et te laisse adapter selon que tu veux le résultat en jours, en mois, en heures, en minutes ou en secondes

[calou_33]

Bonjour Philippe,


Je ne comprends pas trop comment je peux calculer la durée des evements '10' comme celà

Si tu pouvais m'en dire plus?

Merci

[makowski]

qu'exprime tu dans le champs DEBUT ?

[calou_33]

Le champ DEBUT représente la date et l'heure de l'apparition d'un CODE X
Si on prend cet exemple :
439559 01/04/2010 07:01:45 10
439558 01/04/2010 07:02:33 0

Le code 10 est apparu le 1er avril 2010 à 7h01'45'' et il a disparu le meme jour à 7h02 et 33".
Il pourra réaparaitre à 11h etc..

[makowski]

et c'est quoi qui caractérise une apparition ou une disparition ?
et c'est quoi du coup la "durée" d'un événement ?

c'est pas très clair pour moi ce que tu stocke, comment tu le caractérise en ce que tu veux comptabiliser

[calou_33]

Et bien justement. La durée n'est pas comptabilisée
Je voulais savoir si c'était possible de l'extraire en SQL telle que la table était faite sachant qu'on connait l'heure d'apparition et l'heure de disparition du code.

La durée est donc la somme des (heures de disparition - les heures d'apparitions) comme le montre mon 1er exemple.
Avec :
heure de disparition=1er enregistrement chronologique qui suit un enregistrement où CODE=:code (10 dans l'exemple)
Heure d'apparition=Enregistrement avec CODE=:code (10 dans l'exemple)

Merci

[makowski]

mais qu'est ce qui me garanti que c'est une apparition ou une disparition ?

[calou_33]

l'appartion c'est lorsque le champ code vaut :code
Le 1er code qui suivra chronologiquement parlant sera forcement sa disparition.

Si on fait un

Code :

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

select DEBUT as deb from table where CODE=:code order by DEBUT asc

on aura toutes les apparitions

pour avoir les heures de disparition il faut sélectionner le 1er enregistrement pour chaque deb de la requete précédente où DEBUT>:deb en classant en asc

[makowski]

l'appartion c'est lorsque le champ code vaut :code
Le 1er code qui suivra chronologiquement parlant sera forcement sa disparition.

Si on fait un

Code :

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

select DEBUT as deb from table where CODE=:code order by DEBUT asc

on aura toutes les apparitions

heu ...
d'après ce que tu dis, non, on aura toutes les apparitions et toutes les disparitions !
c'est un peu bilboque ta structure non ?

qu'est ce qui me prouve que la date la plus ancienne est bien une apparition ?
cela pourrait tout aussi bien être une disparition

je ne comprend pas ton truc, c'est quoi le contexte ?

sinon donne un script de creation de table et de population des données
et donne aussi une idée du volume potentiel de la table
parce que peut être qu'on peut faire qq chose avec les CTE recursives, mais il y a la limite du nombre d'itérations

[SergioMaster]

la structure de la table est loin d'être idéale

c'est un peu bilboque ta structure non ?

on ne sait pas a priori si le code 0 correspond au début d'un évènement de code 10

il faudrait revoir ça

[calou_33]

Pour le contexte, c'est un automate qui enregistre des evenements qu'il voit passer.
Il a un capteur qui horodate ce qu'il voit sur les entrées.
L'arrivée d'une info sur son entrée annule l'information précédente
Si on prend cet exemple :

ID DEBUT CODE
-----------------------------------------------------------------
439559 01/04/2010 07:01:45 10 => 1ere apparition du code 10 sur la plage de selection
439558 01/04/2010 07:02:33 15=>Apparition du code 15 qui implique une dispartion du code 10. Le code 10 a donc été vu par l'automate 07:02:33-07:01:45 10
439533 01/04/2010 07:05:49 12 =>Apparition du code 12 et disparition du 15
439532 01/04/2010 07:06:37 10 =>Apparition du code 10 et disparition du 12
439557 01/04/2010 07:17:33 11 =>Apparition du code 11 et disparition du code 10. Il a donc été vu par l'automate 07:17:33 - 07:06:37
439556 01/04/2010 07:18:22 0=>Apparition du code 0 et disparition du 11

Le code 10 (dans cet exemple) aura donc duré (ou aura été vu par l'automate)
(07:17:33 - 07:06:37)+(07:02:33-07:01:45 10)

Et il faut pouvoir faire ce calcul pour tous les CODES qui ont eu lieu sur la plage de date définie par l'utilisateur

Merci

[makowski]

je vois mieux
donne un script de creation de table et de population des données
avec un volume réaliste

[calou_33]

Bonjour,

je vois mieux
donne un script de creation de table et de population des données
avec un volume réaliste

Je l'ai envoyé sur l'adresse firebird-fr..org

Merci

[makowski]

avec la vraie table

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
WITH CTEA (DEBUT,CODE_STATUS, REF_TURBINE_VALOREM) AS
(select DEBUT_STATUS,CODE_STATUS, REF_TURBINE_VALOREM
 from STATUS 
 where CODE_STATUS='10' 
 order by REF_TURBINE_VALOREM,DEBUT_STATUS ASC
),
CTEB (CODE_STATUS, REF_TURBINE_VALOREM,DEBUT,FIN) AS
(SELECT STATUS.CODE_STATUS,STATUS.REF_TURBINE_VALOREM,CTEA.DEBUT,MIN(STATUS.DEBUT_STATUS)
 FROM STATUS
 JOIN CTEA ON CTEA.CODE_STATUS = STATUS.CODE_STATUS
  AND CTEA.REF_TURBINE_VALOREM = STATUS.REF_TURBINE_VALOREM
  AND STATUS.DEBUT_STATUS > CTEA.DEBUT
  GROUP BY 1,2,3                           
),
CTEC (CODE_STATUS, REF_TURBINE_VALOREM,DUREE ) AS
(SELECT CODE_STATUS, REF_TURBINE_VALOREM, DATEDIFF(SECOND,DEBUT,FIN)
 FROM CTEB
)
SELECT CODE_STATUS, REF_TURBINE_VALOREM,COUNT(*) OCCURENCE,SUM(DUREE) DUREE_EN_SECONDE
 FROM CTEC
 GROUP BY 1,2

à vérifier

[calou_33]

Merci philippe

Je regarde ca attentivement