중복되는 계층구조를 가진 테이블 값 계산 문의 1 9 117

by 박군two [Oracle 기초] [2017.10.11 18:16:01]


안녕하세요 

뭔가 하나가 이상한거 같은데 영.. 보이지가 않아 문의 드립니다.

아래 내용은 http://blog.daum.net/turnnig-pointer/16498271 의 문제를 다른 방법으로 해결하는 과정에서 생긴 문의 사항입니다.

 

- 중복을 가진 계층 구조가 있음

- 아래와 같이 최상위 노드로부터 현재 노드까지의 qnty 를 곱한값을 출력하고자 함

SEQ LV PARTNOM PARTNO LV_PARTNO QNTY PATH_LIST QNTY_LIST 결과 예상값
1 1 X A A 1 ,A ,1, 1 = 1
2 1 X B B 2 ,B ,2, 2 = 2
3 2 B C     C 3 ,B,C ,2,3, 2 * 3 = 6
4 3 C E         E 3 ,B,C,E ,2,3,3, 2 * 3 * 3 = 18
5 3 C F         F 4 ,B,C,F ,2,3,4,
6 3 C I         I 5 ,B,C,I ,2,3,5,
7 2 B D     D 4 ,B,D ,2,4,
8 3 D H         H 2 ,B,D,H ,2,4,2,
9 3 D J         J 4 ,B,D,J ,2,4,4,
10 4 J C             C 6 ,B,D,J,C ,2,4,4,6,
11 5 C E                 E 3 ,B,D,J,C,E ,2,4,4,6,3,
12 5 C F                 F 4 ,B,D,J,C,F ,2,4,4,6,4,
13 5 C I                 I 5 ,B,D,J,C,I ,2,4,4,6,5,
14 3 D K         K 3 ,B,D,K ,2,4,3, 2 * 4 * 3 = 24

 

WITH T(PARTNO, PARTNOM, QNTY) AS (
     SELECT 'X', '*', 1 FROM DUAL UNION ALL
     SELECT 'A', 'X', 1 FROM DUAL UNION ALL
     SELECT 'B', 'X', 2 FROM DUAL UNION ALL
     SELECT 'C', 'B', 3 FROM DUAL UNION ALL
     SELECT 'E', 'C', 3 FROM DUAL UNION ALL
     SELECT 'F', 'C', 4 FROM DUAL UNION ALL
     SELECT 'I', 'C', 5 FROM DUAL UNION ALL
     SELECT 'D', 'B', 4 FROM DUAL UNION ALL
     SELECT 'H', 'D', 2 FROM DUAL UNION ALL
     SELECT 'C', 'J', 6 FROM DUAL UNION ALL
     SELECT 'J', 'D', 4 FROM DUAL UNION ALL
     SELECT 'K', 'D', 3 FROM DUAL
)
SELECT A.SEQ,
       A.LV,
       A.PARTNOM,
       A.PARTNO,
       A.LV_PARTNO,
       A.QNTY,
       A.PATH_LIST,
       A.QNTY_LIST/*,
       (SELECT EXP(SUM(LN(EACH_QNTY))) EACH_QNTY_NULTI
        FROM   (SELECT TO_NUMBER(SUBSTR(A.QNTY_LIST, 
                                        INSTR(A.QNTY_LIST, ',', 1, LEVEL)+1, 
                                        INSTR(A.QNTY_LIST, ',', 1, LEVEL+1) - INSTR(A.QNTY_LIST, ',', 1, LEVEL)-1)) EACH_QNTY
                FROM   DUAL
                CONNECT BY LEVEL <= LENGTH(A.QNTY_LIST) - LENGTH(REPLACE(A.QNTY_LIST, ',')))
        WHERE  EACH_NO IS NOT NULL
       )*/
FROM   (SELECT ROWNUM SEQ, 
               LEVEL LV, 
               T.PARTNOM, 
               T.PARTNO,
               LPAD(' ', (LEVEL-1)*4) || T.PARTNO LV_PARTNO,
               T.QNTY,
               SYS_CONNECT_BY_PATH(T.PARTNO, ',') PATH_LIST,
               SYS_CONNECT_BY_PATH(T.QNTY, ',') || ',' QNTY_LIST
        FROM   T 
        START WITH T.PARTNOM = 'X'
        CONNECT BY PRIOR T.PARTNO = T.PARTNOM) A
