SHOEISHA iD

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

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

分析関数の衝撃

PostgreSQLの分析関数の衝撃(8)
――RowsとRangeの代用

OracleのRowsやRangeの代用

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

ダウンロード SourceCode (1.6 KB)

 本連載では、分析関数の衝撃シリーズを、PostgreSQL用にアレンジした内容と、OracleやDB2の分析関数をPostgreSQL 8.4で代用する方法を扱います。本稿では、PostgreSQL 8.4でOracleの分析関数のRows指定やRange指定と同じ結果を取得するSQLを解説します。

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

はじめに

 本連載では、分析関数の衝撃シリーズを、PostgreSQL用にアレンジした内容と、OracleやDB2の分析関数をPostgreSQL 8.4で代用する方法を扱います。本稿では、PostgreSQL 8.4でOracleの分析関数のRows指定やRange指定と同じ結果を取得するSQLを扱います。

対象読者

  • PostgreSQLでwindow関数を使ってみたい方
  • 分析関数の理解を深めたい方

必要な環境

 本稿で扱うSQLは、PostgreSQL 8.4 beta2で動作確認しました。その他、次の環境でも応用が可能です。

  • Oracle
  • DB2
  • SQL Server

1. Rows 2 Preceding

 『PostgreSQLの分析関数の衝撃2』の「5. 移動累計を求める」では、PostgreSQL 8.4では文法エラーになるRows 2 precedingsumを代用する方法を扱いました。今度は、Rows 2 Precedingcount(*)minmaxを代用してみます。サンプルを見てみましょう。

OracleRows2
ID sortKey Val
1 1 10
1 5 90
1 8 50
1 9 70
2 1 80
2 3 20
2 6 40
2 7 50
3 1 0
3 2 30
3 5 50
4 1 60
模倣対象のOracleのSQL
select ID,sortKey,Val,
count(*) over(partition by ID
              order by sortKey Rows 2 Preceding) as cnt,
min(Val) over(partition by ID
              order by sortKey Rows 2 Preceding) as minVal,
max(Val) over(partition by ID
              order by sortKey Rows 2 Preceding) as maxVal
  from OracleRows2
order by ID,sortKey;
出力結果
ID sortKey Val cnt minVal maxVal
1 1 10 1 10 10
1 5 90 2 10 90
1 8 50 3 10 90
1 9 70 3 50 90
2 1 80 1 80 80
2 3 20 2 20 80
2 6 40 3 20 80
2 7 50 3 20 50
3 1 0 1 0 0
3 2 30 2 0 30
3 5 50 3 0 50
4 1 60 1 60 60

 Rows 2 Precedingなので、ソートキーをsortKeyとしての1行前と2行前の行の値を求めればよさそうだと考えて、答えは下記となります。

PostgreSQL8.4での代用案
select ID,sortKey,Val,
1+case when Lag1 is null then 0 else 1 end
 +case when Lag2 is null then 0 else 1 end as cnt,
   Least(Val,Lag1,Lag2) as minVal,
Greatest(Val,Lag1,Lag2) as maxVal
from (select ID,sortKey,Val,
      Lag(Val,1) over(partition by ID
                      order by sortKey) as Lag1,
      Lag(Val,2) over(partition by ID
                      order by sortKey) as Lag2
        from OracleRows2) a
order by ID,sortKey;

 Lag関数で1行前と2行前を求めて(なければnull)、case式やLeast関数やGreatest関数で使用してます。補足ですが、OracleやDB2のLeast関数やGreatest関数は、引数に1つでもnullがあると関数の値もnullになりますが、PostgreSQLのLeast関数やGreatest関数はnullを無視してくれます。

 SQLのイメージは下記となります。partition by IDに対応する赤線と、order by sortKey Rows 2 Precedingに対応する黄緑線と青線を引いてます。

SQLのイメージ
SQLのイメージ

会員登録無料すると、続きをお読みいただけます

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

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

メールバックナンバー

次のページ
2. Rows between current row and unbounded following

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

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

もっと読む

この記事の著者

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

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

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

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

この記事をシェア

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

おすすめ

アクセスランキング

アクセスランキング

イベント

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

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

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

メールバックナンバー

アクセスランキング

アクセスランキング