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

Rule expression의 데이타 변환

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(

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

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

데이터베이스 질문과 답변