He filas duplicadas en mi mesa y quiero eliminar duplicados en la forma más eficiente, ya que la tabla es grande. Después de algunas investigaciones, he llegado con esta consulta:

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;

Pero sólo funciona en SQL, no en Netezza. Parece que no le gusta el DELETE después de la WITH cláusula?

  • Si es un trabajo de tiempo – ¿por qué no se ejecuta en postgresql consola?
  • no no es un trabajo de tiempo pero es semanal y siempre nos dan algunos valores duplicados. gracias
  • ¿por qué usted obtener valores duplicados? Lo que si no la puso allí en primer lugar?
  • Son duplicados definido por las columnas (name, address, zipcode)? Hay otras columnas? Son aquellos irrelevantes? Diferentes? Es cualquier combinación de columnas único? Si algunas columnas difieren entre duplicados, que la fila de cada conjunto ¿desea continuar?
  • OBRAS PARA POSTGRESQL (TAMBIÉN FUNCIONA EN AWS REDSHIFT) Ver la respuesta a esta pregunta en otra página
InformationsquelleAutor moe | 2014-11-05

9 Comentarios

  1. 29

    Me gusta @erwin-brandstetter ‘s solución, pero quería mostrar a una solución con la USING palabras clave:

    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 quieres revisar los registros antes de la eliminación de ellos, entonces simplemente reemplazar DELETE con SELECT * y USING con una coma ,, es decir,

    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;

    Actualización: he probado algunas de las diferentes soluciones para la velocidad. Si usted no espera muchos duplicados, entonces esta solución tiene un rendimiento mucho mejor que los que tienen una NOT IN (...) cláusula como los que generan una gran cantidad de filas en la subconsulta.

    Si vuelva a escribir la consulta para utilizar IN (...), a continuación, se realiza de manera similar a la solución que aquí se presenta, pero el código SQL se convierte en mucho menos concisa.

    Actualización 2: Si usted tiene NULL valores en una de las columnas de clave (que en realidad no debería OMI), entonces usted puede utilizar COALESCE() en la condición para la columna, por ejemplo,

      AND COALESCE(T1.col_with_nulls, '[NULL]') = COALESCE(T2.col_with_nulls, '[NULL]')
    • Erwin respuesta es mejor porque controla NULL valores correctamente y no requieren escribir en la columna de los nombres de dos veces.
    • Como he escrito al principio de mi respuesta: I like @erwin-brandstetter 's solution, but wanted to show a solution .... En la búsqueda de los beneficios en el rendimiento, aunque, me gusta el USING solución mejor, especialmente para tablas grandes. He añadido un ejemplo que muestra cómo tratar con NULL valores.
    • Muy agradable, especialmente la posibilidad de tener una primera mirada. Comprobar los valores NULOS en las columnas de datos, generó una T1.col = T2.col OR (T1.col IS NULL AND T2.col IS NULL) criterio de cada columna, basado en la \dS salida de mi mesa. Ahora puedo agregar mi restricción de clave principal.
    • Usted puede poner a prueba los valores NULOS con unen(), como se desee en la Actualización 2 de mi respuesta.
    • A la derecha. Al generar la consulta con expresiones regulares, realmente no importa, y mis expresiones son a prueba de tontos, incluso si no puede ser (por cualquier razón) '[NULL]' valores en algunos campos de texto.
    • La instrucción SELECT muestra sólo un registro si se duplican (bueno), sino que muestra las 3 de ellos si triplicado, por lo tanto ELIMINAR eliminar a todos ellos (!!).
    • Gracias, esto resultó ser mucho más rápido que otras soluciones. Me dio hasta después de 1 hora para algunas de las versiones que hay, esto fue hace casi de manera instantánea

  2. 52

    Si usted no tiene ningún otro identificador único, puede utilizar 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
                     );

    Es una buena idea tener un único, con incremento automático de identificación en cada tabla. Haciendo un delete como esta es una razón importante.

    • yo no tengo ningún campo llamado ctid en mi mesa se puede explicar de dónde sacó esto? gracias
    • ctid es un campo oculto. No se muestra a la hora de recuperar la definición de la tabla. Se trata de un tipo de internos número de fila.
    • La documentación está aquí: postgresql.org/docs/9.2/static/ddl-system-columns.html.
    • where not exists va a eliminar las filas sin duplicados. Debe ser where exists (select 1 `
    • mucho mejor rendimiento con una combinación en la misma mesa
    • Nota: esto no funciona con un campo de json: operator does not exist: json = json
    • OT: yo estoy mirando a tu libro en Amazon. Son los ejemplos en el libro escrito por Postgres o son más genéricos SQL o escrita algunos otros DBMS?
    • Esa pregunta es muy off-topic para un comentario. Presumiblemente, se está refiriendo a «el Análisis de Datos Utilizando SQL y Excel». La versión original fue escrita para SQL Server. Creo que la segunda versión fue también para SQL Server (aunque con toda honestidad, gran parte del trabajo fue realizado en Postgres y luego traducido).
    • Gracias por aclarar. Sé que es off-topic; eso es lo que OT: representa en el prefijo de mi pregunta 😉

  3. 19

    En un mundo perfecto, cada tabla tiene un identificador único de algún tipo.

    En la ausencia de una única columna (o combinación de los mismos), el uso de el ctid columna:

    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 consulta anterior es corto, convenientemente listado de nombres de columna sólo una vez. NOT IN (SELECT ...) es complicado consulta estilo cuando los valores NULOS pueden estar involucrados, pero la columna del sistema ctid nunca es NULA. Ver:

    Utilizando EXISTS como demostrado por @Gordon es típicamente más rápido. Así que es un self-join con la USING cláusula como @isapir añadió más tarde. Ambos deben resultar en el mismo plan de consulta.

    Pero se nota un diferencia importante: Estas otras consultas tratar NULL valores como no es igual, mientras que GROUP BY (o DISTINCT o > DISTINTOS EN ()) trata a los valores NULL como el de la igualdad. No importa si las columnas de clave se definen NOT NULL. Otra cosa, dependiendo de su definición de «duplicar», tendrás la necesidad de un enfoque u otro. O uso NO ES DISTINTA de la DE los en la comparación de los valores (que puede no ser capaz de utilizar algunos de los índices).

    Descargo de responsabilidad:

    ctid es un detalle de implementación interna de Postgres, no está en el estándar SQL y se puede cambiar entre versiones sin previo aviso (incluso si eso es muy raro). Sus valores pueden cambiar entre los comandos debido a los procesos de fondo o concurrente de las operaciones de escritura (pero no en el mismo orden).

    Relacionados con:

    A un lado:

    El destino de un DELETE la declaración no puede ser el CTE, sólo la tabla subyacente. Eso es un desbordamiento de SQL Server – como es su enfoque general.

    • Me gusta esta solución, porque es muy concisa. Cualquier pensamiento sobre el rendimiento de la solución que he publicado a continuación? stackoverflow.com/a/46775289/968244
    • Yo estaba realmente capaz de probarlo. Tengo una tabla con unos 350k filas y tenía 39 duplicados de más de 7 columnas sin índices. He probado el GROUP BY solución primero y fue tomando más de 30 segundos, así que lo mató. Luego trató de la USING solución y se completó en aproximadamente 16 segundos.
    • Como he mencionado de nuevo en 2014: NOT IN está muy bien corto sintaxis, pero EXISTS es más rápido. (Lo mismo que su completamente válido consulta con el USING cláusula.) Pero hay una sutil diferencia. He añadido una nota anterior.
    • Fresco. Gracias por aclarar.
  4. 10

    Aquí es lo que se me ocurrió, utilizando un group by

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

    Elimina los duplicados, conservando el registro más antiguo que se ha duplicados.

    • no tengo id en mi mesa, este es netezza de bases de datos no tienen auto-incremento de los números como de sql server
    • tiene otra columna que identifica de forma exclusiva a las filas?
    • no se no se..
    • no se puede generar manualmente?
    • El HAVING cláusula de ruido para esta consulta. El recuento de todos los existentes id es >= 1 en alguna caso. Usted puede quitar.
    • tienes razón 🙂

  5. 6

    Podemos usar una función de ventana para muy eficaz de eliminación de filas duplicadas:

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

    Algunos de PostgreSQL versión optimizada (con 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. 2

    La sintaxis válida que se especifica en http://www.postgresql.org/docs/current/static/sql-delete.html

    Me gustaría MODIFICAR la tabla para agregar un único incremento automático identificador de clave principal para que se pueda ejecutar una consulta como la siguiente que va a mantener el primer lugar de cada grupo de duplicados (es decir, el uno con el menor id). Tenga en cuenta que la adición de la clave es un poco más complicado en Postgres que algunos otros 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;
  7. 1

    Si quieres mantener una fila de filas duplicadas en la tabla.

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

    Esto va a crear una tabla que se puede copiar.

    Antes de copiar la tabla por favor, elimine la columna «fila_n’

  8. 0

    De la documentación eliminar filas duplicadas

    Una pregunta frecuente en el IRC es cómo eliminar las filas que son duplicados a través de un conjunto de columnas, manteniendo sólo el uno con el ID más bajo.
    Esta consulta no que para todas las filas de tablename tener el mismo columna1, columna2, y columna3.

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

    A veces un campo de marca de hora se utiliza en lugar de un campo de ID.

  9. 0

    Si quieres un identificador único para cada fila, se podría añadir uno (una serie, o un guid), y la trate como a una clave suplente.


    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;
    • Netezza no admite la principal o única restricción de clave
    • Admite índices únicos?
    • No dosent tener .

Dejar respuesta

Please enter your comment!
Please enter your name here