PostgreSQLの分析関数の衝撃1
（モードとメジアン）

はじめに

　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 （総集編）』で扱ったように、旅人算の感覚を使ってもよいでしょう。