Rule expression의 데이타 변환 0 7 328

by MS [SQL Query] [2022.01.22 16:50:38]


복잡한 Rule expression를 간소화 해서 계산을 빠르게 하기 위한 기초 데이타 변환 작업입니다.

아래 정보는 기본 테이블 입니다.

table : basic   
No Family Feature
1 12080 12689
2 12080 12690
3 12090 12811
4 12090 12812
5 12134 12687
6 12134 12688
7 12134 12986
8 12134 12987
9 12134 12988
10 12135 12989
11 12135 12990
12 12135 12991
13 12135 12992
14 12135 12993
15 12135 12994
16 12135 12995
17 12135 12996
18 12135 12997
19 12135 12998
20 12135 12999
21 12146 13049
22 12146 13050
23 12221 15010
24 12221 15011
25 12234 16031
26 12234 16034
27 12234 16036
28 12234 16037

 

table : ruleifthen  
No RULE_EXP1 RULE_EXP2
1 &12688 &13049/15010
2 &12986/12687 &13050/15011
3 &12988 &13050/15011&16031
4 &12988&12689&12811 &12998
5 &12687&12689&12811 &12999
6 &12997 &16034
7 &16036 &13050&15010/16037

 

아래 부분은 원하는 두개의 결과 테이블 정보입니다.

resultA 테이블에 대한 설명:

1. resultA의 id는 family를 고려한 2진법으로 구분하기 위한 표현방식

 
2 resultA의 Nodeid는 family와 expression 을 고려한 방식이다. 

 

table : resultA 

         
No Family Feature Id Node Seq NodeId
1 12080 12689 b0 0 0 !b0
2 12080 12690 b0 1 1 b0
3 12090 12811 b1 0 0 !b1
4 12090 12812 b1 1 1 b1
5 12134 12687 b2b3b4 000 0 (!b2&&!b3&&!b3)
6 12134 12688 b2b3b4 001 1 (!b2&&!b3&&b3)
7 12134 12986 b2b3b4 010 2 (!b2&&b3&&!b3)
8 12134 12987 b2b3b4 011 3 (!b2&&b3&&b3)
9 12134 12988 b2b3b4 100 4 (b2&&!b3&&!b3)
10 12135 12989 b5b6b7b8 0000 0 (!b5&&!b6&&!b7&&!b8)
11 12135 12990 b5b6b7b8 0001 1 (!b5&&!b6&&!b7&&b8)
12 12135 12991 b5b6b7b8 0010 2 (!b5&&!b6&&b7&&!b8)
13 12135 12992 b5b6b7b8 0011 3 (!b5&&!b6&&b7&&b8)
14 12135 12993 b5b6b7b8 0100 4 (!b5&&b6&&!b7&&!b8)
15 12135 12994 b5b6b7b8 0101 5 (!b5&&b6&&!b7&&b8)
16 12135 12995 b5b6b7b8 0110 6 (!b5&&b6&&b7&&!b8)
17 12135 12996 b5b6b7b8 0111 7 (!b5&&b6&&b7&&b8)
18 12135 12997 b5b6b7b8 1000 8 (b5&&!b6&&!b7&&!b8)
19 12135 12998 b5b6b7b8 1001 9 (b5&&!b6&&!b7&&b8)
20 12135 12999 b5b6b7b8 1010 10 (b5&&!b6&&b7&&!b8)
21 12146 13049 b9 0 0 !b9
22 12146 13050 b9 1 1 b9
23 12221 15010 b10 0 0 !b10
24 12221 15011 b10 1 1 b10
25 12234 16031 b11b12 00 0 (!b11&&!b12)
26 12234 16034 b11b12 01 1 (!b11&&b12)
27 12234 16036 b11b12 10 2 (b11&&!b12)
28 12234 16037 b11b12 11 3 (b11&&b12)

ruleResult에 대한 설명

1. RULE_EXP1와 RULE_EXP2의 expression을 구분하기 위해 ()로 묶는다.

 
2. RULE_EXP1와 RULE_EXP2를 && 로 묶는다.      

table : ruleResult

