database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
ㆍOracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
Oracle Q&A 40817 게시물 읽기
No. 40817
쿼리 퀴즈입니다. SQL로 카운트다운 문제 풀기
작성자
김흥수(protokhs)
작성일
2015-06-04 10:05
조회수
10,047

 SQL로 카운트다운 문제 풀기

 
카운트 다운 문제는 다음과 같다.
임의의 n개의 자연수가 주어지고 목표로 하는 값 x 가 주어지면
이들 n개의 자연수를 가지고 최대 1번만 사용하여 가감승제 수식을 만들어서 원하는 목표값이 되는 수식을 찾는 것이다.
단 수식에서 교환 법칙및 결합법칙을 사용하여 동일한 식이라도 그 모양이 서로 다르면 서로 다른 식으로 본다.
(ex (1+2) 와 (2+1) 은 두개의 식으로 본다)
 
예를 들어 1,2,3 이라는 세개의 숫자를 사용하여 9를 만들어보면
(1+2)*3
(2+1)*3
3*(1+2)
3*(2+1)
이 될 것이다.
 
만약 1,3,7,10으로 210이라는 숫자를 구하면 다음과 같은 수식들이 나온다.
(((1*10)*3)*7)
(((1*10)*7)*3)
(((1*3)*10)*7)
(((1*3)*7)*10)
(((1*7)*10)*3)
(((1*7)*3)*10)
(((10*1)*3)*7)
(((10*1)*7)*3)
(((10*3)*1)*7)
(((10*3)*7)*1)
(((10*3)*7)/1)
(((10*3)/1)*7)
(((10*7)*1)*3)
(((10*7)*3)*1)
(((10*7)*3)/1)
(((10*7)/1)*3)
(((10/1)*3)*7)
(((10/1)*7)*3)
(((3*1)*10)*7)
(((3*1)*7)*10)
(((3*10)*1)*7)
(((3*10)*7)*1)
(((3*10)*7)/1)
(((3*10)/1)*7)
(((3*7)*1)*10)
(((3*7)*10)*1)
(((3*7)*10)/1)
(((3*7)/1)*10)
(((3/1)*10)*7)
(((3/1)*7)*10)
(((7*1)*10)*3)
(((7*1)*3)*10)
(((7*10)*1)*3)
(((7*10)*3)*1)
(((7*10)*3)/1)
(((7*10)/1)*3)
(((7*3)*1)*10)
(((7*3)*10)*1)
(((7*3)*10)/1)
(((7*3)/1)*10)
(((7/1)*10)*3)
(((7/1)*3)*10)
((1*(10*3))*7)
((1*(10*7))*3)
((1*(3*10))*7)
((1*(3*7))*10)
((1*(7*10))*3)
((1*(7*3))*10)
((1*10)*(3*7))
((1*10)*(7*3))
((1*3)*(10*7))
((1*3)*(7*10))
((1*7)*(10*3))
((1*7)*(3*10))
((10*(1*3))*7)
((10*(1*7))*3)
((10*(3*1))*7)
((10*(3*7))*1)
((10*(3*7))/1)
((10*(3/1))*7)
((10*(7*1))*3)
((10*(7*3))*1)
((10*(7*3))/1)
((10*(7/1))*3)
((10*1)*(3*7))
((10*1)*(7*3))
((10*3)*(1*7))
((10*3)*(7*1))
((10*3)*(7/1))
((10*3)*7)
((10*3)/(1/7))
((10*7)*(1*3))
((10*7)*(3*1))
((10*7)*(3/1))
((10*7)*3)
((10*7)/(1/3))
((10/(1/3))*7)
((10/(1/7))*3)
((10/1)*(3*7))
((10/1)*(7*3))
((3*(1*10))*7)
((3*(1*7))*10)
((3*(10*1))*7)
((3*(10*7))*1)
((3*(10*7))/1)
((3*(10/1))*7)
((3*(7*1))*10)
((3*(7*10))*1)
((3*(7*10))/1)
((3*(7/1))*10)
((3*1)*(10*7))
((3*1)*(7*10))
((3*10)*(1*7))
((3*10)*(7*1))
((3*10)*(7/1))
((3*10)*7)
((3*10)/(1/7))
((3*7)*(1*10))
((3*7)*(10*1))
((3*7)*(10/1))
((3*7)*10)
((3*7)/(1/10))
((3/(1/10))*7)
((3/(1/7))*10)
((3/1)*(10*7))
((3/1)*(7*10))
((7*(1*10))*3)
((7*(1*3))*10)
((7*(10*1))*3)
((7*(10*3))*1)
((7*(10*3))/1)
((7*(10/1))*3)
((7*(3*1))*10)
((7*(3*10))*1)
((7*(3*10))/1)
((7*(3/1))*10)
((7*1)*(10*3))
((7*1)*(3*10))
((7*10)*(1*3))
((7*10)*(3*1))
((7*10)*(3/1))
((7*10)*3)
((7*10)/(1/3))
((7*3)*(1*10))
((7*3)*(10*1))
((7*3)*(10/1))
((7*3)*10)
((7*3)/(1/10))
((7/(1/10))*3)
((7/(1/3))*10)
((7/1)*(10*3))
((7/1)*(3*10))
(1*((10*3)*7))
(1*((10*7)*3))
(1*((3*10)*7))
(1*((3*7)*10))
(1*((7*10)*3))
(1*((7*3)*10))
(1*(10*(3*7)))
(1*(10*(7*3)))
(1*(3*(10*7)))
(1*(3*(7*10)))
(1*(7*(10*3)))
(1*(7*(3*10)))
(10*((1*3)*7))
(10*((1*7)*3))
(10*((3*1)*7))
(10*((3*7)*1))
(10*((3*7)/1))
(10*((3/1)*7))
(10*((7*1)*3))
(10*((7*3)*1))
(10*((7*3)/1))
(10*((7/1)*3))
(10*(1*(3*7)))
(10*(1*(7*3)))
(10*(3*(1*7)))
(10*(3*(7*1)))
(10*(3*(7/1)))
(10*(3*7))
(10*(3/(1/7)))
(10*(7*(1*3)))
(10*(7*(3*1)))
(10*(7*(3/1)))
(10*(7*3))
(10*(7/(1/3)))
(10/((1/3)/7))
(10/((1/7)/3))
(10/(1/(3*7)))
(10/(1/(7*3)))
(3*((1*10)*7))
(3*((1*7)*10))
(3*((10*1)*7))
(3*((10*7)*1))
(3*((10*7)/1))
(3*((10/1)*7))
(3*((7*1)*10))
(3*((7*10)*1))
(3*((7*10)/1))
(3*((7/1)*10))
(3*(1*(10*7)))
(3*(1*(7*10)))
(3*(10*(1*7)))
(3*(10*(7*1)))
(3*(10*(7/1)))
(3*(10*7))
(3*(10/(1/7)))
(3*(7*(1*10)))
(3*(7*(10*1)))
(3*(7*(10/1)))
(3*(7*10))
(3*(7/(1/10)))
(3/((1/10)/7))
(3/((1/7)/10))
(3/(1/(10*7)))
(3/(1/(7*10)))
(7*((1*10)*3))
(7*((1*3)*10))
(7*((10*1)*3))
(7*((10*3)*1))
(7*((10*3)/1))
(7*((10/1)*3))
(7*((3*1)*10))
(7*((3*10)*1))
(7*((3*10)/1))
(7*((3/1)*10))
(7*(1*(10*3)))
(7*(1*(3*10)))
(7*(10*(1*3)))
(7*(10*(3*1)))
(7*(10*(3/1)))
(7*(10*3))
(7*(10/(1/3)))
(7*(3*(1*10)))
(7*(3*(10*1)))
(7*(3*(10/1)))
(7*(3*10))
(7*(3/(1/10)))
(7/((1/10)/3))
(7/((1/3)/10))
(7/(1/(10*3)))
(7/(1/(3*10)))
 
