소스: sql-src/work_05_dedup/01_detect.sql. 회원 표 member 는 14행입니다. 1·2번은 완전 중복이고, 3번은 1번과 전화·이메일 모양만 다르며, 이메일이 NULL 인 회원이 5명(7, 8, 11, 13, 14)입니다.
3번의 이메일은 앞에 공백이 있고, 9번의 이메일은 뒤에 공백이 있습니다. 결과 표에서는 눈에 잘 띄지 않습니다.
SELECT
phone
, COUNT(*) AS cnt
, MIN(id) AS keep_id
FROM member
GROUP BY phone
HAVING COUNT(*) > 1
ORDER BY phone;PHONE | CNT | KEEP_ID
--------------+-----+--------
010-1111-2222 | 2 | 1
010-3333-4444 | 2 | 4
010-5656-7878 | 2 | 13
(3행)문자열이 정확히 같은 번호만 잡힙니다. 3번 회원의 01011112222 는 하이픈이 없어서 다른 값입니다. MIN(id) 는 나중에 남길 행을 미리 보는 용도입니다.
SELECT
email
, COUNT(*) AS cnt
FROM member
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY email;EMAIL | CNT
----------+----
NULL | 5
kim@a.com | 2
(2행)NULL 그룹이 5건으로 나옵니다. 이 5명은 서로 다른 사람이라 중복이 아닙니다. 이메일 하나만 키로 삼아 지우면 실제로는 서로 다른 회원이 사라집니다.
주의이메일이 선택 입력이면 NULL 이 많습니다. 탐지 쿼리에
WHERE email IS NOT NULL을 넣거나 이름·전화 같은 다른 컬럼을 키에 더합니다.
이름, 전화, 이메일이 모두 같은 행을 찾습니다.
SELECT
name
, phone
, email
, COUNT(*) AS cnt
FROM member
GROUP BY name, phone, email
HAVING COUNT(*) > 1
ORDER BY name;NAME | PHONE | EMAIL | CNT
-------+---------------+-----------+----
김대표 | 010-1111-2222 | kim@a.com | 2
남총무 | 010-5656-7878 | NULL | 2
(2행)남총무의 이메일은 NULL 인데도 한 그룹으로 묶여 잡힙니다. 두 행이 이름·전화가 같고 이메일이 모두 비어 있으므로 사실상 중복입니다. 어느 행들인지 한꺼번에 보려면 윈도 함수를 씁니다.
SELECT
id
, name
, phone
, email
, joined_at
FROM (
SELECT
id
, name
, phone
, email
, joined_at
, COUNT(*) OVER (PARTITION BY phone) AS cnt
FROM member
) t
WHERE cnt > 1
ORDER BY phone, id;ID | NAME | PHONE | EMAIL | JOINED_AT
---+--------+---------------+-----------+-----------
1 | 김대표 | 010-1111-2222 | kim@a.com | 2024-01-10
2 | 김대표 | 010-1111-2222 | kim@a.com | 2024-01-10
4 | 이개발 | 010-3333-4444 | lee@a.com | 2024-02-01
5 | 이개발 | 010-3333-4444 | LEE@A.COM | 2024-02-20
13 | 남총무 | 010-5656-7878 | NULL | 2024-05-15
14 | 남총무 | 010-5656-7878 | NULL | 2024-06-01
(6행)GROUP BY 는 그룹당 한 줄이지만 이 쿼리는 중복 행을 모두 보여 줍니다. 어느 행을 남길지 사람이 판단하기에 좋습니다. 4번과 5번은 이메일 대소문자가 다르고 가입일도 다릅니다.
전화번호에서 하이픈을 뺀 키로 묶습니다.
SELECT
REPLACE(phone, '-', '') AS phone_key
, COUNT(*) AS cnt
, MIN(id) AS keep_id
FROM member
GROUP BY REPLACE(phone, '-', '')
HAVING COUNT(*) > 1
ORDER BY phone_key;PHONE_KEY | CNT | KEEP_ID
------------+-----+--------
01011112222 | 3 | 1
01012123434 | 2 | 9
01033334444 | 2 | 4
01056567878 | 2 | 13
(4행)예제 1 은 3건이었는데 이제 4건이 나옵니다. 김대표는 2건에서 3건으로 늘고 한디자인(9, 10번)이 새로 잡혔습니다.
이메일은 LOWER(TRIM(email)) 로 정규화하고 WHERE email IS NOT NULL 로 NULL 을 제외해 묶습니다. 결과는 han@a.com 2건, kim@a.com 3건, lee@a.com 2건이고 전화 키와 같은 그룹을 가리킵니다. 두 키가 모두 같으면 확실한 중복이고, 하나만 같으면 사람이 확인합니다.
COUNT(*) 는 14, COUNT(email) 은 NULL 을 빼서 9입니다(소스 9번 구간). 같은 컬럼을 자기 조인으로 비교하면 어떨까요.
SELECT
COUNT(*) AS pair_cnt
FROM member a
JOIN member b ON b.email = a.email
AND a.id < b.id;PAIR_CNT
--------
1
(1행)짝은 1과 2번의 한 쌍뿐입니다. GROUP BY 에서는 NULL 5건이 한 그룹으로 나왔지만 = 로는 NULL 끼리 짝이 되지 않습니다. 자기 조인으로 중복을 찾는 방식은 NULL 이 있는 컬럼에서 행을 놓칩니다.