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
列排序。