Shoeisha Technology Media

CodeZine(コードジン)

特集ページ一覧

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

SQLで旅人算の感覚

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

ダウンロード SourceCode (1.3 KB)

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

目次

はじめに

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

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

著者プロフィール

バックナンバー

連載:分析関数の衝撃

もっと読む

All contents copyright © 2005-2019 Shoeisha Co., Ltd. All rights reserved. ver.1.5