[오라클팁] 그룹함수로 중복된 컬럼 제거하기

시스템 모니터링하다 데이터가 중복으로 들어오는 걸 발견했습니다. 테이블에 데이터가 중복으로 수천건이 들어오고 있어서 계속 쌓이기전에 빨리 처리하면서 처리한 방법을 팁으로 남겨봅니다.

 

일단 아래 테이블 양식을 살펴주세요. 제가 사용한 테이블은 시간당 자료를 입력을 받는 테이블이었는데 연계된 하드웨어 특성상 사용자의 의도된 중복된 자료도 넘어올 수 있기에 고유키나 유니크로 설계가 되어 있지 않았습니다. 설계부터가 좀 잘못되어 있었는데 그냥 고칠 수도 없고 중복된 자료나 정리해야죠..

 

일반적으로 테이블에 넘어오는 데이터 양식은 아래와 같습니다.

 

TEST 테이블형식예제) 

SEQ	PJCD	HOUR	MINUTE	SECOND	CONTENT
1	PJCD001	08	10	15	기록1
2	PJCD002	09	12	07	기록2
3	PJCD003	10	13	25	기록3
4	PJCD004	10	20	40	기록4

 

시간에 따라 고유시디 넘버가 입력됩니다. 그런데 장비의 문제로 자료가 이상하게 들어오기 시작했습니다. 시디값은 중복이 될수는 있지만 시디값도 같고 시간이 같은 자료가 여러건이 생겨버렸습니다.

 

 

SEQ	PJCD	HOUR	MINUTE	SECOND	CONTENT
5	PJCD002	12	15	18	기록2
6	PJCD002	12	15	18	기록2
7	PJCD002	12	15	18	기록2
8	PJCD001	20	45	30	기록1
9	PJCD003	09	54	21	기록3
10	PJCD003	09	54	21	기록3
11	PJCD004	13	35	42	기록4

 

자료가 중복으로 들어오기 시작했습니다. 같은 시간대에 같은 CD값을 가진기록이 보입니다. 단지 SEQ 넘버만 넘버링하여 들어오네요.

 

중복된 자료의 SEQ 넘버를 알아내어 일일이 삭제하려면 상당히 귀찮습니다. 게다가 건수가 수천건이면 처리할수도 없죠. 그래서 그룹함수로 처리를 하려고 합니다.

 

기본요지는 다음과 같습니다. 중복된 모든 값들 에서 중복된 그룹의 대표번호를 제외한 데이터를 삭제처리한다.

 

 

1. 그룹의 대표번호를 조회하기

 

같은 시간대의 같은 CD를 가지는 그룹을 조회를 해보도록하죠. 쿼리는 아래와 같습니다.

 

SELECT  MAX(SEQ) as SEQ, MAX(PJCD) as PJCD, COUNT(*) as CNT

  FROM TEST테이블

GROUP BY PJCD||HOUR||MINUTE||SECOND

 

그룹으로 같은시간대를 가지는  시디키값을 추출하였습니다. 결과는 아래를 참고하세요

 

SEQ	PJCD	CNT
7	PJCD002	3
8	PJCD001	1
10	PJCD003	2
11	PJCD004	1

 

같은시간대를 가지는 그룹이 2개인것을 확인할 수 있네요. 여기서 조건을 주어서 그룹에 속해있는 시디값이 1개 이상이면 중복된 자료를 가진 그룹이라고 알 수 있죠. 1건은 단일건수니깐요!

 

그래서 조건을 추가해줍니다. GROUP 함수의 조건문 HAVING 입니다.

 

SELECT  MAX(SEQ) as SEQ, MAX(PJCD) as PJCD, COUNT(*) as CNT

  FROM TEST테이블

GROUP BY PJCD||HOUR||MINUTE||SECOND

HAVING COUNT(*) > 1

 

이렇게 중복된 그룹의 대표 SEQ번호를 골라낼 수 있습니다.  중복된 그룹에서 대표SEQ를 제외한 나머지 값을 삭제처리하면 중복처리가 완료되는 겁니다.

 

SEQ	PJCD	CNT
7	PJCD002	3
10	PJCD003	2

 

 

2. 중복된모든 데이터를 확인하기

 

자, 이제 중복을 가진 모든 데이터를 조회만 하면 마무리 되겠네요.

 

모든 데이터에서 시디키와 시간,분,초 를 같이 쓰이고 있는 데이터를 조회 하면 중복된 모든 데이터가 됩니다.

 

 

in이라는 명령어가 쓰였습니다. in은 WHERE 조건에서 조건에 속하는 자료를 검색합니다.

' PJCD||HOUR||MINUTE||SECOND ' 묶어서 건수가 1건이상 같은 자료가 있으면 중복된 자료로 조회를 하는거죠.

 

SELECT *

  FROM TEST테이블

WHERE PJCD||HOUR||MINUTE||SECOND IN (

             SELECT PJCD||HOUR||MINUTE||SECOND

                FROM TEST테이블

      GROUP BY PJCD||HOUR||MINUTE||SECOND

       HAVING COUNT(+) > 1

)

 

 

마지막으로 중복된 전체자료에서 대표그룹번호만 제거하면 삭제할 중복자료 리스트가 완성됩니다. DELETE 를 하기전에는 항상 SELECT 로 바꿔서 삭제할 자료를 확인한 뒤 삭제처리하시기 바랍니다.

 

DELETE

  FROM TEST테이블

WHERE PJCD||HOUR||MINUTE||SECOND IN (

             SELECT PJCD||HOUR||MINUTE||SECOND

                FROM TEST테이블

      GROUP BY PJCD||HOUR||MINUTE||SECOND

       HAVING COUNT(+) > 1

)

AND SEQ NOT IN (

SELECT  MAX(SEQ) as SEQ

  FROM TEST테이블

GROUP BY PJCD||HOUR||MINUTE||SECOND

HAVING COUNT(*) > 1

)