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

간단한 수학예제쿼리로 어떻게 풀수 있을까요

by 분석함수 MODEL DIMENSION MEASURES [2012.04.10 13:40:52]

이런값이 있습니다.

순서 더하는값 곱하는값
1 1 2
2 1 3
3 4 2

이렇게 나왔을때
첫번째는 1*2 이것을 풀면 : 1*2
두번째는 ((1*2) + 1) * 3 이것을 풀면 : 1*2*3 + 1*3
세번째는 ((((1*2) + 1) * 3)+4) * 2 이것을 풀면 : 1*2*3*2 + 1*3*2 + 4*2

이렇게해서 결과적으로 26이 되는 쿼리를 어떻게 짜면 좋을까요
고수님들 간단하게 알려주심 감사하겠습니다.

CONNECT BY 를 써서 LEVEL 에 따라 연산해야 할까요?

by 제로 [2012.04.10 14:28:21]

with t as
(select 1 no, 1 plus, 2 double from dual union all
 select 2, 1, 3 from dual union all
 select 3, 4, 2 from dual)
select no
   , p plus
   , d double
   , r result
from t
model
dimension by (no)
measures(plus p, double d, 0 r)
rules(r[1] = p[cv()] * d[cv()]
  , r[2] = (r[1] + p[cv()]) * d[cv()]
  , r[3] = (r[2] + p[cv()]) * d[cv()]) ;

by 분석함수 [2012.04.10 14:44:51]

아 이런 것이 있었군요
감사합니다. ^^
그런데~ 혹시 데이터갯수가 동적이라면 어떻게 해야 할까요?

by 제로 [2012.04.10 15:43:02]

SQL 으로는 어려울듯 합니다.
PL/SQL의 동적SQL을 사용하거나 또는 프로그램단에서 처리 해야할듯 합니다.

by 분석함수 [2012.04.10 15:57:00]

제로님이 주신 쿼리 연구 하다가 찾아냈습니다.

with t as

(select 1 no, 1 plus, 2 double from dual union all

select 2, 1, 3 from dual union all

select 3, 4, 2 from dual union all

select 4, 4, 2 from dual)

select no

, p plus

, d double

, r result

from t

model

dimension by (no)

measures(no a,no-1 b,plus p, double d, 0 r)

rules( r[no] =

(nvl(r[b[cv()]],0) + p[cv()]) * d[cv()]

)

order by no

감사합니다. 이로써 이동평균값 구할수 있게 되었습니다. ㅠㅠ

by 손님 [2012.04.10 16:12:08]

앗~ 되는군요... ㅎㅎ 안될줄 알았는데...

by 손님 [2012.04.10 16:24:56]

기타 문법 추가해 봅니다.

with t as
(select 1 no, 1 plus, 2 double from dual union all
 select 2, 1, 3 from dual union all
 select 3, 4, 2 from dual union all
 select 4, 4, 2 from dual )
select no
  , p plus
  , d double
  , r result
from t
model
dimension by (no) 
measures(plus p, double d, 0 r) ignore nav
rules( r[any] = (r[cv()-1] + p[cv()]) * d[cv()] )
;

by 분석함수 [2012.04.10 18:45:59]

더 간단해 졌네요
감사합니다.

by 마농 [2012.04.12 13:02:56]

11G 버전에서는 다음과 같이 가능합니다.

WITH t AS
(
SELECT 1 no, 1 plus, 2 double FROM dual
UNION ALL SELECT 2, 1, 3 FROM dual
UNION ALL SELECT 3, 4, 2 FROM dual
)
, tmp(no, plus, double, r) AS
(
SELECT no, plus, double
     , plus * double r
  FROM t
 WHERE no = 1
 UNION ALL
SELECT b.no, b.plus, b.double
     , (a.r + b.plus) * b.double r
  FROM tmp a, t b
 WHERE a.no + 1 = b.no
)
SELECT *
  FROM tmp
;

by 분석함수 [2012.04.13 14:02:57]

부장님이 MODEL 로 하지말고 분석함수만 써서 해보라 시켜서 -ㅁ- 여러고민끝에 만들어 보게 되었습니다.

WITH T AS
(SELECT 1 NO,1 PLUS, 2 DOUBLE FROM DUAL UNION ALL
 SELECT 2, 1, 3 FROM DUAL UNION ALL
 SELECT 3, 4, 2 FROM DUAL)
SELECT NO_A,SUM(R_C)
 FROM (
SELECT NO_A,NO_B,EXP(SUM(LN(R_C))) AS R_C
 FROM (
SELECT NO_A,NO_B,NO_C,
    DECODE(P_C,MIN_C,MIN_C,1)*D_C AS R_C
 FROM ( 
SELECT A.NO AS NO_A,B.NO AS NO_B,C.NO AS NO_C,
    C.PLUS AS P_C,C.DOUBLE AS D_C,
    MIN(C.PLUS) OVER(PARTITION BY A.NO,B.NO) AS MIN_C
 FROM T A, T B, T C
 WHERE A.NO >= B.NO
  AND A.NO >= C.NO
  AND B.NO <= C.NO
 ORDER BY NO_A,NO_B,NO_C
)
) GROUP BY NO_A,NO_B
) GROUP BY NO_A
ORDER BY NO_A

by 마농 [2012.04.13 16:53:43]

EXP(SUM(LN())) 을 이용한 곱셈 계산식은 오차가 발생합니다.
LN 을 이용한 소수점 이하 미세한 수에 대한 계산으로 오차가 발생하므로
ROUND 로 보정해서 정수로 맞춰주셔야 합니다.

