PK 가 없는 테이블에서 중복된 데이터 제거하기

발생일: 2009.11.06

문제:
타시스템으로부터 인터페이스 받아오는 임시 테이블이 있다.
이 테이블은 데이터 전달용으로 임시로만 사용하기 때문에 따로 PK 를 두지 않았다.
헌데 이 테이블에 중복된 데이터가 인터페이스 되게 되었다.
PK 가 없는 테이블에서 중복된 데이터를 어떻게 제거해야 할까...

다른 테이블로 옮기는 과정에서 프로시저를 통해 중복데이터를 제거하면 되겠지만,
현재 상황에서는 프로시저를 수정할 수는 없다.
단순하게 DELETE 구문을 통해 중복 데이터를 제거하려고 한다.

테이블 스키마는 대략 아래와 같다고 가정한다.
IF_TEMP
id (number)
content (varchar2)

현재 데이터는 아래과 같다.

id	content
123	가나다
123	가나다
234	마바사
234	마바사
234	마바사

중복된 데이터 중 한 개만 남기고 다른 데이터를 삭제하려고 한다.


해결책:
음... id 를 기준으로 그룹별로 번호를 매겨서 번호가 1번이 아닌 데이터를 삭제하면 어떨까?
이런 식으로 말이다.

SELECT
    ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) AS num,
    id, content
FROM if_temp

(ROW_NUMBER() 의 사용법은 그룹별 번호매기기에 대해 정리해둔 포스트를 참고하자)

결과는 아래와 같다.

num	id	content
1	123	가나다
2	123	가나다
1	234	마바사
2	234	마바사
3	234	마바사

num 값이 1보다 큰 값에 대한 조건을 추가해 중복되는 나머지 데이터를 조회할 수 있었다.

SELECT *
FROM (
    SELECT
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) AS num,
        id, content
    FROM if_temp
)
WHERE num > 2


자, 이제 삭제만 하면 된다....
어허,.. 조회를 하긴 했는데.... 삭제를 하려니 데이터에 유니크한 정보가 없어 삭제 조건을 줄 수가 없다.

DELETE FROM if_temp
WHERE... ?

뭔가 선택된 로우에 유니크한 정보가 있어야 할 것 같다.


오라클의 ROWID 를 사용하기로 했다.
ROWID 는 테이블에 있는 로우를 찾기 위해 사용되는 논리적인 정보로 유니크한 값이다.
(자세한 사항은 rowid와 rownum의 정의와 사용법 포스트 참고)

위에서 조회한 값에 rowid 를 붙여서 조회해보기로 했다.

SELECT ROWID
FROM (

    SELECT *

    FROM (
        SELECT
            ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) AS num,
            id, content
        FROM if_temp
    )
    WHERE num > 2
)

이렇게 해주니 row 정보의 유니크한 정보를 가져올 수 있게 됐다.

삭제를 위한 최종적인 쿼리는 아래와 같다.

DELETE FROM if_temp
WHERE ROWID IN (
    SELECT ROWID
    FROM (

        SELECT *

        FROM (
            SELECT
                ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) AS num,
                id, content
            FROM if_temp
        )
        WHERE num > 2
    )
)