Oracle 同一栏位数值相乘的解决办法

一、简述

Oracle 聚合函数有SUM, MAX, MIN, AVG之类的,唯独没有对同一栏位的值做乘积运算。
所以在这里借用举例来解决我们处理乘积运算的需求,核心是借用对数函数的运算逻辑。

举例说明

假设Table同一栏位数值有:0,99, 0.88, 0.90,各个数值相乘可以利用对数函数做转换:

1
product = e^(ln0.99 + ln0.88 + ln0.90) = e^ln(0.99 * 0.88 * 0.90)  = 0.99 * 0.88 * 0.90

转换成SQL表示:product = EXP(SUM(LN(VAL)))
这样子的SQL就能通用了吗,答案是不能的。因为上述的SQL没有针对一些特殊情况做处理,比如栏位数值为0或者为负数的情况。所以我们需要SQL对这特殊逻辑要做处理,不然数值正负有误或者SQL会报错。

  • 对栏位数值为0的情况处理:
1
2
EXP(SUM(LN(DECODE(VAL, 0, 1, VAL)))) 
* DECODE(MAX(DECODE(VAL, 0, 0, NULL)), 0, 0, 1)
  • 对栏位数值出现负值的情况处理:
1
2
EXP(SUM(LN(ABS(VAL))))
* DECODE(MOD(SUM(DECODE(SIGN(YR), -1, 1, NULL)), 2), 0, 1, -1)
  • 针对综合特殊情况的SQL如下:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
WITH SQL1 AS(
SELECT LEVEL ID, TO_NUMBER(REGEXP_SUBSTR('0.99,0.88,-0.90,-1', '[^,]+', 1, LEVEL)) YR FROM DUAL
CONNECT BY REGEXP_SUBSTR('0.99,0.88,-0.90,-1', '[^,]+', 1, LEVEL) IS NOT NULL
)
SELECT TO_CHAR(ID) ID, YR, SIGN(YR) NEGATIVE FROM SQL1
UNION ALL
SELECT 'Total' ID,
EXP(SUM(LN(ABS(DECODE(YR, 0, 1, YR)))))
/*解决负数相乘的情况*/
* DECODE(MOD(SUM(DECODE(SIGN(YR), -1, 1, NULL)), 2), 0, 1, -1)
/*解决真数为0的情况*/
* DECODE(MAX(DECODE(YR, 0, 0, NULL)), 0, 0, 1) YR,
DECODE(MOD(SUM(DECODE(SIGN(YR), -1, 1, NULL)), 2), 0, 1, -1) NEGATIVE
FROM SQL1
评论