使用對數可以達到你要的
下面是注意事項跟照你需求寫的範例
exp(SUM(ln(C))) 會有浮點數問題,所以要使用floor來取整數
with temp_table as (
select 'A' A , 'A' B , 2 C from dual
union all select 'B' A , 'B' B , 2 C from dual
union all select 'B' A , 'B' B , 2 C from dual
union all select 'B' A , 'B' B , 4 C from dual
union all select 'A' A , 'A' B , 58 C from dual
)
select A,B,SUM(C) 相加,floor(exp(SUM(ln(C)))) 相乘結果,count(1) 每組數量 from temp_table
group by A,B
;
會出現這一錯誤訊息。
ORA-01428:argument '0' is out of range
因為數字裡面有0 或是 負數
可以加入where條件
where c > 0
剔除0跟負數問題
例子:
with temp_table as (
select 'A' A , 'A' B , 2 C from dual
union all select 'B' A , 'B' B , 2 C from dual
union all select 'B' A , 'B' B , 2 C from dual
union all select 'B' A , 'B' B , 0 C from dual
union all select 'A' A , 'A' B , 58 C from dual
)
select A,B,SUM(C) 相加,floor(exp(SUM(ln(C)))) 相乘結果,count(1) 每組數量 from temp_table
where c > 0
group by A,B
想出來了,負數跟0的問題都可以解決:
with temp_table as (
select 'A' A , 'A' B , 2 C from dual
union all select 'B' A , 'B' B , 2 C from dual
union all select 'B' A , 'B' B , 2 C from dual
union all select 'B' A , 'B' B , 0 C from dual
union all select 'C' A , 'C' B , -200 C from dual
union all select 'C' A , 'C' B , 4 C from dual
union all select 'A' A , 'A' B , 58 C from dual
)
, 有0的資料 as (
select * from temp_table
where C = 0
)
select A,B,ROUND(exp(sum(ln(abs(C))))*power(-1,sum(decode(sign(C),-1,1,0)))) from temp_table T100
where T100.A||T100.B not in (select A||B from 有0的資料 )
group by A,B
union all
select A,B,0
from 有0的資料
這是個有趣的題目.可以參考 Oreilly SQL Hacks
Multiply Across a Result Set
或是 這裡也有不錯的參考
就是上面使用的方法.以下我來做點不一樣的方式,用Postgresql來作範例.
建立一個乘法的聚合函數. mul(), 直接使用mul()計算.
CREATE AGGREGATE mul(bigint) ( SFUNC = int8mul, STYPE=bigint );
with tmp(a, b, c) as (
values
('A', 'A', 2),
('B', 'B', 2),
('B', 'B', 2),
('B', 'B', 4),
('A', 'A', 58)
)
SELECT a, b
, sum(c)
, mul(c)
FROM tmp
GROUP BY a, b;
a | b | sum | mul
---+---+-----+-----
A | A | 60 | 116
B | B | 8 | 16
(2 筆資料列)