앞서 제출한 퀴즈들
(
SQL로 구문트리화된 수식을 계산하기(database.sarang.net/index.php)
부분수열의 순열들을 모두 구하기 (공집합은 제외)(database.sarang.net/index.php)
SQL로 leaf node가 n개인 모든 이진 트리 구조를 구하기(database.sarang.net/index.php)
)
은 이 문제를 풀기 위한 부분 문제들입니다.
 
그런데 퀴즈를 제출하고 나서 퀴즈에 대한 답을 푸는 과정에서
마농님이 "SQL로 구문트리화된 수식을 계산하기"에서 제시한 방법을
약간 바꿔서 응용하면 "SQL로 leaf node가 n개인 모든 이진 트리 구조를 구하기" 문제를
풀지 않고도 카운트 다운 문제를 푸는 방법이 있다는 것을 알게 되었습니다.
그래서 "SQL로 leaf node가 n개인 모든 이진 트리 구조를 구하기" 문제와 병행하여
이 문제도 제출합니다.
 
마농님 덕에 새로운 풀이를 알게 되어 퀴즈를 제출한 보람이 있었습니다.
감사합니다.^^
 
이 글에 대한 댓글이 총 1건 있습니다.

 내가 생각한 답은 다음과 같다.

with base_data as (

    select '1,2,3' v from dual

)

, 부분순열 as

(

    select

        a.순열번호

        ,a.path v

    from

        (

            select

                rownum 순열번호

                ,substr(sys_connect_by_path(v, ','), 2) path

            from

                (

                    select

                        regexp_substr(t1.v, '[^,]+', 1, level) v

                    from base_data t1

                    connect by level <= regexp_count(t1.v, '[^,]+')

            ) x

            connect by nocycle prior x.v != x.v

              order siblings by v

        ) a

)

, base_ops as

(

    select '+' op from dual union all

    select '-' op from dual union all

    select '*' op from dual union all

    select '/' op from dual

)

, base_recur ( n, v , vs ,cnt) as

(

    select

        a.순열번호 n

        , a.v

        , a.v || '@1@ '||'@1' vs

        ,0

    from    부분순열 a

    union all

    select

        a.n n

        , a.v

        ,replace(a.vs,regexp_substr(b.column_value,'[^$]+',1,2),replace(regexp_substr(b.column_value,'[^$]+',1,2),',','~')||'@'||c.op) || '#'||

        substr(regexp_substr(regexp_substr(b.column_value,'[^$]+',1,2),'[^@]+',1,1),1,instr(regexp_substr(regexp_substr(b.column_value,'[^$]+',1,2),'[^@]+',1,1),',',1,to_number(regexp_substr(b.column_value,'[^$]+',1,1))) - 1) || '@' ||

        (a.cnt * 2 + 2 ) ||'@'||

        regexp_substr(regexp_substr(b.column_value,'[^$]+',1,2),'[^@]+',1,2) ||'@1'||'#'||

        substr(regexp_substr(regexp_substr(b.column_value,'[^$]+',1,2),'[^@]+',1,1),1 + instr(regexp_substr(regexp_substr(b.column_value,'[^$]+',1,2),'[^@]+',1,1),',',1,to_number(regexp_substr(b.column_value,'[^$]+',1,1)))) ||'@' ||

        (a.cnt * 2 + 3 ) ||'@' ||

        regexp_substr(regexp_substr(b.column_value,'[^$]+',1,2),'[^@]+',1,2) ||'@2'

        ,cnt + 1

    from    base_recur a

            , table(

                select

                    collect(level ||'$'||column_value)

                from

                    table(

                        select

                            collect(column_value)

                        from

                            table(

                                select

                                    collect(regexp_substr(a.vs,'[^#]+',1,level))

                                from    dual

                                where   regexp_substr(a.vs,'[^#]+',1,level) is not null

                                connect by

                                    level <= regexp_count(a.vs,'[^#]+')

                            )

                        where   regexp_count(column_value,'[,]+') > 0

                        and     rownum < 2

                    )

                where   column_value is not null

                connect by

                    level <= regexp_count(regexp_substr(column_value , '[^@]+',1,1),'[,]+')

            ) b

            ,base_ops c

        where   cnt < 100

)