RULE_EXP3
((!b2&&!b3&&b3)) && (!b9/!b10)
((!b2&&b3&&!b3)/(!b2&&!b3&&!b3)) && (b9/b10)
((b2&&!b3&&!b3)) && (13050/15011&16031)
((b2&&!b3&&!b3)&!b0&!b1) && ((b5&&!b6&&!b7&&b8))
((!b2&&!b3&&!b3)&!b0&!b1) && ((b5&&!b6&&b7&&!b8))
((b5&&!b6&&!b7&&!b8)) && ((!b11&&b12))
((b11&&!b12)) && (b9&!b10/(b11&&b12))
by 마농 [2022.01.24 10:31:36]
WITH basic AS
(
SELECT 1 no, 12080 family, 12689 feature FROM dual
UNION ALL SELECT  2, 12080, 12690 FROM dual
UNION ALL SELECT  3, 12090, 12811 FROM dual
UNION ALL SELECT  4, 12090, 12812 FROM dual
UNION ALL SELECT  5, 12134, 12687 FROM dual
UNION ALL SELECT  6, 12134, 12688 FROM dual
UNION ALL SELECT  7, 12134, 12986 FROM dual
UNION ALL SELECT  8, 12134, 12987 FROM dual
UNION ALL SELECT  9, 12134, 12988 FROM dual
UNION ALL SELECT 10, 12135, 12989 FROM dual
UNION ALL SELECT 11, 12135, 12990 FROM dual
UNION ALL SELECT 12, 12135, 12991 FROM dual
UNION ALL SELECT 13, 12135, 12992 FROM dual
UNION ALL SELECT 14, 12135, 12993 FROM dual
UNION ALL SELECT 15, 12135, 12994 FROM dual
UNION ALL SELECT 16, 12135, 12995 FROM dual
UNION ALL SELECT 17, 12135, 12996 FROM dual
UNION ALL SELECT 18, 12135, 12997 FROM dual
UNION ALL SELECT 19, 12135, 12998 FROM dual
UNION ALL SELECT 20, 12135, 12999 FROM dual
UNION ALL SELECT 21, 12146, 13049 FROM dual
UNION ALL SELECT 22, 12146, 13050 FROM dual
UNION ALL SELECT 23, 12221, 15010 FROM dual
UNION ALL SELECT 24, 12221, 15011 FROM dual
UNION ALL SELECT 25, 12234, 16031 FROM dual
UNION ALL SELECT 26, 12234, 16034 FROM dual
UNION ALL SELECT 27, 12234, 16036 FROM dual
UNION ALL SELECT 28, 12234, 16037 FROM dual
)
-- resultA --
SELECT no, family, feature
     , LISTAGG('b' || id) WITHIN GROUP(ORDER BY id) id
     , LISTAGG(node) WITHIN GROUP(ORDER BY id) node
     , seq
     , DECODE(cnt, 1, '', '(') ||
       LISTAGG(DECODE(node, 1, '', '!') || 'b' || id, '&&') WITHIN GROUP(ORDER BY id)
       || DECODE(cnt, 1, '', ')') NodeId
  FROM (SELECT no, family, feature
             , cnt, seq, lv
             , DENSE_RANK() OVER(ORDER BY family, lv DESC) - 1 id
             , SIGN(BITAND(seq, POWER(2, lv-1))) node
          FROM (SELECT no, family, feature
                     , COUNT(*) OVER(PARTITION BY family) - 1 cnt
                     , ROW_NUMBER() OVER(PARTITION BY family ORDER BY no) - 1 seq
                  FROM basic
                )
             , (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 9)
         WHERE POWER(2, lv-1) <= cnt
        )
 GROUP BY no, family, feature, cnt, seq
;

 


by 마농 [2022.01.24 10:59:30]
SET DEFINE OFF;

