ORACLE生成累乘积

来源：互联网发布：北大网络教育毕业证编辑：程序博客网时间：2024/06/07 06:58

方法一：

使用窗口函数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函数。

方法二：

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