Suppression de doublons [Résolu]

[nenex73]

Bonjour à tous,

Dans une table, j'enregistre des relevés de compteurs. Cette lecture me conduit à obtenir des suites de valeurs identiques. J'aimerais supprimer les valeurs identiques les plus anciennes et ne conserver que la la récente.
La table comporte environs 2,5 millions d'enr et les compteurs sont aux nombres de 5600.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
 
CREATE TABLE Informations (
  IDInformations INT(10) UNSIGNED NOT NULL AUTOINCREMENT,
  DateHeure TIMESTAMP NOT NULL,
  Valeur VARCHAR(50) NOT NULL,
  IDVersions_Machines INT(10) UNSIGNED NOT NULL,
  IDTypesInformation INT(10) UNSIGNED NOT NULL,
INDEX INDEX 1 (IDInformations),
INDEX INDEX 2 (IDVersions_Machines),
INDEX INDEX 3 (IDTypesInformation)
)
COLLATE='utf8_general_ci' ENGINE=MyISAM

Par la suite, je ne parle plus de la rubrique TIMESTAMP nommée DateHeure qui n'a pas d'intérêt ici.

Exemple de données à supprimer :

Code :

1
2
3
4
5
6
7
 
1   120000     99      324 : A supprimer car 3
2   bleu       124     212 : A supprimer car 4
3   120000     99      324 : RAS
4   bleu       124     212 : A supprimer car 6
5   122000     99      324 : RAS
6   bleu       124     212 : A supprimer car 4

Ce que je fais pour l'instant, c'est construire une table temporaire contenant les ID des valeurs à conserver puis un DELETE .... WHERE NOT IN ...
Et c'est catastrophiquement long !

La requête de sélection des valeurs à conserver

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
 
INSERT INTO
  InformationsTmp(IDInformations)
SELECT
  SAV1.IDInformations
FROM
  InformationsSAV SAV1
WHERE
  IDTypesInformation=entIDTypesInformation
  AND IDVersions_Machines=entIDVersions_Machines
  -- Je conserve si la valeur suivante est identique
  AND SAV1.Valeur<>
    (SELECT
      SAV2.Valeur FROM InformationsSAV SAV2 
    WHERE
      SAV2.IDInformations>SAV1.IDInformations
      AND SAV2.IDTypesInformation=SAV1.IDTypesInformation 
      AND SAV2.IDVersions_Machines=SAV1.IDVersions_Machines
    ORDER BY
      SAV2.IDInformations ASC
    LIMIT
      1
    )
  -- Je conserve toujours la dernière valeur
  AND SAV1.IDInformations<>
   (SELECT
      MAX(SAV2.IDInformations)
    FROM
      InformationsSAV SAV2
    WHERE
      SAV2.IDTypesInformation=SAV1.IDTypesInformation
      AND SAV2.IDVersions_Machines=SAV1.IDVersions_Machines
    )

Cette requête est appelée dans une procstock avec un curseur dont je me sers pour lui passer une paire de valeurs (entIDTypesInformation, entIDVersions_Machines) pour fractionner le traitement
Un index sur Valeur arrangerait-il quelque peu les perf ?

[Fred_34]

Si cette requête te donne bien les lignes a supprimer :

Code :

1
2
3
4
5
SELECT DISTINCT i1.IDInformations 
FROM informations i1
INNER JOIN informations i2
USING(IDVersions_Machines,IDTypesInformation,Valeur)
WHERE i1.IDInformations < i2.IDInformations

Tu peux faire ça :

Code :

1
2
3
4
5
6
7
8
9
10
11
DELETE FROM informations WHERE IDInformations IN  
(
  SELECT * FROM 
  (
    SELECT DISTINCT i1.IDInformations 
    FROM informations i1
    INNER JOIN informations i2
    USING(IDVersions_Machines,IDTypesInformation,Valeur)
    WHERE i1.IDInformations < i2.IDInformations
  ) tmp
)

1) Je ne sais pas si c'est rapide.
2) Il faut surement comparer DateHeure plutot que IDInformations

