SHOEISHA iD

※旧SEメンバーシップ会員の方は、同じ登録情報(メールアドレス&パスワード)でログインいただけます

CodeZine編集部では、現場で活躍するデベロッパーをスターにするためのカンファレンス「Developers Summit」や、エンジニアの生きざまをブーストするためのイベント「Developers Boost」など、さまざまなカンファレンスを企画・運営しています。

分析関数の衝撃

PostgreSQLの分析関数の衝撃5
(Row_Number関数の応用例)

SQLで旅人算の感覚

  • X ポスト
  • このエントリーをはてなブックマークに追加

ダウンロード SourceCode (1.3 KB)

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

  • X ポスト
  • このエントリーをはてなブックマークに追加

はじめに

 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-
データなし  |  |

次のページ
2. 旅人算の感覚を応用する(3人旅人算)

この記事は参考になりましたか?

  • X ポスト
  • このエントリーをはてなブックマークに追加
分析関数の衝撃連載記事一覧

もっと読む

この記事の著者

山岸 賢治(ヤマギシ ケンジ)

趣味が競技プログラミングなWebエンジニアで、OracleSQLパズルの運営者。AtCoderの最高レーティングは1204(水色)。

※プロフィールは、執筆時点、または直近の記事の寄稿時点での内容です

この記事は参考になりましたか?

この記事をシェア

  • X ポスト
  • このエントリーをはてなブックマークに追加
CodeZine(コードジン)
https://codezine.jp/article/detail/2692 2009/10/16 14:00

おすすめ

アクセスランキング

アクセスランキング

イベント

CodeZine編集部では、現場で活躍するデベロッパーをスターにするためのカンファレンス「Developers Summit」や、エンジニアの生きざまをブーストするためのイベント「Developers Boost」など、さまざまなカンファレンスを企画・運営しています。

新規会員登録無料のご案内

  • ・全ての過去記事が閲覧できます
  • ・会員限定メルマガを受信できます

メールバックナンバー

アクセスランキング

アクセスランキング