[SQL]데이터 검증 - 중복 데이터 제거.

SQL 데이터 검증. 중복 데이터 제거

중복된 데이터를 찾고/ 그 데이터의 기본키를 찾아 /기본키를 조건으로 해당 데이터 삭제. 

1. 중복 데이터 찾기.

찾고자하는 데이터 칼럼을 그룹별로 나누어 조회하면 같은것끼리 묶여 그룹지어진다. 이때, 데이터 수를 COUNT()할때, 중복이 아니면 1개가, 중복인 경우 1개 이상이 된다. 그룹에 대한 조건인 HAVING 을 사용하여 COUNT한 값이 1이상인 데이터만 조회하면 중복인 데이터들만 조회할 수 있다. 

예) customer 테이블에서 중복된 id를 구한다고 가정. (PK: cus_no)

SELECT id, COUNT(id) FROM customer 

GROUP BY id

HAVING COUNT(id) > 1; // 그룹에 대한 조건.HAVING 

2. 중복 데이터의 기본키(PK)값 찾기.

중복데이터의 PK값을 찾아서 같은 값끼리의 순위를 매긴다.

SELECT cus_no, 

IF(@id=id, @rownum:=@rownum+1, @rownum=1) as rnum,

(@id:=id) as id 

FROM

(SELECT cus_no , id FROM customer

WHERE id IN (SELECT id FROM customer GROUP BY id HAVING COUNT(id)>1)

)a,

(SELECT @rownum:=1, @id:=' ' FROM DUAL) b

)q ; 

WHERE rnum NOT IN(1) // 순위중 1인것 빼고 다 삭제-> 중복값의 첫번째값을 제외하고 나머지값들이 남는다. 즉, 중복된 삭제될 데이터들만 남는다.

3. 찾은 기본키를 조건으로 해당 데이터를 삭제.

기본키가 다음과 같은(앞에서 구한 삭제할 데이터) 데이터들을 삭제하라.

DELETE FROM customer

WHERE cus_no IN (

SELECT cus_no, 

IF(@id=id, @rownum:=@rownum+1, @rownum=1) as rnum,

(@id:=id) as id 

FROM

(SELECT cus_no , id FROM customer

WHERE id IN (SELECT id FROM customer GROUP BY id HAVING     COUNT(id)>1)

)a,

(SELECT @rownum:=1, @id:=' ' FROM DUAL) b

)q

);