Post List

2015년 1월 9일 금요일

대용량 데이터베이스 솔루션 2권 #09 UNION, GROUP BY

UNION, GROUP BY를 이용한 Data 연결



입금 과 매출이 각각 다른 Table에 있을 경우
방사형 Model로 설계된 경우 이런 일이 자주 발생한다.
입금 매출을 하나의 Record로 보고 싶을 때 어떻게 해야 할까 ?
OUTER JOIN ?
입금 LEFT OUTER JOIN 매출 로 할까 ?
그럼 입금이 NULL 이고 매출값이 있을 경우는 ?
그럼 매출 LEFT OUTER JOIN 입금 은 ?
그렇게 하면 매출은 NULL이고 입금값이 있는 경우는 ?
서로 NULL이 가능한데 상대편 값이라도 출력해야하는데 어떻해야 할까 ?
쌍방 OUTER JOIN ? 이럴 경우는 OUTER JOIN으로 해결이 안된다.
UNION, GROUP BY를 이용하면 된다.

1. M:M JOIN의 해결



M : M 관계를 JOIN으로 처리하면 M * M 으로 처리되지만,
UNION, GROUP BY는 합(+)의 개념이라서 M + M 으로 처리된다.
둘의 결과는 같게 나오지만 처리속도는 UNION, GROUP BY가 훨씬 빠르다.

2. 양측 OUTER JOIN의 해결



이 예제는 앞에서 설명했다.
사례를 하나 보자.


방사형 Model로 설계되었다.
불입년월과 예입년월이 연결되지 않는 경우가 존재할수 있기 때문에
억지로 JOIN으로 처리한다면 불필요한 처리가 증가한다.
SELECT yyyymm, 불입금액, 인출금액
  FROM (SELECT 관리번호, yyyymm
          FROM 예적금원장 A, MONTH_DUAL D
         WHERE A.부서CODE = :deptno
           AND D.yyyymm BETWEEN :in_date AND :end_date) X,
       (SELECT 관리번호, SUBSTR(불입일자,1,6) 불입년월, SUM(원화금액) 불입금액
          FROM 불입내역
         WHERE 불입일자 BETWEEN :in_date AND :end_date
         GROUP BY 관리번호, SUBSTR(불입일자,1,6)) B,
       (SELECT 관리번호, SUBSTR(인출일자,1,6) 인출년월, SUM(원화금액) 인출금액
          FROM 인출내역
         WHERE 인출일자 BETWEEN :in_date AND :end_date
         GROUP BY 관리번호, SUBSTR(인출일자,1,6)) C
 WHERE B.관리번호(+) = X.관리번호             -- X : 기준이 되는 DUMMY TABLE 하나 만들어 놓고
   AND B.불입년월(+) = X.yyyymm               -- X OUTER JOIN B
   AND C.관리번호(+) = X.관리번호
   AND C.인출년월(+) = X.yyyymm               -- X OUTER JOIN C
 GROUP BY yyyymm;
이것을 UNION, GROUP BY로 고쳐보자.
SELECT 년월, NVL(SUM(불입금액), 0), NVL(SUM(인출금액), 0)
  FROM (SELECT 관리번호,
               SUBSTR(불입일자,1,6) 년월,
               원화금액 불입금액,
               TO_NUMBER(NULL) 인출금액
          FROM 불입내역
         WHERE 불입일자 BETWEEN :in_date AND :end_date
         UNION ALL
        SELECT 관리번호,
               SUBSTR(인출일자,1,6) 년월,
               TO_NUMBER(NULL) 불입금액,
               원화금액 인출금액
          FROM 인출내역
         WHERE 인출일자 BETWEEN :in_date AND :end_date)
 GROUP BY 년월;
NULL은 Default로 문자타입인데 TO_NUMBER(NULL)을 하면 숫자타입의 NULL로 처리된다.
실제로 함수가 실행되는건 아니므로 불필요한 연산을 방지할 수 있다.

 * 주의사항

만약 단위가 서로 다른 경우를 UNION, GROUP BY로 처리할 경우
상위단위로 통합할 경우 INDEX를 사용하지 못할 경우가 발생한다.
예를 들어 한 TABLE에는 년월일이 이고 다른 TABLE에는 년월인 경우
년월일을 년월로 단위를 변경하면 Column을 가공했기 때문에 INDEX를 사용못하게 된다.
이럴 경우는 하위단위로 통합하여 GROUP BY 절에서 가공을 하면 된다.

SQL문으로 한번 보자.
SELECT 년월, NVL(SUM(실적금액), 0), NVL(SUM(목표금액), 0)
  FROM (SELECT 제품Code,
               SUBSTR(매출일자,1,6) 년월,
               메출금액 실적금액,
               TO_NUMBER(NULL) 목표금액
          FROM 매출실적
         UNION ALL
        SELECT 제품Code,
               목표년월 년월,
               TO_NUMBER(NULL) 실적금액,
               금액 목표금액
          FROM 매출목표)
 WHERE 년월 BETWEEN SUBSTR(:in_date,1,6) AND SUBSTR(:end_date,1,6)
 GROUP BY 년월;
