oracle sql实现 累乘

[quote]
[color=red]方法一：[/color]
使用窗口函数SUM OVER，用对数相加来模拟乘法操作。

SQL> select empno,ename,sal
2 from emp
3 where deptno=10;

EMPNO ENAME SAL
---------- ---------- ----------
7782 CLARK 2450
7839 KING 5000
7934 MILLER 1300

下面是实现方法

SQL> select empno,ename,sal,
2 exp(sum(ln(sal)) over(order by sal,empno)) as running_prod
3 from emp
4 where deptno=10;

EMPNO ENAME SAL RUNNING_PROD
---------- ---------- ---------- ------------
7934 MILLER 1300 1300
7782 CLARK 2450 3185000
7839 KING 5000 1.5925E+10

注意，在SQL中，对小于等于0的值取对数是无效的。如果表中包含这样的值，一定要避免把这些无效的值传递给SQL的LN函数。[/quote]
[quote]
[color=red]方法二：[/color]
ORACLE 10g引入了MODEL子句，在下面的例子中，每个SAL都是负数，这表明累乘积允许出现负值。

SQL> select empno,ename,sal,tmp as running_prod
2 from
3 (
4 select empno,ename,-sal as sal
5 from emp
6 where deptno=10
7 )
8 model
9 dimension by(row_number() over(order by sal desc) rn)
10 measures(sal,0 tmp,empno,ename)
11 rules
12 (
13 tmp[any]=case when sal[currentv()-1] is null then sal[currentv()]
14 else tmp[currentv()-1]*sal[currentv()]
15 end
16 );

EMPNO ENAME SAL RUNNING_PROD
---------- ---------- ---------- ------------
7934 MILLER -1300 -1300
7782 CLARK -2450 3185000
7839 KING -5000 -1.593E+10
[/quote]
[quote]
[color=red]方法三：(不支持负数)[/color]
--测试数据
create table t(id int,qty int);
insert into t
select 1,2 from dual union all
select 2,4 from dual union all
select 3,6 from dual union all
select 4,5 from dual;
--执行查询
Select power(10, Sum(Log(10, qty))) From t
--查询结果
240
[/quote]