DATOR


중복값을 제외한 데이터 조회시 비효율 제거. 튜닝[SQL]


 SQL 문장을 작성시 아래와 같은 경우를 만나게 됩니다.

 

먼저 모델을 예로 보면

 

SAMPLE4.jpg

입고량, 검사량, 출고량을 조회하게 되면 하나의 입고번호에 대하여 여러개의 출고량 데이터 ROW가 발생되고

 

입고량과 검사량은 중복해서 보여지게 됩니다.

 

입고량 1000 이고 검사는 두번에 걸쳐서 500씩 진행했고 두번의 검사에 대하여 세번의 출고가 일어났습니다.

 

SAMPLE5.jpg  

 

일반적으로 어플리케이션에서 제어하는 것이 일반적이나 DW 쪽으로 데이터를 밀어넣어  줄때나 이행작업시에는

 

출고량 기준으로 SUM 을 하게되면 맞지만 입고량과 검사량 기준으로 SUM 하게 되면 잘못된 값이 나오게 됩니다.

 

따라서 아래와 같은 데이터 값을 얻고자 할경우

 

SAMPLE6.jpg

 

각 테이블을 GROUP BY 하여 UNION ALL 로 처리하는 SQL은 각 테이블을 반복해서 읽어야 하는 문제점이 있습니다.

 

SELECT A.IN_NO 입고번호, A.TEST_NO 검사번호, A.OUT_NO 출고번호, MAX(A.IN_QTY) 입고량, MAX(A.TEST_QTY) 검사량, MAX(OUT_QTY) 출고량
  FROM (
        SELECT A.IN_NO, MAX(A.IN_QTY) IN_QTY, '001' TEST_NO, 0 TEST_QTY, '001' OUT_NO, 0 OUT_QTY       --입고량을 가져오기 위함
          FROM
          (
          SELECT '0001' IN_NO, 1000 IN_QTY FROM DUAL
          ) A,
          (
          SELECT '0001' IN_NO, '001' TEST_NO, 500 TEST_QTY FROM DUAL
          UNION ALL
          SELECT '0001' IN_NO, '002' TEST_NO, 500 TEST_QTY FROM DUAL
          ) B,
          (
          SELECT '0001' IN_NO, '001' TEST_NO, '001' OUT_NO, 500 OUT_QTY FROM DUAL
          UNION ALL
          SELECT '0001' IN_NO, '002' TEST_NO, '001' OUT_NO, 300 OUT_QTY FROM DUAL
          UNION ALL
          SELECT '0001' IN_NO, '002' TEST_NO, '002' OUT_NO, 200 OUT_QTY FROM DUAL
          ) C
         WHERE A.IN_NO   = B.IN_NO
           AND B.IN_NO   = C.IN_NO
           AND B.TEST_NO = C.TEST_NO
         GROUP BY A.IN_NO
        UNION ALL
        SELECT A.IN_NO, 0 IN_QTY, B.TEST_NO,  MAX(B.TEST_QTY) TEST_QTY,  '001' OUT_NO,  0 OUT_QTY   --검사량을 가져오기 위함
          FROM
          (
          SELECT '0001' IN_NO, 1000 IN_QTY FROM DUAL
          ) A,
          (
          SELECT '0001' IN_NO, '001' TEST_NO, 500 TEST_QTY FROM DUAL
          UNION ALL
          SELECT '0001' IN_NO, '002' TEST_NO, 500 TEST_QTY FROM DUAL
          ) B,
          (
          SELECT '0001' IN_NO, '001' TEST_NO, '001' OUT_NO, 500 OUT_QTY FROM DUAL
          UNION ALL
          SELECT '0001' IN_NO, '002' TEST_NO, '001' OUT_NO, 300 OUT_QTY FROM DUAL
          UNION ALL
          SELECT '0001' IN_NO, '002' TEST_NO, '002' OUT_NO, 200 OUT_QTY FROM DUAL
          ) C
         WHERE A.IN_NO   = B.IN_NO
           AND B.IN_NO   = C.IN_NO
           AND B.TEST_NO = C.TEST_NO
        GROUP BY A.IN_NO, B.TEST_NO
        UNION ALL
        SELECT A.IN_NO, 0 IN_QTY, B.TEST_NO, 0 TEST_QTY, C.OUT_NO,  C.OUT_QTY                 --출고량을 가져오기 위함
          FROM
          (
          SELECT '0001' IN_NO, 1000 IN_QTY FROM DUAL
          ) A,
          (
          SELECT '0001' IN_NO, '001' TEST_NO, 500 TEST_QTY FROM DUAL
          UNION ALL
          SELECT '0001' IN_NO, '002' TEST_NO, 500 TEST_QTY FROM DUAL
          ) B,
          (
          SELECT '0001' IN_NO, '001' TEST_NO, '001' OUT_NO, 500 OUT_QTY FROM DUAL
          UNION ALL
          SELECT '0001' IN_NO, '002' TEST_NO, '001' OUT_NO, 300 OUT_QTY FROM DUAL
          UNION ALL
          SELECT '0001' IN_NO, '002' TEST_NO, '002' OUT_NO, 200 OUT_QTY FROM DUAL
          ) C
         WHERE A.IN_NO   = B.IN_NO
           AND B.IN_NO   = C.IN_NO
           AND B.TEST_NO = C.TEST_NO
         ) A
 GROUP BY A.IN_NO, A.TEST_NO, A.OUT_NO

 

위의 SQL은 아래와 같이 분석함수를 이용하여 비효율을 제거 합니다.

 

 SELECT A.IN_NO, A.IN_QTY,
        DECODE(ROW_NUMBER() OVER (PARTITION BY A.IN_NO ORDER BY B.IN_NO), 1, A.IN_QTY, 0) IN_QTY,
        DECODE(ROW_NUMBER() OVER (PARTITION BY B.IN_NO, B.TEST_NO ORDER BY B.IN_NO), 1, B.TEST_QTY, 0) TEST_QTY,
        C.OUT_QTY
  FROM
  (
  SELECT '0001' IN_NO, 1000 IN_QTY FROM DUAL
  ) A,
  (
  SELECT '0001' IN_NO, '001' TEST_NO, 500 TEST_QTY FROM DUAL
  UNION ALL
  SELECT '0001' IN_NO, '002' TEST_NO, 500 TEST_QTY FROM DUAL
  ) B,
  (
  SELECT '0001' IN_NO, '001' TEST_NO, '001' OUT_NO, 500 OUT_QTY FROM DUAL
  UNION ALL
  SELECT '0001' IN_NO, '002' TEST_NO, '001' OUT_NO, 300 OUT_QTY FROM DUAL
  UNION ALL
  SELECT '0001' IN_NO, '002' TEST_NO, '002' OUT_NO, 200 OUT_QTY FROM DUAL
  ) C
 WHERE A.IN_NO   = B.IN_NO
   AND B.IN_NO   = C.IN_NO
   AND B.TEST_NO = C.TEST_NO

 

 

Tag :

Leave Comments