[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)
上記は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がとても有効だと思います。