トップページに戻る    次のSQLパズルへ    前のSQLパズルへ

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;