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

MySQLPostgreSQL 和 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] 的乘积。以此类推,直至完成所有行的计算。
MySQLPostgreSQL 和 SQL Server
请参考前一个实例里针对 MySQL、PostgreSQL 和 SQL Server 解决方案的标量子查询的解释。
注意,基于子查询的解决方案得到的输出结果与 Oracle 和 DB2 解决方案的略有不同,这是由于多了针对 EMPNO 列的比较运算(累计乘积以一种不同的顺序被计算出来)。类似于累计求和的做法,标量子查询驱动了乘积的累计;基于子查询的解决方案按照 EMPNO 列对数据进行排序,而在 Oracle 和 DB2 解决方案里则按照 SAL 列排序。

Python教程

Java教程

Web教程

数据库教程

图形图像教程

大数据教程

开发工具教程

计算机教程