중복 항목을 삭제하는 방법은 무엇입니까?

developer tip

중복 항목을 삭제하는 방법은 무엇입니까?

optionbox 2020. 8. 29. 10:46

중복 항목을 삭제하는 방법은 무엇입니까?

기존 테이블에 고유 한 제약 조건을 추가해야합니다. 테이블에 이미 수백만 개의 행이 있고 많은 행이 추가해야하는 고유 한 제약 조건을 위반한다는 점을 제외하면 괜찮습니다.

문제가되는 행을 제거하는 가장 빠른 방법은 무엇입니까? 중복을 찾아 삭제하는 SQL 문이 있지만 실행하는 데 영원히 걸립니다. 이 문제를 해결할 다른 방법이 있습니까? 테이블을 백업 한 다음 제약 조건이 추가 된 후 복원 할 수 있습니까?

예를 들어 다음과 같이 할 수 있습니다.

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

이러한 접근 방식 중 일부는 약간 복잡해 보이며 일반적으로 다음과 같이 수행합니다.

주어진 table table에서 최대 field3 행을 유지하면서 (field1, field2)에서 고유하고 싶습니다.

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

예를 들어, 테이블이 있고 user_accounts이메일에 고유 한 제약 조건을 추가하고 싶지만 일부 중복 항목이 있습니다. 또한 가장 최근에 만든 항목 (중복 항목 중 최대 ID)을 유지하고 싶다고 말합니다.

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

참고- USING표준 SQL이 아니라 PostgreSQL 확장 (그러나 매우 유용한 확장)이지만 원래 질문에는 특히 PostgreSQL이 언급되어 있습니다.

새 테이블을 생성하는 대신 자른 후 동일한 테이블에 고유 행을 다시 삽입 할 수도 있습니다. 한 번의 트랜잭션으로 모든 작업 을 수행하십시오 . 선택적으로를 사용하여 트랜잭션이 끝날 때 임시 테이블을 자동으로 삭제할 수 있습니다 ON COMMIT DROP. 아래를 참조하십시오.

이 접근 방식은 테이블 전체에서 삭제할 행이 많은 경우에만 유용합니다. 몇 개의 중복에 대해서는 일반 DELETE.

수백만 개의 행을 언급하셨습니다. 작업을 빠르게 하려면 세션에 충분한 임시 버퍼 를 할당 해야합니다. 현재 세션에서 임시 버퍼를 사용 하기 전에 설정을 조정 해야 합니다. 테이블의 크기를 확인하십시오.

SELECT pg_size_pretty(pg_relation_size('tbl'));

temp_buffers그에 따라 설정하십시오 . 메모리 내 표현에는 약간 더 많은 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;

이 방법은 종속 개체가있는 경우 새 테이블을 만드는 것보다 우수 할 수 있습니다 . 테이블을 참조하는 뷰, 인덱스, 외래 키 또는 기타 개체. TRUNCATE어쨌든 깨끗한 슬레이트 (백그라운드의 새 파일)로 시작하고 큰 테이블 보다 훨씬 빠릅니다 DELETE FROM tbl( DELETE실제로 작은 테이블에서 더 빠를 수 있음).

큰 테이블의 경우 인덱스와 외래 키를 삭제하고 테이블을 다시 채우고 이러한 개체를 다시 만드는 것이 정기적으로 더 빠릅니다 . fk 제약 조건에 관한 한 새 데이터가 물론 유효한지 확인해야합니다. 그렇지 않으면 fk를 만들려고 할 때 예외가 발생합니다.

주 TRUNCATE보다 더 공격적으로 잠금이 필요합니다 DELETE. 동시로드가 많은 테이블의 경우 문제가 될 수 있습니다.

경우 TRUNCATE일반적 대한 옵션 아닌지 매체 작은 테이블 과 유사한 기술이있는 데이터 수정 CTE (포스트 그레스 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.

큰 테이블의 경우 TRUNCATE더 느리기 때문에 더 빠릅니다. 그러나 작은 테이블의 경우 더 빠르고 간단 할 수 있습니다.

종속 개체가 전혀없는 경우 새 테이블을 만들고 이전 테이블을 삭제할 수 있지만이 보편적 인 접근 방식을 통해 얻을 수있는 것은 거의 없습니다.

사용 가능한 RAM에 맞지 않는 매우 큰 테이블의 경우 새 테이블을 만드는 것이 훨씬 빠릅니다. 개체에 따라 발생할 수있는 문제 / 오버 헤드와 비교하여이 값을 측정해야합니다.

일반적으로 테이블에서 "표시되지 않는"열인 oid 또는 ctid를 사용할 수 있습니다.

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

PostgreSQL 창 함수는이 문제에 편리합니다.

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

중복 삭제를 참조하십시오 .

중복 삭제를위한 일반화 된 쿼리 :

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

열 ctid은 모든 테이블에 사용할 수있는 특수 열이지만 특별히 언급하지 않는 한 표시되지 않습니다. ctid열의 값은 테이블의 모든 행에 대해 고유 한 것으로 간주된다.

From an old postgresql.org mailing list:

create table test ( a text, b text );

Unique values

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

Duplicate values

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

One more double duplicate

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

select oid, a, b from test;

Select duplicate rows

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

Delete duplicate rows

Note: PostgreSQL dosn't support aliases on the table mentioned in the from clause of a delete.

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

I just used Erwin Brandstetter's answer successfully to remove duplicates in a join table (a table lacking its own primary IDs), but found that there's one important caveat.

Including ON COMMIT DROP means the temporary table will get dropped at the end of the transaction. For me, that meant the temporary table was no longer available by the time I went to insert it!

I just did CREATE TEMPORARY TABLE t_tmp AS SELECT DISTINCT * FROM tbl; and everything worked fine.

The temporary table does get dropped at the end of the session.

This function removes duplicates without removing indexes and does it to any table.

Usage: 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;

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

If you have only one or a few duplicated entries, and they are indeed duplicated (that is, they appear twice), you can use the "hidden" ctid column, as proposed above, together with LIMIT:

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

This will delete only the first of the selected rows.

First, you need to decide on which of your "duplicates" you will keep. If all columns are equal, OK, you can delete any of them... But perhaps you want to keep only the most recent, or some other criterion?

The fastest way depends on your answer to the question above, and also on the % of duplicates on the table. If you throw away 50% of your rows, you're better off doing CREATE TABLE ... AS SELECT DISTINCT ... FROM ... ;, and if you delete 1% of the rows, using DELETE is better.

Also for maintenance operations like this, it's generally good to set work_mem to a good chunk of your RAM: run EXPLAIN, check the number N of sorts/hashes, and set work_mem to your RAM / 2 / N. Use lots of RAM; it's good for speed. As long as you only have one concurrent connection...

I'm working with PostgreSQL 8.4. When I ran the proposed code, I found that it was not actually removing the duplicates. In running some tests, I found that adding the "DISTINCT ON (duplicate_column_name)" and the "ORDER BY duplicate_column_name" did the trick. I'm no SQL guru, I found this in the PostgreSQL 8.4 SELECT...DISTINCT doc.

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;

This works very nicely and is very quick:

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

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

Delete duplicates by column(s) and keep row with lowest id. The pattern is taken from the postgres wiki

Using CTEs you can achieve a more readable version of the above through this

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)

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

참고URL : https://stackoverflow.com/questions/1746213/how-to-delete-duplicate-entries

'developer tip' 카테고리의 다른 글

in_array 여러 값 (0)	2020.08.29
Python의 버전 번호 비교 (0)	2020.08.29
디렉터리에 폴더가 있는지 확인하고 C #을 사용하여 만듭니다. (0)	2020.08.29
UITabBarItem 이미지를 아래로 이동 하시겠습니까? (0)	2020.08.29
!! ~ (틸드 / 뱅뱅 틸드가 아님)는 '포함 / 포함'배열 메서드 호출의 결과를 어떻게 변경합니까? (0)	2020.08.29

현재글중복 항목을 삭제하는 방법은 무엇입니까?

optionbox

중복 항목을 삭제하는 방법은 무엇입니까?

중복 항목을 삭제하는 방법은 무엇입니까?

Unique values

Duplicate values

One more double duplicate

Select duplicate rows

Delete duplicate rows

'developer tip' 카테고리의 다른 글

'developer tip'의 다른글

티스토리툴바

중복 항목을 삭제하는 방법은 무엇입니까?

중복 항목을 삭제하는 방법은 무엇입니까?

Unique values

Duplicate values

One more double duplicate

Select duplicate rows

Delete duplicate rows

'developer tip' 카테고리의 다른 글

'developer tip'의 다른글

관련글

티스토리툴바