repetir - sql eliminar registros repetidos dejando solo 1

Eliminar duplicados de la tabla en función de múltiples criterios y persistir en otra tabla

Question

Tengo una tabla de taccounts con columnas como account_id(PK) , login_name , password , last_login . Ahora tengo que eliminar algunas entradas duplicadas según una nueva lógica comercial. Por lo tanto, las cuentas duplicadas se enviarán con el mismo email o el mismo ( login_name password y password ). La cuenta con el último inicio de sesión debe conservarse.

Aquí están mis intentos (algunos valores de correo electrónico son nulos y están en blanco)

DELETE
FROM taccounts
WHERE email is not null and char_length(trim(both '' '' from email))>0 and last_login NOT IN
(
SELECT MAX(last_login)
FROM taccounts
WHERE email is not null and char_length(trim(both '' '' from email))>0 
GROUP BY lower(trim(both '' '' from email)))

Del mismo modo para login_name y password

DELETE
FROM taccounts
WHERE last_login NOT IN
(
SELECT MAX(last_login)
FROM taccounts
GROUP BY login_name, password)

¿Hay alguna forma mejor o alguna forma de combinar estas dos consultas por separado?

También alguna otra tabla tiene account_id como clave foránea. ¿Cómo actualizar este cambio para esas tablas? `Estoy usando PostgreSQL 9.2.1

EDITAR : Algunos de los valores de correo electrónico son nulos y algunos de ellos están en blanco (''''). Entonces, si dos cuentas tienen nombre de usuario y contraseña diferentes y sus correos electrónicos son nulos o en blanco, entonces deben considerarse como dos cuentas diferentes.

Answer 1

Además de la excelente respuesta de Edwin, a menudo puede ser útil crear en una tabla de enlaces intermedia que relacione las claves antiguas con las nuevas.

DROP SCHEMA tmp CASCADE;
CREATE SCHEMA tmp ;
SET search_path=tmp;
CREATE TABLE taccounts
        ( account_id SERIAL PRIMARY KEY
        , login_name varchar
        , email varchar
        , last_login TIMESTAMP
        );
    -- create some fake data
INSERT INTO taccounts(last_login)
SELECT gs FROM generate_series(''2013-03-30 14:00:00'' ,''2013-03-30 15:00:00'' , ''1min''::interval) gs
        ;
UPDATE taccounts
SET login_name = ''User_'' || (account_id %10)::text
        , email = ''Joe'' || (account_id %9)::text || ''@somedomain.tld''
        ;

SELECT * FROM taccounts;

        --
        -- Create (temp) table linking old id <--> new id
        -- After inspection this table can be used as a source for the FK updates
        -- and for the final delete.
        --
CREATE TABLE update_ids AS
WITH pairs AS (
        SELECT one.account_id AS old_id
        , two.account_id AS new_id
        FROM taccounts one
        JOIN taccounts two ON two.last_login > one.last_login
                AND ( two.email = one.email OR two.login_name = one.login_name)
        )
SELECT old_id,new_id
FROM pairs pp
WHERE NOT EXISTS (
        SELECT * FROM pairs nx
        WHERE nx.old_id = pp.old_id
        AND nx.new_id > pp.new_id
        )
        ;

SELECT * FROM update_ids
        ;

UPDATE other_table_with_fk_to_taccounts dst
SET account_id. = ids.new_id
FROM update_ids ids
WHERE account_id. = ids.old_id
        ;
DELETE FROM taccounts del
WHERE EXISTS (
        SELECT * FROM update_ids ex
        WHERE ex.old_id = del.account_id
        );

SELECT * FROM taccounts;

Otra forma de lograr lo mismo es agregar una columna con un puntero a la tecla preferida de la tabla y usarla para sus actualizaciones y eliminaciones.

ALTER TABLE taccounts
        ADD COLUMN better_id INTEGER REFERENCES taccounts(account_id)
        ;

   -- find the *better* records for each record.
UPDATE taccounts dst
SET better_id = src.account_id
FROM taccounts src
WHERE src.login_name = dst.login_name
AND src.last_login > dst.last_login
AND src.email IS NOT NULL
AND NOT EXISTS (
        SELECT * FROM taccounts nx
        WHERE nx.login_name = dst.login_name
        AND nx.email IS NOT NULL
        AND nx.last_login > src.last_login
        );

    -- Find records that *do* have an email address
UPDATE taccounts dst
SET better_id = src.account_id
FROM taccounts src
WHERE src.login_name = dst.login_name
AND src.email IS NOT NULL
AND dst.email IS NULL
AND NOT EXISTS (
        SELECT * FROM taccounts nx
        WHERE nx.login_name = dst.login_name
        AND nx.email IS NOT NULL
        AND nx.last_login > src.last_login
        );

SELECT * FROM taccounts ORDER BY account_id;

UPDATE other_table_with_fk_to_taccounts dst
SET account_id = src.better_id
FROM update_ids src
WHERE dst.account_id = src.account_id
AND src.better_id IS NOT NULL
        ;

DELETE FROM taccounts del
WHERE EXISTS (
        SELECT * FROM taccounts ex
        WHERE ex.account_id = del.better_id
        );
SELECT * FROM taccounts ORDER BY account_id;

Answer 2

Afortunadamente, está ejecutando PostgreSQL. DISTINCT ON debería hacer esto comparativamente fácil:

Como va a eliminar la mayoría de las filas (~ 90% de engaños) y la tabla probablemente se ajuste fácilmente a la RAM, hice esta ruta:

1. SELECT las filas supervivientes en una tabla temporal.
2. Redirigir columnas de referencia.
3. DELETE todas las filas de la tabla base.
4. Vuelva a INSERT sobrevivientes.

Destilar las filas restantes

CREATE TEMP TABLE tmp AS
SELECT DISTINCT ON (login_name, password) *
FROM  (
   SELECT DISTINCT ON (email) *
   FROM   taccounts
   ORDER  BY email, last_login DESC
   ) sub
ORDER  BY login_name, password, last_login DESC;

Más sobre DISTINCT ON :

• Seleccione la primera fila en cada grupo GROUP BY?

Para eliminar duplicados para dos criterios diferentes, utilizo una subconsulta para aplicar las dos reglas una después de la otra. El primer paso conserva la cuenta th con el último last_login , por lo que es "serializable".

Inspeccione los resultados y pruebe la plausibilidad.

SELECT * FROM tmp;

Una tabla temporal se elimina automáticamente al final de una sesión. En pgAdmin (que pareces estar usando), la sesión permanece mientras se abra la ventana del editor en la que creaste la tabla temporal.

Consulta alternativa para la definición actualizada de "duplicados"

SELECT *
FROM   taccounts t
WHERE  NOT EXISTS (
   SELECT 1
   FROM   taccounts t1
   WHERE (
           NULLIF(t1.email, '''') = t.email OR 
           (NULLIF(t1.login_name, ''''), NULLIF(t1.password, ''''))
         = (t.login_name, t.password)
         )
   AND   (t1.last_login, t1.account_id) > (t.last_login, t.account_id)
   );

Esto no trata a cadena NULL o emtpy ( '''' ) como idénticas en ninguna de las columnas "duplicadas".

La expresión de fila (t1.last_login, t1.account_id) se ocupa de la posibilidad de que dos dupes puedan compartir el mismo last_login . Tomo el que tiene el account_id más account_id en este caso, que es único, ya que es el PK.

Cómo identificar todas las FK entrantes

SELECT c.confrelid::regclass::text AS referenced_table
      ,c.conname AS fk_name
      ,pg_get_constraintdef(c.oid) AS fk_definition
FROM   pg_attribute a 
JOIN   pg_constraint c ON (c.conrelid, c.conkey[1]) = (a.attrelid, a.attnum)
WHERE  c.confrelid = ''taccounts ''::regclass   -- (schema-qualified) table name
AND    c.contype  = ''f''
ORDER  BY 1, contype DESC;

Solo se construye en la primera columna de la clave externa. Más sobre eso:

• Encuentre el nombre de tabla referenciado usando tabla, campo y nombre de esquema

O puede inspeccionar al Dependents en la ventana de la derecha del navegador de objetos de pgAdmin, luego de seleccionar las taccounts .

Cambiar a nuevo maestro

Si tiene tablas que hacen referencia a las taccounts (claves externas entrantes para las taccounts ), querrá actualizar todos esos campos, antes de eliminar los duplicados.
Redirija todos a la nueva fila maestra:

UPDATE referencing_tbl r
SET    referencing_column = tmp.reference_column
FROM   tmp
JOIN   taccounts t1 USING (email)
WHERE  r.referencing_column = t1.referencing_column
AND    referencing_column IS DISTINCT FROM tmp.reference_column;

UPDATE referencing_tbl r
SET    referencing_column = tmp.reference_column
FROM   tmp
JOIN   taccounts t2 USING (login_name, password)
WHERE  r.referencing_column = t1.referencing_column
AND    referencing_column IS DISTINCT FROM tmp.reference_column;

Entra para matar

Ahora, los incautos no tienen más enlaces a ellos. Entra para matar.

ALTER TABLE taccounts DISABLE TRIGGER ALL;
DELETE FROM taccounts;
VACUUM taccounts;
INSERT INTO taccounts
SELECT * FROM tmp;
ALTER TABLE taccounts ENABLE TRIGGER ALL;

Desactivo todos los disparadores durante la operación. Esto evita verificar la integridad referencial durante la operación. Todo debería estar bien, una vez que vuelvas a activar los disparadores. Nos ocupamos de todos los FK entrantes anteriores. Se garantiza que los FK salientes sean sólidos, ya que no tiene acceso concurrente y todos los valores han estado allí antes.