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 41188 게시물 읽기
No. 41188
실용성이 있는 SQL 퀴즈입니다. ^^
작성자
김흥수(protokhs)
작성일
2016-06-24 01:41ⓒ
2016-06-24 01:43ⓜ
조회수
11,260

 실용성이 있는 SQL 퀴즈입니다.

참 오래간만에 사랑넷에 온 것 같은데요... 오늘 낮에 회사에서 일하다가. 옛날에 java로 작성한 irr 함수가 있어서 이것을 순수 SQL로 가능할까? 하는 질문을 스스로에게 던졌더니 의외로 재미 있는 결과가 나와서 이렇게 퀴즈로 냅니다.

 

먼저 IRR(ko.wikipedia.org/wiki/%EB%82%B4%EB%B6%80%EC%88%98%EC%9D%B5%EB%A5%A0)

이 무어냐면 NPV(순현재가치ko.wikipedia.org/wiki/%EC%88%9C%ED%98%84%EC%9E%AC%EA%B0%80%EC%B9%98)를 0으로 만드는 할인율을 말합니다.

NPV는 현금흐름이 주어질 때 이 현금흐름의 현가 할인 가치의 합계를 말합니다.

뭐 자세한 공부는 재미 없으니까 인터넷을 찾아보시면 있구요 우리 프로그래머는 코드로 말하니까...

다음과 같이 임의의 10개의 현금흐름을 만들 때

 

with base as
(
    select level seq
            , dbms_random.value(-10000000000,10000000000) amt
            , 0.01 rat
    from    dual
    connect by
        level <= 10
)
 
 
다음과 같은 SQL로 구할 수 있습니다.
with base as
(
    select level seq
            , dbms_random.value(-10000000000,10000000000) amt
            , 0.01 rat
    from    dual
    connect by
        level <= 10
)
select
    b.amt / nullif(power(1+b.rat ,b.seq),0) pv
    , sum(b.amt / nullif(power(1+b.rat,b.seq),0)) over () npv
from    base b
/
 
즉 이론을 몰라도 일단 공식이 있으니까 답을 구할 수 있다는 것이죠...
 
그런데 문제는 IRR이라는 것을 구해야 한다는 것인데
위의 문제에서 npv가 0이 되는 할인율(위의 경우 0.01이 할인율임) 을 구해야 한다는 것입니다.
 
엑셀에는 NPV나 IRR 함수가 있습니다(재무함수)
 
일반적으로 절차적 언어로 푼다면 시행착오법으로 구합니다.
근을 구하는 수치해석 방법있죠? f(x1) < 0 이고 f(x2) >0 인 경우 x~x2 구간사이에 반드시 하나 이상의 근이 있다는 것을 이용하는 것...
 
만약 SQL로 구한다면 어떻게 풀면 될까요?
 
이게 퀴즈입니다.
이 글에 대한 댓글이 총 3건 있습니다.

WITH base AS
(
SELECT LEVEL seq
     , dbms_random.value(-10000000000, 10000000000) amt
  FROM dual
 CONNECT BY LEVEL <= 10
)
, rate AS
(
SELECT (LEVEL-1) / 10000 rat
  FROM dual
 CONNECT BY LEVEL <= 10000 + 1
)
SELECT *
  FROM (SELECT rat
             , SUM(amt / NULLIF(POWER(1+rat, seq), 0)) npv
          FROM rate r
             , base b
         GROUP BY rat
         ORDER BY ABS(npv), rat
        )
 WHERE ROWNUM = 1
;


0.00% ~ 100.00% 까지 소수 2자리 정확도로
만건의 rat 를 대입해 가장 작은 npv 를 구하는 방법.

마농(manon94)님이 2016-06-29 15:09에 작성한 댓글입니다.

 현실적인 현금흐름이면 irr이 양수로 나오지만 무작위이면 가장 작은 npv가 0이 아닐 수도 있습니다.

김흥수(protokhs)님이 2016-06-30 00:39에 작성한 댓글입니다.
제가 이 문제가 퀴즈로 가치가 있다고 생각한 것은 오라클 12에 도입된 lateral join을 쓰면 표기가 간단해지는 좋은 사례이기 때문입니다.
lateral join은 쓰면 유용한 경우가 생각보다 많지 않아보입니다.
그런데 이 경우는 확실히 쓰면 편합니다.
 
