Comment supprimer les lignes dupliquées sans identifiant unique

j'ai des lignes dupliquées dans ma table et je veux supprimer les doublons de la manière la plus efficace puisque la table est grande. Après quelques recherches, j'ai trouvé cette requête:

WITH TempEmp AS
(
SELECT name, ROW_NUMBER() OVER(PARTITION by name, address, zipcode ORDER BY name) AS duplicateRecCount
FROM mytable
)
-- Now Delete Duplicate Records
DELETE FROM TempEmp
WHERE duplicateRecCount > 1;

mais il ne fonctionne QU'en SQL, pas en Netezza. Il semblerait qu'elle n'aime pas la clause DELETE après la clause WITH ?

31
demandé sur Erwin Brandstetter 2014-11-06 02:58:45

10 réponses

j'aime la solution de @erwin-brandstetter, mais je voulais montrer une solution avec le USING mot clé:

DELETE   FROM table_with_dups T1
  USING       table_with_dups T2
WHERE  T1.ctid    < T2.ctid       -- delete the "older" ones
  AND  T1.name    = T2.name       -- list columns that define duplicates
  AND  T1.address = T2.address
  AND  T1.zipcode = T2.zipcode;

si vous voulez examiner les enregistrements avant de les supprimer, alors remplacez simplement DELETE par SELECT * et USING par une virgule , , i.e.

SELECT * FROM table_with_dups T1
  ,           table_with_dups T2
WHERE  T1.ctid    < T2.ctid       -- select the "older" ones
  AND  T1.name    = T2.name       -- list columns that define duplicates
  AND  T1.address = T2.address
  AND  T1.zipcode = T2.zipcode;

mise à jour: j'ai testé quelques-unes des différentes solutions ici pour la vitesse. Si vous ne vous attendez pas à beaucoup de doubles, alors cette solution effectue beaucoup mieux que ceux qui ont une clause NOT IN (...) car ceux-ci génèrent beaucoup de lignes dans le sous-jeu.

si vous réécrivez la requête en utilisant IN (...) alors il fonctionne de la même façon que la solution présentée ici, mais le code SQL devient beaucoup moins concis.

mise à jour 2: Si vous avez des valeurs NULL dans une des colonnes clés (que vous ne devriez pas IMO), alors vous pouvez utiliser COALESCE() dans la condition pour cette colonne, par exemple

  AND COALESCE(T1.col_with_nulls, '[NULL]') = COALESCE(T2.col_with_nulls, '[NULL]')
22
répondu isapir 2018-03-09 22:07:58

si vous n'avez pas d'autre identifiant unique, vous pouvez utiliser ctid :

delete from mytable
    where exists (select 1
                  from mytable t2
                  where t2.name = mytable.name and
                        t2.address = mytable.address and
                        t2.zip = mytable.zip and
                        t2.ctid > mytable.ctid
                 );

c'est une bonne idée d'avoir un id unique, incrémentant dans chaque table. Faire un delete comme c'est une raison importante.

47
répondu Gordon Linoff 2016-03-04 18:46:17

dans un monde parfait, chaque table a un identifiant unique d'une sorte.

