꿈꾸는 개발자, DBA 커뮤니티 구루비

연속 합산질문입니다.

by 정규직 [SQL Query] [2014.11.27 10:47:21]

아래와 같이 테이블이 구성되어 있습니다.

# TABLE A : 품목별 금액이 들어 있습니다.
---------------------------
   품목     |     금 액
---------------------------
    A1     |     1,000
    B1      |        500
    C1      |     1,000
    D1      |     2,000
    E1      |        100
--------------------------

# TABLE B : 그룹(집게)품목의 구성품목 및 계산구분이 들어 있습니다.
-----------------------------------------------
그룹품목    |     품목      |     계산구분
-----------------------------------------------
      T1         |      A1        |     + (합산)
      T1          |      B1        |     +
      T1          |      C1        |     +
      T2          |      T1        |     +
      T2          |      D1        |     - (차감)
      T3          |      T2        |     +
      T3          |      E1        |     -
-----------------------------------------------

# TABLE B에 있는 정보를 이용해 최종적으로 TABLE A 에 계산결과를 INSERT 하려고 합니다.
---------------------------
   품목     |     금 액
---------------------------
    A1     |     1,000
    B1      |        500
    C1      |     1,000
    T1      |     2,500
    D1      |     2,000
    T2      |       500
    E1      |        100
    T3      |        400
--------------------------

# 예시자료)
CREATE TABLE A AS
SELECT 'A1' AS ITEM_CD, 1000 AS AMT FROM DUAL UNION ALL
SELECT 'B1' AS ITEM_CD, 500 AS AMT FROM DUAL UNION ALL
SELECT 'C1' AS ITEM_CD, 1000 AS AMT FROM DUAL UNION ALL
SELECT 'D1' AS ITEM_CD, 2000 AS AMT FROM DUAL UNION ALL
SELECT 'E1' AS ITEM_CD, 100 AS AMT FROM DUAL;

CREATE TABLE B AS
SELECT 'T1' AS GRP_CD, 'A1' AS ITEM_CD, '+' AS CALC_SE FROM DUAL UNION ALL
SELECT 'T1' AS GRP_CD, 'B1' AS ITEM_CD, '+' AS CALC_SE FROM DUAL UNION ALL
SELECT 'T1' AS GRP_CD, 'C1' AS ITEM_CD, '+' AS CALC_SE FROM DUAL UNION ALL
SELECT 'T2' AS GRP_CD, 'T1' AS ITEM_CD, '+' AS CALC_SE FROM DUAL UNION ALL
SELECT 'T2' AS GRP_CD, 'D1' AS ITEM_CD, '-' AS CALC_SE FROM DUAL UNION ALL
SELECT 'T3' AS GRP_CD, 'T2' AS ITEM_CD, '+' AS CALC_SE FROM DUAL UNION ALL
SELECT 'T3' AS GRP_CD, 'E1' AS ITEM_CD, '-' AS CALC_SE FROM DUAL;

# SQL)
MERGE INTO A
USING (SELECT B.GRP_CD,
SUM(CASE WHEN B.CALC_SE = '+' THEN A.AMT ELSE A.AMT * -1 END) AS AMT
FROM B JOIN A ON A.ITEM_CD = B.ITEM_CD
GROUP BY B.GRP_CD
ORDER BY B.GRP_CD) B ON (A.ITEM_CD = B.GRP_CD)
WHEN NOT MATCHED THEN
INSERT (ITEM_CD, AMT)
VALUES (B.GRP_CD, B.AMT);

위에 있는 SQL로 처리하려고 했으나 T1까지는 문제 없이 계산되는거 같은데...
T2, T3 계산시 계산된 값인 T1, T2를 참조하지 못하는 거 같네요.

해결방법이 없을까요 ?
많은 도움 부탁드립니다.

by mypark [2014.11.27 11:18:20]

아래와 같이 Table B의 품목에 Table A의 품목만 나오도록 변경한 후 Join하면 될 거 같은데요.

WITH TableB AS (
SELECT 'T1' g, 'A1' i, '+' c FROM DUAL UNION ALL
SELECT 'T1', 'B1', '+' FROM DUAL UNION ALL
SELECT 'T1', 'C1', '+' FROM DUAL UNION ALL
SELECT 'T2', 'T1', '+' FROM DUAL UNION ALL
SELECT 'T2', 'D1', '-' FROM DUAL UNION ALL
SELECT 'T3', 'T2', '+' FROM DUAL UNION ALL
SELECT 'T3', 'E1', '-' FROM DUAL
)
SELECT CONNECT_BY_ROOT g g, i, c
FROM TableB
WHERE CONNECT_BY_ISLEAF = 1
CONNECT BY PRIOR i = g;

by 마농 [2014.11.27 11:31:06]

계층구조 합산 문제중 부호가 들어가는 문제입니다.
이 때 주의할 사항은 마이너스가 두번 나오면 플러스가 되는 특성을 고려해야 한다는 거죠.
http://www.gurubee.net/lecture/2643
http://www.gurubee.net/article/46097

SELECT c.grp_cd
     , SUM(a.amt * c.s) amt
  FROM (SELECT CONNECT_BY_ROOT grp_cd grp_cd
             , item_cd
             , DECODE(MOD(REGEXP_COUNT(
               SYS_CONNECT_BY_PATH(calc_se, ',')
               , '-'), 2), 0, 1, -1) s
          FROM b
         WHERE CONNECT_BY_ISLEAF = 1
         CONNECT BY PRIOR item_cd = grp_cd
        ) c
     , a
 WHERE c.item_cd = a.item_cd
 GROUP BY c.grp_cd
;

by 정규직 [2014.11.27 11:55:16]

감사합니다. 많은 도움이 되었습니다.

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변