Postgres SQL에서 중복을 제거하는 방법

---

내 블로그에 교차 게시됨
여기에서 읽어보실 수 있습니다

---

우리의 스키마

create table "post" (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  content TEXT NOT NULL
);

create table "user" (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL
)

create table "post_like" (
  id SERIAL PRIMARY KEY,
  post_id INTEGER NOT NULL REFERENCES post(id),
  user_id INTEGER NOT NULL REFERENCES user(id)
)

이제 각 사용자가 동일한 게시물을 두 번 이상 좋아할 수 없도록 하고 싶습니다.
이는 다음을 통해 예방할 수 있습니다.

• post_like 테이블의 post_id user_id 열 쌍에 고유 제약 조건을 사용합니다.
• 또는 post_like 테이블의 id 열을 제거하고 post_id user_id에 복합 기본 키를 사용합니다.

그러나 중복 항목이 이미 존재하는 시점에 있다고 가정하면 해당 항목을 제거해야 합니다.

중복이 있는지 확인하세요

select 
  post_id, 
  user_id,
  count(*)
from post_like
group by post_id, user_id
having count(*) > 2
;

| post_id | user_id | count |
| ------- | ------- | ----- |
| 3       | 2       | 2     |

이 출력은 사용자 2가 게시물 3을 두 번 이상, 구체적으로 2번 좋아요를 누른 것을 나타냅니다.

중복 제거

이제 중복 항목이 있음을 알았으므로 제거할 수 있습니다.

이 프로세스는 두 단계로 나뉩니다.

• 중복 읽기
• 중복 항목 제거(모의 실행)
• 중복 제거(실제 실행)

중복 읽기

거래 롤백

실제 데이터를 제거하지 않고 쿼리를 테스트하기 위해 쿼리가 올바른지 확인할 때까지 트랜잭션 롤백 기능을 사용합니다.

이렇게 하면 쿼리가 커밋되지 않으며
다른 애플리케이션(예:
)에서 찾을 수 있는 "모의 실행" 개념 rsync).

CTE

CTE는 좋은 DX를 제공하기 때문에 사용합니다.

CTE를 사용하면 쿼리를 실행하고 결과를 임시 테이블에 저장한 후 후속 쿼리에 동일한 테이블을 사용할 수 있습니다.

이 정신 모델은 임시 변수를 생성하여 코딩할 때 일반적으로 수행하는 작업과 유사합니다.

CTE 구문은

 with 
 <cte_name> as (
   <query>
 ),
 <cte_name_2> as (
   <query_2> -- here we can refernce <cte_name>
 )
 <final_query> -- here we can refernce <cte_name> and <cte_name_2>

트랜잭션과 CTE를 모두 사용하여 다음을 수행할 수 있습니다.

begin; -- start transaction

with
duplicates_info as (
  select
    row_number() over (
      partition by post_id, user_id order by user_id
    ) as group_index,
    id,
    post_id,
    user_id
  from post_like
)
select *
from duplicates_info
;

rollback; -- ends transaction discarding every changes to the database 

| group_index | id | post_id | user_id |
| ----------- | -- | ------- | ------- |
| 1           | 1  | 1       | 1       |
| 1           | 2  | 2       | 2       |
| 1           | 3  | 3       | 2       |
| 2           | 4  | 3       | 2       |

group_index가 2인 결과의 최신 행은 이 행이 post_id = 3이고 user_id = 2인 그룹에서 두 번째 행임을 의미합니다.

구문은 어떻게 되나요?

row_number() over (partition by ...) as group_index는 먼저 partition by 절의 열을 기준으로 행을 그룹화한 다음 행의 인덱스를 기준으로 각 행에 숫자를 할당하는 창 함수입니다. 그룹에서.

partition은 공통 열을 기준으로 행을 그룹화한다는 점에서 group by와 유사하지만, group by가 각 그룹에 대해 1개의 행만 반환하는 경우 partition을 사용하면 그룹을 기반으로 소스 테이블에 새 열을 추가할 수 있습니다.

group_index는 열 이름 별칭, 일반 SQL 구문입니다.

중복된 ​​항목만 필터링

이제 group_index > 1은 해당 행이 그룹의 첫 번째 행이 아니라는 의미, 즉 중복 행이라는 의미입니다.

create table "post" (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  content TEXT NOT NULL
);

create table "user" (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL
)

create table "post_like" (
  id SERIAL PRIMARY KEY,
  post_id INTEGER NOT NULL REFERENCES post(id),
  user_id INTEGER NOT NULL REFERENCES user(id)
)

select 
  post_id, 
  user_id,
  count(*)
from post_like
group by post_id, user_id
having count(*) > 2
;

ID가 4인 이 행만 제거해야 합니다.

중복 제거 - 테스트 실행

이제 더 이상 cte Duplicates_info에서 읽지 않고 post_like 테이블에서 읽도록 최종 쿼리를 다시 작성합니다.
우리는 여전히 cte Duplicates_info를 사용하여 중복 항목의 ID를 얻습니다.

| post_id | user_id | count |
| ------- | ------- | ----- |
| 3       | 2       | 2     |

삭제하려는 기록이 표시됩니다.

올바른지 확인한 후 선택을 삭제로 바꿉니다.

 with 
 <cte_name> as (
   <query>
 ),
 <cte_name_2> as (
   <query_2> -- here we can refernce <cte_name>
 )
 <final_query> -- here we can refernce <cte_name> and <cte_name_2>

이 마지막 쿼리가 우리가 최종적으로 실행하려는 쿼리입니다.

하지만 아직 롤백 문이 있기 때문에 이러한 변경 사항은 시뮬레이션되고 데이터베이스에는 적용되지 않습니다.

중복 제거 - 실제 실행

드디어 실제로 중복 항목을 제거할 수 있습니다.
여기서는 롤백 대신 커밋을 사용하여 변경 사항이 데이터베이스에 적용되도록 합니다.

begin; -- start transaction

with
duplicates_info as (
  select
    row_number() over (
      partition by post_id, user_id order by user_id
    ) as group_index,
    id,
    post_id,
    user_id
  from post_like
)
select *
from duplicates_info
;

rollback; -- ends transaction discarding every changes to the database 

최종 코드

| group_index | id | post_id | user_id |
| ----------- | -- | ------- | ------- |
| 1           | 1  | 1       | 1       |
| 1           | 2  | 2       | 2       |
| 1           | 3  | 3       | 2       |
| 2           | 4  | 3       | 2       |

결론

나는 주로 내 미래에 도움이 되거나 작업에 사용하는 도구의 성장을 돕기 위해 기사를 씁니다.

이 글이 도움이 되셨다면 좋아요를 남겨주세요

특정 주제에 대해 이야기하고 싶으신가요?

댓글로 알려주세요!

위 내용은 Postgres SQL에서 중복을 제거하는 방법의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!