SQL로 중복 데이터 확인 및 삭제하기

SQL로 중복 데이터를 확인 및 삭제하는 법을 알아본다.

사용 데이터

id	first_name	last_name	email
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	email	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

DB Fiddle - MySQL 5 에서 확인

중복 데이터 삭제

중복 데이터를 삭제하기 위해서는 먼저 남길 데이터를 제외한 데이터를 구분할 컬럼(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	email	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

DB Fiddle - MySQL 8 에서 확인

데이터 삭제 쿼리

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 ) a
             WHERE row_num > 1);

MySQL 5

중복 데이터 확인 쿼리에 DISTINCT를 추가해서 활용했다.

삭제할 데이터 id 확인

-- IN subquery 버전
SELECT *
FROM contacts
WHERE (first_name, last_name, email) IN ( SELECT DISTINCT first_name, last_name, email
                                          FROM contacts
                                          GROUP BY email, first_name, last_name
                                          HAVING COUNT(email) > 1 AND COUNT(first_name) > 1 
                                                 AND COUNT(last_name) > 1)
ORDER BY first_name;

-- JOIN 버전
SELECT a.*
FROM contacts a
JOIN (SELECT DISTINCT first_name, last_name, email
      FROM contacts
      GROUP BY email, first_name, last_name
      HAVING COUNT(email) > 1 AND COUNT(first_name) > 1 AND COUNT(last_name) > 1) b
ON a.first_name=b.first_name AND a.last_name=b.last_name AND a.email=b.email
ORDER BY a.first_name;

DB Fiddle - MySQL 5 에서 확인

데이터 삭제 쿼리

MIN 함수를 활용해서 남길 데이터를 제외한 id를 NOT IN으로 처리하였다.

참고 : MySQL은 DELETE 시 자체 데이터를 바로 사용 못하는 이유로 아래 Error reference를 참고했다.

DELETE FROM contacts
WHERE id NOT IN ( SELECT *
                  FROM (SELECT MIN(id)
                        FROM contacts
                        WHERE (first_name,last_name,email) IN ( SELECT DISTINCT first_name, last_name, email
                                                                FROM contacts
                                                                GROUP BY email, first_name, last_name
                                                                HAVING COUNT(email) > 1 AND COUNT(first_name) > 1 
                                                                       AND COUNT(last_name) > 1)
                        GROUP BY first_name, last_name, email) tmp);

References :