복붙노트

[SQL] 어떻게 중복 된 항목을 삭제하려면?

SQL

어떻게 중복 된 항목을 삭제하려면?

나는 기존 테이블에 고유 제한 조건을 추가해야합니다. 이 테이블은 이미 수백만 개의 행이 있고, 행의 많은 내가 추가 할 필요가 고유 제한 조건을 위반하는 것을 제외하고는 괜찮습니다.

잘못된 행을 제거하는 가장 빠른 방법은 무엇입니까? 나는 중복을 발견하고이를 삭제 SQL 문을 가지고 있지만 그것을 실행하는 데 시간이 너무 오래 걸립니다. 이 문제를 해결하는 또 다른 방법이 있습니까? 어쩌면 제약 조건이 추가 된 후 복원 한 후, 테이블 백업?

해결법

  1. ==============================

    1.당신이 할 수 예를 들면 :

    당신이 할 수 예를 들면 :

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

    2.이러한 방법 중 일부는 조금 복잡한 것, 나는 일반적으로이 작업을 수행 :

    이러한 방법 중 일부는 조금 복잡한 것, 나는 일반적으로이 작업을 수행 :

    테이블 테이블을 감안할 때, 최대 FIELD3로 행을 유지 (필드 1, 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;
  3. ==============================

    3.대신 새 테이블을 만드는, 당신은 또한 다시 삽입 할 수있는 고유 행을 같은 테이블에 그것을 절단 후. 하나의 트랜잭션에서 모든 작업을 수행 할 수 있습니다. 선택적으로, DROP을 COMMIT ON 자동으로 트랜잭션의 끝에서 임시 테이블을 삭제할 수 있습니다. 아래를 참조하십시오.

    대신 새 테이블을 만드는, 당신은 또한 다시 삽입 할 수있는 고유 행을 같은 테이블에 그것을 절단 후. 하나의 트랜잭션에서 모든 작업을 수행 할 수 있습니다. 선택적으로, DROP을 COMMIT ON 자동으로 트랜잭션의 끝에서 임시 테이블을 삭제할 수 있습니다. 아래를 참조하십시오.

    테이블 곳곳에서 삭제할 행을 많이가있는 경우이 방법은 유용하다. 몇 중복의 경우, 일반 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)와 TBL에서 삭제를보다 있습니다.

    큰 테이블의 경우, 인덱스 및 외래 키를 드롭 테이블을 보충하고 이러한 개체를 다시 정기적으로 빠릅니다. 지금까지 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에 맞지 않을 것입니다 매우 큰 테이블의 경우, 상당히 빨라집니다. 당신은 오버 헤드 따라 개체 / 트러블에 대해이 무게를해야합니다.

  4. ==============================

    4.당신은 일반적으로 테이블의 "보이지 않는"열입니다 OID 또는 CTID를 사용할 수 있습니다 :

    당신은 일반적으로 테이블의 "보이지 않는"열입니다 OID 또는 CTID를 사용할 수 있습니다 :

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

    5.PostgreSQL의 창 기능은이 문제에 대한 편리합니다.

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

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

  6. ==============================

    6.오래된 postgresql.org 메일 링리스트에서 :

    오래된 postgresql.org 메일 링리스트에서 :

    create table test ( a text, b text );

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

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

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