, base_make as

(

    select

        a.n

        , a.v

        ,a.vs

        ,rownum grp

    from    base_recur a

    where   instr(a.vs ,',') = 0

)

, 구문트리 as

(

    select

        a.n 순열번호

        , a.grp 산식번호

        ,regexp_substr(b.column_value,'[^@]+',1,2) 노드번호

        ,trim(regexp_substr(b.column_value,'[^@]+',1,3)) 부모노드

        ,to_number(regexp_substr(b.column_value,'[^@]+',1,4)) 순서

        ,to_number(case when instr(regexp_substr(b.column_value,'[^@]+',1,1),'~') > 0 then null else regexp_substr(b.column_value,'[^@]+',1,1) end) 노드값

        ,case when instr(regexp_substr(b.column_value,'[^@]+',1,1),'~') > 0 then regexp_substr(b.column_value,'[^@]+',1,5) else null end 연산자

    from    base_make a

            , table ( select collect (regexp_substr(a.vs,'[^#]+',1,level)) from dual connect by level <= regexp_count(a.vs,'[^#]+',1)) b

)

, 계층화된산식 as

(

    select

        a.순열번호

        , a.산식번호

        , a.노드번호

        , a.연산자

        , a.노드값

        , a.순서

        , a.부모노드

        , a.레벨

        , row_number() over ( partition by a.산식번호 order by a.산식번호,a.순번) 순번

        , count(*) over ( partition by a.산식번호 ) 노드갯수

    from

        (

            select

                a.순열번호

                , a.산식번호

                , a.노드번호

                , a.연산자

                , a.노드값

                , a.순서

                , a.부모노드

                , level 레벨

                , rownum 순번

            from    구문트리 a 

            connect by

                prior   a.노드번호 = a.부모노드

            and prior   a.산식번호 = a.산식번호

            and prior   a.순열번호 = a.순열번호

            start with a.부모노드 is null

            order siblings by

                a.순서

        ) a

)

,  recur_base ( 순열번호,산식번호,노드번호,연산자,노드값,순서,부모노드,레벨,순번,연산결과,연산식결과 ) as

(

    select

        a.순열번호

        , a.산식번호

        , a.노드번호

        , a.연산자

        , a.노드값

        , a.순서

        , a.부모노드

        , a.레벨

        , a.순번

        , ','||a.노드값||','

        , ','||a.노드값||','

    from    계층화된산식 a

    where   a.순번 = a.노드갯수

    union all

    select

        b.순열번호

        , b.산식번호

        , b.노드번호

        , b.연산자

        , b.노드값

        , b.순서

        , b.부모노드

        , b.레벨

        , b.순번

        , case when b.레벨 < a.레벨 then

            ','||

            case

                when b.연산자 = '+' then

                    (to_number(regexp_substr(a.연산결과, '[^,]+', 1, 1)) + 

                    to_number(regexp_substr(a.연산결과, '[^,]+', 1, 2)))

                when b.연산자 = '-' then

                    (to_number(regexp_substr(a.연산결과, '[^,]+', 1, 1)) - 

                    to_number(regexp_substr(a.연산결과, '[^,]+', 1, 2)))

                when b.연산자 = '*' then

                    (to_number(regexp_substr(a.연산결과, '[^,]+', 1, 1)) *

                    to_number(regexp_substr(a.연산결과, '[^,]+', 1, 2)))

                when b.연산자 = '/' then

                    (to_number(regexp_substr(a.연산결과, '[^,]+', 1, 1)) /

                    nullif(to_number(regexp_substr(a.연산결과, '[^,]+', 1, 2)),0) )

            end || substr(a.연산결과, nvl(nullif(instr(a.연산결과,',',1,3),0),length(a.연산결과)))

        else

            ',' || b.노드값 || a.연산결과

        end

        , case when b.레벨 < a.레벨 then

            ','||

            case when regexp_count(regexp_substr(a.연산식결과, '[^,]+', 1, 1),'\+|\-|\*|/') > 0 then

                '( '||regexp_substr(a.연산식결과, '[^,]+', 1, 1) ||' )'

            else

                regexp_substr(a.연산식결과, '[^,]+', 1, 1)

            end

             || ' '||b.연산자||' '||

            case when regexp_count(regexp_substr(a.연산식결과, '[^,]+', 1, 2),'\+|\-|\*|/') > 0 then

                '( '||regexp_substr(a.연산식결과, '[^,]+', 1, 2) ||' )'

            else

                regexp_substr(a.연산식결과, '[^,]+', 1, 2)

            end

            || substr(a.연산식결과, nvl(nullif(instr(a.연산식결과,',',1,3),0),length(a.연산식결과)))

        else

            ',' || b.노드값 || a.연산식결과

        end

    from    recur_base a

            , 계층화된산식 b

    where   a.순번 - 1 = b.순번

    and     a.산식번호 = b.산식번호

    and     a.순열번호 = b.순열번호

)

, 수식치환 as

(

    select

        to_number(replace( a.연산결과,',')) 연산결과

        ,replace( a.연산식결과,',') 산식

        ,rownum rn

    from    recur_base a

    where   a.순번 = 1

)

select

    a.연산결과

    ,a.산식

from    수식치환 a

where   a.연산결과 = 9

/

약간 설명을 하면
주어진 숫자로 가능한 모든 수식을 구문트리 형태로 구하고 이 구문트리를 평가하여 그 값이 원하는 값인 수식을 찾아서 산식을 출력한 것이다.
 
여기서 몇가지 핵심이 있는데 첫째는 주어진 숫자로 가능한 모든 수식을 어떻게 하면 구하느냐 하는 것이고 두번째는 그렇게 구한 산식을 어떻게 계산하느냐 하는 것이다.
 
첫번째 문제는 다음과 같이 생각할 수 있다.
주어진 숫자로 가능한 모든 수식은 주어진 숫자로 나열 가능한 모든 부분 순열을 구하고 이 부분 순열로 가능한 모든 이진 트리를 구하고 이렇게 구해진 이진 트리의 모든 내부 노드에 사칙연산 연산자를 집어넣어 그 곱집합을 구하면 모든 수식의 구문트리가 만들어진다.
 
