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

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

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 까지 못들어간다는거는 처음 알았네요

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

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(

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

Rich Text Editor, commentnote

Editor toolbars

Press ALT 0 for help

◢Elements path

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

데이터베이스 질문과 답변