この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
はじめに
一時的なテーブルが欲しい場合、一時テーブルやサブクエリを利用する事があります。ただ、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)
上記は2つのサブクエリを作り、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がとても有効だと思います。