먼저 제가 처음으로 시도한 SQL은 다음과 같습니다.
with base as
(
    select level seq
            , dbms_random.value(-10000000000,10000000000) amt
    from    dual
    connect by
        level <= 10
)
, base_rat as
(
    select 0.01 init_rat , 2 scale from dual
)
, base_recur ( npv, old_npv,guess,minus_value , plus_value , sign, irr, scale , cnt ) as
(
    select
        sum( a.amt / 
                nullif(power(1 + b.init_rat,a.seq ),0) ) npv
        , null old_npv
        , b.init_rat guess
        , to_number(null) minus_value
        , to_number(null) plus_value
        , -1 sign
        , null irr
        , b.scale scale 
        , 0 cnt
    from    base a
            , base_rat b
    group by
        b.init_rat
        , b.scale
    union all
    select
        sum( a.amt / nullif(power(1 + b.guess,a.seq ),0) ) npv
        , b.npv old_npv
        , case
            when b.minus_value is null or b.plus_value is null then
                b.guess * 10 * b.sign
            else 
                (b.minus_value + b.plus_value ) / 2
        end guess
        , case 
            when round(b.npv,b.scale) = 0 then b.guess
            when sum( a.amt / nullif(power(1 + b.guess,a.seq ),0) ) < 0 then
                b.guess
            else
                b.minus_value
        end  minus_value
        , case 
            when round(b.npv,b.scale) = 0 then b.guess
            when sum( a.amt / nullif(power(1 + b.guess,a.seq ),0) ) >= 0 then
                b.guess
            else
                b.plus_value
        end  plus_value
        , - b.sign sign
        , b.guess irr
        , b.scale
        , b.cnt + 1 cnt
    from    base_recur b
            , base a
    where   b.cnt < 1000
    and     ( b.old_npv is null or round(b.old_npv , b.scale ) != 0 )
    group by
        b.npv
        , b.old_npv
        , b.guess
        , b.minus_value
        , b.plus_value
        , b.sign
        , b.irr
        , b.scale
        , b.cnt
)
select
    b.seq
    , b.amt
    , a.irr
    , a.npv npv
    , a.max_cnt
    , b.amt / nullif(power(1+a.irr,b.seq),0) pv
    , sum(b.amt / nullif(power(1+a.irr,b.seq),0)) over () npv_with_irr
    from
        (
            select
                a.irr
                , a.npv
                , max(a.cnt) over () max_cnt 
                , a.cnt
            from    base_recur a
        )  a
        , base b
where   a.max_cnt = a.cnt
/
상당히 복잡합니다.
그러나 원리는 npv가 양수인 rat와 음수인 rat를 구하고 그 다음부터는 두 구간의 중간 값을 취하여 근의 범위를 점차 줄여 나가는 것입니다.
 
그런데 이 SQL은 에러가 납니다.
 
ORA-32486: 순환 WITH 절의 순환 분기에 지원되지 않는 작업이 있습니다. 
 
그 이유는 recursive with 에서는 제한이 있는데 순환 반복이 되도록 drive 하는 쪽이 반드시 하나의 행이 되도록 SQL이 구현이 되어야 합니다.
그런데 이 SQL은 group by sum을 하기 때문에 recursive with에서 사용할 수 없습니다.
 
그래서 두번째로 시도한 것이 다음과 같은 SQL입니다.
with base as
(
    select level seq
            , dbms_random.value(-10000000000,10000000000) amt
    from    dual
    connect by
        level <= 10
)
, base_rat as
(
    select 0.01 init_rat , 2 scale from dual
)
, base_collect as
(
    select ( select collect(','||to_char(a.seq) ||','||to_char(a.amt)||',') from base a) a from dual a
)
, base_recur ( npv, old_npv,guess,minus_value , plus_value , sign, irr, scale , cnt ) as
(
    select
        ( select sum( to_number(regexp_substr(a.column_value ,'[^,]+',1,2)) / 
                nullif(power(1 + b.init_rat,to_number(regexp_substr(a.column_value ,'[^,]+',1,1)) ),0) ) from table(a.a) a ) npv
        , null old_npv
        , b.init_rat guess
        , to_number(null) minus_value
        , to_number(null) plus_value
        , -1 sign
        , null irr
        , b.scale scale 
        , 0 cnt
    from    base_collect a
            , base_rat b
    union all
    select
        ( select sum( to_number(regexp_substr(a.column_value ,'[^,]+',1,2)) / 
                nullif(power(1 + b.guess,to_number(regexp_substr(a.column_value ,'[^,]+',1,1)) ),0) ) from table(a.a) a ) npv
        , b.npv old_npv
        , case
            when b.minus_value is null or b.plus_value is null then
                b.guess * 10 * b.sign
            else 
                (b.minus_value + b.plus_value ) / 2
        end guess
        , case 
            when round(b.npv,b.scale) = 0 then b.guess
            when ( select sum( to_number(regexp_substr(a.column_value ,'[^,]+',1,2)) / 
                nullif(power(1 + b.guess,to_number(regexp_substr(a.column_value ,'[^,]+',1,1)) ),0) ) from table(a.a) a ) < 0 then
                b.guess
            else
                b.minus_value
        end  minus_value
        , case 
            when round(b.npv,b.scale) = 0 then b.guess
            when ( select sum( to_number(regexp_substr(a.column_value ,'[^,]+',1,2)) / 
                nullif(power(1 + b.guess,to_number(regexp_substr(a.column_value ,'[^,]+',1,1)) ),0) ) from table(a.a) a ) >= 0 then
                b.guess
            else
                b.plus_value
        end  plus_value
        , - b.sign sign
        , b.guess irr
        , b.scale
        , b.cnt + 1 cnt
    from    base_recur b
            , base_collect a
    where   b.cnt < 1000
    and     ( b.old_npv is null or round(b.old_npv , b.scale ) != 0 )
)
select
    b.seq
    , b.amt
    , a.irr
    , a.npv npv
    , a.max_cnt
    , b.amt / nullif(power(1+a.irr,b.seq),0) pv
    , sum(b.amt / nullif(power(1+a.irr,b.seq),0)) over () npv_with_irr
    from
        (
            select
                a.irr
                , a.npv
                , max(a.cnt) over () max_cnt 
                , a.cnt
            from    base_recur a
        )  a
        , base b
