안녕하세요.
날씨가 많이 선선해졌네요~ 다들 건강 잘 챙기시길 바랍니다.
다름이 아니고 논리게이트 관련 계산 질문 드립니다.
구조는 위와 같고, 데이터는 다음과 같습니다.
WITH T1 AS (
SELECT 0 AS ID, 'ND' AS UNIT, NULL AS VAL FROM DUAL
UNION ALL SELECT 1, 'AG', NULL FROM DUAL
UNION ALL SELECT 2, 'ND', NULL FROM DUAL
UNION ALL SELECT 3, 'OG', NULL FROM DUAL
UNION ALL SELECT 4, 'ND', 0.003 FROM DUAL
UNION ALL SELECT 5, 'ND', 0.004 FROM DUAL
UNION ALL SELECT 6, 'ND', 0.005 FROM DUAL
)
SELECT * FROM T1;
WITH T2 AS (
SELECT 0 AS START_NODE_ID, 1 AS END_NODE_ID FROM DUAL
UNION ALL SELECT 1 AS START_NODE_ID, 2 AS END_NODE_ID FROM DUAL
UNION ALL SELECT 2 AS START_NODE_ID, 3 AS END_NODE_ID FROM DUAL
UNION ALL SELECT 3 AS START_NODE_ID, 4 AS END_NODE_ID FROM DUAL
UNION ALL SELECT 3 AS START_NODE_ID, 5 AS END_NODE_ID FROM DUAL
UNION ALL SELECT 1 AS START_NODE_ID, 6 AS END_NODE_ID FROM DUAL
)
SELECT * FROM T2;
ID 0, 2의 값을 생성하고 싶습니다.
AND는 곱 OR는 합이고,
ID 2의 값은 0.007
ID 0의 값은 0.000035 입니다.
WITH t1 AS ( SELECT 0 id, 'ND' unit, NULL val FROM dual UNION ALL SELECT 1, 'AG', NULL FROM dual UNION ALL SELECT 2, 'ND', NULL FROM dual UNION ALL SELECT 3, 'OG', NULL FROM dual UNION ALL SELECT 4, 'ND', 0.003 FROM dual UNION ALL SELECT 5, 'ND', 0.004 FROM dual UNION ALL SELECT 6, 'ND', 0.005 FROM dual ) , t2 AS ( SELECT 0 start_node_id, 1 end_node_id FROM dual UNION ALL SELECT 1, 2 FROM dual UNION ALL SELECT 2, 3 FROM dual UNION ALL SELECT 3, 4 FROM dual UNION ALL SELECT 3, 5 FROM dual UNION ALL SELECT 1, 6 FROM dual ) , t3 AS ( SELECT id , val , val || '' v , '' unit FROM t1 WHERE val IS NOT NULL UNION ALL SELECT a.start_node_id id , a.end_node_id val , 'a' || a.end_node_id || 'a' v , DECODE(unit, 'AG', '*', 'OG', '+') unit FROM t2 a , t1 b WHERE b.id = a.start_node_id ) SELECT TO_NUMBER( dbms_xmlgen.getxmltype( 'SELECT ' || v || ' FROM dual').Extract('//text()' ) ) x FROM (SELECT ROW_NUMBER() OVER(ORDER BY MIN(ROWNUM)) rn , id , '('||LISTAGG(v, unit) WITHIN GROUP(ORDER BY ROWNUM)||')' v FROM t3 GROUP BY id, unit START WITH id = 2 -- 조건 CONNECT BY PRIOR val = id ) MODEL RETURN UPDATED ROWS DIMENSION BY (rn) MEASURES ( id, v ) RULES ITERATE (20) ( v[1] = REPLACE( v[1] , 'a'||id[ITERATION_NUMBER+2]||'a' , v[ITERATION_NUMBER+2] ) ) ; -- http://database.sarang.net/?inc=read&aid=40811&criteria=oracle
원 쿼리로 해결 못 하는 저는.. 재귀함수를 사용해봤습니다.
--재귀함수 생성 CREATE OR REPLACE FUNCTION FC_TMP_CALC(IN_NODE NUMBER, IN_GB VARCHAR2) RETURN NUMBER IS V_FORMULA NUMBER; BEGIN FOR CUR IN (SELECT A.ID, A.UNIT, TO_CHAR(A.VAL) VAL FROM TMP_20170919_B B , TMP_20170919_A A WHERE B.START_NODE_ID = IN_NODE AND B.END_NODE_ID = A.ID) LOOP IF CUR.VAL IS NULL THEN IF V_FORMULA IS NULL THEN V_FORMULA := FC_TMP_CALC(CUR.ID, CUR.UNIT); ELSE IF IN_GB = 'AG' THEN V_FORMULA := V_FORMULA * (FC_TMP_CALC(CUR.ID, CUR.UNIT)); ELSIF IN_GB = 'OG' THEN V_FORMULA := V_FORMULA + (FC_TMP_CALC(CUR.ID, CUR.UNIT)); END IF; END IF; ELSE IF V_FORMULA IS NULL THEN V_FORMULA := CUR.VAL; ELSE IF IN_GB = 'AG' THEN V_FORMULA := V_FORMULA * CUR.VAL; ELSIF IN_GB = 'OG' THEN V_FORMULA := V_FORMULA + CUR.VAL; END IF; END IF; END IF; END LOOP; RETURN V_FORMULA; END; --테이블 생성 CREATE TABLE TMP_20170919_A ( ID NUMBER, UNIT VARCHAR2(2 BYTE), VAL NUMBER ) CREATE TABLE TMP_20170919_B ( START_NODE_ID NUMBER, END_NODE_ID NUMBER ) --데이터 삽입 INSERT INTO TMP_20170919_A SELECT 0 id, 'ND' unit, NULL val FROM dual UNION ALL SELECT 1, 'AG', NULL FROM dual UNION ALL SELECT 2, 'ND', NULL FROM dual UNION ALL SELECT 3, 'OG', NULL FROM dual UNION ALL SELECT 4, 'ND', 0.003 FROM dual UNION ALL SELECT 5, 'ND', 0.004 FROM dual UNION ALL SELECT 6, 'ND', 0.005 FROM dual INSERT INTO TMP_20170919_B SELECT 0 start_node_id, 1 end_node_id FROM dual UNION ALL SELECT 1, 2 FROM dual UNION ALL SELECT 2, 3 FROM dual UNION ALL SELECT 3, 4 FROM dual UNION ALL SELECT 3, 5 FROM dual UNION ALL SELECT 1, 6 FROM dual --사용 SELECT FC_TMP_CALC(2 --> 시작 node ,NULL) VAL FROM DUAL