ORACLE生成累乘积

方法一：

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