Garder uniquement une entrée par heure [Résolu]

[HpXtech]

Bonjour à tous,
Ceci est mon premier message sur ce forum, j'espère ne pas vous décourager en postant cet énorme pavé, mais il me semble important d'expliquer en détail le problème pour pouvoir le résoudre...

Dans le cadre d'un projet de monitoring très spécifique, je recherche une solution viable pour limiter le nombre d'entrées dans ma base de données.

Le contexte est le suivant :
Je reçoit des données de plusieurs centaines d'automates toutes les 5 minutes sous la forme de fichiers XML. J'effectue un traitement procédural pour stocker toutes les données dans une table qui contient un minimum d'INDEX, pour ne pas diminuer les temps d'écritures.
Malheureusement, les données qui m'intéressent ne sont pas les données stockées toutes les 5 minutes, mais seulement les données stockées à l'instant T chaque heure.

Exemple :
Je reçoit 10 enregistrements d'un automate de 8h à 9h, je ne veux garder que le premier enregistrement dans l'heure (celui aux alentours de 8h/8h05), et supprimer les autres;
Par contre, je ne peux en aucun cas faire mon tri sur les minutes, puisque les automates m'envoient les données à des moments différents (8h01/8h06 s'il est en retard, etc...).

La requête que j'ai trouvée permettant de faire cela est :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
DELETE FROM `webbox`
                WHERE uid NOT IN (
                     SELECT uid 
                     FROM (
                            SELECT *
                            FROM (SELECT * FROM `webbox` ORDER BY timestamp ASC) AS wb
                            GROUP BY wb_serial,type,seriennummer,
                            YEAR(`timestamp`),
                            MONTH(`timestamp`),
                            DAY(`timestamp`),
                            HOUR(`timestamp`)
                            ORDER BY wb_serial,type,seriennummer ASC, `timestamp` ASC
                      ) foo
                )

Dans la requête je fait deux select, c'est pour tromper MySQL et lui renvoyer un DELETE avec une liste d'uid à supprimer.
En clair la requête vérifie que pour chaque enregistrement, les champs obligatoires existent au moins une fois (wb_serial + type + seriennummer ) et tout cela à chaque plage horaire.

Je me confronte à deux problèmes :
1 / Si j'exécute la requète plusieurs fois, il trouve toujours des enregistrement à enlever jusqu'à un certain point!?
Exemple (uniquement sur le SELECT après avoir exécuté la requête):
- 1ere fois : 1700 résultats
- 2eme fois : 1600 résultats
- 3eme fois : 1500 résultats
..
- 10 eme fois : 900 résultats
- 11 eme fois : 900 résultats
2 / Vu que le problème est récurrent en ligne, et ne se présente jamais en local, j'en vient à penser que cela puisse venir de ma base en ligne ?

Config en ligne :

Code :

1
2
3
4
5
protocol_version 	10
version 	5.1.49-3-log
version_comment 	(Debian)
version_compile_machine 	x86_64
version_compile_os 	debian-linux-gnu

Config locale :

Code :

1
2
3
4
5
protocol_version 	10
version 	5.1.30-community-log
version_comment 	MySQL Community Server (GPL)
version_compile_machine 	ia32
version_compile_os 	Win32

Vu que j'ai l'impression d'être dans l'impasse, si vous pensez qu'il existe une autre requête qui effectue la même chose plus simplement, je suis preneur.

[skuatamad]

Quelques questions :
1/ Pourquoi insérer les lignes si c'est pour les supprimer.
2/ Qu'entends tu par :

c'est pour tromper MySQL

3/ Le GROUP BY est faux il faudrait une fonction d'aggrégation non ?
Que donne :

Code :

1
2
3
4
5
6
7
8
9
DELETE FROM webbox
 WHERE uid NOT IN (SELECT min(uid)
                     FROM webbox
                    GROUP BY wb_serial,type,seriennummer,
                             YEAR(timestamp),
                             MONTH(timestamp),
                             DAY(timestamp),
                             HOUR(timestamp)
                  )

[HpXtech]

@skuatamad
Merci de ta réponse,

1/ Réflexion pertinente, j'en conviens, cela peut paraître surprenant. J'ai bien sur pensé à filtrer en amont les enregistrements faits dans ma base de données, mais ce n'est pas simple à incorporer dans le process du traitement (ZIP => XMLs => BDD et j'en passe).
Par ailleurs, il faut que je m'assure que le premier enregistrement dans l'heure n'est pas une valeur de minute supérieur à 5 (ex:12:06:10).
Mais, c'est aussi une piste que j'envisage.

EDIT : Malheureusement, après une batterie de tests, je confirme:
Les automates ne m'envoient pas systématiquement un enregistrement entre 12:00 et 12:15 par exemple. Le cas échéant, si une valeur m'est envoyée à 12:16, celle-ci devient la première valeur de l'heure en cours, et je doit pouvoir l'utiliser.



2/ J'entendais juste par la que c'était le seul moyen de faire un DELETE avec un SELECT, ni plus ni moins.

