Q. 테이블에서 중복 레코드를 삭제하려고 한다

/* 예제 테이블 및 레코드 생성 */
create table dupes (id integer, name varchar(10));
>>
Table created.

insert into dupes values(1, 'NAPOLEON');
>>
1 row(s) inserted.

insert into dupes values(2, 'DYNAMITE');
>>
1 row(s) inserted.

insert into dupes values(3, 'DYNAMITE');
>>
1 row(s) inserted.

insert into dupes values(4, 'SHE SELLS');
>>
1 row(s) inserted.

insert into dupes values(5, 'SEA SHELLS');
>>
1 row(s) inserted.

insert into dupes values(6, 'SEA SHELLS');
>>
1 row(s) inserted.

insert into dupes values(7, 'SEA SHELLS');
>>
1 row(s) inserted.

'SEA SHELLS'와 같은 중복 이름 그룹에 대해, 하나의 ID만 유지하고 나머지는 삭제하려고 한다.
몇 번째 행을 삭제하든 상관 없이 하나의 레코드만 필요하다.

A. 집계함수를 통해 남겨둘 ID를 임의로 선택해둔다

delete from dupes
	where id not in ( select min(id)
    					from dupes
                        group by name
                    );
>>
3 row(s) deleted.

select * from dupes;
>>
ID	NAME
1	NAPOLEON
2	DYNAMITE
4	SHE SELLS
5	SEA SHELLS
4 rows selected.

SELET min(id) FROM dupes GROUP BY name의 결과셋은 name들 중 ID값이 가장 작은 레코드들이다.

select min(id), name
	from dupes
	group by name
>>

MIN(ID)	NAME
1		NAPOLEON
2		DYNAMITE
4		SHE SELLS
5		SEA SHELLS

4 rows selected.

그룹화를 수행하는 GROUP BY와 집계 함수는 아주 친하다.

집계함수의 작동은 GROUP BY를 통해 그룹화가 된 이후에 이루어져야 하기 때문이다.

이는 절의 실행 순서와 관련이 깊다.
Group By 설명 참고

해설

중복을 삭제할 떄 가장 먼저 해야 할 일은 두 행이 서로 '중복된 것으로 간주된다'는 말의 정확한 의미를 정의하는 것이다.

이 예제에서의 '중복'이란 '두 레코드가 NAME 열에 같은 값을 포함한다'는 의미를 지닌다.

이렇게 문제를 정의하면 중복 집합을 구별하고, 유지할 레코드를 식별하는 것이 편하다.

열은 기본 키(PK)인 경우가 가장 좋다.
여기서 ID열은 같은 레코드를 갖지 않으므로 식별을 돕는 좋은 선택이 된다.

Maria(MySQL)에서는 같은 테이블을 두 번 참조할 수 없다.
위의 Oracle 쿼리에서는 'DUPES' 테이블을 외부쿼리와 서브쿼리에서 각각 조회했지만, Maria에서는 작동하지 않는다.

delete from dupes
	where id not in ( selet min(id)
    					from ( select id, name from dupes) tmp
                        group by name
                    );

정리

중복된 값을 정리할 때 난감할 때가 꽤 자주 있었다.
고유 키를 중심으로 집계함수를 사용하는 아이디어가 멋지다.
써 먹을 일이 있을 때 써 먹어야겠다.