iT邦幫忙

0

Oracle group by 相乘

大家好:
想請教一個問題有關於Oracle問題,如何使用PL/SQL語法下Group by 能做相乘。
一般我們都是
SELECT A, B, SUM(C)
FROM TEMP
GROUP BY A, B

如何SUM()函式,我是需要做GROUP BY 的相同類型做數值相乘。

感謝!

2 個回答

4
暐翰
iT邦大師 10 級 ‧ 2018-03-14 15:32:00
最佳解答

概念:

使用對數可以達到你要的
下面是注意事項跟照你需求寫的範例

注意:

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 或是 負數


解決方式:

A方法:假如不同意 0 * 1 * 2 = 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

B方法:假如同意 0 * 1 * 2 = 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 '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的資料

看更多先前的回應...收起先前的回應...
尼克 iT邦高手 1 級 ‧ 2018-03-14 15:43:10 檢舉

會出現這一錯誤訊息。
ORA-01428:argument '0' is out of range

暐翰 iT邦大師 10 級 ‧ 2018-03-14 16:02:04 檢舉

我更新內文回答問題了 看你有沒有0跟負數的需求

尼克 iT邦高手 1 級 ‧ 2018-03-14 16:23:52 檢舉

感謝你,我沒用過exp(SUM(ln(C))) 這方式,你真是太厲害了!

暐翰 iT邦大師 10 級 ‧ 2018-03-14 16:28:40 檢舉

我又更新了,可以解決負數跟0問題 /images/emoticon/emoticon08.gif

你在測試看看有沒有異常

邏輯:

先把會造成異常的0數字挑出
然後再相乘結果出來時,使用union all添加進去
因為(任意數*0都為0)原則下,含有0的組別是不用浪費時間去計算

0
一級屠豬士
iT邦新手 3 級 ‧ 2018-03-15 01:19:29

這是個有趣的題目.可以參考 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 筆資料列)
尼克 iT邦高手 1 級 ‧ 2018-03-15 08:43:43 檢舉

謝謝你!

我要發表回答

立即登入回答