10-123 分析関数でのignore nulls

SQLパズル

テーブル
ID  VI   C1
--  --  ---
 1   0    0
 1   1   10
 1   2   20
 1   3   25
 1   4   50
 2   1  100
 2   2  200
 3   3  500

IDごとにVIが1のレコードのC1との差分を計算し、
C2として出力する

VIが1のレコードが存在しない場合と、
VIが1のレコードのC2はnullとして出力する

出力結果
ID  VI   C1    C2
--  --  ---  ----
 1   0    0   -10
 1   1   10  null
 1   2   20    10
 1   3   25    15
 1   4   50    40
 2   1  100  null
 2   2  200   100
 3   3  500  null

SQL

--■■■First_Valueを使う方法(10g以降)■■■
with hoge as
(select 1 as ID,0 as VI,0 as C1 from dual
union select 1,1,10 from dual
union select 1,2,20 from dual
union select 1,3,25 from dual
union select 1,4,50 from dual
union select 2,1,100 from dual
union select 2,2,200 from dual
union select 3,3,500 from dual)
select ID,VI,C1,
case VI when 1 then null
else c1- First_Value(case VI when 1 then C1 end ignore nulls)
         over(partition by ID order by VI Rows between Unbounded
Preceding and Unbounded Following) end as C2
from hoge;

--■■■min関数を使う方法■■■
with hoge as
(select 1 as ID,0 as VI,0 as C1 from dual
union select 1,1,10 from dual
union select 1,2,20 from dual
union select 1,3,25 from dual
union select 1,4,50 from dual
union select 2,1,100 from dual
union select 2,2,200 from dual
union select 3,3,500 from dual)
select ID,VI,C1,
case VI when 1 then null
else c1- min(case VI when 1 then C1 end) over(partition by ID) end as C2
from hoge;

解説

case式とignore nullsを組み合わせてます

min関数は元々ignore nullsで、
First_Value関数はignore nullsを指定すると、ignore nullsになります

---