0

## Oracle group by 相乘

SELECT A, B, SUM(C)
FROM TEMP
GROUP BY A, B

### 2 個回答

4

iT邦大師 1 級 ‧ 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

#### A方法:假如不同意 0 * 1 * 2 = 0 的情況

``````where c > 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
``````

#### 範例:

``````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的資料
``````

ORA-01428:argument '0' is out of range

#### 邏輯:

0

iT邦新手 2 級 ‧ 2018-03-15 01:19:29

Multiply Across a Result Set

``````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 筆資料列)
``````