再帰with句09 総積を求める

SQLパズル

ValTable
ID  Val
--  ---
 1    5
 1    7
 1    0
 1   -2
 2   -1
 2   -2
 2   -3
 3    5
 3    9
 4   10

IDごとの総積を求める。

ID  souseki
--  -------
 1        0
 2       -6
 3       45
 4       10

データ作成スクリプト

create table ValTable(ID,Val) as
select 1, 5 union all
select 1, 7 union all
select 1, 0 union all
select 1,-2 union all
select 2,-1 union all
select 2,-2 union all
select 2,-3 union all
select 3, 5 union all
select 3, 9 union all
select 4,10;

SQL

--■■■再帰with句を使う方法■■■
with recursive RowN(ID,Val,rn,TreeHeight) as(
select ID,Val,
Row_Number() over(partition by ID order by Val),
Count(*) over(partition by ID)
  from ValTable),
w(ID,rn,TreeHeight,souseki) as(
select ID,rn,TreeHeight,Val
  from RowN
 where rn=1
union all
select w.ID,b.rn,w.TreeHeight,w.souseki*b.Val
  from w,RowN b
 where w.ID=b.ID
   and w.rn=b.rn-1)
select ID,souseki from w
 where rn=TreeHeight
order by ID;

--■■■対数を使う方法■■■
select ID,
round(case when bool_or(Val = 0) then 0
      else exp(sum(ln(abs(nullif(Val,0)))))
          *power(-1,sum(case sign(Val) when -1 then 1 else 0 end)) end) as souseki
from ValTable
group by ID;

解説

再帰with句の使いどころでしょうねぇ

---