두번째 문제는 어려운 점이 하나 있는데, 구문 트리를 평가하려면 구문 트리에 대하여 깊이 우선 탐색을 하면서 그 결과 값을 자식 노드의 연산 결과를 가지고 연산해야 한다는 점이다. 왜 이게 어렵냐면 SQL은 이런 것을 지원하지 않는다.
일반적으로 connect by로 계층구조의 상위집계를 하는 경우나 recursive-with로 계층구조의 상위집계를 하는 경우에 사용하는 방법은 내부 노드와 간노드 간의 연결을 만들고 내부노드를 그룹지어 집계를 하는 형태를 취한다. 그 이유는 recursive-with 같은 것을 사용하더라도 재귀 테이블이 항상 드라이브가 되기 때문에 연산 결과를 트리를 운행하면서 계산하는 방법이 없다.
그래서 이 문제를 극복하기 위해 스택을 도입하여 shift-reduce를 흉내내어 구문트리를 평가한 것이다.
 
 

마농님의 방법(

http://database.sarang.net/?inc=read&aid=40811&criteria=oracle&subcrit=qna&id=&limit=20&keyword=&page=1

데이터베이스를 사랑하는 사람들의 모임 데이터베이스 사랑넷
 
database.sarang.net
본문으로 이동

)을 응용하여 문제를 단순화 하면

 

 

 

with base_data as (

    select '1,2,3' v from dual

)

, 부분순열 as

(

    select

        a.순열번호

        ,a.path v

    from

        (

            select

                rownum 순열번호

                ,substr(sys_connect_by_path(v, ','), 2) path

            from

                (

                    select

                        regexp_substr(t1.v, '[^,]+', 1, level) v

                    from base_data t1

                    connect by level <= regexp_count(t1.v, '[^,]+')

            ) x

            connect by nocycle prior x.v != x.v

              order siblings by v

        ) a

)

, base_ops as

(

    select '+' op from dual union all

    select '-' op from dual union all

    select '*' op from dual union all

    select '/' op from dual

)

, base_recur ( n, v , vs,cnt) as

(

    select

        a.순열번호 n

        , a.v

        , '@'||a.v||'@' vs

        ,0

    from    부분순열 a

    union all

    select

        a.n n

        , a.v

        ,replace(a.vs,

            regexp_substr(b.column_value,'[^$]+',1,1),

            '('||'@'||substr(replace(regexp_substr(b.column_value,'[^$]+',1),'@','') , 1 , instr(replace(regexp_substr(b.column_value,'[^$]+',1),'@',''),',',1,c.column_value) - 1)||'@' || d.op ||

            case when d.op = '/' then 'nullif(' else '' end ||'@'||substr(replace(regexp_substr(b.column_value,'[^$]+',1),'@',''),instr(replace(regexp_substr(b.column_value,'[^$]+',1),'@',''),',',1,c.column_value) + 1)||'@'||case when d.op = '/' then '_0)' else '' end||')'

        ) vs

        ,cnt + 1

    from    base_recur a

            , table(

                select

                    collect(column_value)

                from

                    table (

                        select

                            collect(regexp_substr(a.vs,'@[^@]+@',1,level)||'$'|| regexp_count(regexp_substr(a.vs,'@[^@]+@',1,level),','))

                        from

                            dual

                        connect by

                            level <= regexp_count(a.vs,'@[^@]+@')

                    )

                where   to_number(regexp_substr(column_value,'[^$]+',1,2)) > 0

                and     rownum < 2

            ) b

            , table (

                select

                    collect(level)

                from    dual

                connect by level <= to_number(regexp_substr(b.column_value,'[^$]+',1,2))

            ) c

            , base_ops d

    where   cnt < 20

)

, 만들어진산식 as

(

    select

        distinct

        replace(replace(a.vs,'@',''),'_',',') 산식

    from    base_recur a

    where   instr(a.vs,',') = 0

    and     rownum < 100000000000

)

, 산식계산 as

(

    select

        산식

        , TO_NUMBER(

           dbms_xmlgen.getxmltype(

           'SELECT ' || 산식 || ' FROM dual').Extract('//text()'

           ) ) 연산결과

    from    만들어진산식

)

select

    산식

    ,연산결과

from    산식계산 a

where   연산결과 = 9

/

 
마농님의 방법을 응용하면
구문트리를 만들고 다시 계산하는 2중 작업이 한번에 끝난다.
 
그러나 마농님의 방법은 약간 문제가 있는데

dbms_xmlgen.getxmltype(

           'SELECT ' || 산식 || ' FROM dual').Extract('//text()'

           ) 

이 부분이 데이타가 많아지면 오류(ORA-22813: 연산자 값이 시스템 한계를 넘었습니다)가 난다.


일단 이 문제 해결을 위해 동적 sql을 실행하는 함수를 하나 만들면

create or replace function f_calc

(

    a_v         varchar2

)

Return Number

Is

    n           Number;

Begin

    If a_v Is Null Then

        Return Null;

    End If;

    Execute Immediate ' select '||a_v ||' from dual' into n;

    Return n;

Exception When Others Then

    Return Null;

End;

 

/

 
 
