だいぶ間が空いてしまいましたが、前回は「Window関数」で強化された集計機能を紹介しました。今回も、SQL関連の機能強化として追加された「WITH」句の使い方をみていくことにしましょう。

PostgreSQL 8.4では、問い合わせのSQL文中で「WITH」句が使えるようになりました。WITH句は次のような書式で使います。

WITHの書式

WITH 共通表式名 AS (SELECT ....)

ここでWITHが何を表現しているかと言うと、「AS」以降に記述されたSELECT文による問い合わせ結果に対し、「共通表式名」に指定した名前を定義しているわけですね。ここで付けられた名前は、この問い合わせ中に限り、何度でも、AS以降のSELECT文を書く代わりに使用できます。つまり、「同じ副問い合わせを何度も書かねばならない」ような場合に、SQL文を大幅に簡略できるのです。

具体的な例で見てみましょう。まず、前回使用した検定のテーブル「exam」を使い、受験者各自の得点と、その得点が国別の合計得点のうち何パーセントを占めたかの割合を算出するSQL文を考えてみます。WITH句を使わない前提だと、以下のように書くことになるでしょう。

SELECT exam.id, exam.name, exam.country, exam.point, round(exam.point::real / t1.total::real * 100) AS ratio
FROM exam, (SELECT country, sum(point) AS total FROM exam GROUP BY country) AS t1
WHERE exam.country = t1.country
ORDER BY country, ratio;

この例では、「国別の合計得点」の計算を副問い合わせで実現しています。各自の得点が占める割合は、ここで集計された国別の得点合計値と各自の得点から計算して求められます。すでにちょっとややこしいですが、まだ副問い合わせが1つしかないのでマシな方です。実際のシステム開発の現場では、問い合わせの中で同じ副問い合わせを3つも4つも発行しなければならないケースも結構あるのです。

こういった複雑なSQL文は、開発時点ではなんとかなっても、後でメンテナンスしようと思うと「この問い合わせは一体なにをやってるんだ? 」ということになりかねません。そこで、「WITH」が登場するのです。先の問い合わせをWITHを使って書き換えてみましょう。

WITH t1 AS  (SELECT country, sum(point) AS total FROM exam GROUP BY country) 
SELECT exam.id, exam.name, exam.country, exam.point, round(exam.point::real / t1.total::real * 100) AS ratio
FROM exam, t1
WHERE exam.country = t1.country
ORDER BY country, ratio;

SQL文自体の長さは変わりませんが、副問い合わせをSELECT文の外に追い出すことで、かなり見通しがよくなりました。同じ副問い合わせを何度も書いて自己結合するようなSQLの場合には、さらにSQL文自体も短く、すっきりと書けるはずです。

さらに、WITH句に以下のように列挙することで、複数の副問い合わせをまとめて定義しておくこともできます。

WITH t1 AS  (SELECT country, sum(point) AS total FROM exam GROUP BY country) ,
t2 AS  (SELECT country, count(*) AS num FROM exam GROUP BY country) 
SELECT....

ここまででなんとなくイメージは掴めたと思いますが、WITH句はビューととてもよく似た機能です。頻繁に実行する集計クエリなどは、あらかじめビューを作成しておくことが多いと思います。ビューとWITH句が異なるのは、ビューは一度作成すると消すまで残るのに対し、WITH句は問い合わせが実行されている間だけ、特定の問い合わせに名前がつけられるという点です。あまり使う機会は多くないかもしれませんが、「ビューをつくるほどではないけれど、この問い合わせを実行する時だけ、やたらと副問い合わせを書かなければならない」といったケースに遭遇した時のために、覚えておいて損はないでしょう。

もちろん、WITH句が活躍するのはこういった稀なケースだけではありません。次回は、WITHを用いた「再帰」の処理を紹介してみたいと思います。