分析関数の衝撃

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

order by句でのwindow関数など

2010/02/15 14:00

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

ソートキーを条件分岐

　別のサンプルとして、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