3/ Cette requête ne renvoie pas ce qu'il faut. Dans ce que tu me propose (au passage, je te remercie de prendre du temps pour te pencher sur le problème), on fait un SELECT par rapport à l'uid min. Dans mon cas, l'uid n'a pas vraiment de rapport avec le premier enregistrement dans l'heure.

Je vais tenter de montrer un exemple simple de la table que j'ai :

Code :

1
2
3
4
5
6
7
8
9
 
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+     uid     +          timestamp         +     seriennummer  +
+    12349    +    '2011-10-20 12:06:24'   +     xy987654321   +
+    12348    +    '2011-10-20 12:01:24'   +     xy987654321   +
+    12347    +    '2011-10-20 12:10:15'   +     xy123456789   +
+    12346    +    '2011-10-20 12:05:15'   +     xy123456789   +
+    12345    +    '2011-10-20 12:00:15'   +     xy123456789   +
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

après traitement j'aimerais avoir tout simplement :

Code :

1
2
3
4
5
6
 
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+     uid     +          timestamp         +     seriennummer  +
+    12348    +    '2011-10-20 12:01:24'   +     xy987654321   +
+    12345    +    '2011-10-20 12:00:15'   +     xy123456789   +
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Uniquement les premiers enregistrements de chaque heure de chaque automate.

[GyZmoO]

Salut.

As-tu pensé à utiliser un trigger "BEFORE INSERT" sur ta table ?

Dans ce trigger tu pourrais faire :

if (premier résultat pour cette heure)
then insertion
else rien

[CinePhil]

Quel est le premier timestamp par heure pour chaque automate ?

Code :

1
2
3
4
5
6
SELECT seriennummer, 
	DATE(`timestamp`) AS jour,
	EXTRACT(HOUR FROM `timestamp`) AS heure,
	MIN(`timestamp`) AS premier_timestamp
FROM la_table
GROUP BY seriennummer, DATE(`timestamp`), EXTRACT(HOUR FROM `timestamp`)

On récupère les données correspondant à ce premier timestamp :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT t1.uid, t1.`timestamp`, t1.serienummer
FROM la_table t1
INNER JOIN
(
	SELECT seriennummer, 
		DATE(`timestamp`) AS jour,
		EXTRACT(HOUR FROM `timestamp`) AS heure,
		MIN(`timestamp`) AS premier_timestamp
	FROM la_table
	GROUP BY seriennummer, DATE(`timestamp`), EXTRACT(HOUR FROM `timestamp`)
) tmp 
	ON tmp.serienummer = t1.serienummer
	AND tmp.premier_timestamp = t1.`timestamp`

Au passage, timestamp est un mot réservé du langage SQL ; tu ne devrais pas appeler une colonne ainsi.

[Maljuna Kris]

Saluton,
La solution du trigger de GyZmoO mérite qu'on s'y attarde, non ?

[CinePhil]

J'ai cru comprendre, à la lecture du premier message de HpXtech, que son processus récupère de toute façon toutes les données dans une table. Ma requête permet d'extraire de cette table seulement les lignes qui l'intéresse afin d'alimenter la vraie table de données à exploiter.

Mais s'il s'agit d'améliorer le processus d'importation des données pour n'alimenter MySQL qu'avec les données finales alors peut-être que le trigger est meilleur.

Tout dépend comment se déroule le processus d'importation des données.
Si c'est au coup par coup, même avec un flux de données très rapide en provenance des automates, le trigger est probablement meilleur puisqu'on traite les données au fur et à mesure qu'elles arrivent.
Si par contre c'est une importation en masse des données des automates à périodes régulières, il vaut mieux je pense importer en masse dans une table d'importation de MySQL à l'aide par exemple d'une requête LOAD DATA INFILE puis utiliser ma requête pour extraire les données intéressantes.

[HpXtech]

Merci pour toutes vos réponses.

Je retiens effectivement de nombreuses remarques pertinentes :
- la colonne "timestamp" est à renommer pour éviter toute ambiguïté avec TIMESTAMP.
- Les requêtes de CinePhil fonctionnent, tout comme celle que j'utilisais au départ mais semble plus rapide avec les quelques tests que j'ai pu faire.

Je ne suis pas familier avec le système de triggers, mais il semble que ce soit dans mon cas la meilleure solution.
L'idée de départ :
Si la combinaison "wb_serial", "timestamp", "type", "seriennummer", annee, mois, jour, heure n'existe pas dans la base de donées, par rapport à l'enregistrement en cours,
alors on insère une ligne.

Je vais faire quelques recherches pour trouver la syntaxe exacte.

