programing

작은 테이블에서 중복 행 삭제

lovejava 2023. 5. 9. 21:57

작은 테이블에서 중복 행 삭제

저는 Postgre에 자리가 있습니다.SQL 8.3.8 데이터베이스에 키/제약 조건이 없고 값이 정확히 동일한 여러 행이 있습니다.

저는 모든 중복을 제거하고 각 행의 복사본을 1개만 보관하고 싶습니다.

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

어떻게 해야 합니까? (단일 SQL 명령으로 하는 것이 이상적입니다.)
이 경우 속도는 문제가 되지 않습니다(몇 행만 있음).

더 빠른 솔루션은

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 a
WHERE a.ctid <> (SELECT min(b.ctid)
                 FROM   dupes b
                 WHERE  a.key = b.key);

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

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

자세한 내용이 포함된 고유 식별자 없이 중복 행을 삭제하는 방법에 대한 답변도 참조하십시오.

EXISTS 단순하고 대부분의 데이터 배포 속도가 가장 빠릅니다.

DELETE FROM dupes d
WHERE  EXISTS (
   SELECT FROM dupes
   WHERE  key = d.key
   AND    ctid < d.ctid
   );

된 각 행 에서 (동일한 으로 정의됨)key , , 를 한이 유지됩니다.ctid.

결과는 a_horse가 현재 수락한 답변과 동일합니다.더 빨리, 왜냐하면EXISTS는 첫 할 수 , 으로 사용할 수 있는 경우는 다음과 같습니다.min()에서는 최소값을 계산하기 위해 그룹당 모든 행을 고려해야 합니다.속도는 이 문제와 무관하지만, 왜 그렇게 생각하지 않습니까?

정리 후 중복이 다시 슬금슬금 들어오는 것을 방지하기 위해 제약 조건을 추가할 수 있습니다.

ALTER TABLE dupes ADD CONSTRAINT constraint_name_here UNIQUE (key);

시스템 열 정보:

정의된 UNIQUE NOT NULL예: " " ")PRIMARY KEY) 그렇다면, 어떻게든, 대신에 그것을 사용하세요.ctid.

한다면key될 수 있고 당신은 그것들 중 하나만 사용하기를 원합니다.= 항목

속도가 느리기 때문에 위의 쿼리를 그대로 실행할 수 있으며 추가로 다음을 수행할 수 있습니다.

DELETE FROM dupes d
WHERE  key IS NULL
AND    EXISTS (
   SELECT FROM dupes
   WHERE  key IS NULL
   AND    ctid < d.ctid
   );

다음을 고려해 보십시오.

작은 테이블의 경우 인덱스는 일반적으로 성능에 도움이 되지 않습니다.그리고 우리는 더 이상 볼 필요가 없습니다.

큰 테이블과 적은 수의 중복의 경우 기존 인덱스(key)도움이 될 수 있습니다

대부분의 중복 항목의 경우 인덱스를 동시에 최신 상태로 유지해야 하므로 이점보다 비용이 더 많이 들 수 있습니다.인덱스 없이 중복 항목을 찾는 것은 매우 많기 때문에 더 빨라집니다.EXISTS하나만 찾으면 됩니다.그러나 경제적인 여유가 있다면 완전히 다른 접근 방식을 고려해 보십시오(즉, 동시 액세스가 허용됨).남은 몇 개의 행을 새 테이블에 씁니다.또한 프로세스의 테이블(및 인덱스) 번짐도 제거합니다.참조:

시도해 봤습니다.

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 Wiki에서 제공:

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

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

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

다음 합니다.tab 이름을 변경합니다.tab_temp안으로tab.

저는 저만의 버전을 만들어야 했습니다.@a_horse_with_no_name이(가) 작성한 버전이 테이블에서 너무 느립니다(2,100만 행).그리고 @rapimo는 단순히 dups를 삭제하지 않습니다.

Postgre에서 사용하는 것은 다음과 같습니다.SQL 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);

