WITH t AS ( SELECT 1 a, 2 b FROM dual UNION ALL SELECT 3, 4 FROM dual UNION ALL SELECT 5, 6 FROM dual ) -- 1. 분석함수 와 수학공식 이용(누적곱) -- SELECT ROW_NUMBER() OVER(ORDER BY a) rn , a , b , ROUND(EXP(SUM(LN(b)) OVER(ORDER BY a))) c FROM t ;
WITH t AS ( SELECT 1 a, 2 b FROM dual UNION ALL SELECT 3, 4 FROM dual UNION ALL SELECT 5, 6 FROM dual ) -- 2. Recursive 쿼리 이용(누적곱) -- , t1 AS ( SELECT ROW_NUMBER() OVER(ORDER BY a) rn , a, b FROM t ) , t2(rn, a, b, c) AS ( SELECT rn , a , b , b c FROM t1 WHERE rn = 1 UNION ALL SELECT b.rn , b.a , b.b , a.c * b.b FROM t2 a , t1 b WHERE b.rn = a.rn + 1 ) SELECT * FROM t2 ;
WITH t AS ( SELECT 1 a, 2 b FROM dual UNION ALL SELECT 3, 4 FROM dual UNION ALL SELECT 5, 6 FROM dual ) -- 3. Model 절 이용 SELECT * FROM t MODEL DIMENSION BY (ROW_NUMBER() OVER(ORDER BY a) rn) MEASURES (a, b, b c) RULES (c[rn > 1] = c[cv()-1] * b[cv()]) ;