Shoeisha Technology Media

CodeZine(コードジン)

特集ページ一覧

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

OracleのRowsやRangeの代用

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

ダウンロード SourceCode (1.6 KB)

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

目次

はじめに

 本連載では、分析関数の衝撃シリーズを、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のイメージ

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

著者プロフィール

バックナンバー

連載:分析関数の衝撃

もっと読む

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