En l'absence d'une colonne unique (ou d'une combinaison de celles-ci), utilisez la ctid colonne :

DELETE FROM tbl
WHERE  ctid NOT IN (
   SELECT min(ctid)                    -- ctid is NOT NULL by definition
   FROM   tbl
   GROUP  BY name, address, zipcode);  -- list columns defining duplicates

la requête ci-dessus est courte, énumérant commodément les noms de colonne une seule fois. NOT IN (SELECT ...) est un style de requête délicat lorsque des valeurs nulles peuvent être impliquées, mais la colonne système ctid n'est jamais NULL. Voir:

en utilisant EXISTS comme démontré par @Gordon est typiquement plus rapide. C'est aussi une auto-jointure avec le USING clause comme @isapir ajouté plus tard . Les deux devraient aboutir au même plan de requête.

mais noter une différence importante : ces d'autres requêtes traitent NULL comme n'est pas égal , tandis que GROUP BY (ou DISTINCT ou DISTINCT ON () ) traite les valeurs nulles comme égales. Peu importe si les colonnes clés sont définies NOT NULL . Sinon, selon votre définition de" dupliquer", vous aurez besoin de l'une ou l'autre approche. ou utiliser IS NOT DISTINCT FROM en comparaison de valeurs (qui peut ne pas être en mesure d'utiliser certains indices).

clause de non-responsabilité:

ctid est un détail d'implémentation interne de Postgres, il n'est pas dans le standard SQL et peut être modifié entre les versions majeures sans avertissement (même si c'est très peu probable). Ses valeurs peuvent changer entre les commandes en raison des processus de fond ou des opérations d'écriture simultanées (mais pas dans la même commande).

Related:

à part:

la cible d'un énoncé DELETE ne peut pas être le TCE, seulement la table sous-jacente. C'est un spillover de SQL Server - comme l'ensemble de votre démarche.

17
répondu Erwin Brandstetter 2018-01-13 12:11:59

voici ce que j'ai trouvé, en utilisant un group by

DELETE FROM mytable
WHERE id NOT in (
  SELECT MIN(id) 
  FROM mytable
  GROUP BY name, address, zipcode
)

il supprime les doublons, en préservant le document le plus ancien qui a des doublons.

10
répondu Bruno Calza 2014-11-06 11:14:28

nous pouvons utiliser une fonction de fenêtre pour enlever très efficacement les lignes dupliquées:

DELETE FROM tab 
  WHERE id IN (SELECT id 
                  FROM (SELECT row_number() OVER (PARTITION BY column_with_duplicate_values), id 
                           FROM tab) x 
                 WHERE x.row_number > 1);

une version optimisée de PostgreSQL (avec ctid):

DELETE FROM tab 
  WHERE ctid = ANY(ARRAY(SELECT ctid 
                  FROM (SELECT row_number() OVER (PARTITION BY column_with_duplicate_values), ctid 
                           FROM tab) x 
                 WHERE x.row_number > 1));
6

la syntaxe valide est spécifiée à http://www.postgresql.org/docs/current/static/sql-delete.html

je modifierais votre table pour ajouter un id de clé primaire unique auto-incrémentant de sorte que vous puissiez exécuter une requête comme la suivante qui gardera le premier de chaque ensemble de doublons (c'est-à-dire celui avec l'id le plus bas). Notez que l'ajout de la clé est un peu plus compliqué dans Postgres que certains autres DBs.

DELETE FROM mytable d USING (
  SELECT min(id), name, address, zip 
  FROM mytable 
  GROUP BY name, address, zip HAVING COUNT() > 1
) AS k 
WHERE d.id <> k.id 
AND d.name=k.name 
AND d.address=k.address 
AND d.zip=k.zip;
2
répondu Joe Murray 2018-03-09 22:19:40

si vous voulez garder une ligne hors des lignes dupliquées dans le tableau.

create table some_name_for_new_table as 
(select * from (select *,row_number() over (partition by pk_id) row_n from 
your_table_name_where_duplicates_are_present) a where row_n = 1);

cela créera une table que vous pouvez copier.

avant de copier le tableau, veuillez supprimer la colonne "row_n "

1
répondu Aditya Nathireddy 2017-03-16 10:58:36

dans la documentation supprimer les doubles des lignes

une question fréquente dans IRC est de savoir comment supprimer des lignes qui sont des doublons sur un ensemble de colonnes, en gardant seulement celui avec L'ID le plus bas. Cette requête fait cela pour toutes les lignes de tablename ayant le même column1, column2, et column3.

DELETE FROM tablename
WHERE id IN (SELECT id
          FROM (SELECT id,
                         ROW_NUMBER() OVER (partition BY column1, column2, column3 ORDER BY id) AS rnum
                 FROM tablename) t
          WHERE t.rnum > 1);

parfois, un champ timestamp est utilisé à la place d'un champ ID.

0
répondu Chad Crowe 2017-02-08 15:39:44

si vous voulez un identifiant unique pour chaque ligne, vous pouvez juste en ajouter un (une série, ou un guid), et le traiter comme une clé de remplacement .


CREATE TABLE thenames
        ( name text not null
        , address text not null
        , zipcode text not null
        );
INSERT INTO thenames(name,address,zipcode) VALUES
('James', 'main street', '123' )
,('James', 'main street', '123' )
,('James', 'void street', '456')
,('Alice', 'union square' , '123')
        ;

SELECT*FROM thenames;

        -- add a surrogate key
ALTER TABLE thenames
        ADD COLUMN seq serial NOT NULL PRIMARY KEY
        ;
SELECT*FROM thenames;

DELETE FROM thenames del
WHERE EXISTS(
        SELECT*FROM thenames x
        WHERE x.name=del.name
        AND x.address=del.address
        AND x.zipcode=del.zipcode
        AND x.seq < del.seq
        );

        -- add the unique constrain,so that new dupplicates cannot be created in the future
ALTER TABLE thenames
        ADD UNIQUE (name,address,zipcode)
        ;

SELECT*FROM thenames;
0
répondu wildplasser 2017-10-21 16:09:29

je vais vous donner une solution Simple.1ère copie une ligne(Pour copier cette ligne, cliquez sur sql généré colonne).Maintenant, supprimez toutes les lignes qui r dupliquent.Maintenant l'option d'édition de 200 lignes de sql server open puis coller cette ligne que u a copiée plus tôt.

0
répondu saktiprasad swain 2018-07-23 11:04:28