다음과 같이 사용할 수 있다.
with base_data as (
    select '1,3,7,10' v from dual
)
, 부분순열 as
(
    select
        a.순열번호
        ,a.path v
    from
        (
            select
                rownum 순열번호
                ,substr(sys_connect_by_path(v, ','), 2) path
            from
                (
                    select
                        regexp_substr(t1.v, '[^,]+', 1, level) v
                    from base_data t1
                    connect by level <= regexp_count(t1.v, '[^,]+')
            ) x
            connect by nocycle prior x.v != x.v
              order siblings by v
        ) a
)
, base_ops as
(
    select '+' op from dual union all
    select '-' op from dual union all
    select '*' op from dual union all
    select '/' op from dual
)
, base_recur ( n, v , vs,cnt) as
(
    select
        a.순열번호 n
        , a.v
        , '@'||a.v||'@' vs
        ,0
    from    부분순열 a
    union all
    select
        a.n n
        , a.v
        ,replace(a.vs,
            regexp_substr(b.column_value,'[^$]+',1,1),
            '('||'@'||substr(replace(regexp_substr(b.column_value,'[^$]+',1),'@','') , 1 , instr(replace(regexp_substr(b.column_value,'[^$]+',1),'@',''),',',1,c.column_value) - 1)||'@' || d.op ||
            case when d.op = '/' then 'nullif(' else '' end ||'@'||substr(replace(regexp_substr(b.column_value,'[^$]+',1),'@',''),instr(replace(regexp_substr(b.column_value,'[^$]+',1),'@',''),',',1,c.column_value) + 1)||'@'||case when d.op = '/' then '_0)' else '' end||')'
        ) vs
        ,cnt + 1
    from    base_recur a
            , table(
                select
                    collect(column_value)
                from
                    table (
                        select
                            collect(regexp_substr(a.vs,'@[^@]+@',1,level)||'$'|| regexp_count(regexp_substr(a.vs,'@[^@]+@',1,level),','))
                        from
                            dual
                        connect by
                            level <= regexp_count(a.vs,'@[^@]+@')
                    )
                where   to_number(regexp_substr(column_value,'[^$]+',1,2)) > 0
                and     rownum < 2
                
            ) b
            , table (
                select
                    collect(level)
                from    dual
                connect by level <= to_number(regexp_substr(b.column_value,'[^$]+',1,2))
            ) c
            , base_ops d
    where   cnt < 20
)
, 만들어진산식 as
(
    select
        replace(a.vs,'@','') 산식
    from    base_recur a
    where   instr(a.vs,',') = 0
)
, 산식계산 as
(
    select
        산식
        , f_calc(산식) 연산결과
    from    만들어진산식
)
select
    산식
    ,연산결과
from    산식계산 a
where   연산결과 = 210
/
 