select oid, a, b from test;

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

    참고 : PostgreSQL의 dosn't 지원 별명에 로부터 절에 언급 된 테이블 삭제의.

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

    7.삭제 중복에 일반화 된 쿼리 :

    삭제 중복에 일반화 된 쿼리 :

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

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

  8. ==============================

    8.난 그냥이 테이블 (테이블에는 주 ID를 결여) 가입에 중복을 제거하기 위해 성공적으로 어윈 Brandstetter의 답변을 사용하지만 하나의 중요한 단서가 있음을 발견했다.

    난 그냥이 테이블 (테이블에는 주 ID를 결여) 가입에 중복을 제거하기 위해 성공적으로 어윈 Brandstetter의 답변을 사용하지만 하나의 중요한 단서가 있음을 발견했다.

    DROP을 COMMIT ON 포함하는 것은 임시 테이블은 트랜잭션의 끝에서 떨어 얻을 것을 의미합니다. 나를 위해, 그 의미 임시 테이블 내가 삽입 갔다 그 때까지는 더 이상 사용할 수 없었다!

    난 그냥 AS TBL 구별 *를 선택 임시 테이블 t_tmp을 생성했다; 모든 것이 벌금을했다.

    임시 테이블은 세션의 끝에서 떨어졌다받을 않습니다.

  9. ==============================

    9.이 기능은 제거 인덱스없이 중복을 제거하고 모든 테이블에 않습니다.

    이 기능은 제거 인덱스없이 중복을 제거하고 모든 테이블에 않습니다.

    사용법 : 선택 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;
  10. ==============================

    10.

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

    11.당신은 단지 하나 또는 몇 중복 항목이 있고, 그들이 참으로 복제하는 경우 위의 제안으로, 당신이 함께 LIMIT와의 "숨겨진"CTID 열을 사용할 수 있습니다 (즉, 그들은 두 번 표시) :

    당신은 단지 하나 또는 몇 중복 항목이 있고, 그들이 참으로 복제하는 경우 위의 제안으로, 당신이 함께 LIMIT와의 "숨겨진"CTID 열을 사용할 수 있습니다 (즉, 그들은 두 번 표시) :

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

    이것은 단지 선택된 행의 첫 번째 삭제합니다.

  12. ==============================

    12.첫째, 당신은 당신의 "중복"에있는 당신이 계속됩니다 결정해야합니다. 모든 열이 동일한 경우, OK, 당신은 그들 중 하나를 삭제할 수 있습니다 ...하지만 아마 당신은 가장 최근의, 또는 다른 기준을 유지하려면?

    첫째, 당신은 당신의 "중복"에있는 당신이 계속됩니다 결정해야합니다. 모든 열이 동일한 경우, OK, 당신은 그들 중 하나를 삭제할 수 있습니다 ...하지만 아마 당신은 가장 최근의, 또는 다른 기준을 유지하려면?

    가장 빠른 방법은, 또한 테이블에 중복의 %에 위의 질문에 대한 대답에 따라 달라집니다. 당신이 멀리 행의 50 %가 발생하는 경우 AS DISTINCT SELECT ... 더 나은 표를 작성하고 길 이죠 ... FROM, 및 삭제를 사용하여 행의 1 %를 삭제하면 좋습니다.

    또한이 같은 유지 보수 작업을 위해, 당신의 RAM의 좋은 청크로 설정 work_mem 일반적으로 좋은 : 실행은, EXPLAIN 당신의 RAM / 2 / N. 사용 많은 RAM의에 종류 / 해시 및 설정 work_mem의 수 N을 확인; 그것은 속도 좋다. 만큼 당신이 하나의 동시 연결을 가지고 ...

  13. ==============================

    13.나는 PostgreSQL을 8.4 함께 일하고 있어요. 내가 제안 된 코드를 실행하면, 나는 그것이 아니라는 것을 발견 실제로 중복을 제거. 몇 가지 테스트를 실행에, 나는이 추가 발견 "DISTINCT ON (duplicate_column_name)"및 "duplicate_column_name BY ORDER은"트릭했다. 나는 내가 PostgreSQL의 8.4 SELECT ... DISTINCT 문서에서 이걸 발견, 어떤 SQL 전문가는 아니지만.

    나는 PostgreSQL을 8.4 함께 일하고 있어요. 내가 제안 된 코드를 실행하면, 나는 그것이 아니라는 것을 발견 실제로 중복을 제거. 몇 가지 테스트를 실행에, 나는이 추가 발견 "DISTINCT ON (duplicate_column_name)"및 "duplicate_column_name BY ORDER은"트릭했다. 나는 내가 PostgreSQL의 8.4 SELECT ... DISTINCT 문서에서 이걸 발견, 어떤 SQL 전문가는 아니지만.

    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;
  14. ==============================

    14.이것은 매우 잘 작동하고 매우 빠른입니다 :

    이것은 매우 잘 작동하고 매우 빠른입니다 :

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

    15.

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

    컬럼 (들)과 가장 낮은 ID로 행을 유지하여 삭제 중복. 패턴은 포스트 그레스에서 가져 위키

    열팽창 계수를 사용하면이를 통해 위의 더 읽기 버전을 달성 할 수있다

    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)
  16. ==============================

    16.

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

    17. from https://stackoverflow.com/questions/1746213/how-to-delete-duplicate-entries by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 인덱스의 열 순서는 얼마나 중요합니까?  (0) 2020.03.24
[SQL] SQL은 벤 다이어그램으로 조인  (0) 2020.03.24
[SQL] 어떻게 다른 테이블에 일치하는 항목이있는 행을 선택하려면?  (0) 2020.03.24
[SQL] 2008 스튜디오 SQL 서버 관리에서 모든 테이블을 검색하여 문자열 찾기  (0) 2020.03.24
[SQL] 트리 데이터 구조에 대한 데이터베이스 구조  (0) 2020.03.24
,

티스토리툴바