ORDER BY A.SEQ
;

주석처리된 구문을 따로 분리해서 검증할 때는 정상적으로 출력되는데

select 절에 추가하면 인식(?) 이 안되네요 ..

간단한 문제인듯 생각되나 도통 생각이 안나서 문의 드립니다.

 

by 박군two [2017.10.11 18:19:09]

EXP(SUM(LN(EACH_QNTY)))

구문은 구글링한 결과로 누적 곱셈을 구하는 식입니다.


by jkson [2017.10.11 18:59:11]

산수를 못해서 EXP_SUM_LN 방식이 잘 이해가 안 되네요.

이래서 학교 다닐 때 다들 공부해라 공부해라 그러셨구나..ㅠㅠ


by 우리집아찌 [2017.10.11 19:07:45]

LN 같은거 통계쪽이나 나오는함수인데.. 거의 쓸일없을듯 딱한번 써봄

엑셀에도 같은 함수 지원


by 박군two [2017.10.12 11:48:43]

http://www.gurubee.net/lecture/2879

마농님은 모르는게 없으시네요 


by 마농 [2017.10.11 18:44:11]

1. 스칼라서브쿼리를 2 depth 로 작성하셨네요.
  - main 의 항목이 2depth 까지 들어가지 못합니다.
  - 2 depth 가 필요 없어 보이네요. 1 depth 로 바꿔보세요.
2. path 를 split 하는 계산식에 오류가 있네요.
  - 9i 때 사용하던 방식인데 오류 잡으려면 좀 복잡합니다.
  - 10g 부터는 regexp_count, regexp_substr 등을 사용하면 편합니다.
3. 기타
  - dbms_xmlgen 을 이용해 계산식을 직접 처리하는 방법도 있구요
  - 11g 에서는 Recursive SQL 를 사용 할 수 있습니다.
4. exp_sum_ln 을 이용한 곱셈은
  - 소수점 이하 오차가 발생됩니다. (예 : 2.0000001, 1.99999999)
  - round 로 보정 해줘야 합니다.


by 박군two [2017.10.12 10:28:35]

마농님 너무 감사합니다.

MAIN에서 2DEPTH 까지 못들어간다는거는 처음 알았네요

WITH T(PARTNO, PARTNOM, QNTY) AS (
     SELECT 'X', '*', 1 FROM DUAL UNION ALL
     SELECT 'A', 'X', 1 FROM DUAL UNION ALL
     SELECT 'B', 'X', 2 FROM DUAL UNION ALL
     SELECT 'C', 'B', 3 FROM DUAL UNION ALL
     SELECT 'E', 'C', 3 FROM DUAL UNION ALL
     SELECT 'F', 'C', 4 FROM DUAL UNION ALL
     SELECT 'I', 'C', 5 FROM DUAL UNION ALL
     SELECT 'D', 'B', 4 FROM DUAL UNION ALL
     SELECT 'H', 'D', 2 FROM DUAL UNION ALL
     SELECT 'C', 'J', 6 FROM DUAL UNION ALL
     SELECT 'J', 'D', 4 FROM DUAL UNION ALL
     SELECT 'K', 'D', 3 FROM DUAL
)
SELECT A.SEQ,
       A.LV,
       A.PARTNOM,
       A.PARTNO,
       A.LV_PARTNO,
       A.QNTY,
       A.PATH_LIST,
       A.QNTY_LIST,
       --1번 내용
       (SELECT ROUND(EXP(SUM(LN(TO_NUMBER(SUBSTR(A.QNTY_LIST, --4번 내용
                                                 INSTR(A.QNTY_LIST, ',', 1, LEVEL)+1, 
                                                 INSTR(A.QNTY_LIST, ',', 1, LEVEL+1) - INSTR(A.QNTY_LIST, ',', 1, LEVEL)-1)))))) EACH_QNTY
        FROM   DUAL
        CONNECT BY LEVEL <= LENGTH(A.QNTY_LIST) - LENGTH(REPLACE(A.QNTY_LIST, ','))
       ) EACH_QNTY_MULTI
