SQL 计算累计乘积|极客教程

SQL 计算累计乘积，你想计算某个数值列的累计乘积。这个操作类似于上一个实例，只不过不使用加法，而改用乘法。

SQL 计算累计乘积问题描述

你想计算某个数值列的累计乘积。这个操作类似于上一个实例，只不过不使用加法，而改用乘法。

SQL 计算累计乘积解决方案

例如，你想计算员工工资的累计乘积。虽然工资的累计乘积可能用处不大，但同样的方法也能方便地应用于其他更有用的领域。
DB2 和 Oracle
使用窗口函数 SUM OVER，并利用对数来模拟乘法。

1 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          15925000000

不能使用 SQL 计算负数和零的对数。如果表里有这样的值，应该避免把它们传递给 SQL 的 LN 函数。为了便于理解，本解决方案并没有针对这些值和 Null 做防范处理，但是在真实环境下，你应该考虑在代码中添加防范措施。如果你一定要处理负数和零，那么该解决方案可能不适用。
还有一个只适用于 Oracle 的解决方案，那就是使用 Oracle Database 10 **g ** 开始支持的 MODEL 子句。在下面的例子里，SAL 列中的每一个值都会被转换成负数，这是为了证明负数不会给计算累计乘积带来任何问题。

 1 select empno, ename, sal, tmp as running_prod
 2   from (
 3 select empno,ename,-sal as sal
 4   from emp
 5  where deptno=10
 6        )
 7  model
 8    dimension by(row_number() over(order by sal desc) rn )
 9    measures(sal, 0 tmp, empno, ename)
10    rules (
11      tmp[any] = case when sal[cv()-1] is null then sal[cv()]
12                      else tmp[cv()-1]*sal[cv()]
13                 end
14    )
　
EMPNO ENAME       SAL         RUNNING_PROD
----- ---------- ---- --------------------
 7934 MILLER    -1300                -1300
 7782 CLARK     -2450              3185000
 7839 KING      -5000         -15925000000

MySQL、PostgreSQL 和 SQL Server
我们仍然需要使用对数求和，但是这些数据库不支持窗口函数，因而改用标量子查询。

1 select e.empno,e.ename,e.sal,
2         (select exp(sum(ln(d.sal)))
3            from emp d
4           where d.empno <= e.empno
5             and e.deptno=d.deptno) as running_prod
6   from emp e
7   where e.deptno=10
　
EMPNO ENAME       SAL         RUNNING_PROD
----- ---------- ---- --------------------
 7782 CLARK      2450                 2450
 7839 KING       5000             12250000
 7934 MILLER     1300          15925000000

对于 SQL Server 而言，还需要用 LOG 函数来替代 LN 函数。

SQL 计算累计乘积扩展知识

除了仅适用于 Oracle Database 10g 及其后续版本的 MODEL 子句，另外两个解决方案都使用了下面的把两个数字累加的做法：
(1) 计算它们各自的自然对数；
(2) 把上述自然对数的计算结果累加起来；
(3) 把上述累加结果作为指数，以数学常量 e 为底数，进行幂运算（使用 EXP 函数）。
注意，上述做法不适用于零和负数的累计，因为 SQL 的对数函数不支持小于或等于零的值。
DB2 和 Oracle
关于窗口函数 SUM OVER 的工作原理，请参考前一个实例的相关内容。
对于 Oracle Database 10g 及其后续版本，可以使用 MODEL 子句来计算累计乘积。使用 MODEL 子句和窗口函数 ROW_NUMBER，很容易找到当前行的前一行。可以像访问数组一样访问 MEASURES 列表的每一项，也可以使用 DIMENSIONS 列表的项（即 ROW_NUMBER 函数的返回值，别名为 RN）来查找数组。

select empno, ename, sal, tmp as running_prod,rn
  from (
select empno,ename,-sal as sal
  from emp
 where deptno=10
       )
 model
   dimension by(row_number() over(order by sal desc) rn )
   measures(sal, 0 tmp, empno, ename)
  rules ()
　
EMPNO ENAME             SAL RUNNING_PROD         RN
----- ---------- ---------- ------------ ----------
 7934 MILLER          -1300            0          1
 7782 CLARK           -2450            0          2
 7839 KING            -5000            0          3

我们看到 SAL[1] 的值是–1300。由于数字从 1 开始连续增加，因此能通过把行号减去一来访问前一行。RULES 子句如下所示。

rules (
   tmp[any] = case when sal[cv()-1] is null then sal[cv()]
                   else tmp[cv()-1]*sal[cv()]
              end
 )

使用内置运算符 ANY 可以遍历每一行，而无须硬编码。在这个例子里，ANY 会被分别赋值为 1、2 和 3。TMP[n] 的初始值为 0。通过评估当前的值（CV 函数返回当前值）来决定 TMP[n] 的值。TMP[1] 的初始值为 0，而 SAL[1] 是–1300。因为 SAL[0] 不存在，所以 TMP[1] 被设置为 SAL[1]。设置好 TMP[1] 后，下一行是 TMP[2]。SAL[1] 会被评估（SAL[CV()–1] 是 SAL[1]，因为 ANY 的当前值是 2）。SAL[1] 不是 Null，它的值为–1300，因而 TMP[2] 就是 TMP[1] 和 SAL[2] 的乘积。以此类推，直至完成所有行的计算。
MySQL、PostgreSQL 和 SQL Server
请参考前一个实例里针对 MySQL、PostgreSQL 和 SQL Server 解决方案的标量子查询的解释。
注意，基于子查询的解决方案得到的输出结果与 Oracle 和 DB2 解决方案的略有不同，这是由于多了针对 EMPNO 列的比较运算（累计乘积以一种不同的顺序被计算出来）。类似于累计求和的做法，标量子查询驱动了乘积的累计；基于子查询的解决方案按照 EMPNO 列对数据进行排序，而在 Oracle 和 DB2 解决方案里则按照 SAL 列排序。