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
?
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]')
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.
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:
-
Comment puis-je (Ou puis-je) sélectionner DISTINCT sur plusieurs colonnes?
-
Comment utiliser l'emplacement physique de lignes (ROWID) dans une instruction DELETE
à 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.
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.
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));
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;
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 "
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.
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;
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.