SHOEISHA iD

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

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

分析関数の衝撃

PostgreSQLの分析関数の衝撃(7)
――window関数の変わった使用例

order by句でのwindow関数など

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

ダウンロード SourceCode (1.3 KB)

 本連載では、分析関数の衝撃シリーズを、PostgreSQL用にアレンジした内容と、OracleやDB2の分析関数をPostgreSQL 8.4で代用する方法を扱います。本稿では、window関数の変わった使用例を扱います。

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

はじめに

 2009年7月に正式リリースされたPostgreSQL 8.4で、分析関数(window関数)がサポートされました。本連載では、分析関数の衝撃シリーズを、PostgreSQL用にアレンジした内容と、OracleやDB2の分析関数をPostgreSQL 8.4で代用する方法を扱います。本稿では、window関数の変わった使用例を扱います。

対象読者

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

必要な環境

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

  • Oracle
  • DB2
  • SQL Server

1. order by句でwindow関数

 まずは、order by句でのwindow関数の使用例です。『PostgreSQLのマニュアル 3.5. ウィンドウ関数』に記述されているように、window関数はselect句とorder by句で使用することができます。サンプルを見てみましょう。

amountData
shopID dayCol amount
111 2010-01-01 100
111 2010-01-12 200
111 2010-01-13 400
111 2010-01-24 10
222 2010-01-01 900
222 2010-01-12 900
333 2010-01-01 100
333 2010-01-12 100
333 2010-01-23 100

 shopIDごとのamountの合計の降順、shopIDの昇順、dayColの昇順に出力してみます。

order by句でwindow関数
select shopID,dayCol,amount
  from amountData
order by sum(amount) over(partition by shopID) desc,
         shopID,dayCol;
出力結果
shopID dayCol amount
222 2010-01-01 900
222 2010-01-12 900
111 2010-01-01 100
111 2010-01-12 200
111 2010-01-13 400
111 2010-01-24 10
333 2010-01-01 100
333 2010-01-12 100
333 2010-01-23 100

 SQLのイメージは下記となります。partition by shopIDで赤線を引いてます。

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

ソートキーを条件分岐

 別のサンプルとして、amountDataテーブルのレコード数が5以上ならamountの降順、shopIDの昇順、dayColの昇順。amountDataテーブルのレコード数が5未満ならshopIDの昇順、dayColの昇順に出力してみます。

order by句でcase式とRow_Number関数
select shopID,dayCol,amount
  from amountData
order by
case when count(*) over() >= 5
     then Row_Number() over(order by amount desc,
                            dayCol,shopID)
     else Row_Number() over(order by shopID,dayCol) end;
出力結果
shopID dayCol amount
222 2010-01-01 900
222 2010-01-12 900
111 2010-01-13 400
111 2010-01-12 200
111 2010-01-01 100
333 2010-01-01 100
333 2010-01-12 100
333 2010-01-23 100
111 2010-01-24 10

 上記のように、order by句でcase式とRow_Number関数を使ってソートキーを条件分岐させることができます。

 別の使用例としては、ソートキーを条件分岐させたいけど、動的SQLではなく、バインド変数を使ったSQLにしたい時なども考えられます。

次のページ
2. update文でwindow関数の値に更新

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

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

もっと読む

この記事の著者

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

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

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

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

この記事をシェア

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

おすすめ

アクセスランキング

アクセスランキング

イベント

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

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

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

メールバックナンバー

アクセスランキング

アクセスランキング