SQL로 중복 데이터 확인 및 삭제하기
게시일 : 2019년 09월 08일
수정일 : 2020년 08월 06일
# PostgreSQL
# MySQL
# duplicates
# DELETE JOIN
SQL로 중복 데이터를 확인 및 삭제하는 법을 알아본다.
사용 데이터
id | first_name | last_name | |
---|---|---|---|
1 | Carine | Schmitt | carine.schmitt@verizon.net |
2 | Jean | King | jean.king@me.com |
3 | Peter | Ferguson | peter.ferguson@google.com |
4 | Janine | Labrune | janine.labrune@aol.com |
5 | Susan | Nelson | susan.nelson@comcast.net |
.. | … | … | … |
출처 : MYSQLTUTORIAL (아래 Reference에 명시)
중복 데이터 확인
공통
GROUP BY와 HAVING을 사용하면 된다. 중복을 확인할 컬럼의 수에 따라 조건을 추가해주면 된다.
SELECT first_name, last_name, email, COUNT(*) as cnt
FROM contacts
GROUP BY email, first_name, last_name
HAVING COUNT(email) > 1 AND COUNT(first_name) > 1 AND COUNT(last_name) > 1;
first_name | last_name | cnt | |
---|---|---|---|
Janine | Labrune | janine.labrune@aol.com | 4 |
Jean | King | jean.king@me.com | 2 |
Roland | Keitel | roland.keitel@yahoo.com | 3 |
Susan | Nelson | susan.nelson@comcast.net | 3 |
중복 데이터 삭제
중복 데이터를 삭제하기 위해서는 먼저 남길 데이터를 제외한 데이터를 구분할 컬럼(e.g. id)의 값을 구해야 한다.
PostgreSQL & MySQL 8
WINDOW FUNCTION을 지원하는 경우, ROW_NUMBER를 활용하여 처리할 수 있다.
PARTITION BY로 중복된 컬럼을 지정하고, ROW_NUM > 1을 설정하여 삭제할 데이터만 가져올 수 있다.
삭제할 데이터 id 확인
SELECT *
FROM ( SELECT id, first_name, last_name, email,
ROW_NUMBER() OVER (PARTITION BY first_name, last_name, email) as row_num
FROM contacts ) a
WHERE row_num > 1
ORDER BY first_name; -- 단순 정렬 용도
id | first_name | last_name | row_num | |
---|---|---|---|---|
7 | Janine | Labrune | janine.labrune@aol.com | 2 |
9 | Janine | Labrune | janine.labrune@aol.com | 3 |
13 | Janine | Labrune | janine.labrune@aol.com | 4 |
15 | Jean | King | jean.king@me.com | 2 |
17 | Roland | Keitel | roland.keitel@yahoo.com | 2 |
18 | Roland | Keitel | roland.keitel@yahoo.com | 3 |
8 | Susan | Nelson | susan.nelson@comcast.net | 2 |
16 | Susan | Nelson | susan.nelson@comcast.net | 3 |
데이터 삭제 쿼리
DELETE FROM contacts
WHERE id IN (SELECT id
FROM (
SELECT id, ROW_NUMBER() OVER (PARTITION BY first_name, last_name, email) as row_num
FROM contacts
) tmp
WHERE row_num > 1);
MySQL 5
삭제할 데이터 id 확인
SELF JOIN을 통해 중복된 row의 id를 확인하는 방법이지만 결과를 보면 데이터가 오히려 중복되어 더 생기는 현상이 있다. (삭제 쿼리에 활용할 경우, 문제는 없어보임)
SELECT t2.*
FROM contacts t1 JOIN contacts t2
ON t1.first_name=t2.first_name AND t1.last_name=t2.last_name AND t1.email=t2.email
WHERE t1.id < t2.id;
데이터 삭제 쿼리
MySQL에서 지원하는 DELETE JOIN 방식을 활용한다.
DELETE t2 FROM contacts t1
JOIN contacts t2
ON t1.first_name=t2.first_name AND t1.last_name=t2.last_name AND t1.email=t2.email
WHERE t1.id < t2.id;
References
: