My Photo

« PostgreSQLの"::"はキャスト | Main | PostgreSQLのPREPARE »

February 14, 2014

共通表式(共通テーブル式)

共通表式はSQL:1999で標準化された機能である。PostgreSQLの文書では「その問い合わせのみに存在する一時テーブルを定義するもの」とある。

以下のようなテーブルを考える。
supは供給業者、areaは地域、amountは売り上げ金額である。

supareaamount
A東京70
B東京30
C東京80
D東京20
E大阪83
F大阪36
G大阪29
H大阪52

各業者について、地域、売り上げ金額に加えて地域における金額ベースのシェアを表示したい場合、共通表式を使うとSQLは以下のようになる。

WITH sum_item AS (
       SELECT area, SUM(amount) AS sum_amount
       FROM suppliers
       GROUP BY area
     )
SELECT t1.*, CAST(t1.amount AS real) / t2.sum_amount * 100 AS share
FROM suppliers t1
INNER JOIN sum_item t2
ON t1.area = t2.area
ORDER BY area, share DESC
結果
supareaamountshare
E大阪8341.5
H大阪5226
F大阪3618
G大阪2914.5
C東京8040
A東京7035
B東京3015
D東京2010

上記の場合WITHを使わなくても同じ結果を得るSQL書くことはできる。
WITHを使うことによるメリットは以下が考えられる。

  • 副問い合わせが複雑な場合にはWITHを使った方がSQLが理解しやすくなる場合があることである。
  • 同じ副問い合わせが複数出現するような場合はSQLの記述量の削減になる。

"WITH RECURSIVE" という構文で再帰的な問い合わせができる。これはツリー構造のデータに対処するのに使用される。
ということらしいが、よくわからない^^;

下記のSQLのように、先に定義したWITH問い合わせを後のWITH問い合わせ内で使うこともできる。

WITH regional_sales AS (
        SELECT region, SUM(amount) AS total_sales
        FROM orders
        GROUP BY region
     ), top_regions AS (
        SELECT region
        FROM regional_sales
        WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
     )
SELECT region,
       product,
       SUM(quantity) AS product_units,
       SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;

2014/02/26追記
PostgreSQL 8.4.18 では共通表式を使ったSELECTはできたが、UPDATEはできなかった。
9.2.4のドキュメントにはINSERTとUPDATEもできると書いてあった。

参考文献:WEB+DB PRESS Vol.66 SQL緊急救命室 第5回

参考サイト:PostgreSQL 8.4.4文書 7.8 WITH問い合わせ

« PostgreSQLの"::"はキャスト | Main | PostgreSQLのPREPARE »

PostgreSQL」カテゴリの記事

SQL」カテゴリの記事

Comments

Post a comment

(Not displayed with comment.)

TrackBack

TrackBack URL for this entry:
http://app.cocolog-nifty.com/t/trackback/26461/59132968

Listed below are links to weblogs that reference 共通表式(共通テーブル式):

« PostgreSQLの"::"はキャスト | Main | PostgreSQLのPREPARE »

August 2017
Sun Mon Tue Wed Thu Fri Sat
    1 2 3 4 5
6 7 8 9 10 11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28 29 30 31    
無料ブログはココログ

日本blog村

  • にほんブログ村 IT技術ブログへ
  • にほんブログ村 アニメブログへ
  • にほんブログ村 サッカーブログ アルビレックス新潟へ

好きな音楽家

メモ

XI-Prof