sql - repetidos - ¿Cómo eliminar entradas duplicadas?

sql eliminar registros duplicados menos uno

Question

Tengo que agregar una restricción única a una tabla existente. Esto está bien, excepto que la tabla ya tiene millones de filas, y muchas de las filas violan la restricción única que necesito agregar.

¿Cuál es el enfoque más rápido para eliminar las filas ofensivas? Tengo una declaración de SQL que encuentra los duplicados y los elimina, pero lleva una eternidad ejecutarlos. ¿Hay alguna otra manera de resolver este problema? Tal vez una copia de seguridad de la tabla, luego restaurar después de que se agrega la restricción?

Answer 1

Acabo de utilizar la respuesta de Erwin Brandstetter con éxito para eliminar duplicados en una tabla de unión (una tabla que carece de sus propias ID principales), pero descubrí que hay una advertencia importante.

Incluir ON COMMIT DROP significa que la tabla temporal se eliminará al final de la transacción. Para mí, eso significaba que la mesa temporal ya no estaba disponible para cuando la inserté.

Acabo de CREATE TEMPORARY TABLE t_tmp AS SELECT DISTINCT * FROM tbl; y todo funcionó bien

La tabla temporal se descarta al final de la sesión.

Answer 2

Algunos de estos enfoques parecen un poco complicados, y generalmente hago esto como:

Dada la tabla de table , quiere unírselo en único (campo1, campo2) manteniendo la fila con el campo máximo3:

DELETE FROM table USING table alias
WHERE table.field1 = alias.field1 AND table.field2 = alias.field2 AND
table.max_field < alias.max_field

Por ejemplo, tengo una tabla, user_accounts , y quiero agregar una restricción única en el correo electrónico, pero tengo algunos duplicados. Digamos también que quiero mantener el más reciente creado (identificación máxima entre los duplicados).

DELETE FROM user_accounts USING user_accounts ua2
WHERE user_accounts.email = ua2.email AND user_account.id < ua2.id;

• Nota: USING no es SQL estándar, es una extensión de PostgreSQL (pero muy útil), pero la pregunta original menciona específicamente a PostgreSQL.

Answer 3

Consulta generalizada para eliminar duplicados:

DELETE FROM table_name
WHERE ctid NOT IN (
SELECT max(ctid) FROM table_name
GROUP BY column1, [column 2, ...]
);

La columna ctid es una columna especial disponible para cada tabla pero no visible a menos que se mencione específicamente. El valor de la columna ctid se considera único para cada fila en una tabla.

Answer 4

De una antigua lista de correo postgresql.org :

create table test ( a text, b text );

Valores únicos

insert into test values ( ''x'', ''y'');
insert into test values ( ''x'', ''x'');
insert into test values ( ''y'', ''y'' );
insert into test values ( ''y'', ''x'' );

Valores duplicados

insert into test values ( ''x'', ''y'');
insert into test values ( ''x'', ''x'');
insert into test values ( ''y'', ''y'' );
insert into test values ( ''y'', ''x'' );

Un duplicado doble más

insert into test values ( ''x'', ''y'');
select oid, a, b from test;

Seleccionar filas duplicadas

select o.oid, o.a, o.b from test o
where exists ( select ''x''
from test i
where i.a = o.a
and i.b = o.b
and i.oid < o.oid
);

Eliminar filas duplicadas

Nota: PostgreSQL no admite alias en la tabla mencionada en la cláusula from de una eliminación.

delete from test
where exists ( select ''x''
from test i
where i.a = test.a
and i.b = test.b
and i.oid < test.oid
);

Answer 5

En lugar de crear una tabla nueva, también puede volver a insertar filas únicas en la misma tabla después de truncarla. Hazlo todo en una transacción . Opcionalmente, puede soltar la tabla temporal al final de la transacción automáticamente con ON COMMIT DROP . Vea abajo.

Este enfoque solo es útil cuando hay muchas filas para eliminar de toda la tabla. Por solo unos pocos duplicados, use un DELETE simple.

