꿈꾸는 개발자, 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 까지 못들어간다는거는 처음 알았네요

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(

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

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

데이터베이스 질문과 답변