1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
SELECT
if_result_avg
FROM
(
SELECT
MAX(@RN2:=@RN2*if_result_avg) AS if_result_avg
FROM (
SELECT 3.70 AS if_result_avg
UNION ALL SELECT 2.95 AS if_result_avg
UNION ALL SELECT 1.55 AS if_result_avg
UNION ALL SELECT 1.18 AS if_result_avg
UNION ALL SELECT 1.04 AS if_result_avg
)TB,
(SELECT @RN2:=1) AS R2
) AS TB
|
cs |
오라클로 변경하려고 하는데 어떻게 해야할까요? ㅠㅠ
집합의 곱셈 결과를 얻는거로군요.
WITH tb AS ( SELECT 3.70 if_result_avg FROM dual UNION ALL SELECT 2.95 FROM dual UNION ALL SELECT 1.55 FROM dual UNION ALL SELECT 1.18 FROM dual UNION ALL SELECT 1.04 FROM dual ) SELECT EXP(SUM(LN(if_result_avg))) FROM tb ; ----> 20.76207639999999999999999999999999999953
수학공식을 이용하는 방법인데 LN 이라고 하는 로그함수를 이용하다 보니 오차가 발생하네요.
소수점 두자리 고정이라 가정하고 5건이면 10자리에서 반올림 하면 보정됩니다.
SELECT ROUND( EXP(SUM(LN(if_result_avg))) , COUNT(*) * 2) x FROM tb ; ----> 20.7620764
다음은 MODEL 절을 이용해 순차적으로 곱해나가는 방법이구요.(10G)
WITH tb AS ( SELECT 3.70 if_result_avg FROM dual UNION ALL SELECT 2.95 FROM dual UNION ALL SELECT 1.55 FROM dual UNION ALL SELECT 1.18 FROM dual UNION ALL SELECT 1.04 FROM dual ) SELECT x FROM (SELECT * FROM tb MODEL PARTITION BY (COUNT(*) OVER() cnt) DIMENSION BY (ROWNUM rn) MEASURES (if_result_avg, 1 x) RULES ( x[ANY] = NVL(x[CV()-1], 1) * if_result_avg[CV()]) ) WHERE cnt = rn ;
다음은 Recursive 쿼리를 이용하는 방법입니다.(11G)
WITH tb AS ( SELECT 3.70 if_result_avg FROM dual UNION ALL SELECT 2.95 FROM dual UNION ALL SELECT 1.55 FROM dual UNION ALL SELECT 1.18 FROM dual UNION ALL SELECT 1.04 FROM dual ) , tmp1 AS ( SELECT ROWNUM rn , if_result_avg FROM tb ) , tmp2(rn, if_result_avg, x) AS ( SELECT rn , if_result_avg , if_result_avg x FROM tmp1 WHERE rn = 1 UNION ALL SELECT a.rn , a.if_result_avg , a.if_result_avg * b.x x FROM tmp1 a , tmp2 b WHERE a.rn = b.rn + 1 ) SELECT MAX(x) KEEP(DENSE_RANK LAST ORDER BY rn) x FROM tmp2 ;
다음은 곱셈식을 문자열로 만들어 dbms_xmlgen 을 이용해 수행하는 방법입니다.
WITH tb AS ( SELECT 3.70 if_result_avg FROM dual UNION ALL SELECT 2.95 FROM dual UNION ALL SELECT 1.55 FROM dual UNION ALL SELECT 1.18 FROM dual UNION ALL SELECT 1.04 FROM dual ) SELECT TO_NUMBER( dbms_xmlgen.getxmltype('SELECT ' || x || ' c FROM dual').Extract('//text()') ) x FROM (SELECT LISTAGG(if_result_avg, '*') WITHIN GROUP(ORDER BY ROWNUM) x FROM tb ) ;
- PS -
기존 MySQL 에서 MAX 가져오는 부분은 오류가 아닐까? 생각되네요.
예시자료는 모두 1보다 큰데, 혹시 1보다 작은 자료가 존재할 경우엔 Max 로 가져오면 안될 것 같네요.