EDIT : après quelques recherches...mon premier trigger
Je suis parti du principe qu'il fallait comparer la ligne qu'on veut insérer (ROW ?) avec ce que j'ai dans la base de données. Si on trouve un enregistrement identique, on fait rien, sinon on ajoute l'enregistrement.
Mais quelque chose m'échappe...
Le trigger c'est un "déclencheur", donc je le crée et après j'y touche plus?
J'imagine qu'il faut juste autoriser l'enregistrement dans le ELSE, mais comment faire ?

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CREATE TRIGGER checkInsertExists BEFORE INSERT ON webbox
FOR EACH ROW
BEGIN
     IF 
       (SELECT uid
        FROM webbox 
        WHERE wb_serial = ROW.wb_serial
        AND `timestamp`= ROW.timestamp
        AND `type`= ROW.type
        AND `seriennummer` = ROW.seriennummer
        AND YEAR(`timestamp`) = YEAR(ROW.timestamp)
        AND MONTH(`timestamp`) = MONTH(ROW.timestamp)
        AND DAY(`timestamp`) = DAY(ROW.timestamp)
        AND HOUR(`timestamp`) = HOUR(ROW.timestamp)
       )
     THEN // Rien ?
     ELSE
         INSERT INTO ' . $table_name . ' (' . $fields_list . ') VALUES (' . $values_list . ')
     END IF;
 END

[Maljuna Kris]

Inverse la condition IF NOT EXISTS et ne mets pas de ELSE.

[HpXtech]

Ok!
Soit le code suivant :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
CREATE TRIGGER checkInsertExists BEFORE INSERT ON webbox
FOR EACH ROW
BEGIN
     IF NOT EXISTS
       (SELECT uid
        FROM webbox 
        WHERE wb_serial = ROW.wb_serial
        AND `timestamp`= ROW.timestamp
        AND `type`= ROW.type
        AND `seriennummer` = ROW.seriennummer
        AND YEAR(`timestamp`) = YEAR(ROW.timestamp)
        AND MONTH(`timestamp`) = MONTH(ROW.timestamp)
        AND DAY(`timestamp`) = DAY(ROW.timestamp)
        AND HOUR(`timestamp`) = HOUR(ROW.timestamp)
       )
     THEN
     END IF;
 END

Mais du coup dans le THEN je met quoi ?
Parce qu'en lisant la doc, je me suis aperçu qu'un TRIGGER on le pré-paramètre avant de lancer les requêtes. Donc j'imagine qu'il faut dire dans le THEN "Autorises l'insertion" ou "Insères dans `webbox` les enregistrements en cours", mais comment ?

[HpXtech]

Bonjour à tous,
Après de pas mal de recherches je pense avoir trouvé la syntaxe pour créer mon trigger, mais mysql me renvoi mon message d'erreur préféré :
You have an error in your SQL syntax; [...] near ' ' at line 50.

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
CREATE TRIGGER checkInsertExists BEFORE INSERT ON webbox
FOR EACH ROW
BEGIN 
	IF NOT EXISTS (
     	SELECT uid 
        FROM webbox 
        WHERE wb_serial = NEW.wb_serial 
        AND `timestamp`= NEW.timestamp 
        AND `type`= NEW.type 
        AND `seriennummer` = NEW.seriennummer 
        AND YEAR(`timestamp`) = YEAR(NEW.`timestamp`) 
        AND MONTH(`timestamp`) = MONTH(NEW.`timestamp`) 
        AND DAY(`timestamp`) = DAY(NEW.`timestamp`) 
        AND HOUR(`timestamp`) = HOUR(`NEW.timestamp`)
        ) 
	THEN
        INSERT INTO `webbox`
        VALUES (
            NEW.`date_ajout`,
            NEW.`type`,
            NEW.`timestamp`,
            NEW.`wb_serial`,
            NEW.`e-total`,
            NEW.`event-cnt`,
            NEW.`fac`,
            NEW.`fehler`,
            NEW.`h-on`,
            NEW.`h-total`,
            NEW.`iac-ist`,
            NEW.`gridms.a.phsa`,
            NEW.`gridms.a.phsb`,
            NEW.`gridms.a.phsc`,
            NEW.`ipv`,
            NEW.`a.ms.amp`,
            NEW.`b.ms.amp`,
            NEW.`pac`,
            NEW.`riso`,
            NEW.`seriennummer`,
            NEW.`status`,
            NEW.`uac`,
            NEW.`gridms.phv.phsa`,
            NEW.`gridms.phv.phsb`,
            NEW.`gridms.phv.phsc`,
            NEW.`upv-ist`,
            NEW.`a.ms.vol`,
            NEW.`b.ms.vol`,
            NEW.`intsolirr`,
            NEW.`tmpamb c`,
            NEW.`tmpmdul c`,
            NEW.`windvel km/h`);
	END IF;
END

Toutes les autres syntaxes essayées se sont soldés par des erreurs SQL bien plus précises ("near 'END IF'" quand j'oubliait ";", etc...), mais là je n'arrive pas à comprendre mon erreur. Visiblement, le contenu de mon THEN ne lui plait pas...

[HpXtech]

Bonjour,

Je n'ai toujours pas réussi à résoudre ce problème de syntaxe au niveau du trigger, même si ça semble être LA solution idéale.
Dans tous les cas les réponses qui m'ont été apportées concernant la suppression des lignes pour chaque heure marchent nickel, et cela résout en grande partie mon problème.

Merci à vous, je passe le sujet en résolu.