CodeZine(コードジン)

特集ページ一覧

分析関数の衝撃6(応用編)

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

  • LINEで送る
  • このエントリーをはてなブックマークに追加
2008/10/31 14:00

ダウンロード SourceCode (3.0 KB)

目次

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のイメージ
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オプション」の類題を置いてます。


  • LINEで送る
  • このエントリーをはてなブックマークに追加

バックナンバー

連載:分析関数の衝撃

もっと読む

著者プロフィール

あなたにオススメ

All contents copyright © 2005-2021 Shoeisha Co., Ltd. All rights reserved. ver.1.5