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

ニュース
トップ

【コラム】

PHP開発のエッセンス

10 データベースの近況(3) PostgreSQL 8.4の新機能-2

鶴田展之  [2009/07/30]
  • >
  • >>

10/10

だいぶ間が空いてしまいましたが、前回は「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を用いた「再帰」の処理を紹介してみたいと思います。

  • >
  • >>

10/10

インデックス

連載目次
第10回 データベースの近況(3) PostgreSQL 8.4の新機能-2
第9回 データベースの近況(2) PostgreSQL 8.4の新機能
第8回 データベースの近況
第7回 PHP 6のこまかい変更点
第6回 PHPとロケール
第5回 PHP 6と国際化
第4回 PHP 6を先取り(3) PHP 6のnamespace
第3回 PHP 6を先取り(2) PHP 6をビルドする
第2回 PHP 6を先取り(1) - インストールの準備
第1回 PHPとその周辺

もっと見る

関連したタグ


マイナビニュースのセミナー情報

オススメ記事

マイナビニュースの集合特集

新着記事

転職ノウハウ

あなたの仕事適性診断
あなたの仕事適性診断

4つの診断で、自分の適性を見つめなおそう!

Heroes File ~挑戦者たち~
Heroes File ~挑戦者たち~

働くこと・挑戦し続けることへの思いを綴ったインタビュー

はじめての転職診断
はじめての転職診断

あなたにピッタリのアドバイスを読むことができます。

転職Q&A
転職Q&A

転職に必要な情報が収集できます

ドS美人面接官 vs モテたいエンジニア
ドS美人面接官 vs モテたいエンジニア

入室しようとしたら、マサカリ投げられちゃいました!?

特別企画

一覧

    人気記事

    一覧

    イチオシ記事

    新着記事

    新バラエティ番組『ネリさまぁ~ず』、Huluでテレビ放送に先駆け配信開始
    [23:30 7/22] エンタメ
    資生堂パーラー、羽田空港限定「東京おやつ しっとりア・ラ・プリン」発売
    [23:29 7/22] ライフ
    ブラジル代表の新監督にドゥンガ氏が就任…2010年に解任以来の2度目
    [23:25 7/22] ライフ
    応募者1万人から選ばれたファン3名、C・ロナウドを前に大興奮
    [23:09 7/22] ライフ
    単独初来日のC・ロナウド、自身のキャリアは「あと6、7年はプレーしたい」
    [22:48 7/22] ライフ

    特別企画

    一覧

      求人情報