매출실적에 년월이 가공된 Column이라서 WHERE 절에서의 비교에서 INDEX를 사용하지 못한다.
정상적으로 INDEX를 사용하게 하려면 아래와 같이 수정해야 한다.
SELECT SUBSTR(년월일,1,6), NVL(SUM(실적금액), 0), NVL(SUM(목표금액), 0)
  FROM (SELECT 제품Code,
               매출일자 년월일,
               메출금액 실적금액,
               TO_NUMBER(NULL) 목표금액
          FROM 매출실적
         UNION ALL
        SELECT 제품Code,
               목표년월 년월일,
               TO_NUMBER(NULL) 실적금액,
               금액 목표금액
          FROM 매출목표)
 WHERE 년월일 BETWEEN SUBSTR(:in_date,1,6) AND SUBSTR(:end_date,1,6)
 GROUP BY SUBSTR(년월일,1,6);

특수 활용 사례

1. 예제



총매출액 Table : 1
매출원가 Table : 2
3 = 1 - 2
전표집계 Table : 4 ~ 11, 13
12 = SUM(4 ~ 11), 단 8은 마이너스(-)
14 = 12 - 13
로 계산되어야 한다.

이것을 한방 SQL로 해결하기 위해서는 2가지를 고려해야 한다.
1. LINE 에 맞춰서 출력을 어떻게 해야 할까?
2. 1 - 2 값을 어떻게 구해야 할까 ?
위의 문제만 해결하면 어떻게든 SQL을 만드는 것이 가능하게 보인다.
LINE에 맞춰서 출력은 뭐 그냥 LINE Column을 하나 만들어서 나중에 GROUP BY 계산을 할때 덩달아 SORT가 자동으로 되니깐 그걸 이용하면 될듯하고,
1 - 2 값을 구하는게 왜 문제가 되냐고 ?
GROUP 함수에 SUM은 있다는걸 다 알고 있겠지만, SUM에서 빼기연산이 가능한가 ? 못한다.
그럼 어떻게 해야 할까 ?
값에 * -1 을 해서 SUM을 하면 된다.

이로서 어려운 문제는 대충 해결 된듯하다. 이제 한방SQL문을 만들어보자.
SELECT sum(tot*decode(NO-LINE,1,-1,3,-1,1) ) TOT,
       sum(W01*decode(NO-LINE,1,-1,3,-1,1)), ...
  FROM (SELECT LINE, sum(AMT) TOT,
               sum(decode(MM,'01',AMT)) W01, ...
          FROM (SELECT y.NO LINE, MM,                                 -- 3
                       sum(AMT*decode(y.NO*LINE,6,-1,1)) AMT
                  FROM (SELECT '1' LINE, substr(년월,5,2) MM,         -- 1
                               sum(AMT) AMT
                          FROM 매출집계
                         WHERE 년월 LIKE '1997%'
                         GROUP BY substr(년월,5,2)
                         UNION ALL
                        SELECT '2' LINE,  substr(년월,5,2) MM,        -- 2
                               sum(AMT) AMT
                          FROM 매출원가
                         WHERE 년월 LIKE '1997%'
                         GROUP BY substr(년월,5,2) ) x, COPY_T y
                         WHERE y.NO IN (LINE, 3)
                         GROUP BY y.NO, MM
                         UNION ALL
                        SELECT y.NO LINE, MM,                                         -- 8만 (8 * 11) (-)
                               SUM(AMT*decode(y.NO*LINE,88,-1,1) ) AMT
                          FROM (SELECT decode(substr(계정,1,2),’21’, 13,           -- 4 ~ 11, 13
                                       substr(계정,2,2)+1) LINE,
                                       substr(일자,5,2) MM, 
                                       SUM(AMT) AMT
                                  FROM 전표집계
                                 WHERE 일자 LIKE '1997%'
                                   AND 계정 BETWEEN '203' AND '219'
                                 GROUP BY decode(substr(계정,1,2),’21’, 13,
                                          substr(계정,2,2)+1),
                                          substr(일자,5,2) ) x,
                              COPY_T y
                        WHERE y.NO IN ( LINE,decode(LINE,13,NULL,11) ) -- LINE이 13이면 NULL, 아니면 11로 COPY_T
                GROUP BY y.NO, MM)
         GROUP BY LINE ) x,
       COPY_T y
 WHERE y.NO IN (LINE, decode(LINE,3,12,11,12), decode(LINE,3,14,11,14,13,14)) 
 GROUP BY y.NO;

 2. 예제



출력 Data는 당월매출과 전월대비 당월의 증감액이다.
즉 한 Row에서 필요한 Data는 이번달 매출과 전달 매출이다.


