PostgreSQLの分析関数の衝撃（7）
――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句で使用することができます。サンプルを見てみましょう。

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

select shopID,dayCol,amount
  from amountData
order by sum(amount) over(partition by shopID) desc,
         shopID,dayCol;

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

SQLのイメージ

ソートキーを条件分岐

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

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;

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

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