where   a.max_cnt = a.cnt
/
 
위의 SQL은 collect를 사용하여 sum을 스칼라 서브퀴리로 밀어넣기위해 nested table을 활용한 꼼수입니다.
 
그런데 만약 오라클 12라면 lateral join을 사용하여 recursive with 에서 sum을 사용하도록 변형할 수 있습니다.
 
with base as
(
    select level seq
            , dbms_random.value(-10000000000,10000000000) amt
    from    dual
    connect by
        level <= 10
)
, base_rat as
(
    select 0.01 init_rat , 2 scale from dual
)
, base_recur ( npv, old_npv,guess,minus_value , plus_value , sign, irr, scale , cnt ) as
(
    select
        sum( a.amt / 
                nullif(power(1 + b.init_rat,a.seq ),0) ) npv
        , null old_npv
        , b.init_rat guess
        , to_number(null) minus_value
        , to_number(null) plus_value
        , -1 sign
        , null irr
        , b.scale scale 
        , 0 cnt
    from    base a
            , base_rat b
    group by
        b.init_rat
        , b.scale
    union all
    select
        a.npv npv
        , b.npv old_npv
        , case
            when b.minus_value is null or b.plus_value is null then
                b.guess * 10 * b.sign
            else 
                (b.minus_value + b.plus_value ) / 2
        end guess
        , case 
            when round(b.npv,b.scale) = 0 then b.guess
            when a.npv < 0 then
                b.guess
            else
                b.minus_value
        end  minus_value
        , case 
            when round(b.npv,b.scale) = 0 then b.guess
            when a.npv >= 0 then
                b.guess
            else
                b.plus_value
        end  plus_value
        , - b.sign sign
        , b.guess irr
        , b.scale
        , b.cnt + 1 cnt
    from    base_recur b
            , lateral
            (
                select
                    sum( a.amt /  nullif(power(1 + b.guess,a.seq ),0) ) npv
                from    base a
           ) a /*sum을 인라인 뷰 안쪽으로 밀어넣어 base_recur의 한 행과 대응되게 하였다.*/
    where   b.cnt < 1000
    and     ( b.old_npv is null or round(b.old_npv , b.scale ) != 0 )
)
select
    b.seq
    , b.amt
    , a.irr
    , a.npv npv
    , a.max_cnt
    , b.amt / nullif(power(1+a.irr,b.seq),0) pv
    , sum(b.amt / nullif(power(1+a.irr,b.seq),0)) over () npv_with_irr
    from
        (
            select
                a.irr
                , a.npv
                , max(a.cnt) over () max_cnt 
                , a.cnt
            from    base_recur a
        )  a
        , base b
where   a.max_cnt = a.cnt
/
 
훨씬 알아보기 좋아졌고 원리도 더 잘 드러납니다.
 
lateral join을 쓰면 좋은 괜찮은 사례라고 생각합니다.
김흥수(protokhs)님이 2016-06-30 00:58에 작성한 댓글입니다.
이 댓글은 2016-06-30 01:30에 마지막으로 수정되었습니다.
[Top]
No.
제목
작성자
작성일
조회
41191update sql 퀴즈입니다. [2]
김흥수
2016-06-30
7930
41190이 sql 의 문제 무엇일까요. 의견부탁드려요. [2]
니노
2016-06-29
7861
41189두개의 테이블 검색(도움요청) [3]
염진호
2016-06-29
8051
41188실용성이 있는 SQL 퀴즈입니다. ^^ [3]
김흥수
2016-06-24
11260
41187아카이브 로그파일 위치 변경 . [1]
dba
2016-06-23
8221
41186비정상데이터 connect by 조회가능할까요. [1]
김정묵
2016-06-23
7965
41185고수님들의 뜨거운 조언을 구합니다. [3]
벌집
2016-06-23
8253
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2024 DSN, All rights reserved.
작업시간: 0.021초, 이곳 서비스는 PostgreSQL v16.4로 자료를 관리합니다