[nenex73]

Merci Fred

Pquoi penses-tu qu'il faudrait plutôt utiliser DateHeure ?
Je pensais, assez naturellement, que la comparaison d'entiers était plus rapide que celle de timestamp. Me serais-je fourvoyé ?

Le problème de ta requête, c'est qu'elle ne va pas vérifier que c'est la valeur IMMEDIATEMENT suivante qui est identique.

Dans :

Code :

1
2
3
4
 
1  rouge  12  99  324
2  bleu   12  99  324
3  rouge  12  99  324

Ton code supprimerait l'enr 1 car il est identique à 3 MAIS négligerait le changement de valeur intervenu entre temps (2 = bleu)

[Fred_34]

Envoyé par nenex73

Pquoi penses-tu qu'il faudrait plutôt utiliser DateHeure ?
Je pensais, assez naturellement, que la comparaison d'entiers était plus rapide que celle de timestamp. Me serais-je fourvoyé ?

Ce n'est pas une question de rapidité, mais de cohérence des résultats. On est sur de l'ordre de DateHeure mais pas forcement de IDInformations.

Envoyé par nenex73

Le problème de ta requête, c'est qu'elle ne va pas vérifier que c'est la valeur IMMEDIATEMENT suivante qui est identique.

Et en complétant la requête par un "NOT EXISTS", ça donne quoi ?:

Code :

1
2
3
4
5
6
7
8
9
10
11
SELECT DISTINCT i1.IDInformations 
    FROM informations i1
    INNER JOIN informations i2
    USING(IDVersions_Machines,IDTypesInformation,Valeur)
    WHERE i1.IDInformations < i2.IDInformations
 
AND NOT EXISTS(SELECT i3.IDInformations FROM informations i3 
WHERE i3.IDInformations BETWEEN i1.IDInformations AND i2.IDInformations  
AND i3.Valeur<>i1.Valeur 
AND i1.IDVersions_Machines =i3.IDVersions_Machines 
AND i1.IDTypesInformation =i3.IDTypesInformation)

1) Il y a forcement plus simple...

[nenex73]

Houuu, c'est ingénieux ça.
Je ne sais pas si c'est rapide mais ça vaut le coup de tester.
Je te tiens au courant.

En lisant ton post, ça me donne une idée.
Peut-être qu'en créant une table temporaire ou ne serait présent que les "informations" filtrées sur IDVersions_Machines et IDTypesInformation cela réduirait considérablement la table informations (2.5M / 5000) et permettrait en théorie de faire baisser drastiquement le temps de traitement.

Pour la cohérence IDInformations, DateHeure, aucun risque.
C'est la valeur NOW() qui est insérée donc si DateHeureN> DateHeureN+1 alors IDInformationsN > IDInformationsN+1

Et pour l'index sur Valeur, valable ou pas ?

[nenex73]

Bon c'est pas transcendant de rapidité mais l'idée était brillante.

La solution la plus rapide est celle qui passe par une table temporaire.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
CREATE TABLE InformationsTmp1 LIKE InformationsSAV
;
 
INSERT INTO
	InformationsTmp1
SELECT
	*
FROM
	InformationsSAV
WHERE
	IDTypesInformation=entIDTypesInformation
	AND IDVersions_Machines=entIDVersions_Machines
;

Puis

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
 
INSERT INTO
	InformationsTmp(IDInformations)
SELECT
	SAV1.IDInformations
FROM
	InformationsTmp1 SAV1
WHERE
	SAV1.Valeur<>(
		SELECT
			SAV2.Valeur
		FROM
			InformationsTmp1 SAV2
		WHERE
			SAV2.IDInformations>SAV1.IDInformations
		ORDER BY
			SAV2.IDInformations ASC
		LIMIT
			1
		)
	AND SAV1.IDInformations<>(
		SELECT
			MAX(SAV2.IDInformations)
		FROM
			InformationsTmp1 SAV2			
		)
;

Merci à toi Fred. Sans en parler ensemble, je n'aurais pas eu l'idée.