WITH t(no, plus, double) AS
(
SELECT 1, 1, 2 FROM dual
UNION ALL SELECT 2, 1, 3 FROM dual
UNION ALL SELECT 3, 4, 2 FROM dual
)
SELECT rno no
     , MAX(DECODE(lv, 1, plus  )) plus
     , MAX(DECODE(lv, 1, double)) double
     , SUM(r) r
  FROM (SELECT rno, lv
             , no, plus, double
             , ROUND(
               EXP(SUM(LN(double)) OVER(PARTITION BY rno ORDER BY lv))
               ) * plus r
          FROM (SELECT no, plus, double
                     , LEVEL lv
                     , CONNECT_BY_ROOT no rno
                  FROM t
                 CONNECT BY PRIOR no - 1 = no
                )
        )
 GROUP BY rno
;

by 분석함수 [2012.04.16 14:18:26]

와 감사합니다. ^^ 마농님 좀더 좋게 된거 같습니다.

ROUND 에 CONNECT_BY_ROOT 가 들어가다니

계속 여쭤봐서 죄송하지만 한가지 더 궁금한게 있는데요 ㅠㅠ

혹시 중간 2번째 DOUBLE 에 0이 들어갔을때 어떻게 해야 할지 궁금합니다.

중간에 0이 들어가면 위의 식으로는 누적곱셉이 성립이 안되는거 같아서요 ㅠㅠ

by 마농 [2012.04.16 15:00:07]

WITH t(no, plus, double) AS
(
SELECT 1, 1, 2 FROM dual
UNION ALL SELECT 2, 1, 0 FROM dual
UNION ALL SELECT 3, 4, 2 FROM dual
)
SELECT rno no
     , MAX(DECODE(lv, 1, plus  )) plus
     , MAX(DECODE(lv, 1, double)) double
     , SUM(r) r
  FROM (SELECT rno, lv
             , no, plus, double
             , ROUND(
               -- 아주 작은 수(0.000000001) 을 더해서 에러 회피 --
               EXP(SUM(LN(double + 1E-9)) OVER(PARTITION BY rno ORDER BY lv))
               ) * plus r
          FROM (SELECT no, plus, double
                     , LEVEL lv
                     , CONNECT_BY_ROOT no rno
                  FROM t
                 CONNECT BY PRIOR no - 1 = no
                )
        )
 GROUP BY rno
;

by 분석함수 [2012.04.17 09:14:06]

감사합니다. 마농님 ㅠㅠ

- 가 들어가는 부분도 한번 추가 해보았습니다.

 WITH T AS
(
SELECT 1 AS NO, 1 AS PLUS, 2 AS DOUBLE FROM DUAL
UNION ALL SELECT 2, 2, -1 FROM DUAL
UNION ALL SELECT 3, 3, 2 FROM DUAL
)
SELECT RNO NO
   , MAX(DECODE(LV, 1, PLUS )) PLUS
   , MAX(DECODE(LV, 1, DOUBLE)) DOUBLE
   , SUM(R) R
 FROM (
    SELECT RNO, LV , NO, PLUS, DOUBLE,R*PLUS*MIN(DIGN) OVER(PARTITION BY RNO ORDER BY LV) AS R
     FROM (
        SELECT RNO, LV
           , NO, PLUS, DOUBLE
           , ROUND(
           EXP(SUM(LN(ABS(double)+ (1E-9))) OVER(PARTITION BY RNO ORDER BY LV))
           ) R,
           SIGN(DECODE(DOUBLE,0,1,DOUBLE)) AS DIGN
        FROM (
           SELECT NO, PLUS, DOUBLE
               , LEVEL LV
               , CONNECT_BY_ROOT NO RNO
            FROM T
           CONNECT BY PRIOR NO - 1 = NO
           )
        ORDER BY RNO,LV
       )        
   )
GROUP BY RNO

by 마농 [2012.04.17 10:17:49]

WITH t(no, plus, double) AS
(
SELECT 1, 1, 2 FROM dual
UNION ALL SELECT 2, 2, -1 FROM dual
UNION ALL SELECT 3, 3, -2 FROM dual
)
SELECT rno no
     , MAX(DECODE(lv, 1, plus )) plus
     , MAX(DECODE(lv, 1, double)) double
     , SUM(r * plus * dign) r
  FROM (
        SELECT rno, lv
             , no, plus, double
             , ROUND(
               EXP(SUM(LN(ABS(double) + 1E-9)) OVER(PARTITION BY rno ORDER BY lv))
               ) r
             , DECODE(MOD(LENGTH(p) - LENGTH(REPLACE(p, '-')), 2), 0, 1, -1) dign
          FROM (
                SELECT no, plus, double
                     , LEVEL lv
                     , CONNECT_BY_ROOT no rno
                     , SYS_CONNECT_BY_PATH(SIGN(double), ',') p
                  FROM t
                 CONNECT BY PRIOR no - 1 = no
                   AND 0 NOT IN (PRIOR double, double)
                )
        )
 GROUP BY rno
;

- 가 있는지 여부만 가지고 부호를 판별해서는 오류가 발생합니다.
- 가 두번 곱해지면 양수가 되고 세번 곱해지면 음수가 됩니다.
- 가 여러번 있을때까지 고려하셔야 합니다.
그래서 - 의 갯수를 파악해서 반영하려고 했는데.
여기서 또 문제에 봉착했습니다.
앞에서 - 가 홀수개 있어서 음수일지라도 0을 만나면 무효가 됩니다.
하지만 우리는 0 대신 0.000000001을 사용했으므로 부호가 그대로 남아있게 됩니다.
그래서 0을 만나면 더이상 하위로의 계산식이 진행되지 않도록 계층쿼리에 조건을 추가했습니다.

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

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

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

데이터베이스 질문과 답변