데이터베이스 질문과 답변

반복된 계산을 하는 쿼리 문의드립니다. 1 6 712

by 아이쪼아라 [SQL Query] [2016.10.19 19:44:41]

안녕하세요.

쿼리를 짜는 도중 도저히 생각이 안나서 문의드립니다ㅠ.ㅠ

아래와 같은 데이터로 next_amt를 추출하는 건데요.

프로시저로 말고 쿼리로 나머지 행들의 값을 한번에 구하는 방법이 있을까요?

WITH t AS

(
SELECT 1 no, 1.17 pct, 100 amt FROM dual
UNION ALL SELECT 2, 1.11, null FROM dual
UNION ALL SELECT 3, 0.83, null FROM dual
UNION ALL SELECT 4, 0.76, null FROM dual
UNION ALL SELECT 5, 0.93, null FROM dual
UNION ALL SELECT 6, 0.78, null FROM dual
)   
select
no
, pct
, amt
, lag(amt) over (order by no)*pct as next_amt
from
    t 

 

NO PCT AMT NEXT_AMT NEXT_AMT로 채워질 값
1 1.17 100    
2 1.11   111  
3 0.83     92.1(=111*0.83)
4 0.76     70.0(=92.1*0.76)
5 0.93     65.1(=70.0*0.93)
6 0.78     50.8(=65.1*0.78)

 

방법을 아시는 분 계시면 답변 부탁드립니다~!

감사합니다.

by jkson [2016.10.20 08:00:31] 
--재귀
WITH T1 AS
(
 SELECT 1 NO, 1.17 PCT, 100 AMT FROM DUAL
 UNION ALL SELECT 2, 1.11, NULL FROM DUAL
 UNION ALL SELECT 3, 0.83, NULL FROM DUAL
 UNION ALL SELECT 4, 0.76, NULL FROM DUAL
 UNION ALL SELECT 5, 0.93, NULL FROM DUAL
 UNION ALL SELECT 6, 0.78, NULL FROM DUAL
 )
, T2(NO, PCT, AMT) AS
(
SELECT NO, PCT, AMT 
  FROM T1
 WHERE NO = 1
UNION ALL
SELECT B.NO, B.PCT, ROUND(A.AMT * B.PCT,1) AMT
  FROM T2 A, T1 B
 WHERE A.NO + 1 = B.NO
)
SELECT * 
  FROM T2
--모델
WITH T1 AS
(
 SELECT 1 NO, 1.17 PCT, 100 AMT FROM DUAL
 UNION ALL SELECT 2, 1.11, NULL FROM DUAL
 UNION ALL SELECT 3, 0.83, NULL FROM DUAL
 UNION ALL SELECT 4, 0.76, NULL FROM DUAL
 UNION ALL SELECT 5, 0.93, NULL FROM DUAL
 UNION ALL SELECT 6, 0.78, NULL FROM DUAL
 )
SELECT NO, PCT, ROUND(NEXTAMT,1) AMT
  FROM T1
MODEL
DIMENSION BY (NO)
MEASURES(PCT, AMT, 0 NEXTAMT)
RULES AUTOMATIC ORDER -- 마농님 댓글보고 수정
(NEXTAMT[ANY] = NVL(NEXTAMT[CV()-1] * PCT[CV()],AMT[CV()]))
ORDER BY NO

 

 

 

 

by 마농 [2016.10.20 08:35:10] 
-- 1. Recursive SQL(11G) 사용
-- jkson 님 방법

-- 2. Model 절(10G) 사용.
SELECT *
  FROM t
 MODEL
 DIMENSION BY (no)
 MEASURES (pct, amt)
 RULES AUTOMATIC ORDER
 ( amt[no > 1] = ROUND(amt[CV()-1] * pct[CV()], 1) )
;

-- 3. Exp(Sum(Ln(pct))) 를 이용한 누적 곱 계산 활용(8i)
SELECT no, pct
     , ROUND(
       EXP(SUM(LN(DECODE(no, 1, 1, pct))) OVER(ORDER BY no))
       * MIN(amt) OVER()
       , 1) amt
  FROM t
;

-- 단, 1, 2 번 방법은 절차적 방법으로 순차적으로 반올림 하며 계산하지만
-- 3번 방법은 집합적 방법으로 최종 결과에 반올림 하므로 오차가 있음.

 

by jkson [2016.10.20 08:39:49]

마농님 질문이요.

저같은 경우 모델문에서 RULES AUTOMATIC ORDER 처리를 안해도

키값 순서대로 계산한 것 같은데 RULES AUTOMATIC ORDER 구문을

안써주면 계산값이 틀려질 가능성이 있는 건가요?

어떨 때 RULES AUTOMATIC ORDER 문을 꼭 써야하는 것인지 궁금합니당~

by 마농 [2016.10.20 08:48:58]

예시 자료가 예쁘게 정렬되어 있네요.

자료 순서를 바꾸어 실행해 보면 에러를 보실 수 있을 것입니다.

by jkson [2016.10.20 08:51:36]
아하~ 그렇군요. 실제 테이블에서 모델문을 써본적 없이 예문으로만 공부하다보니..^^; 많이 쓸 기회가 없어서 자꾸 사용법을 잊어버리네요ㅜ
by 아이쪼아라 [2016.10.20 09:53:20]

답변 정말 감사합니다~^^

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