Suppression des lignes en double dans une base de données sqlite

Question

J'ai une énorme table - 36 millions de lignes - dans SQLite3. Dans cette très grande table, il y a deux colonnes :

• hash - texte
• d - réel

Certaines des lignes sont des doublons. C'est-à-dire que les deux hash et d ont les mêmes valeurs. Si deux hachages sont identiques, les valeurs des éléments suivants le sont également d . Cependant, deux personnes identiques d n'implique pas que deux personnes identiques hash 'es.

Je veux supprimer les lignes en double. Je n'ai pas de colonne de clé primaire.

Quel est le moyen le plus rapide de le faire ?

Answer 1

Vous devez trouver un moyen de distinguer les rangs. D'après votre commentaire, vous pourriez utiliser la fonction spéciale colonne rowid pour ça.

Pour supprimer les doublons en conservant le plus petit rowid par (hash,d) :

delete   from YourTable
where    rowid not in
         (
         select  min(rowid)
         from    YourTable
         group by
                 hash
         ,       d
         )

Answer 2

Je suppose que le plus rapide serait d'utiliser la même base de données pour cela : ajouter une nouvelle table avec les mêmes colonnes, mais avec des contraintes appropriées (un index unique sur la paire hachage/réel ?), itérer à travers la table originale et essayer d'insérer des enregistrements dans la nouvelle table, en ignorant les erreurs de violation de contrainte (c'est-à-dire continuer l'itération lorsque des exceptions sont levées).

Ensuite, supprimez l'ancienne table et renommez la nouvelle en l'ancienne.

Answer 3

Si l'ajout d'une clé primaire n'est pas envisageable, une approche consiste à stocker les doublons DISTINCT dans une table temporaire, à supprimer tous les enregistrements en double de la table existante, puis à ajouter les enregistrements dans la table originale à partir de la table temporaire.

Par exemple (écrit pour SQL Server 2008, mais la technique est la même pour toute base de données) :

DECLARE @original AS TABLE([hash] varchar(20), [d] float)
INSERT INTO @original VALUES('A', 1)
INSERT INTO @original VALUES('A', 2)
INSERT INTO @original VALUES('A', 1)
INSERT INTO @original VALUES('B', 1)
INSERT INTO @original VALUES('C', 1)
INSERT INTO @original VALUES('C', 1)

DECLARE @temp AS TABLE([hash] varchar(20), [d] float)
INSERT INTO @temp
SELECT [hash], [d] FROM @original 
GROUP BY [hash], [d]
HAVING COUNT(*) > 1

DELETE O
FROM @original O
JOIN @temp T ON T.[hash] = O.[hash] AND T.[d] = O.[d]

INSERT INTO @original
SELECT [hash], [d] FROM @temp

SELECT * FROM @original

Je ne suis pas sûr que sqlite possède une fonction ROW_NUMBER() mais si c'est le cas, vous pouvez aussi essayer certaines des approches listées ici : Supprimer les enregistrements en double d'une table SQL sans clé primaire