복붙노트

[SQL] 고유 식별자없이 중복 행을 삭제하는 방법

SQL

고유 식별자없이 중복 행을 삭제하는 방법

내 테이블에 중복 행이 테이블이 큰이기 때문에 나는 가장 효율적인 방법으로 중복을 삭제합니다. 몇 가지 조사 후, 나는이 쿼리와 함께 올라와있다 :

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;

그러나 그것은뿐만 아니라 네티에서, SQL에서 작동합니다. 이 WITH 절 후 DELETE 좋아하지 않는 것처럼 보인다?

해결법

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

    1.@ 어윈 - brandstetter의 솔루션과 같은 I,하지만은 사용 키워드 솔루션을 보여주고 싶었어요 :

    @ 어윈 - brandstetter의 솔루션과 같은 I,하지만은 사용 키워드 솔루션을 보여주고 싶었어요 :

    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;

    당신이 그들을 삭제하기 전에 기록을 검토 할 경우, 단순히 SELECT와 DELETE 대체 * 쉼표 ,, 즉 함께 사용

    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;

    업데이트 : 나는 속도 여기에 다른 솔루션의 일부를 테스트했다. 당신이 많은 중복을 기대하지 않을 경우,이 솔루션이 수행하는 더 나은 것과 같은 NOT IN (...) 절을 가지고있는 것보다 서브 쿼리의 행을 많이 생성합니다.

    쿼리를 다시 작성하는 경우 솔루션이 여기에 제시된에 (...)이 다음은 유사하게 수행 사용하지만, SQL 코드는 훨씬 간결하게합니다.

    2 업데이트 : 당신이 (당신이 진짜로 IMO 안 함), 당신은 그 열, 예를 들어,의 상태 COALESCE ()를 사용할 수있는 키 열 중 하나에 NULL 값이있는 경우

      AND COALESCE(T1.col_with_nulls, '[NULL]') = COALESCE(T2.col_with_nulls, '[NULL]')
  2. ==============================

    2.당신이 다른 고유 식별자가없는 경우 CTID를 사용할 수 있습니다 :

    당신이 다른 고유 식별자가없는 경우 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
                 );

    모든 테이블에 고유 한 자동 증가 ID를 가질 수있는 좋은 아이디어이다. 이 같은 삭제를하는 것은 하나의 중요한 이유입니다.

  3. ==============================

    3.완벽한 세계에서, 모든 테이블은 어떤 종류의 고유 한 식별자를 가지고있다. 고유 한 열 (또는 이들의 조합)의 부재에서, CTID 컬럼을 사용

    완벽한 세계에서, 모든 테이블은 어떤 종류의 고유 한 식별자를 가지고있다. 고유 한 열 (또는 이들의 조합)의 부재에서, CTID 컬럼을 사용

    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

    위의 쿼리는 편리 한 번만 열 이름을 나열 짧습니다. NOT IN (SELECT는 ...) NULL 값을 포함 할 수있는 복잡한 쿼리 스타일이지만, 시스템 열 CTID는 NULL 결코 없다. 보다:

    같은 @Gordon에 의해 입증 EXISTS 사용하는 것은 일반적으로 빠르다. 그래서입니다 나중에 추가 @isapir 같은 USING 절과 자체 조인. 모두 같은 쿼리 계획을 초래할 것이다.

    그러나 중요한 차이를주의 : 동일하지 이러한 다른 쿼리가 같은 것으로, 동안 GROUP BY (또는 DISTINCT 또는 DISTINCT ON ()) 취급 NULL 값을 NULL 값을 처리합니다. 키 열이 NULL을 NOT 정의 된 경우 문제가되지 않습니다. 그밖에, "복제"의 당신의 정의에 따라, 당신은 하나 또는 다른 접근 방식이 필요합니다. 또는 사용 (일부 인덱스를 사용하지 못할 수 있습니다) 값의 비교는 구별을지지 않습니다.

    기권:

    CTID IT는 SQL 표준에없는과 (즉, 매우 가능성이 경우에도) 경고없이 주요 버전 사이에서 변경 될 수 있습니다, 포스트 그레스의 내부 구현 세부입니다. 그 값은 (그러나 같은 명령 내)으로 인한 백그라운드 프로세스 또는 동시 기록 동작 명령 사이에서 변경할 수있다.

    관련 :

    곁에:

    DELETE 문의 대상이 CTE 만 기본 테이블이 될 수 없습니다. 로 전체 접근 방식 - 즉 SQL Server에서 유출입니다.

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

    4.여기에 내가 그룹에 의해를 사용 해낸 것입니다

    여기에 내가 그룹에 의해를 사용 해낸 것입니다

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

    그것은 중복이 가장 오래된 기록을 보존 중복을 삭제합니다.

  5. ==============================

    5.우리는 중복 행의 매우 효과적인 제거를 위해 창 기능을 사용할 수 있습니다 :

    우리는 중복 행의 매우 효과적인 제거를 위해 창 기능을 사용할 수 있습니다 :

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

    (CTID와) 일부의 PostgreSQL의 최적화 된 버전 :

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

    6.당신은 테이블에 중복 행 한 행을 유지합니다.

    당신은 테이블에 중복 행 한 행을 유지합니다.

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

    이것은 당신이 복사 할 수 있습니다 테이블을 생성합니다.

    테이블을 복사하기 전에 열 'row_n'을 삭제하시기 바랍니다

  7. ==============================

    7.유효한 구문은 http://www.postgresql.org/docs/current/static/sql-delete.html에서 지정

    유효한 구문은 http://www.postgresql.org/docs/current/static/sql-delete.html에서 지정

    나는 당신이 (즉, 가장 낮은 ID를 가진 일) 중복의 각 세트의 첫 번째를 유지합니다 다음과 같은 쿼리를 실행할 수 있도록 고유 한 자동 증가 기본 키 ID를 추가 할 테이블을 변경합니다. 키를 추가하는 것은 좀 더 다른 데시벨보다 포스트 그레스에 복잡합니다.

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

    8.문서에서 중복 행을 삭제

    문서에서 중복 행을 삭제

    IRC에서 자주 질문은 가장 낮은 ID 만 하나를 유지하는 열 집합에 걸쳐 중복되는 행을 삭제하는 방법입니다. 이 쿼리는 TABLENAME의 모든 행에 대해 동일한 1 열, 2 열 및 3 열을 갖는 않습니다.

    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 필드 사용됩니다.

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

    9.당신이 모든 행에 대한 고유 식별자를 원하는 경우에, 당신은 단지 하나 (직렬 또는 GUID)를 추가하고, 대리 키처럼 취급 할 수있다.

    당신이 모든 행에 대한 고유 식별자를 원하는 경우에, 당신은 단지 하나 (직렬 또는 GUID)를 추가하고, 대리 키처럼 취급 할 수있다.

    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;

    10. from https://stackoverflow.com/questions/26769454/how-to-delete-duplicate-rows-without-unique-identifier by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 어떻게 특정 문자까지 오라클 SQL에서 문자열을 선택하려면?  (0) 2020.04.29
[SQL] 또 다른 하나 개의 테이블 일치 조건에서 선택?  (0) 2020.04.29
[SQL] SQL 서버 관리 스튜디오를 사용하여 데이터베이스로 BLOB를 삽입하는 방법  (0) 2020.04.29
[SQL] SQL 서버의 모든 데이터베이스의 모든 테이블에 대한 열 이름을 찾는 방법  (0) 2020.04.29
[SQL] PostgreSQL의에서 어떤 연산자 대 IN  (0) 2020.04.29
,

티스토리툴바