はじめに
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のイメージ
ソートキーを条件分岐
別のサンプルとして、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にしたい時なども考えられます。
