PHP開発のエッセンス(9) データベースの近況(2) PostgreSQL 8.4の新機能

早速、PostgreSQL 8.4の新機能を見ていくことにしましょう。

インストール方法については、PostgreSQLユーザ会のWebサイト等に詳しいドキュメントがありますので割愛。8.4ベータも、従来のバージョンとインストール方法は変化ありません。基本的にはソースコードを入手してconfigure; make; make installするだけなので簡単ですが、一点だけ、root権限でインストールを実行しないように注意しましょう。データディレクトリがrootの所有になっていると、データベースクラスタの初期化(initdb)を正しく行うことができません。

では、まず追加された便利な機能から紹介していきましょう。最初は「Window関数による集計機能の強化」です。「Window関数」は、簡単に言うと「複雑なデータ分析要求に迅速に答える」ための機能のひとつです。例えば、BI(ビジネスインテリジェンス)の分野では膨大なデータを分析することで新たな知識発見を試みますが、単純な検索・集計機能だけでは、分析に多大な時間と労力が必要になってしまいます。

Window関数を使うと、テーブルのデータを特定の列の値でグルーピングし、各グループ毎の「順位」や「全体に占める割合」などを簡単に取得できます。SQLには「GROUP BY」もありますが、「GROUP BY」ではグルーピングした行が1行に集約されてしまうのに対し、Window関数では元の行をそのまま取得できる点で、より柔軟な使い方が可能になります。これまで頭を捻って無理矢理なSQLを書いていたような処理も、スマートかつ効率的に書けるようになる、というわけです。言葉だけではなかなかイメージしづらいと思いますので、実例を挙げて説明しましょう。

ここでは例として、Web上に「○○検定」の簡単な検定フォームを設置したと想定し、その結果を格納するためのテーブル「exam」を一つ用意します。

create table exam (
    id integer primary key,     -- ID
    name text,              -- 名前
    country text,               -- 国
    city text,                  -- 都市
    email text,             -- メールアドレス
    point integer               -- 得点
);

このテーブルには、適当なテストデータを格納しておきます。テストデータを自分で作成するのは大変なので、今回はGENERATEDATA.COMのサービスを使って生成した、200件のランダムなデータを利用します。データの内容はこんな感じ。

-[ RECORD 1 ]-------------------------------------------------
id      | 1
name    | Cheryl Vincent
country | United Kingdom
city    | Elkhart
email   | neque.vitae@netuset.org
point   | 53
-[ RECORD 2 ]-------------------------------------------------
id      | 2
name    | Althea Mayer
country | Macao
city    | Modesto
email   | pede.Nunc@Vivamussit.ca
point   | 59
-[ RECORD 3 ]-------------------------------------------------
id      | 3
name    | Karleigh Reed
country | Belize
city    | Muncie
email   | aliquam.adipiscing.lacus@parturientmontesnascetur.ca
point   | 70
:
:

例えば国別の得点順位表が欲しい場合、Window関数を使うと以下のような問い合わせを記述できます。

select country, name, point, rank() over (partition by country order by point desc) from exam;

この問い合わせを実行してみたところ、以下の結果が返りました。マカオ、マレーシアといった国別に、得点上位から順位が1,2,3と振られているのがわかりますね。

:
 Macao                             | Althea Mayer        |    59 |    1
 Macao                             | Rhonda Hutchinson   |    52 |    2
 Macao                             | Ashely Sheppard     |    21 |    3
 Macedonia                         | Zoe Brock           |    94 |    1
 Malawi                            | Clementine Deleon   |    96 |    1
 Malawi                            | Courtney Weeks      |    57 |    2
 Malaysia                          | Briar Griffith      |   100 |    1
 Malaysia                          | Aretha Walsh        |    87 |    2
 Malaysia                          | Briar Humphrey      |    43 |    3
:
:

先ほどの問い合わせでは、Window関数「rank()」を使用していますが、関数自体よりも重要なのはむしろ「OVER」句でしょう。「partition by country(国で区切れ)」「order by point desc(得点の降順で並び替えろ)」といった条件をOVERで指定することで、rank()関数は区切られた範囲内の順位を集計してくれるわけです。

PostgreSQL 8.4では、rank()の他にもいくつかのWindow関数が用意されています。

関数名	処理
row_number()	区間内での行番号を返します
rank()	順位(同位がある場合、次点は番号が飛ぶ)を返します
dense_rank()	順位(同位がある場合でも、次点は番号が飛ばない)を返します
percent_rank()	順位(割合-%で表示)を返します
cume_dist()	区間内での相対的な位置を返します
ntile(N)	N分位数に分割 ※ Nに3を指定すると、1位～6位までを1,2,3,1,2,3のように1～3位の繰り返しで返します
lag(value, offset, default)	区間内でoffsetで指定された行数分だけ前の行の、valueに指定された列の値を返します。指定された行が存在しなければdefaultに指定した値を返します
lead(value, offset, default)	区間内でoffsetで指定された行数分だけ後の行の、valueに指定された列の値を返します。指定された行が存在しなければdefaultに指定した値を返します
first_value(value)	区間内でvalueに指定された列の最初の値を返します
last_value(value)	区間内でvalueに指定された列の最後の値を返します
nth_value(value any, N)	区間内でvalueに指定された列のN番目の値を返します