Mencionaste millones de filas. Para que la operación sea rápida , desea asignar suficientes almacenamientos intermedios temporales para la sesión. La configuración debe ajustarse antes de utilizar cualquier memoria temporal en su sesión actual. Averigua el tamaño de tu mesa:

SELECT pg_size_pretty(pg_relation_size(''tbl''));

Establezca temp_buffers consecuencia. Redondee generosamente porque la representación en memoria necesita un poco más de RAM.

SET temp_buffers = 200MB; -- example value
BEGIN;
-- CREATE TEMPORARY TABLE t_tmp ON COMMIT DROP AS -- drop temp table at commit
CREATE TEMPORARY TABLE t_tmp AS -- retain temp table after commit
SELECT DISTINCT * FROM tbl; -- DISTINCT folds duplicates
TRUNCATE tbl;
INSERT INTO tbl
SELECT * FROM t_tmp;
-- ORDER BY id; -- optionally "cluster" data while being at it.
COMMIT;

Este método puede ser mejor que crear una nueva tabla si existen objetos dependientes. Vistas, índices, claves foráneas u otros objetos que hacen referencia a la tabla. TRUNCATE te hace comenzar con un borrón y cuenta nueva de todos modos (un nuevo archivo en el fondo) y es mucho más rápido que DELETE FROM tbl con tablas grandes ( DELETE puede ser más rápido con tablas pequeñas).

Para las tablas grandes, es regularmente más rápido eliminar índices y claves externas, rellenar la tabla y recrear estos objetos. En lo que se refiere a las restricciones fk, debes estar seguro de que los nuevos datos son válidos, por supuesto, o te toparás con una excepción al tratar de crear el fk.

Tenga en cuenta que TRUNCATE requiere un bloqueo más agresivo que DELETE . Esto puede ser un problema para las tablas con carga concurrente pesada.

Si TRUNCATE no es una opción o generalmente para tablas pequeñas a medianas, existe una técnica similar con un CTE modificador de datos (Postgres 9.1 +):

WITH del AS (DELETE FROM tbl RETURNING *)
INSERT INTO tbl
SELECT DISTINCT * FROM del;
-- ORDER BY id; -- optionally "cluster" data while being at it.

Más lento para mesas grandes, porque TRUNCATE es más rápido allí. Pero puede ser más rápido (¡y más simple!) Para tablas pequeñas.

Si no tiene ningún objeto dependiente, puede crear una nueva tabla y eliminar la anterior, pero apenas gana nada por encima de este enfoque universal.

Para tablas muy grandes que no caben en la memoria RAM disponible , crear una nueva tabla será considerablemente más rápido. Tendrás que comparar esto con posibles problemas / gastos generales con objetos dependientes.

Answer 6

Esta función elimina duplicados sin eliminar índices y lo hace a cualquier tabla.

Uso: select remove_duplicates(''mytable'');

--- --- remove_duplicates(tablename) removes duplicate records from a table (convert from set to unique set) --- CREATE OR REPLACE FUNCTION remove_duplicates(text) RETURNS void AS $$ DECLARE tablename ALIAS FOR $1; BEGIN EXECUTE ''CREATE TEMPORARY TABLE _DISTINCT_'' || tablename || '' AS (SELECT DISTINCT * FROM '' || tablename || '');''; EXECUTE ''DELETE FROM '' || tablename || '';''; EXECUTE ''INSERT INTO '' || tablename || '' (SELECT * FROM _DISTINCT_'' || tablename || '');''; EXECUTE ''DROP TABLE _DISTINCT_'' || tablename || '';''; RETURN; END; $$ LANGUAGE plpgsql;

Answer 7

Esto funciona muy bien y es muy rápido:

CREATE INDEX otherTable_idx ON otherTable( colName );
CREATE TABLE newTable AS select DISTINCT ON (colName) col1,colName,col2 FROM otherTable;

Answer 8

Estoy trabajando con PostgreSQL 8.4. Cuando ejecuté el código propuesto, descubrí que en realidad no estaba eliminando los duplicados. Al ejecutar algunas pruebas, descubrí que agregar el "DISTINCT ON (duplicate_column_name)" y el "ORDER BY duplicate_column_name" funcionaba bien. No soy un gurú de SQL, lo encontré en el documento PostgreSQL 8.4 SELECT ... DISTINCT.

