分析関数の衝撃

PostgreSQLの分析関数の衝撃6
（window関数の応用例）

複数列のdistinctなcountなど

2010/01/20 14:00

ポスト

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

　最後にFirst_Value関数とLast_Value関数の、case式とignore nullsオプションの組み合わせを模倣するSQLです。Oracle 10gやDB2 V9.5では、First_Value関数とLast_Value関数で、ignore nullsオプションが使用できます。

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

　PostgreSQL 8.4でcase式とignore nullsオプションを組み合わせた、First_Value関数とLast_Value関数を模倣してみます。まずは、テーブルのデータと出力結果を考えます。

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の値（Las）と、Flag=1でSeqが自分以上で最小な行のValの値（Fir）を求めます。

出力結果

Seq	Flag	Val	Las	Fir
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

　『分析関数の衝撃6 （応用編）』で使用した下記のignore nullsオプションを使ったSQLは、PostgreSQL 8.4では文法エラーになります。

PostgreSQL 8.4では文法エラー

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

　答えは下記となります。

ignore nullsオプションを模倣する方法

select Seq,Flag,Val,
max(case when Seq = LasSeq then Val end)
over(partition by LasSeq) as Las,
max(case when Seq = firSeq then Val end)
over(partition by firSeq) as Fir
from (select Seq,Flag,Val,
      max(case when Flag = 1 then Seq end)
      over(order by Seq) as LasSeq,
      min(case when Flag = 1 then Seq end)
      over(order by Seq desc) as firSeq
        from FlagTable) a
order by Seq;

　インラインビューの中で、Flag=1でSeqが自分以下で最大な行のSeqをLasSeqとして求め、Flag=1でSeqが自分以上で最小な行のSeqをfirSeqとして求めてます。次にmax関数とcase式を組み合わせて、そのSeqの行のValを求めてます。

　SQLのイメージは下記となります。First_Value(case when Flag = 1 then Val end ignore nulls) over(order by Seq rows between Current Row and Unbounded Following)に対応する青線と、Last_Value(case when Flag = 1 then Val end ignore nulls) over(order by Seq)に対応する黄緑線を引いてます。

SQLのイメージ