SHOEISHA iD

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

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

分析関数の衝撃

PostgreSQLの分析関数の衝撃3
(数列を扱うSQLとrange指定)

Lag関数とLead関数の使用例


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

ダウンロード SourceCode (2.3 KB)

5. 「2日前の値」

 次も移動累計を求めるSQLのアレンジ問題です。次は2日前からの移動累計ではなく、2日前の値を求めるSQLを考えます。なお、2日前のデータがない場合は、0として扱います。

 まずは、テーブルのデータと出力結果を考えます。

Accounts
prc_date prc_amt
2006-10-26 100
2006-10-28 200
2006-10-31 400
2006-11-03 800
2006-11-04 1600
2006-11-06 3200
2006-11-11 6400
出力結果
prc_date prc_amt before_prc_amt
2006-10-26 100 0
2006-10-28 200 100
2006-10-31 400 0
2006-11-03 800 0
2006-11-04 1600 0
2006-11-06 3200 1600
2006-11-11 6400 0

 答えは下記となります。

window関数で書いたSQL
select prc_date,prc_amt,
case when Lag(prc_date,2) over(order by prc_date) = prc_date- interVal '2 day'
     then Lag(prc_amt,2)  over(order by prc_date)
     when Lag(prc_date)   over(order by prc_date) = prc_date- interVal '2 day'
     then Lag(prc_amt)    over(order by prc_date)
     else 0 end as before_prc_amt
from Accounts;

 前問と似たような考え方で、case式を使って、Lag関数で2行前のprc_dateを調べ、2日前ならprc_amtをcase式の値にし、同様に、Lag関数で1行前のprc_dateを調べ、2日前ならprc_amtをcase式の値にし、else句で2日前のデータがない場合は0として扱ってます。SQLのイメージは下記となります。

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

 上記のSQLは、2日前の値ではなく、75日前の値を求める場合などにLag関数を大量に記述しないといけないので、相関サブクエリと外部結合で2日前の値を求めるSQLを紹介しておきましょう。

相関サブクエリで書いたSQL
select prc_date,prc_amt,
coalesce((select b.prc_amt
            from Accounts b
           where b.prc_date = a.prc_date-interVal '2 day')
         ,0) as before_prc_amt
from Accounts a;
外部結合で書いたSQL
select a.prc_date,a.prc_amt,
coalesce(b.prc_amt,0) as before_prc_amt
  from Accounts a Left Join Accounts b
    on a.prc_date-interVal '2 day' = b.prc_date;

最後に

 本稿では、『分析関数の衝撃3 (後編)』をPostgreSQL 8.4用にリニューアルした内容を扱いました。次回は、『分析関数の衝撃4 (完結編)』をPostgreSQL8.4用にリニューアルした内容を扱います。

参考資料

  • 9.19. ウィンドウ関数』(PostgreSQL 8.4.0文書)

    PostgreSQLのマニュアルです。ウィンドウ関数に関する説明です。

  • SQLクックブック

    「レシピ10.3 連続する値の範囲の最初と最後を求める」に本稿の「3. 最大何人まで座れますか?」で使った考え方が掲載されてます。

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

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

もっと読む

この記事の著者

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

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

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

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

この記事をシェア

  • X ポスト
  • このエントリーをはてなブックマークに追加
CodeZine(コードジン)
https://codezine.jp/article/detail/2695 2009/08/18 19:53

おすすめ

アクセスランキング

アクセスランキング

イベント

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

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

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

メールバックナンバー

アクセスランキング

アクセスランキング