はじめに
2009年7月に正式リリースされたPostgreSQL 8.4で、分析関数（window関数）がサポートされました。本連載では、分析関数の衝撃シリーズを、PostgreSQL用にアレンジした内容と、OracleやDB2の分析関数をPostgreSQL 8.4で代用する方法を扱います。
本稿では、『分析関数の衝撃1 （前編）』を、PostgreSQL8.4用にリニューアルした内容を扱います。
対象読者
- PostgreSQLでwindow関数を使ってみたい方
- 分析関数の理解を深めたい方
『HAVING句の力』に記載されているSQLをwindow関数を使って記述していきますので、『HAVING句の力』を読まれてからの方が理解しやすいと思います。
必要な環境
本稿で扱うSQLは、PostgreSQL 8.4 beta2で動作確認しました。その他、次の環境でも応用が可能です。
- Oracle
- DB2
- SQL Server
1. 歯抜けを探す
まずは歯抜けを探すSQLについてです。『HAVING句の力』では、歯抜けの最小値を探す2つのSQLが提示されています。
SELECT '歯抜けあり' AS gap FROM SeqTbl HAVING COUNT(*) <> MAX(seq);
SELECT MIN(seq + 1) AS gap FROM SeqTbl WHERE (seq+ 1) NOT IN ( SELECT seq FROM SeqTbl);
これらをwindow関数で書き換えてみます。 最初のSQLで歯抜けの有無を調べ、次のSQLで歯抜けの最小値を探してますね。 これら2つをまとめて、以下の仕様を満たすSQLを作ります。
case1 case2 case3 case4 case5
seq seq seq seq seq
----- ----- ----- ----- ----------
1 1 2 1 データなし
2 2 3 2
3 3 4 3
5 4 4
6 6 5
case1では、歯抜けの最小値として4を返す。
case2では、歯抜けの最小値として5を返す。
case3では、歯抜けの最小値として1を返す。
case4では、歯抜けの最小値として6を返す。
case5では、歯抜けの最小値として1を返す。
答えは、下記となります。
select coalesce(max(seq), 0) + 1 as gap
from (select seq, Row_Number() over(order by seq) as Rn
from SeqTbl) a
where seq = Rn;
SQLの処理イメージと考え方は以下の通りです。なお、seqの順位を持つRn列も付与して考えます。
case1 case2 case3 case4 case5 seq Rn seq Rn seq Rn seq Rn seq Rn --- -- --- -- --- -- --- -- --- ----- 1 1 1 1 2 1 1 1 データなし 2 2 2 2 3 2 2 2 3 3 3 3 4 3 3 3 5 4 4 4 4 4 6 5 6 5 5 5
case1とcase2に注目すると、1から（歯抜けの最小値 - 1）まで、seqとRnが等しいことが分かります。case4に注目すると、全レコードのseqとRnが等しいことが分かります。case1とcase2とcase4では、seqとRnが等しいなかで最大のseqに1足した値が、歯抜けの最小値になっていることが分かります。case3とcase5では、seqとRnが等しいなかで最大のseqが、nullとなるので、coalesce関数で対応してます。
上記のように、細かく場合分けを行って検証するのも一つの方法ですが、『分析関数の衝撃5 （総集編）』で扱ったように、旅人算の感覚を使ってもよいでしょう。
