分析関数の衝撃

分析関数の衝撃6（応用編）

CodeZineに掲載されたSQLを分析関数で記述する 6

2008/10/31 14:00

ポスト

4. case式とignore nullsオプション

　最後に、case式とignore nullsオプションを使うSQLです。

　Oracle10gから、First_Value関数とLast_Value関数に、ignore nullsオプションが追加されました。ignore nullsオプションを使うと、ソートキーの順序で、null値を無視して、最初の行の値（First_Value）または最後の行の値（Last_Value）を取得することができますが、case式と組み合わせることもできるのです。

　例をあげて解説しましょう。まずは、テーブルのデータと、出力結果を考えます。

FlagTable

Seq	Flag	Val
1	1	aaa
2	1	111
3	0	bbb
4	0	222
5	0	ccc
6	1	333
7	0	ddd
8	1	444
9	1	eee
10	0	555
11	1	fff

　Seqの昇順で、各行ごとに、Flag=1でSeqが自分以下で最大な行のValの値（LastVal）と、Flag=1でSeqが自分以上で最小な行のValの値（FirstVal）を求めます。

出力結果

Seq	Flag	Val	LastVal	FirstVal
1	1	aaa	aaa	aaa
2	1	111	111	111
3	0	bbb	111	333
4	0	222	111	333
5	0	ccc	111	333
6	1	333	333	333
7	0	ddd	333	444
8	1	444	444	444
9	1	eee	eee	eee
10	0	555	eee	fff
11	1	fff	fff	fff

　Flag=1でない行は、無視すればよいので、答えはこうなります。

答え

select Seq,Flag,Val,
Last_Value (case when Flag = 1
                 then Val end ignore nulls)
over(order by Seq) as LastVal,
First_Value(case when Flag = 1
                 then Val end ignore nulls)
over(order by Seq
     rows between Current Row
              and Unbounded Following) as FirstVal
  from FlagTable
order by Seq;

　case式には単純case式と検索case式がありますが両方とも、上から順にwhen句が条件を満たすかを調べ、条件を満たしたらthen句の値を返します。全てのwhen句が条件を満たさなかったらelse句の値を返しますが、else句がなければnullを返します。そして、ignore nullsオプションを使うと、ソートキーの順序で、null値を無視して、最初の行の値（First_Value）または最後の行の値（Last_Value）を取得することができます。

　上記をふまえて、Flag=1ならValの値を返し、Flag=1でないならnullを返す検索case式と、ignore nullsオプションと組み合わせてます。いいかえると、case式とignore nullsオプションによって、Flag=1を満たさない行を無視してます。

　SQLのイメージは下記となります。青線がFirst_Valueのイメージで、緑線がLast_Valueのイメージです。

SQLのイメージ

最後に

　連載記事、分析関数の衝撃も前編,中編,後編,完結編,総集編,応用編ときて一段落となりました。次のOracleのSQLをメインとした連載は、階層問い合わせを扱う予定です。

参考資料

達人に学ぶ SQL徹底指南書『一意集合と多重集合の一般化（192ページ）』
本稿の「1. 複数列のdistinctなcount」の元ネタです。
DB2 SQLパズル『分析関数のcount関数のdistinctオプションを模倣』
本稿の「1. 複数列のdistinctなcount」の応用例（SQLServerやDB2で、OLAPのcount関数のdistinctオプションを模倣する方法）を置いてます。
OracleSQLパズル『集合関数のdense_rank関数』
本稿の「1. 複数列のdistinctなcount」の集合関数版を置いてます。
OracleSQLパズル『正順位と逆順位』
本稿の「1. 複数列のdistinctなcount」に関連する考察を置いてます。
OracleSQLパズル『前後を差が1のデータで挟まれていなければ出力』
本稿の「2. range指定のcount関数」の元ネタです。
OracleSQLパズル『次の入社日を求める』
本稿の「3. 次の入社日を求める」の別解を置いてます。
OracleSQLパズル『case式とignore nullsその1』
本稿の「4. case式とignore nullsオプション」の類題を置いてます。
OracleSQLパズル『case式とignore nullsその2』
本稿の「4. case式とignore nullsオプション」の類題を置いてます。