위의 연산 수행 결과는 다음과 같다.
산식 연산결과
((1*7)*(10*3)) 210
(10*((1*7)*3)) 210
(10*((3/nullif(1,0))*7)) 210
((10*7)*(1*3)) 210
((1*(10*3))*7) 210
(1*((7*3)*10)) 210
(10*((1*3)*7)) 210
((10*(7*3))*1) 210
((3*(10*7))/nullif(1,0)) 210
(3/nullif((1/nullif((10*7),0)),0)) 210
(1*((3*7)*10)) 210
(3*((7*10)*1)) 210
(((3*7)*10)/nullif(1,0)) 210
((1*(3*7))*10) 210
(7*((10*3)*1)) 210
((7/nullif(1,0))*(10*3)) 210
(7*(3*(1*10))) 210
(3/nullif(((1/nullif(10,0))/nullif(7,0)),0)) 210
(((7*1)*3)*10) 210
(((3*10)*7)/nullif(1,0)) 210
(3*(10*(7/nullif(1,0)))) 210
(7*(10*(3*1))) 210
(7*(3*10)) 210
(7*(10/nullif((1/nullif(3,0)),0))) 210
(10*(3*(7/nullif(1,0)))) 210
((10*(1*3))*7) 210
(((3*1)*10)*7) 210
((3*10)*(7/nullif(1,0))) 210
((10/nullif((1/nullif(7,0)),0))*3) 210
(7*(1*(10*3))) 210
(10*((7*3)/nullif(1,0))) 210
((3*7)*(10*1)) 210
(10/nullif((1/nullif((3*7),0)),0)) 210
((3*10)*(1*7)) 210
(3/nullif(((1/nullif(7,0))/nullif(10,0)),0)) 210
(3*(7/nullif((1/nullif(10,0)),0))) 210
((1*(3*10))*7) 210
(7*((10*1)*3)) 210
(3*(7*10)) 210
((7*3)*10) 210
(((1*10)*7)*3) 210
(10*(7*(1*3))) 210
(7*(3/nullif((1/nullif(10,0)),0))) 210
((3*1)*(10*7)) 210
((7*1)*(3*10)) 210
((7*10)*(3/nullif(1,0))) 210
((10*(7*3))/nullif(1,0)) 210
((3/nullif((1/nullif(10,0)),0))*7) 210
(((10*7)*3)*1) 210
(3*((10/nullif(1,0))*7)) 210
((7*(1*3))*10) 210
(((10*1)*7)*3) 210
(3*(1*(7*10))) 210
(10*(3/nullif((1/nullif(7,0)),0))) 210
((7*10)*3) 210
(((10*7)*3)/nullif(1,0)) 210
((3*(1*7))*10) 210
(1*(3*(7*10))) 210
((3/nullif(1,0))*(7*10)) 210
((7*3)*(10*1)) 210
(((7/nullif(1,0))*3)*10) 210
(3*((1*10)*7)) 210
(10*(7/nullif((1/nullif(3,0)),0))) 210
(3*((7/nullif(1,0))*10)) 210
((10*(3*1))*7) 210
(3*(1*(10*7))) 210
(7*((1*10)*3)) 210
(1*(10*(3*7))) 210
((3*7)*(1*10)) 210
(((7*3)*10)*1) 210
((7*(3/nullif(1,0)))*10) 210
((3*(10*1))*7) 210
((7/nullif((1/nullif(10,0)),0))*3) 210
((7*10)*(3*1)) 210
(1*((10*3)*7)) 210
(((10*3)*1)*7) 210
(((10/nullif(1,0))*7)*3) 210
((10*3)/nullif((1/nullif(7,0)),0)) 210
(1*((7*10)*3)) 210
(((1*3)*10)*7) 210
((7*(3*1))*10) 210
(10*(3*7)) 210
((10*1)*(7*3)) 210
((10*3)*(7*1)) 210
((7*3)*(1*10)) 210
(10*(7*(3*1))) 210
((10*7)/nullif((1/nullif(3,0)),0)) 210
(7*((1*3)*10)) 210
(((7*10)/nullif(1,0))*3) 210
((7*(10*3))/nullif(1,0)) 210
(((1*3)*7)*10) 210
((10*7)*(3/nullif(1,0))) 210
(7/nullif(((1/nullif(10,0))/nullif(3,0)),0)) 210
(10*(3*(1*7))) 210
(((3*1)*7)*10) 210
(10*(7*3)) 210
(1*((10*7)*3)) 210
((10*(3/nullif(1,0)))*7) 210
(((3/nullif(1,0))*10)*7) 210
(((7*10)*1)*3) 210
(10*(7*(3/nullif(1,0)))) 210
(7*(3*(10*1))) 210
((7/nullif((1/nullif(3,0)),0))*10) 210
((1*3)*(7*10)) 210
((10*3)*(7/nullif(1,0))) 210
((1*10)*(7*3)) 210
((1*3)*(10*7)) 210
((3*(10*7))*1) 210
(3*(7*(10/nullif(1,0)))) 210
((7/nullif(1,0))*(3*10)) 210
(3*(7*(10*1))) 210
(7*(10*(3/nullif(1,0)))) 210
(7*(10*3)) 210
(((7*1)*10)*3) 210
(10*((3*1)*7)) 210
((1*7)*(3*10)) 210
((3*(7*10))/nullif(1,0)) 210
(((7*3)*10)/nullif(1,0)) 210
((3*(7*1))*10) 210
(3*(10*(7*1))) 210
((10*(7/nullif(1,0)))*3) 210
((7*(10/nullif(1,0)))*3) 210
((7*3)/nullif((1/nullif(10,0)),0)) 210
(((3*10)*1)*7) 210
(10*(1*(7*3))) 210
((10*3)*7) 210
(((7*10)*3)*1) 210
(3*(10*7)) 210
(10/nullif(((1/nullif(7,0))/nullif(3,0)),0)) 210
(3*(10/nullif((1/nullif(7,0)),0))) 210
((7*10)/nullif((1/nullif(3,0)),0)) 210
(1*(7*(10*3))) 210
(10*((3*7)/nullif(1,0))) 210
(7*((3*10)/nullif(1,0))) 210
(3*((10*1)*7)) 210
(((1*7)*3)*10) 210
((7*3)*(10/nullif(1,0))) 210
(((3*7)/nullif(1,0))*10) 210
(((3*7)*1)*10) 210
(((3*7)*10)*1) 210
(7*((3*1)*10)) 210
(10*((7*3)*1)) 210
(((10/nullif(1,0))*3)*7) 210
(((10*7)/nullif(1,0))*3) 210
((7*(3*10))*1) 210
(10*(1*(3*7))) 210
((10*(3*7))*1) 210
(((10*3)*7)/nullif(1,0)) 210
((3/nullif(1,0))*(10*7)) 210
((1*10)*(3*7)) 210
(1*(3*(10*7))) 210
(((7/nullif(1,0))*10)*3) 210
(((10*1)*3)*7) 210
((10/nullif(1,0))*(3*7)) 210
(3*((1*7)*10)) 210
(3*(7*(1*10))) 210
(((7*3)/nullif(1,0))*10) 210
(3/nullif((1/nullif((7*10),0)),0)) 210
(((7*10)*3)/nullif(1,0)) 210
((3*(1*10))*7) 210
((1*(10*7))*3) 210
(((3*10)/nullif(1,0))*7) 210
((3*(7*10))*1) 210
(7*((3*10)*1)) 210
(((10*7)*1)*3) 210
(7/nullif((1/nullif((10*3),0)),0)) 210
(7*(3*(10/nullif(1,0)))) 210
(((1*10)*3)*7) 210
((10/nullif((1/nullif(3,0)),0))*7) 210
((10/nullif(1,0))*(7*3)) 210
((3*7)*10) 210
(((3/nullif(1,0))*7)*10) 210
((3*10)*(7*1)) 210
(7*((10/nullif(1,0))*3)) 210
(1*(7*(3*10))) 210
((3/nullif((1/nullif(7,0)),0))*10) 210
((3*7)*(10/nullif(1,0))) 210
((3*(7/nullif(1,0)))*10) 210
((10*(1*7))*3) 210
(3*((10*7)/nullif(1,0))) 210
(3*((7*10)/nullif(1,0))) 210
(((10*3)/nullif(1,0))*7) 210
(((7*3)*1)*10) 210
((10*1)*(3*7)) 210
((7*1)*(10*3)) 210
(7*(10*(1*3))) 210
((1*(7*10))*3) 210
((3*7)/nullif((1/nullif(10,0)),0)) 210
((3*10)*7) 210
((7*10)*(1*3)) 210
(7*((10*3)/nullif(1,0))) 210
((7*(10*3))*1) 210
((10*3)*(1*7)) 210
(1*(10*(7*3))) 210
(((10*3)*7)*1) 210
(10*((7/nullif(1,0))*3)) 210
(3*((7*1)*10)) 210
((7*(3*10))/nullif(1,0)) 210
(((3*10)*7)*1) 210
(7*(1*(3*10))) 210
(10*((7*1)*3)) 210
(1*((3*10)*7)) 210
(10*(3*(7*1))) 210
((3*10)/nullif((1/nullif(7,0)),0)) 210
(10/nullif(((1/nullif(3,0))/nullif(7,0)),0)) 210
((7*(1*10))*3) 210
((10*(7*1))*3) 210
((3*(10/nullif(1,0)))*7) 210
(7/nullif(((1/nullif(3,0))/nullif(10,0)),0)) 210
(7*((3/nullif(1,0))*10)) 210
(7/nullif((1/nullif((3*10),0)),0)) 210
((10*7)*3) 210
((10*(3*7))/nullif(1,0)) 210
((3*1)*(7*10)) 210
(3*((10*7)*1)) 210
(3*(10*(1*7))) 210
(10/nullif((1/nullif((7*3),0)),0)) 210
(10*((3*7)*1)) 210
((1*(7*3))*10) 210
(((1*7)*10)*3) 210
((10*7)*(3*1)) 210
((7*(10*1))*3) 210
 