FROM   (SELECT ROWNUM SEQ, 
               LEVEL LV, 
               T.PARTNOM, 
               T.PARTNO,
               LPAD(' ', (LEVEL-1)*4) || T.PARTNO LV_PARTNO,
               T.QNTY,
               SYS_CONNECT_BY_PATH(T.PARTNO, ',') PATH_LIST,
               SYS_CONNECT_BY_PATH(T.QNTY, ',') || ',' QNTY_LIST
        FROM   T 
        START WITH T.PARTNOM = 'X'
        CONNECT BY PRIOR T.PARTNO = T.PARTNOM) A
ORDER BY A.SEQ
;

WITH T(PARTNO, PARTNOM, QNTY) AS (
     SELECT 'X', '*', 1 FROM DUAL UNION ALL
     SELECT 'A', 'X', 1 FROM DUAL UNION ALL
     SELECT 'B', 'X', 2 FROM DUAL UNION ALL
     SELECT 'C', 'B', 3 FROM DUAL UNION ALL
     SELECT 'E', 'C', 3 FROM DUAL UNION ALL
     SELECT 'F', 'C', 4 FROM DUAL UNION ALL
     SELECT 'I', 'C', 5 FROM DUAL UNION ALL
     SELECT 'D', 'B', 4 FROM DUAL UNION ALL
     SELECT 'H', 'D', 2 FROM DUAL UNION ALL
     SELECT 'C', 'J', 6 FROM DUAL UNION ALL
     SELECT 'J', 'D', 4 FROM DUAL UNION ALL
     SELECT 'K', 'D', 3 FROM DUAL
)
SELECT A.SEQ,
       A.LV,
       A.PARTNOM,
       A.PARTNO,
       A.LV_PARTNO,
       A.QNTY,
       A.PATH_LIST,
       A.QNTY_LIST,
       -- 2번 내용
       (SELECT ROUND(EXP(SUM(LN(TO_NUMBER(REGEXP_SUBSTR(A.QNTY_LIST, '[^,]+', 1, LEVEL))))))--4번 내용
        FROM   DUAL
        CONNECT BY LEVEL <= REGEXP_COUNT(A.QNTY_LIST, ',') + 1
       ) EACH_QNTY_MULTI_1,
       -- 3-1번 내용 
       TO_NUMBER(DBMS_XMLGEN.GETXMLTYPE(
                 'SELECT ' || REPLACE(A.QNTY_LIST, ',' ,'*') || ' FROM DUAL').EXTRACT('//text()') --jkson 님 감사합니다.
       ) EACH_QNTY_MULTI_2
FROM   (SELECT ROWNUM SEQ, 
               LEVEL LV, 
               T.PARTNOM, 
               T.PARTNO,
               LPAD(' ', (LEVEL-1)*4) || T.PARTNO LV_PARTNO,
               T.QNTY,
               SUBSTR(SYS_CONNECT_BY_PATH(T.PARTNO, ','), 2) PATH_LIST,
               SUBSTR(SYS_CONNECT_BY_PATH(T.QNTY, ','), 2) QNTY_LIST -- 보기 편하게 하기 위해 앞에 , 제거
        FROM   T 
        START WITH T.PARTNOM = 'X'
        CONNECT BY PRIOR T.PARTNO = T.PARTNOM) A
ORDER BY A.SEQ;