CREATE OR REPLACE FUNCTION remove_duplicates(text, text) RETURNS void AS $$
DECLARE
tablename ALIAS FOR $1;
duplicate_column ALIAS FOR $2;
BEGIN
EXECUTE ''CREATE TEMPORARY TABLE _DISTINCT_'' || tablename || '' AS (SELECT DISTINCT ON ('' || duplicate_column || '') * FROM '' || tablename || '' ORDER BY '' || duplicate_column || '' ASC);'';
EXECUTE ''DELETE FROM '' || tablename || '';'';
EXECUTE ''INSERT INTO '' || tablename || '' (SELECT * FROM _DISTINCT_'' || tablename || '');'';
EXECUTE ''DROP TABLE _DISTINCT_'' || tablename || '';'';
RETURN;
END;
$$ LANGUAGE plpgsql;

Answer 9

La función de ventana PostgreSQL es útil para este problema.

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);

Consulte Eliminar duplicados .

Answer 10

Por ejemplo, usted podría:

CREATE TABLE tmp ...
INSERT INTO tmp SELECT DISTINCT * FROM t;
DROP TABLE t;
ALTER TABLE tmp RENAME TO t;

Answer 11

Primero, debe decidir cuál de sus "duplicados" conservará. Si todas las columnas son iguales, OK, puede eliminar cualquiera de ellas ... ¿Pero quizás quiera conservar solo el criterio más reciente o algún otro?

La forma más rápida depende de su respuesta a la pregunta anterior, y también del% de duplicados en la tabla. Si tira el 50% de sus filas, es mejor que lo haga CREATE TABLE ... AS SELECT DISTINCT ... FROM ... ; , y si elimina el 1% de las filas, usar DELETE es mejor.

También para operaciones de mantenimiento como este, generalmente es bueno establecer work_mem en una buena parte de tu RAM: ejecuta EXPLAIN, revisa el número N de ordenadas / hashes, y establece work_mem en tu RAM / 2 / N. Usa mucha RAM; es bueno para la velocidad. Siempre y cuando solo tengas una conexión simultánea ...

Answer 12

Puede usar oid o ctid, que normalmente son columnas "no visibles" en la tabla:

DELETE FROM table
WHERE ctid NOT IN
(SELECT MAX(s.ctid)
FROM table s
GROUP BY s.column_has_be_distinct);

Answer 13

Si solo tiene una o unas pocas entradas duplicadas, y de hecho están duplicadas (es decir, aparecen dos veces), puede usar la columna ctid "oculta", como se propuso anteriormente, junto con LIMIT :

DELETE FROM mytable WHERE ctid=(SELECT ctid FROM mytable WHERE […] LIMIT 1);

Esto eliminará solo la primera de las filas seleccionadas.

Answer 14

CREATE TABLE test (col text);
INSERT INTO test VALUES
(''1''),
(''2''), (''2''),
(''3''),
(''4''), (''4''),
(''5''),
(''6''), (''6'');
DELETE FROM test
WHERE ctid in (
SELECT t.ctid FROM (
SELECT row_number() over (
partition BY col
ORDER BY col
) AS rnum,
ctid FROM test
ORDER BY col
) t
WHERE t.rnum >1);

Answer 15

DELETE FROM table
WHERE something NOT IN
(SELECT MAX(s.something)
FROM table As s
GROUP BY s.this_thing, s.that_thing);

Answer 16

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);

Elimine los duplicados por columna (s) y mantenga la fila con la ID más baja. El patrón está tomado de la wiki de postgres

Usando CTE puedes lograr una versión más legible de lo anterior a través de este

WITH duplicate_ids as (
SELECT id, rnum
FROM num_of_rows
WHERE rnum > 1
),
num_of_rows as (
SELECT id,
ROW_NUMBER() over (partition BY column1,
column2,
column3 ORDER BY id) AS rnum
FROM tablename
)
DELETE FROM tablename
WHERE id IN (SELECT id from duplicate_ids)