8-10 first_Value関数を模倣(ignore nullsあり)

SQLパズル

FirstValueTable2
Code  SortKey   Val
----  -------  ----
AAAA      100  null
AAAA      200   888
AAAA      300  null
AAAA      400   666
BBBB      500  null
BBBB      600  null
BBBB      610   333
BBBB      620  null
BBBB      630   999
CCCC      100  null

select Code,SortKey,Val,
First_Value(Val ignore nulls)
over(partition by Code order by SortKey
Rows between Unbounded Preceding and Unbounded Following) as "first_Value"
from FirstValueTable2;
を模倣する。

出力結果
Code  SortKey   Val  first_Value
----  -------  ----  -----------
AAAA      100  null          888
AAAA      200   888          888
AAAA      300  null          888
AAAA      400   666          888
BBBB      500  null          333
BBBB      600  null          333
BBBB      610   333          333
BBBB      620  null          333
BBBB      630   999          333
CCCC      100  null         null

データ作成スクリプト

create table FirstValueTable2 as
select 'AAAA' as Code,100 as SortKey,to_number(null) as Val from dual
union select 'AAAA',200, 888 from dual
union select 'AAAA',300,null from dual
union select 'AAAA',400, 666 from dual
union select 'BBBB',500,null from dual
union select 'BBBB',600,null from dual
union select 'BBBB',610, 333 from dual
union select 'BBBB',620,null from dual
union select 'BBBB',630, 999 from dual
union select 'CCCC',100,null from dual;

SQL

--■■■min関数を使う方法■■■
select Code,SortKey,Val,
max(decode(SortKey,minSortKey,Val)) over(partition by Code) as "first_Value"
from (select Code,SortKey,Val,
      min(nvl2(Val,SortKey,null)) over(partition by Code) as minSortKey
        from FirstValueTable2)
order by Code,SortKey;

--■■■keepを使う方法■■■
select Code,SortKey,Val,
max(Val) Keep (Dense_Rank First order by nvl2(Val,0,1),SortKey)
over(partition by Code) as "first_Value"
  from FirstValueTable2
order by Code,SortKey;

--■■■First_Value関数のソートキーを変える方法■■■
select Code,SortKey,Val,
First_Value(Val) over(partition by Code order by nvl2(Val,SortKey,null)
Rows between Unbounded Preceding and Unbounded Following) as "first_Value"
  from FirstValueTable2
order by Code,SortKey;

解説

min関数を使う方法は、
DB2やSQL Sreverでも応用できるでしょう。

keepを使う方法は、
Oracle9iでignore nullsを代用する用途に使えるでしょう。


sinceなFirst_Value(ignore nulls)の代用例

select Code,SortKey,Val,
First_Value(Val ignore nulls)
over(partition by Code order by SortKey
Rows between current row
         and Unbounded Following) as "first_Value2"
from FirstValueTable2
order by Code,SortKey;

を模倣する。

出力結果
Code  SortKey   Val  sinceFirst
----  -------  ----  ----------
AAAA      100  null        null
AAAA      200   888         888
AAAA      300  null         888
AAAA      400   666         666
BBBB      500  null        null
BBBB      600  null        null
BBBB      610   333         333
BBBB      620  null         333
BBBB      630   999         999
CCCC      100  null        null

select Code,SortKey,Val,
max(decode(SortKey,minSortKey,Val)) over(partition by Code,minSortKey) as "first_Value2"
  from (select Code,SortKey,Val,
        min(nvl2(Val,SortKey,null))
        over(partition by Code order by SortKey
        Rows between current row
                 and Unbounded Following) as minSortKey
          from FirstValueTable2)
order by Code,SortKey;

---