[Redshift][PostgreSQL] WITH句を使ってSQLをシンプルに。

はじめに

一時的なテーブルが欲しい場合、一時テーブルやサブクエリを利用する事があります。ただ、SQLが長くなったり分かり辛くなったりします。こういった場合はWITH句が有効な事があると教えてもらったので、使い方など調べた結果を書いておきます。

WITH句について

詳細は下記公式をご覧下さい。
Amazon Redshift　WITH句

SELECT文の実行前にサブクエリを作っておこうという事みたいです。サブクエリなのでクエリの終了と同時に削除されます。欠点は、Redshiftの場合は圧縮形式を指定できません。
別名で共通テーブル式、CTEなどと呼ばれている様です。

構文

WITH サブクエリ名 AS ( ...select文など... )

使用場所

以下のSQLステートメントで使用できます。

SELECT (ステートメント内のサブクエリを含む)

SELECT INTO

CREATE TABLE AS

CREATE VIEW

DECLARE

EXPLAIN

INSERT INTO...SELECT

PREPARE

(WHERE句サブクエリ内の) UPDATE

環境

MAC OSX 10.10.5 Yosemite

使い方（例）

テーブルの準備

CREATE TABLE items (
 id SMALLINT
 , name VARCHAR(16)
 , item_id SMALLINT
 , item_name VARCHAR(16)
 , PRIMARY KEY(id, item_id)
);

INSERT INTO items VALUES
  (1, '文房具', 1, 'シャーペン')
  , (1, '文房具', 2, '消しゴム')
  , (1, '文房具', 3, '定規')
  , (2, 'かばん', 1, 'リュックサック')
  , (2, 'かばん', 2, 'ショルダーバッグ');

CREATE TABLE genre (
  id SMALLINT
  , name VARCHAR(16)
  ,PRIMARY KEY(id)
);

# SELECT * FROM items;
 id |  name  | item_id |    item_name     
----+--------+---------+------------------
  1 | 文房具 |       1 | シャーペン
  1 | 文房具 |       2 | 消しゴム
  1 | 文房具 |       3 | 定規
  2 | かばん |       1 | リュックサック
  2 | かばん |       2 | ショルダーバッグ
(5 rows)

SELECT

WITH CTE1 AS (
    SELECT item_id, item_name FROM items
  ), CTE2 AS (
    SELECT item_id, item_name FROM items
  )
SELECT * FROM CTE1
UNION ALL
SELECT * FROM CTE2;

 item_id |    item_name     
---------+------------------
       1 | シャーペン
       2 | 消しゴム
       3 | 定規
       1 | リュックサック
       2 | ショルダーバッグ
       1 | シャーペン
       2 | 消しゴム
       3 | 定規
       1 | リュックサック
       2 | ショルダーバッグ
(10 rows)

上記は２つのサブクエリを作り、UNIONで繋げているだけです。
文末のセミコロン「；」まではサブクエリは消え無い様です。

INSERT

INSERT INTO genre
WITH CTE AS (
  SELECT id, name FROM items GROUP BY id, name
)
SELECT * 
FROM CTE;

# select * from genre;
 id |  name  
----+--------
  1 | 文房具
  2 | かばん
(2 rows)

INSERTに使う場合はINSERT句とSELECT句の間に書きます。

後方参照が可能

WITH cte1 AS (SELECT * FROM items)
   , cte2 AS (SELECT * FROM cte1)
SELECt * FROM cte1;

こんな風に前方参照は不可能です。

WITH cte1 AS (SELECT * FROM items)
   , cte2 AS (SELECT * FROM cte3)
   , cte3 AS (SELECT * FROM cte2)
SELECT * FROM items;

さいごに

SQLは結果に辿り着く方法は複数存在するので、できるだけ分かりやすく書くためにWITHがとても有効だと思います。