--3-2 번 내용 (마농님 sql과 http://www.gurubee.net/lecture/2223 참조)
WITH T(PARTNO, PARTNOM, QNTY) AS (
     SELECT 'X', '*', 1 FROM DUAL UNION ALL
     SELECT 'A', 'X', 1 FROM DUAL UNION ALL
     SELECT 'B', 'X', 2 FROM DUAL UNION ALL
     SELECT 'C', 'B', 3 FROM DUAL UNION ALL
     SELECT 'E', 'C', 3 FROM DUAL UNION ALL
     SELECT 'F', 'C', 4 FROM DUAL UNION ALL
     SELECT 'I', 'C', 5 FROM DUAL UNION ALL
     SELECT 'D', 'B', 4 FROM DUAL UNION ALL
     SELECT 'H', 'D', 2 FROM DUAL UNION ALL
     SELECT 'C', 'J', 6 FROM DUAL UNION ALL
     SELECT 'J', 'D', 4 FROM DUAL UNION ALL
     SELECT 'K', 'D', 3 FROM DUAL
),
T1 (LV, PARTNOM, PARTNO, LV_PARTNO, QNTY, PATH_LIST, QNTY_LIST, EACH_QNTY_MULTI) AS(
    SELECT 1 LV,
           PARTNOM,
           PARTNO,
           PARTNO        AS LV_PARTNO,
           QNTY,
           PARTNO        AS PATH_LIST,
           TO_CHAR(QNTY) AS QNTY_LIST,
           QNTY          AS EACH_QNTY_MULTI
    FROM   T
    WHERE  PARTNOM = '*'
    UNION ALL
    SELECT A.LV + 1 LV,
           B.PARTNOM,
           B.PARTNO,
           LPAD(' ', (A.LV-1)*4) || B.PARTNO  AS LV_PARTNO,
           B.QNTY,
           A.PATH_LIST || ',' || B.PARTNO     AS PATH_LIST,
           A.QNTY_LIST || ',' || B.QNTY       AS QNTY_LIST,
           A.EACH_QNTY_MULTI * B.QNTY         AS EACH_QNTY_MULTI
    FROM   T1 A,
           T  B
    WHERE  A.PARTNO = B.PARTNOM
)
SEARCH DEPTH FIRST BY partno SET seq 
SELECT *
FROM   T1
WHERE  T1.PARTNO <> 'X';

by jkson [2017.10.11 18:44:57]
--원하시는 방식
WITH T(PARTNO, PARTNOM, QNTY) AS (
     SELECT 'X', '*', 1 FROM DUAL UNION ALL
     SELECT 'A', 'X', 1 FROM DUAL UNION ALL
     SELECT 'B', 'X', 2 FROM DUAL UNION ALL
     SELECT 'C', 'B', 3 FROM DUAL UNION ALL
     SELECT 'E', 'C', 3 FROM DUAL UNION ALL
     SELECT 'F', 'C', 4 FROM DUAL UNION ALL
     SELECT 'I', 'C', 5 FROM DUAL UNION ALL
     SELECT 'D', 'B', 4 FROM DUAL UNION ALL
     SELECT 'H', 'D', 2 FROM DUAL UNION ALL
     SELECT 'C', 'J', 6 FROM DUAL UNION ALL
     SELECT 'J', 'D', 4 FROM DUAL UNION ALL
     SELECT 'K', 'D', 3 FROM DUAL
)
SELECT A.SEQ,
       A.LV,
       A.PARTNOM,
       A.PARTNO,
       A.LV_PARTNO,
       A.QNTY,
       A.PATH_LIST,
       A.QNTY_LIST,
       (SELECT ROUND(EXP(SUM(LN(TO_NUMBER(REGEXP_SUBSTR(A.QNTY_LIST,'[^,]+',1, LEVEL))))),5)--마농님 댓글 보고 ROUND 추가
          FROM DUAL
       CONNECT BY LEVEL < REGEXP_COUNT(A.QNTY_LIST,',')
       ) RESULT
FROM   (SELECT ROWNUM SEQ, 
               LEVEL LV, 
               T.PARTNOM, 
               T.PARTNO,
               LPAD(' ', (LEVEL-1)*4) || T.PARTNO LV_PARTNO,
               T.QNTY,
               SYS_CONNECT_BY_PATH(T.PARTNO, ',') PATH_LIST,
               SYS_CONNECT_BY_PATH(T.QNTY, ',') || ',' QNTY_LIST
        FROM   T 
        START WITH T.PARTNOM = 'X'
        CONNECT BY PRIOR T.PARTNO = T.PARTNOM) A
ORDER BY A.SEQ
;

--좀더 효율적일 것으로 추측되는 방식
WITH T(PARTNO, PARTNOM, QNTY) AS (
     SELECT 'X', '*', 1 FROM DUAL UNION ALL
     SELECT 'A', 'X', 1 FROM DUAL UNION ALL
     SELECT 'B', 'X', 2 FROM DUAL UNION ALL
     SELECT 'C', 'B', 3 FROM DUAL UNION ALL
     SELECT 'E', 'C', 3 FROM DUAL UNION ALL
     SELECT 'F', 'C', 4 FROM DUAL UNION ALL
     SELECT 'I', 'C', 5 FROM DUAL UNION ALL
     SELECT 'D', 'B', 4 FROM DUAL UNION ALL
     SELECT 'H', 'D', 2 FROM DUAL UNION ALL
     SELECT 'C', 'J', 6 FROM DUAL UNION ALL
     SELECT 'J', 'D', 4 FROM DUAL UNION ALL
     SELECT 'K', 'D', 3 FROM DUAL
)
SELECT A.SEQ,
       A.LV,
       A.PARTNOM,
       A.PARTNO,
       A.LV_PARTNO,
       A.QNTY,
       A.PATH_LIST,
       A.QNTY_LIST,
       TO_NUMBER(DBMS_XMLGEN.GETXMLTYPE (
                'SELECT ' || REPLACE(TRIM(BOTH ',' FROM A.QNTY_LIST ),',','*') || 
                ' FROM DUAL').EXTRACT( '//text()' )) RESULT
FROM   (SELECT ROWNUM SEQ, 
               LEVEL LV, 
               T.PARTNOM, 
               T.PARTNO,
               LPAD(' ', (LEVEL-1)*4) || T.PARTNO LV_PARTNO,
               T.QNTY,
               SYS_CONNECT_BY_PATH(T.PARTNO, ',') PATH_LIST,
               SYS_CONNECT_BY_PATH(T.QNTY, ',') || ',' QNTY_LIST
        FROM   T 
        START WITH T.PARTNOM = 'X'
        CONNECT BY PRIOR T.PARTNO = T.PARTNOM) A
ORDER BY A.SEQ
;

by 박군two [2017.10.12 10:29:26]

JKSON님 너무 감사합니다 

DBMS_XMLGEN 전에 써본적은 있는데 응용력이 부족하네요


by 마농 [2017.10.11 19:33:47]
WITH t(partno, partnom, qnty) AS
(
SELECT 'X', '*', 1 FROM dual
UNION ALL SELECT 'A', 'X', 1 FROM dual
UNION ALL SELECT 'B', 'X', 2 FROM dual
UNION ALL SELECT 'C', 'B', 3 FROM dual
UNION ALL SELECT 'E', 'C', 3 FROM dual
UNION ALL SELECT 'F', 'C', 4 FROM dual
UNION ALL SELECT 'I', 'C', 5 FROM dual
UNION ALL SELECT 'D', 'B', 4 FROM dual
UNION ALL SELECT 'H', 'D', 2 FROM dual
UNION ALL SELECT 'C', 'J', 6 FROM dual
UNION ALL SELECT 'J', 'D', 4 FROM dual
UNION ALL SELECT 'K', 'D', 3 FROM dual
)
-- 11G Recursive SQL --
, t1(lv, partnom, partno, lv_partno, qnty, path_list, qnty_list, result) AS
(
SELECT 1 lv
     , partnom
     , partno
     , CAST(partno      AS VARCHAR(99)) lv_partno
     , qnty
     , CAST(','||partno AS VARCHAR(99)) path_list
     , CAST(','||qnty   AS VARCHAR(99)) qnty_list
     , qnty result
  FROM t
 WHERE partnom = '*'
 UNION ALL
SELECT a.lv + 1 lv
     , b.partnom
     , b.partno
     , LPAD(' ', a.lv*4) || b.partno lv_partno
     , b.qnty
     , a.path_list||','||b.partno path_list
     , a.qnty_list||','||b.qnty   qnty_list
     , a.result * b.qnty result
  FROM t1 a
     , t  b
 WHERE b.partnom = a.partno
)
SEARCH DEPTH FIRST BY partno SET seq 
SELECT *
  FROM t1
;

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입