Shoeisha Technology Media

CodeZine(コードジン)

記事種別から探す

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

PostgreSQLの基本的なwindow関数の使用例

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

ダウンロード SourceCode (1.1 KB)

 2009年7月に正式リリースされたPostgreSQL 8.4で、分析関数(window関数)がサポートされました。本稿では、『分析関数の衝撃1(前編)』をPostgreSQL 8.4用にリニューアルした内容を扱います。

目次

はじめに

 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を返す。

 答えは、下記となります。

window関数で書き換えたSQL
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 (総集編)』で扱ったように、旅人算の感覚を使ってもよいでしょう。


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

著者プロフィール

バックナンバー

連載:分析関数の衝撃

もっと読む

おすすめ記事

All contents copyright © 2006-2017 Shoeisha Co., Ltd. All rights reserved. ver.1.5