WITH basic AS
(
SELECT 1 no, 12080 family, 12689 feature FROM dual
UNION ALL SELECT  2, 12080, 12690 FROM dual
UNION ALL SELECT  3, 12090, 12811 FROM dual
UNION ALL SELECT  4, 12090, 12812 FROM dual
UNION ALL SELECT  5, 12134, 12687 FROM dual
UNION ALL SELECT  6, 12134, 12688 FROM dual
UNION ALL SELECT  7, 12134, 12986 FROM dual
UNION ALL SELECT  8, 12134, 12987 FROM dual
UNION ALL SELECT  9, 12134, 12988 FROM dual
UNION ALL SELECT 10, 12135, 12989 FROM dual
UNION ALL SELECT 11, 12135, 12990 FROM dual
UNION ALL SELECT 12, 12135, 12991 FROM dual
UNION ALL SELECT 13, 12135, 12992 FROM dual
UNION ALL SELECT 14, 12135, 12993 FROM dual
UNION ALL SELECT 15, 12135, 12994 FROM dual
UNION ALL SELECT 16, 12135, 12995 FROM dual
UNION ALL SELECT 17, 12135, 12996 FROM dual
UNION ALL SELECT 18, 12135, 12997 FROM dual
UNION ALL SELECT 19, 12135, 12998 FROM dual
UNION ALL SELECT 20, 12135, 12999 FROM dual
UNION ALL SELECT 21, 12146, 13049 FROM dual
UNION ALL SELECT 22, 12146, 13050 FROM dual
UNION ALL SELECT 23, 12221, 15010 FROM dual
UNION ALL SELECT 24, 12221, 15011 FROM dual
UNION ALL SELECT 25, 12234, 16031 FROM dual
UNION ALL SELECT 26, 12234, 16034 FROM dual
UNION ALL SELECT 27, 12234, 16036 FROM dual
UNION ALL SELECT 28, 12234, 16037 FROM dual
)
, ruleifthen AS
(
SELECT 1 no, '&12688' rule_exp1, '&13049/15010' rule_exp2      FROM dual
UNION ALL SELECT 2, '&12986/12687'      , '&13050/15011'       FROM dual
UNION ALL SELECT 3, '&12988'            , '&13050/15011&16031' FROM dual
UNION ALL SELECT 4, '&12988&12689&12811', '&12998'             FROM dual
UNION ALL SELECT 5, '&12687&12689&12811', '&12999'             FROM dual
UNION ALL SELECT 6, '&12997'            , '&16034'             FROM dual
UNION ALL SELECT 7, '&16036'            , '&13050&15010/16037' FROM dual
)
, resultA AS
(
SELECT no, family, feature
     , LISTAGG('b' || id) WITHIN GROUP(ORDER BY id) id
     , LISTAGG(node) WITHIN GROUP(ORDER BY id) node
     , seq
     , DECODE(cnt, 1, '', '(') ||
       LISTAGG(DECODE(node, 1, '', '!') || 'b' || id, '&&') WITHIN GROUP(ORDER BY id)
       || DECODE(cnt, 1, '', ')') NodeId
  FROM (SELECT no, family, feature
             , cnt, seq, lv
             , DENSE_RANK() OVER(ORDER BY family, lv DESC) - 1 id
             , SIGN(BITAND(seq, POWER(2, lv-1))) node
          FROM (SELECT no, family, feature
                     , COUNT(*) OVER(PARTITION BY family) - 1 cnt
                     , ROW_NUMBER() OVER(PARTITION BY family ORDER BY no) - 1 seq
                  FROM basic
                )
             , (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 9)
         WHERE POWER(2, lv-1) <= cnt
        )
 GROUP BY no, family, feature, cnt, seq
)
-- ruleResult --
SELECT a.no
     , a.rule_exp1
     , a.rule_exp2
     , '(' || a.rule_exp3 || ') && (' ||
       SUBSTR(
       LISTAGG(SUBSTR(a.rule_exp2, INSTR(a.rule_exp2, b.feature)-1, 1) || b.nodeid)
             WITHIN GROUP(ORDER BY INSTR(a.rule_exp2, b.feature)), 2)
       || ')' rule_exp3
  FROM (SELECT a.no
             , a.rule_exp1
             , a.rule_exp2
             , SUBSTR(
               LISTAGG(SUBSTR(a.rule_exp1, INSTR(a.rule_exp1, b.feature)-1, 1) || b.nodeid)
                     WITHIN GROUP(ORDER BY INSTR(a.rule_exp1, b.feature)), 2) rule_exp3
          FROM ruleifthen a
             , resulta b
         WHERE INSTR(a.rule_exp1, b.feature) > 0
         GROUP BY a.no, a.rule_exp1, a.rule_exp2
        ) a
     , resulta b
 WHERE INSTR(a.rule_exp2, b.feature) > 0
 GROUP BY a.no, a.rule_exp1, a.rule_exp2, a.rule_exp3
;

 


by MS [2022.01.24 14:09:54]

정말 감사합니다. 구정 잘 보내세요.


by MS [2022.01.25 16:48:00]

실제 사용하다보니 한가지 다른 부분을 발견했습니다.

table : ruleResult 의 첫번째 결과가 ((!b2&&!b3&&b3)) && (!b9/!b10) 이어야 하는데 ((!b2&&!b3&&b3)) && (!b9&&!b10)로 결과가 나옵니다.

수고하세요.

 
 

 


by 마농 [2022.01.25 16:51:56]

어제 결과 다른거 확인하고,
다시 수정해서 올려 놨는데.
수정전 쿼리 실행하셨나 보네요.


by 마농 [2022.01.25 16:55:01]

아! 그리고 b3 두번 나오는거 아닌 듯 한데요? b3b4 로 나오는게 맞죠?


by MS [2022.01.25 20:11:55]

맞습니다. 역시 마농님 이시네요. 감사합니다.

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