8. SQL 기능확장을 위한 중간집합 생성

예시 테이블

• TAB1 테이블은 기본키가 'ITEM+SEQ'로 되어 있고, TAB2는 이것을 월별로 ITEM으로 집계해둔 테이블
• 정상적으로 수행시 TAB1을 집계한 것과 TAB2는 일치하겠으나 무엇인가 잘못된 처리로 인해 일관성이 깨지는 경우가 자주 발생함
  

문제제시

• 사용자는 다음과 같이 일관성이 흩트려진 항목들을 찾아 차이를 보여주고, 그 아래에 집계원 데이터가 되는 TAB1의 내역을 출력하고 싶어함

ITEM   SEQ     T1_AMT   T2_AMT      차이
-------- ------ -----------  ------------ ---------
   A      TOT          450           450           0
               1             100
               2             200
               3             150
   B      TOT          420                         420
               1             100
               2             120
               3             200
   C      TOT                            300     -300
   D      TOT          600            500       100
               1             100
               2             300
               3             200

쿼리문

SELECT min(decode(No, 1, item))               Item,
           min(decode(No, 1,'TOT', seq))          seq,
           sum(decode(sw, 0, amt))             t1_amt,
           sum(decode(sw, 2, amt))             t2_amt,
           sum((1-sw)*decode(No,1,amt))         차이
FROM  ( SELECT item, No, decode(No,1,'TOT',seq) seq, sum(amt) amt, 0 sw
              FROM  (SELECT item, seq, amt  FROM TAB1
                            WHERE ymd like '199803%' ) x, COPY_T y
             WHERE  y.No <= 2
             GROUP BY item, No, decode(No,1,'TOT',seq)
            UNION ALL
             SELECT item, 1 No, 'TOT' seq, amt, 2 sw
                FROM TAB2
             WHERE ym = '199803' )
GROUP BY item, No, seq ;

• TAB1에서 가공한 집합과 TAB2 집합과의 양방향 OUTER조인에 대한 문제는 두집합의 합집합(Union all)을 구하여 GROUP BY함으로 해결함
• TAB1의 집합에서 TAB2와 비교하는 것뿐만 아니라 그 내역도 추출해야하므로 COPY_T 테이블을 이용하여 집합을 두가지로 복제함
  • 복제된 집합 중에서 하나는 TAB2의 집합과 1:1이 되도록 하였음
  • 다른 한가지는 원래 집합을 그대로 두도록 DECODE를 이용하여 GROUP BY하였음

결론

인라인뷰를 사용하면 특수 목적을 위한 임의의 중간집합을 다양하게 생성할 수 있으며, 이러한 집합을 적절하게 조인하여 전체 집합을 확장하고 DECODE를 사용하여 상황에 따라 다양한 IF처리를 한다면 무한한 SQL의 확장이 가능함

문서에 대하여

• 최초작성자 : 임주영
• 최초작성일 : 2007년 12월 15일
• 이 문서는 오라클클럽 대용량 데이터베이스 스터디 모임에서 작성하였습니다.
• {*}이 문서의 내용은 이화식님의 대용량 데이터베이스 솔루션2 을 참고했습니다.*