그리고 위함수를 사용하면 nullif 처리가 필요 없으므로
다음과 같이 할 수 있다.
with base_data as (
    select '1,3,7,10' v from dual
)
, 부분순열 as
(
    select
        a.순열번호
        ,a.path v
    from
        (
            select
                rownum 순열번호
                ,substr(sys_connect_by_path(v, ','), 2) path
            from
                (
                    select
                        regexp_substr(t1.v, '[^,]+', 1, level) v
                    from base_data t1
                    connect by level <= regexp_count(t1.v, '[^,]+')
            ) x
            connect by nocycle prior x.v != x.v
              order siblings by v
        ) a
)
, base_ops as
(
    select '+' op from dual union all
    select '-' op from dual union all
    select '*' op from dual union all
    select '/' op from dual
)
, base_recur ( n, v , vs,cnt) as
(
    select
        a.순열번호 n
        , a.v
        , '@'||a.v||'@' vs
        ,0
    from    부분순열 a
    union all
    select
        a.n n
        , a.v
        ,replace(a.vs,
            regexp_substr(b.column_value,'[^$]+',1,1),
            '('||'@'||substr(replace(regexp_substr(b.column_value,'[^$]+',1),'@','') , 1 , instr(replace(regexp_substr(b.column_value,'[^$]+',1),'@',''),',',1,c.column_value) - 1)||'@' || d.op ||
            '@'||substr(replace(regexp_substr(b.column_value,'[^$]+',1),'@',''),instr(replace(regexp_substr(b.column_value,'[^$]+',1),'@',''),',',1,c.column_value) + 1)||'@'||')'
        ) vs
        ,cnt + 1
    from    base_recur a
            , table(
                select
                    collect(column_value)
                from
                    table (
                        select
                            collect(regexp_substr(a.vs,'@[^@]+@',1,level)||'$'|| regexp_count(regexp_substr(a.vs,'@[^@]+@',1,level),','))
                        from
                            dual
                        connect by
                            level <= regexp_count(a.vs,'@[^@]+@')
                    )
                where   to_number(regexp_substr(column_value,'[^$]+',1,2)) > 0
                and     rownum < 2
                
            ) b
            , table (
                select
                    collect(level)
                from    dual
                connect by level <= to_number(regexp_substr(b.column_value,'[^$]+',1,2))
            ) c
            , base_ops d
    where   cnt < 20
)
, 만들어진산식 as
(
    select
        replace(a.vs,'@','') 산식
    from    base_recur a
    where   instr(a.vs,',') = 0
)
, 산식계산 as
(
    select
        산식
        , f_calc(산식) 연산결과
    from    만들어진산식
)
select
    산식
    ,연산결과
from    산식계산 a
where   연산결과 = 210
/
 
