Shoeisha Technology Media

CodeZine(コードジン)

特集ページ一覧

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

order by句でのwindow関数など

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

ダウンロード SourceCode (1.3 KB)

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

目次

はじめに

 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にしたい時なども考えられます。


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

著者プロフィール

バックナンバー

連載:分析関数の衝撃

もっと読む

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