정렬 변환을 이용한 중복 데이터 제거 방법

20070205_정렬변환옵션.dtsx

정렬 변환을 이용한 중복 데이터 제거

 

한대성

에이디컨설팅 책임 컨설턴트| SQLLeader.com 운영자

 

다음과 같은 예제 데이터를 생각해 봅시다.

 

아래의 결과는 SQL 2000의 예제 데이터베이스인 pubs에서 이용할 수 있습니다.

 

SELECT title_id, type, pub_id, royalty FROM pubs..titles WHERE royalty IS NOT NULL ORDER BY pub_id, royalty, title_id

 

 

 

다음과 같은 요구 사항을 생각해 봅시다.

pub_id별로 가장 royalty가 낮은 건을 추출하시오. 만약 royalty가 동일한 건이 여러 건 있는 경우, title_id가 가장 높은 건 하나만 추출하시오.

 

데이터 건수가 얼마 안되니깐, 우선 결과를 생각해보면 다음과 같습니다.

 

이것을 쿼리로 한 번 생각해봅시다. 여러 형태의 쿼리가 있을 수 있겠지만, 저는 다음과 같이 작성할 것 같습니다.

 

 

SELECT title_id, type, pub_id, royalty

INTO #TEMP

FROM pubs..titles

WHERE royalty IS NOT NULL

ORDER BY pub_id, royalty, title_id

 

 

SELECT A.title_id, MIN(type) as type, A.pub_id, A.royalty

FROM #TEMP A

JOIN

(

             SELECT B.pub_id, B.royalty, max(title_id) as title_id

             FROM #TEMP B

             JOIN

                           (

                                        SELECT pub_id, min(royalty) as royalty

                                        FROM #TEMP

                                        GROUP BY pub_id

                           ) C ON B.pub_id = C.pub_id AND B.royalty = C.royalty

             GROUP BY B.pub_id, B.royalty

) D ON A.title_id = D.title_id AND A.pub_id = D.pub_id AND A.royalty = D.royalty

GROUP BY A.title_id, A.pub_id, A.royalty

ORDER BY A.pub_id

 

MIN(type)를 추가한 것은 title_id, pub_id, royalty가 모두 같은 중복건이 있을 경우에 하나만 출력하도록 하기 위함이며, MIN 대신 MAX를 이용하여도 상관 없습니다.  어쨌든 간단하지는 않네요.

 

 

이를 SSIS의 변환을 이용해서 구현해보도록 하겠습니다.

 

데이터 흐름 작업을 하나 추가한 후, OLE DB 원본을 이용하여 원본 데이터를 입력 받습니다. 쿼리는 위의 쿼리를 입력해주면 됩니다.

 

왼쪽의 도구상자에서 정렬 변환을 추가한 후, OLE DB 원본과 연결합니다.

정렬을 다음과 같이 설정합니다. 이 때 아래에 있는 중복되는 정렬 값이 있는 행 제거 옵션을 체크합니다.

 

정렬 변환을 또 추가한 후, 앞의 정렬 변환과 연결하고 다음과 같이 설정합니다.

 

마찬가지로 정렬 변환을 하나 더 추가한 후, 앞의 정렬 변환과 연결하고 다음과 같이 설정합니다.

 

이제 행 변환 또는 아무 데이터 대상을 추가해서 마지막의 정렬 변환과 연결합니다.

 

각 단계에 데이터 뷰어를 추가해서 수행되는 결과를 살펴봅시다.

우선 첫 번째 OLE DB 원본에서 정렬 1로의 흐름은 테이블에서 읽어온 데이터 전부이겠네요.

 

그 다음, 정렬 1과 정렬 2 사이의 데이터 뷰어 결과는 다음과 같습니다.

순서 변화만 있고 다른 변화는 없지요? 만약에 pub_id, royalty, title_id가 같은 데이터가 있었다면 하나만 남고 제거되었을 것입니다. 이에 대해서는 다음 단계에서 자세히 설명토록 하겠습니다.

 

정렬 2와 정렬 3 사이의 데이터 뷰어입니다. 즉, 정렬 2를 통과한 결과이지요.

정렬 2는? pub_id를 오름 차순으로 royalty를 내림차순으로 정렬하고, 중복되는 행은 제거하라는 것이었습니다.

앞의 결과와 같이 비교해서 봅시다.

복잡하게 보이지만 나름대로 규칙이 있습니다. pub_id와 royalty를 기준으로 정렬했고 중복을 제거했기 때문에 처리 결과는 pub_id와 royalty별 한 개만 출력됩니다. 그럼 어떤 값이 출력될까요? 가장 마지막에 입력되는 데이터입니다.

즉, pub_id가 0877, royalty가 10인 데이터의 경우, 가장 마지막에 입력되는 TC7777이 출력됩니다.

 

정렬 3을 통과한 결과도 마찬가지입니다. 정렬 3은 pub_id로만 정렬하였으며, 중복 데이터를 제거하기 때문에 동일한 pub_id의 데이터 중에서 royalty값이 가장 낮은 값만 출력됩니다.(∵정렬 2에서 pub_id 오름차순, royalty 내림차순으로 출력되기 때문)

 

 

원하는 결과가 나왔네요.

 

어떻게 보면 쿼리보다 복잡할 수도 있겠지만, 열의 수가 더 많아지거나 테이블 형태의 데이터가 아닌 경우에는 SSIS가 더 좋을 수도 있겠지요.

 

중요한 것은?

SSIS의 정렬 변환에는 특이한(^^)옵션인 중복되는 정렬 값이 있는 행 제거(R) 옵션이 있다는 것입니다.