그 결과는 
산식 연산결과
((1*7)*(10*3)) 210
(10*((1*7)*3)) 210
((10*7)*(1*3)) 210
(((10*7)/1)*3) 210
((1*(10*3))*7) 210
(1*((7*3)*10)) 210
(10*((1*3)*7)) 210
((10*(7*3))*1) 210
((10*(7*3))/1) 210
(1*((3*7)*10)) 210
(((10/1)*7)*3) 210
(3*((7*10)*1)) 210
((1*(3*7))*10) 210
(7*((10*3)*1)) 210
(7*(3*(1*10))) 210
((10/1)*(7*3)) 210
(((7*1)*3)*10) 210
(7*(10*(3*1))) 210
(((10*3)*7)/1) 210
(7*(3*10)) 210
(3*(10/(1/7))) 210
(((7/1)*10)*3) 210
((10*(1*3))*7) 210
(((3*1)*10)*7) 210
((3*7)*(10/1)) 210
((7*(3*10))/1) 210
(7*(1*(10*3))) 210
((10/1)*(3*7)) 210
((3*7)*(10*1)) 210
((3*10)*(1*7)) 210
((10*3)*(7/1)) 210
((1*(3*10))*7) 210
(10*(3*(7/1))) 210
((3/(1/10))*7) 210
(7*((10*1)*3)) 210
(3*(7*10)) 210
((7*3)*10) 210
(7*((10/1)*3)) 210
(7*((3/1)*10)) 210
(7/((1/10)/3)) 210
(((1*10)*7)*3) 210
(10*(7*(1*3))) 210
((3*1)*(10*7)) 210
((7*1)*(3*10)) 210
(7/(1/(3*10))) 210
(7*(3/(1/10))) 210
(((7*3)*10)/1) 210
(((10*7)*3)*1) 210
((7*(1*3))*10) 210
(((10*3)/1)*7) 210
(((10*1)*7)*3) 210
(3*(1*(7*10))) 210
(((3*7)*10)/1) 210
((7*10)*3) 210
((3*(1*7))*10) 210
(1*(3*(7*10))) 210
((7*3)*(10*1)) 210
(10*((7/1)*3)) 210
(3*((10/1)*7)) 210
(3*((10*7)/1)) 210
(((3*10)*7)/1) 210
(3*((1*10)*7)) 210
((3*(7/1))*10) 210
((10*(3*1))*7) 210
(3*(1*(10*7))) 210
(10/((1/3)/7)) 210
(7*((1*10)*3)) 210
(1*(10*(3*7))) 210
((3*7)*(1*10)) 210
(((7*3)*10)*1) 210
((3*(10*1))*7) 210
((7*10)*(3*1)) 210
(1*((10*3)*7)) 210
(((10*3)*1)*7) 210
(3*(7/(1/10))) 210
(1*((7*10)*3)) 210
(((1*3)*10)*7) 210
((7*(3*1))*10) 210
(10*(3*7)) 210
((10*1)*(7*3)) 210
((10*3)*(7*1)) 210
(3*(10*(7/1))) 210
((7*3)*(1*10)) 210
(10*(7*(3*1))) 210
(7*((1*3)*10)) 210
(((1*3)*7)*10) 210
(3*((7/1)*10)) 210
((10/(1/3))*7) 210
((7/1)*(10*3)) 210
(10*(3*(1*7))) 210
(((3*1)*7)*10) 210
(10*(7*3)) 210
(1*((10*7)*3)) 210
(((7*10)*1)*3) 210
(7*(3*(10*1))) 210
((1*3)*(7*10)) 210
((1*10)*(7*3)) 210
((7/(1/10))*3) 210
((10*7)*(3/1)) 210
(7*(10*(3/1))) 210
((1*3)*(10*7)) 210
(3/((1/7)/10)) 210
((3*(10*7))*1) 210
(3/((1/10)/7)) 210
(3/(1/(10*7))) 210
(3*(7*(10*1))) 210
(7*(10*3)) 210
(((7*1)*10)*3) 210
((10/(1/7))*3) 210
(10*((3*1)*7)) 210
((1*7)*(3*10)) 210
((3*(7*1))*10) 210
(3*((7*10)/1)) 210
(3*(10*(7*1))) 210
(3/(1/(7*10))) 210
((10*(3/1))*7) 210
(((3*10)*1)*7) 210
(10*(1*(7*3))) 210
((10*3)*7) 210
(((7*10)*3)*1) 210
(3*(10*7)) 210
((7/1)*(3*10)) 210
((7*3)/(1/10)) 210
((7*(10*3))/1) 210
(7/(1/(10*3))) 210
(1*(7*(10*3))) 210
(10*((7*3)/1)) 210
(10*(7/(1/3))) 210
((3*(10/1))*7) 210
((3*7)/(1/10)) 210
((7*(3/1))*10) 210
(3*((10*1)*7)) 210
(((1*7)*3)*10) 210
((3/1)*(10*7)) 210
((10*(3*7))/1) 210
(7*((10*3)/1)) 210
(10*(7*(3/1))) 210
(((3/1)*10)*7) 210
(10*((3/1)*7)) 210
(((3*7)*1)*10) 210
(((3*7)*10)*1) 210
(7*((3*1)*10)) 210
(10*((7*3)*1)) 210
((7*(3*10))*1) 210
(10*(1*(3*7))) 210
((10*(3*7))*1) 210
((3/1)*(7*10)) 210
(((3*7)/1)*10) 210
((1*10)*(3*7)) 210
(1*(3*(10*7))) 210
(((10*1)*3)*7) 210
(10/(1/(7*3))) 210
(3*((1*7)*10)) 210
(3*(7*(1*10))) 210
((7*3)*(10/1)) 210
((3*(7*10))/1) 210
((3*(1*10))*7) 210
(10/((1/7)/3)) 210
(7*((3*10)/1)) 210
((1*(10*7))*3) 210
(((10*7)*3)/1) 210
((3*(7*10))*1) 210
(7*((3*10)*1)) 210
((10*3)/(1/7)) 210
(((3*10)/1)*7) 210
(((7*10)*3)/1) 210
(((10/1)*3)*7) 210
((10*7)/(1/3)) 210
(((10*7)*1)*3) 210
(((7*10)/1)*3) 210
(((1*10)*3)*7) 210
((3*7)*10) 210
((3*10)*(7*1)) 210
(7/((1/3)/10)) 210
((7/(1/3))*10) 210
(1*(7*(3*10))) 210
(((7*3)/1)*10) 210
((10*(1*7))*3) 210
((3*10)*(7/1)) 210
(((3/1)*7)*10) 210
(((7*3)*1)*10) 210
((10*1)*(3*7)) 210
((7*1)*(10*3)) 210
(7*(10*(1*3))) 210
((1*(7*10))*3) 210
((3*10)*7) 210
((7*10)*(1*3)) 210
((7*10)/(1/3)) 210
((7*10)*(3/1)) 210
((7*(10*3))*1) 210
((10*3)*(1*7)) 210
(7*(10/(1/3))) 210
(1*(10*(7*3))) 210
(((10*3)*7)*1) 210
(3*((7*1)*10)) 210
(10*((3*7)/1)) 210
(((3*10)*7)*1) 210
(7*(1*(3*10))) 210
(10*((7*1)*3)) 210
(1*((3*10)*7)) 210
(10*(3*(7*1))) 210
((3*10)/(1/7)) 210
(3*(7*(10/1))) 210
((7*(1*10))*3) 210
((10*(7/1))*3) 210
((10*(7*1))*3) 210
(7*(3*(10/1))) 210
((10*7)*3) 210
((3*1)*(7*10)) 210
(3*((10*7)*1)) 210
(3*(10*(1*7))) 210
(10*((3*7)*1)) 210
((1*(7*3))*10) 210
(((1*7)*10)*3) 210
((7*(10/1))*3) 210
(10*(3/(1/7))) 210
((10*7)*(3*1)) 210
(((7/1)*3)*10) 210
((7*(10*1))*3) 210
(10/(1/(3*7))) 210
((3/(1/7))*10) 210
((3*(10*7))/1) 210
 

 

 

 

김흥수(protokhs)님이 2015-06-11 14:15에 작성한 댓글입니다.
[Top]
No.
제목
작성자
작성일
조회
40820문의드립니다. (세로 ㅡ> 가로) [1]
2015-06-06
7980
40819숫자구간으로 조회하는 정규식 질의 [1]
슈렉
2015-06-05
7450
40818recursive-with의 이상한 오류에 대하여 문의드립니다.
김흥수
2015-06-04
7776
40817쿼리 퀴즈입니다. SQL로 카운트다운 문제 풀기 [1]
김흥수
2015-06-04
10047
40816쿼리퀴즈입니다. SQL로 leaf node가 n개인 모든 이진 트리 구조를 구하기 [1]
김흥수
2015-06-03
9472
40815쿼리 정렬 질문 [3]
화생방
2015-06-03
7351
40814PK의 성능차이 문의드립니다. [1]
궁금
2015-06-02
8673
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2024 DSN, All rights reserved.
작업시간: 0.018초, 이곳 서비스는 PostgreSQL v16.4로 자료를 관리합니다