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

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

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

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

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

　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をメインとした連載は、階層問い合わせを扱う予定です。

参考資料

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