はじめに
2009年7月に正式リリースされたPostgreSQL 8.4で、分析関数（window関数）がサポートされました。本連載では、分析関数の衝撃シリーズを、PostgreSQL用にアレンジした内容と、OracleやDB2の分析関数をPostgreSQL 8.4で代用する方法を扱います。
本稿では、『分析関数の衝撃5 （総集編）』を、PostgreSQL8.4用にリニューアルした内容を扱います。
対象読者
- PostgreSQLでwindow関数を使ってみたい方
- 分析関数の理解を深めたい方
必要な環境
本稿で扱うSQLは、PostgreSQL 8.4 beta2で動作確認しました。その他、次の環境でも応用が可能です。
- Oracle
- DB2
- SQL Server
1. 旅人算の感覚を応用する（2人旅人算）
旅人算というのは、有名な算数の問題です。旅人算の問題を解くには、速さの異なる複数の旅人を脳内でイメージする感覚が有効ですが、SQLにおいて、旅人算の感覚を応用することができるのです。本稿では、旅人算の感覚の使用例を2つ扱います。
最初は、2人旅人算の感覚を応用したSQLです。『PostgreSQLの分析関数の衝撃1』の「1. 歯抜けの最小値を探す」では、下記のselect文を場合分けを行って検証しました。
歯抜けの最小値を探す
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;
上記のselect文を、旅人算の感覚（2人旅人算）を使って検証してみましょう。 速さが異なる2人の旅人（旅人R,旅人S）が数直線の原点からプラス方向に同時にスタートしたとして、
速さが1の旅人Rの位置
Row_Number() over(order by seq)
速さが1以上の整数の旅人Sの位置
seq
と考えると、2人の旅人の位置の差は、広義の単調増加（大きくなるかそのまま）であると分かります。
そして、歯抜けが発生するなら、その位置は、(2人の旅人が最後に同じだった位置)+1だと分かります。歯抜けが発生しなくても、同様に(2人の旅人が最後に同じだった位置)+1が求めるべき値となります。SQLのイメージは下記となります。
case1 歯抜けの最小値は4
seq rn 2人の旅人の位置 --- -- - 1- 2- 3- 4- 5- 6- 1 1 |SR| | | | | | 2 2 | |SR| | | | | 3 3 | | |SR| | | | 5 4 | | | | R|S | | 6 5 | | | | | R|S |
case2 歯抜けの最小値は5
seq rn 2人の旅人の位置 --- -- - 1- 2- 3- 4- 5- 6- 1 1 |SR| | | | | | 2 2 | |SR| | | | | 3 3 | | |SR| | | | 4 4 | | | |SR| | | 6 5 | | | | | R|S |
case3 歯抜けの最小値は1
seq rn 2人の旅人の位置 --- -- - 1- 2- 3- 4- 2 1 | R|S | | | 3 2 | | R|S | | 4 3 | | | R|S |
case4 歯抜けの最小値は6
seq rn 2人の旅人の位置 --- -- - 1- 2- 3- 4- 5- 1 1 |SR| | | | | 2 2 | |SR| | | | 3 3 | | |SR| | | 4 4 | | | |SR| | 5 5 | | | | |SR|
case5 歯抜けの最小値は1
seq rn 2人の旅人の位置 --- ---- - 1- データなし | |
