DB의 중복 값 지우기

한 테이블에 유니크조건을 추가하고 싶은데, 기존 데이터가 유니크조건에 위배되어 줄 수 없는 상황이었다. 굳이 유니크조건을 주지 않고 처리하는 등 해결방법은 많았지만, 잘못된 설계로 id만 다른 값이 많이 들어가 있었으므로, 중복된 값을 제거해주는 방향을 택했다.

중복된 데이터 예시

my_table

id | col1 | col2 | col3 | value
1 | a | 가 | v1 | 100
2 | a | 가 | v1 | 90
3 | b | 가 | v1 | 100
4 | b | 가 | v1 | 100
5 | a | 나 | v1 | 100
6 | a | 나 | v1 | 100
7 | a | 나 |  | 100
8 | a | 나 |  | 100

위 테이블을 보면 id만 다르고 똑같은 값이 두 개씩 들어있다.

요구사항

이를 다음과 같이 id가 큰 값만 남겨두고 중복되는 것은 모두 삭제하고 싶다.

반영된 요구사항 예시

my_table

id | col1 | col2 | col3 | value
2 | a | 가 | v1 | 90
4 | b | 가 | v1 | 100
6 | a | 나 | v1 | 100
8 | a | 나 |  | 100

STEP 1. 중복 기준을 정하기

col1, col2, col3에 유니크 조건으로 처리하려 한다.

STEP 2. GROUP BY와 COUNT를 이용하여 중복 여부 확인

SELECT col1, col2, col3, COUNT(id) AS cnt FROM my_table GROUP BY col1, col2, col3;

col1 | col2 | col3 | cnt
a | 가 | v1 | 2
b | 가 | v1 | 2
a | 나 | v1 | 2
a | 나 |  | 2

STEP 3. 삭제하기

DBMS마다 차이는 있겠지만, postgres의 경우 USING을 사용하여 편하게 처리할 수 있다.

DELETE FROM my_table AS a
              USING my_table AS b
              WHERE a.id < b.id
                   AND a.col1 = b.col1
                   AND a.col2 = b.col2
                   AND (a.col3 is null AND b.col3 is null OR a.col3 = b.col3);  # 변수에 null이 들어간 경우 '='연산자로 제대로 비교되지 않는다

STEP 4. 확인하기

SELECT * FROM my_table;

id | col1 | col2 | col3 | value
2 | a | 가 | v1 | 90
4 | b | 가 | v1 | 100
6 | a | 나 | v1 | 100
8 | a | 나 |  | 100

맞는지 확인은 꼭 하는 것이 좋다.