Comment puis-je supprimer les lignes en double ?

Question

Quelle est la meilleure façon de supprimer les lignes en double d'un fichier assez volumineux ? SQL Server (c'est-à-dire 300 000+ lignes) ?

Bien entendu, les lignes ne seront pas des doublons parfaits en raison de l'existence de l'élément RowID le champ d'identité.

MaTable

RowID int not null identity(1,1) primary key,
Col1 varchar(20) not null,
Col2 varchar(2048) not null,
Col3 tinyint not null

Answer 1

En supposant qu'il n'y ait pas de nulles, vous GROUP BY les colonnes uniques, et SELECT le site MIN (or MAX) RowId comme la ligne à conserver. Ensuite, il suffit de supprimer tout ce qui n'a pas d'ID de rangée :

DELETE FROM MyTable
LEFT OUTER JOIN (
   SELECT MIN(RowId) as RowId, Col1, Col2, Col3 
   FROM MyTable 
   GROUP BY Col1, Col2, Col3
) as KeepRows ON
   MyTable.RowId = KeepRows.RowId
WHERE
   KeepRows.RowId IS NULL

Dans le cas où vous avez un GUID au lieu d'un entier, vous pouvez remplacer

MIN(RowId)

con

CONVERT(uniqueidentifier, MIN(CONVERT(char(36), MyGuidColumn)))

Answer 2

Une autre façon de procéder est la suivante

; 

--Ensure that any immediately preceding statement is terminated with a semicolon above
WITH cte
     AS (SELECT ROW_NUMBER() OVER (PARTITION BY Col1, Col2, Col3 
                                       ORDER BY ( SELECT 0)) RN
         FROM   #MyTable)
DELETE FROM cte
WHERE  RN > 1;

J'utilise ORDER BY (SELECT 0) ci-dessus car le choix de la ligne à préserver en cas d'égalité est arbitraire.

Pour conserver le dernier en date dans RowID par exemple, vous pourriez utiliser ORDER BY RowID DESC

Plans d'exécution

Le plan d'exécution est souvent plus simple et plus efficace que celui de la réponse acceptée, car il ne nécessite pas de jointure automatique.

Ce n'est cependant pas toujours le cas. Un endroit où le GROUP BY peut être préférée dans les situations où une agrégat de hachage serait choisi de préférence à un agrégat de flux.

En ROW_NUMBER donnera toujours à peu près le même plan, alors que la solution de la GROUP BY est plus flexible.

Les facteurs qui pourraient favoriser l'approche de l'agrégat de hachage seraient les suivants

• Pas d'index utile sur les colonnes de partitionnement
• relativement moins de groupes avec relativement plus de doublons dans chaque groupe

Dans les versions extrêmes de ce deuxième cas (s'il y a très peu de groupes avec beaucoup de doublons dans chacun d'entre eux), on peut aussi envisager d'insérer simplement les lignes à conserver dans une nouvelle table, alors TRUNCATE -sur l'original et les recopier pour minimiser la journalisation par rapport à la suppression d'une très grande partie des lignes.

Answer 3

Il y a un bon article sur suppression des doublons sur le site du support Microsoft. C'est assez conservateur - ils vous demandent de tout faire en plusieurs étapes - mais cela devrait bien fonctionner pour les grandes tables.

J'ai utilisé des auto-joints pour faire cela dans le passé, bien que cela puisse probablement être amélioré avec une clause HAVING :

DELETE dupes
FROM MyTable dupes, MyTable fullTable
WHERE dupes.dupField = fullTable.dupField 
AND dupes.secondDupField = fullTable.secondDupField 
AND dupes.uniqueField > fullTable.uniqueField

Answer 4

La requête suivante est utile pour supprimer les lignes en double. La table dans cet exemple a ID comme une colonne d'identité et les colonnes qui ont des données en double sont Column1 , Column2 y Column3 .

DELETE FROM TableName
WHERE  ID NOT IN (SELECT MAX(ID)
                  FROM   TableName
                  GROUP  BY Column1,
                            Column2,
                            Column3
                  /*Even if ID is not null-able SQL Server treats MAX(ID) as potentially
                    nullable. Because of semantics of NOT IN (NULL) including the clause
                    below can simplify the plan*/
                  HAVING MAX(ID) IS NOT NULL) 

Le script suivant montre l'utilisation de la fonction GROUP BY , HAVING , ORDER BY en une seule requête, et renvoie les résultats avec la colonne dupliquée et son nombre.

SELECT YourColumnName,
       COUNT(*) TotalCount
FROM   YourTableName
GROUP  BY YourColumnName
HAVING COUNT(*) > 1
ORDER  BY COUNT(*) DESC

Answer 5

delete t1
from table t1, table t2
where t1.columnA = t2.columnA
and t1.rowid>t2.rowid

Postgres :

delete
from table t1
using table t2
where t1.columnA = t2.columnA
and t1.rowid > t2.rowid