통상 처리 하길 1월 ~ 12월 치를 A로 읽고 전해 12월 ~ 11월을 B로 읽어서 UNION ALL 로 결합한 다음 빼주는 방법을 사용한다.
이렇게 하면 1월 ~ 11월의 Data를 2번 읽게 된다.
절대 한 Data를 2번 읽지 말아야 한다.
00 ~ 12 로 13개월치 Data를 읽어서 이것을 COPY_T를 이용해서 12개월치씩 복사해서 사용하면 될듯하다.
SELECT RPAD(,2)||'월' 구분,
       SUM(당월) 당월
       SUM(당월 - 전월) 증감액
       SUM(DECODE(당월,0,NULL,(당월-전월)*100/당월)) 증감율
  FROM (SELECT DECODE(NO, 2, MM+1, MM+0),
               NVL(SUM(DECODE(NO,1,AMT)),0) 당월,
               NVL(SUM(DECODE(NO,2,AMT)),0) 전월
          FROM (SELECT DECODE(년월,'201312','00', SUBSTR(년월,5,2)) MM,
                       SUM(AMT) AMT
                  FROM 매출집계
                 WHERE 사업장 = '1공장'
                   AND 년월 BETWEEN '201312' AND '201412'
                 GROUP BY DECODE(년월,'201312','00', SUBSTR(년월,5,2))) X,
               COPY_T Y
        WHERE NO BETWEEN DECODE(MM,'00',2,1) AND DECODE(MM,'12',1,2)
        GROUP BY DECODE(NO,2,MM+1,MM+0))
 GROUP BY;

3. 예제



위 프로그램은 FA(공장자동화) 분야에서는 자주 볼 수 있는 화면이다.
조회년월을 선택하면 각 검사공정별 전월/당월 검사 개수, 합격 개수, 합격율을 출력해주고
1주 ~ 5주 별로 해당 자료를 계산해야 한다.
단 여기서 주의해야 할 것은
단순히 1 ~7일은 1주 이런식이 아니라 월 ~ 일 을 1주로 계산해야 한다는 것이다.
어떻게 해야할까 ? 이것에 대한 계산식은 쉽지않다.

DB의 DATE 계산함수 중에 특정 날짜가 월 ~ 일 중 어느 요일인지에 대해서 답을 주는 함수는 있다.
그것을 이용해서 해당 월의 첫날이 무슨 요일인지를 구한뒤
그만큼 날짜에서 값을 더해서 7로 나눠서 GROUP BY로 SUM을 하면 된다.
말로는 이해가 힘든가 ?
예를 들어서 1일이 목요일이라면, 모든 날에 +4를 해서 GROUP BY를 하면 된다는 말이다.
SELECT 품목, DECODE(NO,1,'검사',2,'합격','율(%)'),
       SUM(DECODE(,0,cnt)),
       DECODE(NO,3,ROUND(SUM(분자)*100/SUM(분모),3),SUM(분자)+SUM(분모)),
       SUM(DECODE(,1,건수)), SUM(DECODE(,2,건수)), SUM(,3,건수)),
       SUM(DECODE(,4,건수)), SUM(DECODE(,5,건수)), SUM(,6,건수))
  FROM (SELECT 품목,NO,,
               DECODE(NO,3,ROUND(SUM(합격)*100/SUM(검사),3), SUM(합격)+SUM(검사)),
               SUM(DECODE(,0,NULL,검사)) 분모,
               SUM(DECODE(,0,NULL,합격)) 분자
          FROM (SELECT 품목,
                       DECODE(TO_CHAR(검사일,'yyyymm'), '201312','0',
                              CEIL((TO_CHAR(검사일,'dd')+TO_CHAR(TRUNC(검사일,'mm'),'d')-1)/7)),
                       COUNT(*) 검사수,
                       0 합격,
                       1 SW
                  FROM 품질검사
                 WHERE 공정 = 'PRESS'
                   AND 검사일 BETWEEN '20131201' AND '20140131'
                 GROUP BY 품목,
                          DECODE(TO_CHAR(검사일,'yyyymm'), '201312','0',
                                 CEIL((TO_CHAR(검사일,'dd')+TO_CHAR(TRUNC(검사일,'mm'),'d')-1)/7))
                 UNION ALL
                 SELECT 품목,
                       DECODE(TO_CHAR(검사일,'yyyymm'), '201312','0',
                              CEIL((TO_CHAR(검사일,'dd')+TO_CHAR(TRUNC(검사일,'mm'),'d')-1)/7)),
                       0 검사수,
                       DECODE(합격항목수) 합격수,
                       2 SW
                 FROM (SELECT 품목,검사일,
                              COUNT(*) 검사수,
                              SUM(DECODE(LEAST(grade,'C'),'C',1)) 합격항목수
                         FROM 품질검사
                        WHERE 공정 = 'PRESS'
                          AND 검사일 BETWEEN '20131201' AND '20140131'
                     GROUP BY 품목,공정,검사일,일련번호)
         WHERE (검사항목수=합격항목수)or(합격항목수 is null and rownum = 1)
         GROUP BY 품목,
                   DECODE(TO_CHAR(검사일,'yyyymm'), '201312','0',
                          CEIL((TO_CHAR(검사일,'dd')+TO_CHAR(TRUNC(검사일,'mm'),'d')-1)/7))) X,
         COPY_T Y
        WHERE NO in (SW,3) AND NO <= 3
        GROUP BY 품목,NO,)
GROUP BY 품목,NO;
작가
이화식
출판
엔코아컨설팅
발매
1999.11.20
평점
블로거의 오늘의 책에 참여한 포스트 입니다