PostgreSQL에서 중복 레코드 삭제

PostgreSQL에서 중복 레코드 삭제

---

PostgreSQL 8.3.8 데이터베이스에 키 / 제약 조건이없고 정확히 동일한 값을 가진 여러 행이있는 테이블이 있습니다.

모든 중복을 제거하고 각 행의 사본을 1 개만 유지하고 싶습니다.

특히 중복을 식별하는 데 사용할 수있는 하나의 열 ( "키"라고 함)이 있습니다 (즉, 각 고유 "키"에 대해 하나의 항목 만 있어야 함).

어떻게 할 수 있습니까? (이상적으로는 단일 SQL 명령 사용)이 경우 속도는 문제가되지 않습니다 (행이 몇 개만 있음).

---

DELETE FROM dupes a
WHERE a.ctid <> (SELECT min(b.ctid)
                 FROM   dupes b
                 WHERE  a.key = b.key);

---

더 빠른 솔루션은

DELETE FROM dups a USING (
      SELECT MIN(ctid) as ctid, key
        FROM dups 
        GROUP BY key HAVING COUNT(*) > 1
      ) b
      WHERE a.key = b.key 
      AND a.ctid <> b.ctid

---

이것은 빠르고 간결합니다.

DELETE FROM dupes T1
    USING   dupes T2
WHERE   T1.ctid < T2.ctid  -- delete the older versions
    AND T1.key  = T2.key;  -- add more columns if needed

자세한 정보를 포함하는 고유 식별자없이 중복 행을 삭제하는 방법의 내 대답을 참조하십시오.

---

나는 이것을 시도했다 :

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

Postgres 위키에서 제공 :

https://wiki.postgresql.org/wiki/Deleting_duplicates

---

나만의 버전을 만들어야했습니다. @a_horse_with_no_name에 의해 작성된 버전은 내 테이블에서 너무 느립니다 (21M 행). 그리고 @rapimo는 단순히 dups를 삭제하지 않습니다.

PostgreSQL 9.5에서 사용하는 것은 다음과 같습니다.

DELETE FROM your_table
WHERE ctid IN (
  SELECT unnest(array_remove(all_ctids, actid))
  FROM (
         SELECT
           min(b.ctid)     AS actid,
           array_agg(ctid) AS all_ctids
         FROM your_table b
         GROUP BY key1, key2, key3, key4
         HAVING count(*) > 1) c);

---

임시 테이블을 사용합니다.

create table tab_temp as
select distinct f1, f2, f3, fn
  from tab;

그런 다음, 삭제 tab및 이름 변경 tab_temp에 tab.

---

이것은 나를 위해 잘 작동했습니다. 중복 값이 ​​포함 된 테이블, 용어가 있습니다. 모든 중복 행으로 임시 테이블을 채우는 쿼리를 실행했습니다. 그런 다음 임시 테이블에서 해당 ID로 a delete 문을 실행했습니다. value는 중복 항목이 포함 된 열입니다.

        CREATE TEMP TABLE dupids AS
        select id from (
                    select value, id, row_number() 
over (partition by value order by value) 
    as rownum from terms
                  ) tmp
                  where rownum >= 2;

delete from [table] where id in (select id from dupids)

---

SQL의 모든 변형 / 풍미에 대해 작동 (AWS REDSHIFT [POSTGRESQL]에서도 작동)

1. 중복 삭제를위한 최상의 방법-> CTE 사용

WITH DUPLICATE_CTE AS 
( SELECT KEY,COUNT(1) AS RANKED FROM <SCHEMANAME>.<TABLENAME>
  GROUP BY KEY )
DELETE FROM DUPLICATE_CTE WHERE RANKED > 1

2. 쉬운 방법-> row_number () / rank, dense_rank () 함수 사용

DELETE FROM <TABLE_ALIAS>
FROM (
SELECT <COLUMN_NAMES>,
ROW_NUMBER() OVER (PARTITION BY KEY) AS RANKED
FROM <SCHEMANAME>.<TABLENAME>
) <TABLE_ALIAS>
WHERE <TABLE_ALIAS>.RANKED >1

위를 사용하는 것보다 비용이 많이들 수 있습니다.

3.Lay-Mans (aka LAME :p ) Method ( Most Generic method to remove Perfect Duplicates)

DROP TABLE IF EXISTS backupOfTheTableContainingDuplicates;

CREATE TABLE aNewEmptyTemporaryOrBackupTable 
AS SELECT DISTINCT * FROM originalTableContainingDuplicates;

TRUNCATE TABLE originalTableContainingDuplicates;

INSERT INTO originalTableContainingDuplicates SELECT * FROM 
aNewEmptyTemporaryOrBackupTable ;

DROP TABLE aNewEmptyTemporaryOrBackupTable ;

EXPLANATION OF THE ABOVE SQL SCRIPT

So,

The 1st query ensures, If you have any backup/temporary table of the original table containing duplicates then first drop that table.

The 2nd query, creates a new table(Temporary/Backup) table with unique entries in the original Table containing duplicate, so the new temporary table is same as the original table MINUS the duplicate entries.

The 3rd Query, truncates or empties the original table.

the 4th Query, inserts or copies all the unique entries in the temporary table to the original table which has been recently truncated (So has no Data). After this query is executed, the Original Table will be populated with UNIQUE data that was in the temporary table.

The 5th Query, removes/drops the unnecessary temporary table.

So End result is, the original table has only UNIQUE ENTRIES and no duplicates.

참고URL : https://stackoverflow.com/questions/6583916/delete-duplicate-records-in-postgresql