最新版PostgreSQLバージョン8.4の機能

SQL言語の拡張（1）ウィンドウ関数のサポート

　2009年7月に、およそ1年半ぶりのメジャーバージョンアップとなるPostgreSQLバージョン8.4がリリースされました。本記事執筆時点（2009年10月）ですでに、いくつかのバグ修正が適用された8.4.1がリリースされています。実稼働システムでの採用はこれから始まるといった段階です。

　バージョン8.4は、性能アップというよりは機能アップに重点のおかれたリリースです。例えば、SQLで新たに重要な構文がいくつかサポートされました。また、運用担当者向けに、実務上の要望にこたえる機能追加がされています。今回は、バージョン8.4の新機能について紹介します。

　新たに追加された機能の1つが、ウィンドウ関数です。ウィンドウ関数とは、集計をより柔軟に行うものであり、商用データベース製品にはあって、長らくPostgreSQLには無かった機能です。例えば、以下のようなテーブル

---------------------------------------------------
=# SELECT * FROM t_shopsale;

店舗ID | 形態 | 地域 | 年月 | 売上
--------+------+------+------------+------
101 | 駅前 | 九州 | 2009-04-30 | 6258
102 | 郊外 | 関東 | 2009-04-30 | 7352
103 | 駅前 | 東海 | 2009-04-30 | 4265
104 | 郊外 | 関東 | 2009-04-30 | 8901
（以下省略）
---------------------------------------------------

　では、次のようにOVER句を使って、店舗ID順に並べたまま、複数の分類項目ごとの合計や順位を、各店舗の行ごとに出力することができます。表計算ソフトのような集計技法が、SQLにおける1つの表で可能になったと言えます。ただし、集計分析用の機能の中でも、ROLLUPやCUBEなどの構文は、いまだサポートされていません。

---------------------------------------------------
=# SELECT "店舗ID", "形態", "地域", "売上",
sum("売上") OVER (PARTITION BY "地域") AS "地域計",
rank() OVER (ORDER BY "売上" DESC),
rank() OVER (PARTITION BY "形態" ORDER BY "売上" DESC) AS "形態別",
rank() OVER (PARTITION BY "地域" ORDER BY "売上" DESC) AS "地域別"
FROM t_shopsale WHERE "年月" = '2009-04-30' ORDER BY "店舗ID";

店舗ID | 形態 | 地域 | 売上 | 地域計 | rank | 形態別 | 地域別
--------+------+------+------+--------+------+--------+--------
101 | 駅前 | 九州 | 6258 | 127070 | 44 | 23 | 9
102 | 郊外 | 関東 | 7352 | 165516 | 33 | 17 | 10
103 | 駅前 | 東海 | 4265 | 146342 | 72 | 37 | 19
104 | 郊外 | 関東 | 8901 | 165516 | 14 | 10 | 4
（以下省略）
---------------------------------------------------

SQL言語の拡張（2）WITH構文による再帰結合

　バージョン8.4ではまた、WITH句による再帰結合の構文がサポートされました。図1のデータを表現する、以下のようなテーブル

---------------------------------------------------
=# SELECT * FROM t_section ;

組織ID | 組織名 | 上位組織
--------+------------+----------
1 | 取締役会 |
2 | 営業本部 | 1
3 | 法人営業部 | 2
（以下省略）
---------------------------------------------------

を、次のように結合できます。

---------------------------------------------------
=# WITH RECURSIVE r ("組織ID", "組織名", "上位ライン") AS
(SELECT "組織ID", "組織名", '{}'::TEXT[] FROM t_section WHERE "上位組織" IS NULL
UNION ALL
SELECT s."組織ID", s."組織名", ARRAY[r."組織名"] || r."上位ライン"
FROM t_section s JOIN r ON (s."上位組織" = r."組織ID")
) SELECT * FROM r;

組織ID | 組織名 | 上位ライン
--------+------------+---------------------
1 | 取締役会 | {}
2 | 営業本部 | {取締役会}
6 | 生産本部 | {取締役会}
10 | 企画室 | {取締役会}
3 | 法人営業部 | {営業本部,取締役会}
4 | 個人営業部 | {営業本部,取締役会}
（以下省略）
---------------------------------------------------

　WITH句は、その後に続くSQLによってビューのように参照される、一時的なテーブルを定義するものです。そこで「WITH RECURSIVE」と指定すると、自分自身と結合するSELECT文を、UNION ALLの後に記述することができます。これにより、再帰的にテーブルを手繰るようなSELECT文を記述することができます。なお、この例では、「上位ライン」カラムにTEXTの配列データ型を使っています。