create table sekiT(ID,Val) as
select 1, 5 from dual union all
select 1, 7 from dual union all
select 1, 0 from dual union all
select 1,-2 from dual union all
select 2,-1 from dual union all
select 2,-2 from dual union all
select 2,-3 from dual union all
select 3, 5 from dual union all
select 3, 9 from dual union all
select 4,10 from dual;
--■■■再帰with句を使う方法(11gR2以降)■■■
with tmp(ID,Val,rn,maxRn) as(
select ID,Val,
Row_Number() over(partition by ID order by 1),
count(*) over(partition by ID)
from sekiT),
rec(ID,rn,souseki,maxRn) as(
select ID,rn,Val,maxRn
from tmp
where rn=1
union all
select a.ID,b.rn,a.souseki*b.Val,a.maxRn
from rec a,tmp b
where a.ID = b.ID
and a.rn+1=b.rn)
select * from rec
where rn = maxRn
order by ID;
--■■■model句を使う方法(10g以降)■■■
select ID,Souseki
from sekiT
model return updated rows
partition by(ID)
dimension by(Row_Number() over(partition by ID order by 1) as rn)
measures(Val,1 as Souseki)
rules iterate(999) until presentv(Val[iteration_number+2],1,0) = 0
(Souseki[1] = Souseki[1] * Val[iteration_number+1])
order by ID;
--■■■対数を使う方法■■■
select ID,
case when max(decode(Val,0,1,0)) = 1 then 0
else round(exp(sum(Ln(abs(nullif(Val,0)))))) end
* power(-1,sum(decode(sign(Val),-1,1,0))) as souseki
from sekiT
group by ID
order by ID;