다른 를 들어, 다른접다방식근작경있( 에는드가유한은과음다▁like▁field작)▁another▁(▁only▁you동만경에우(▁have▁any있▁if)과 같은 고유한 필드가 때만 작동합니다.id 열별로 ID를 목록에합니다.

DELETE
FROM users
WHERE users.id NOT IN (SELECT DISTINCT ON (username, email) id FROM users);

Postgresql에는 창 기능이 있습니다. rank()를 사용하여 목표를 보관할 수 있습니다. 샘플:

WITH ranked as (
    SELECT
        id, column1,
        "rank" () OVER (
            PARTITION BY column1
            order by column1 asc
        ) AS r
    FROM
        table1
) 
delete from table1 t1
using ranked
where t1.id = ranked.id and ranked.r > 1

여기 저에게 효과가 있었던 또 다른 해결책이 있습니다.

delete from table_name a using table_name b
where a.id < b.id
  and a.column1 = b.column1;

어때요?

와 함께uAS(_table에서 * 구분하여 선택),x AS(_테이블에서 삭제)_table SELECT에 삽입 * From you;

SELECT DISTINCT 이전에 DELETE가 발생할까 걱정했는데, 저는 잘 됩니다.그리고 테이블 구조에 대한 지식이 필요하지 않다는 추가적인 이점이 있습니다.

다음은 와 가상 열을 사용하는 솔루션입니다. 가상 열은 적어도 단일 세션 내에서 기본 키처럼 작동합니다.

DELETE FROM dups
USING (
  SELECT
    ctid,
    (
      ctid != min(ctid) OVER (PARTITION BY key_column1, key_column2 [...])
    ) AS is_duplicate
  FROM dups 
) dups_find_duplicates
WHERE dups.ctid == dups_find_duplicates.ctid
AND dups_find_duplicates.is_duplicate

한 "키 열"을 "키 열"을 공유하는지하는 데 됩니다.ctid동일한 키를 공유하는 행의 "슬롯"에서 발견되는 "첫 번째" 키입니다.

즉, "first"는 다음과 같이 정의됩니다.

  • min(ctid) OVER (PARTITION BY key_column1, key_column2 [...])

모든 에서 그런다음, 위행든모(치)가 나옵니다.is_duplicate는 해당 해항의는것삭은제된해(에) true에 됩니다.ctid.

설명서에서 는 다음을 나타냅니다(광산 강조).

테이블 내 행 버전의 실제 위치입니다.ctid를 사용하여 행 버전을 매우 빠르게 찾을 수 있지만, VUCUM FULL을 사용하여 업데이트하거나 이동하면 행의 ctid가 변경됩니다. 따라서 ctid는 장기 행 식별자로 사용할 수 없습니다.기본 키는 논리적 행을 식별하는 데 사용해야 합니다.

제 사용 사례인 ID가 중복되면 이 솔루션 중 어떤 것도 작동하지 않을 것입니다. 그렇다면 솔루션은 간단합니다.

myTable:
id  name
0   value
0   value
0   value
1   value1
1   value1

create dedupMyTable as select distinct * from myTable;
delete from myTable;
insert into myTable select * from dedupMyTable;

select * from myTable;
id  name
0   value
1   value1

테이블에 PK 제약 조건이 없거나 하이브/데이터 레이크 테이블과 같이 단순히 지원하지 않는 경우가 아니라면 테이블에 ID를 복제해서는 안 됩니다.

ID에 대한 중복을 방지하기 위해 데이터를 로드할 때 주의를 기울이는 것이 좋습니다.

DELETE FROM tracking_order 
WHERE 
    mvd_id IN (---column you need to remove duplicate
        SELECT 
            mvd_id 
        FROM (
            SELECT                         
                mvd_id,thoi_gian_gui,
                ROW_NUMBER() OVER (
                    PARTITION BY mvd_id
                    ORDER BY thoi_gian_gui desc) AS row_num
            FROM 
                tracking_order
        ) s_alias
        WHERE row_num > 1)
    AND thoi_gian_gui in ( --column you used to compare to delete duplicates, eg last update time
        SELECT 
                thoi_gian_gui 
        FROM (
            SELECT                         
                thoi_gian_gui,
                ROW_NUMBER() OVER (
                    PARTITION BY mvd_id
                    ORDER BY thoi_gian_gui desc) AS row_num
            FROM 
                tracking_order
        ) s_alias
        WHERE row_num > 1)

제 코드는 7800445 행의 중복을 모두 제거하고 7분 28초 동안 각 행의 복사본을 1개만 보관합니다.여기에 이미지 설명 입력

이것은 저에게 잘 통했습니다.저는 중복된 값이 포함된 표, 용어를 가지고 있었습니다.쿼리를 실행하여 모든 중복 행으로 임시 테이블을 채웠습니다.그런 다음 임시 테이블에 있는 ID로 삭제 문을 실행했습니다.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)

언급URL : https://stackoverflow.com/questions/6583916/delete-duplicate-rows-from-small-table

'programing' 카테고리의 다른 글

Swift에서 문자열에 다른 문자열이 포함되어 있는지 확인하려면 어떻게 해야 합니까?  (0) 2023.05.09
Xcode 4로 업그레이드한 후 파일 경고 누락  (0) 2023.05.09
.gitignore를 사용하지 않고 추적되지 않은 파일이 표시되지 않도록 'git status'를 어떻게 해야 합니까?  (0) 2023.05.04
날짜별 Java/MongoDB 쿼리  (0) 2023.05.04
UIScene에서 탈퇴 선택(위임자/스위프트)iOS의 UI  (0) 2023.05.04

'programing'의 다른글

  • 현재글작은 테이블에서 중복 행 삭제

관련글

댓글

티스토리툴바