メニュー
Amazon Redshift
データベース開発者ガイド (API Version 2012-12-01)

WITH 句

WITH 句は、クエリ内の SELECT リストに先行するオプション句です。WITH 句は、1 つまたは複数のサブクエリを定義します。各サブクエリは、ビュー定義に似ている一時テーブルを定義します。このような一時テーブルは FROM 句内で参照可能で、一時テーブルが所属するクエリの実行中のみ使用可能です。WITH 句内の各サブクエリは、テーブル名、列名のオプションリスト、およびテーブルに対して評価を実行するクエリ表現 (SELECT ステートメント) を指定します。

WITH 句のサブクエリは、単一のクエリ実行中に、使用可能なテーブルを効率的に定義します。SELECT ステートメントの本文内でサブクエリを使用することで、すべてのケースで同じ結果を実現できますが、WITH 句のサブクエリの方が、読み書きが簡単になることがあります。可能な場合は、複数回参照される、WITH 句のサブクエリは、一般的な副次式として最適化されます。つまり、一度 WITH サブクエリを評価すると、その結果を再利用することができるということです。 (一般的な副次式は、WITH 句内で定義される副次式に制限されないちおう点に注意してください。)

構文

Copy
[ WITH with_subquery [, ...] ]

ここで with_subquery は次のようになります。

Copy
with_subquery_table_name [ ( column_name [, ...] ) ] AS ( query )

Parameters

with_subquery_table_name

WITH 句のサブクエリの結果を定義する一時テーブルの一意な名前。単一の WITH 句内で重複する名前を使用することはできません。各サブクエリには、FROM 句 で参照可能なテーブル名を付ける必要があります。

column_name

WITH 句サブクエリの出力列名を、カンマで区切ったオプションリスト指定された列名の数は、サブクエリで定義した列数以下でなければなりません。

query

Amazon Redshift がサポートするすべての SELECT クエリ。「SELECT」を参照してください。

使用に関する注意事項

次の SQL ステートメントで WITH 句を使用できます。

  • SELECT (SELECT ステートメント内のサブクエリを含みます)

  • SELECT INTO

  • CREATE TABLE AS

  • CREATE VIEW

  • DECLARE

  • EXPLAIN

  • INSERT INTO...SELECT

  • PREPARE

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

WITH 句を含んでいるクエリの FROM 句が WITH 句によって定義されたテーブルを参照していない場合、WITH 句は無視され、クエリは通常どおり実行されます。

WITH 句のサブクエリで定義されたテーブルは、WITH 句が開始した SELECT クエリの範囲でのみ参照可能です。例えば、このようなテーブルは、SELECT リスト、WHERE 句、または HAVING 句内のサブクエリの FROM 句で参照できます。サブクエリ内で WITH 句を使用して、メインクエリまたは別のサブクエリの FROM 句でそのテーブルを参照することはできません。このクエリパターンを使用すると、WITH 句のテーブルに対して、relation table_name does not exist という形式のエラーメッセージが発生します。

WITH 句のサブクエリ内で、別の WITH 句を指定することはできません。

WITH 句のサブクエリによって定義されたテーブルに対して、前方参照を作成することはできません。例えば次のクエリでは、テーブル W1 の定義内でテーブル W2 への前方参照を設定しているため、エラーが帰されます。

Copy
with w1 as (select * from w2), w2 as (select * from w1) select * from sales; ERROR: relation "w2" does not exist

WITH 句のサブクエリは、SELECT INTO ステートメントを構成できません。しかし、SELECT INTO ステートメント内で WITH 句を使用することは可能です。

次の例では、WITH 句を含む最もシンプルなケースを示します。VENUECOPY という名前の WITH クエリは、VENUE テーブルからすべての行を選択します。次にメインクエリでは、VENUECOPY からすべての行を選択します。VENUECOPY テーブルは、このクエリの有効期間中だけ存在します。

Copy
with venuecopy as (select * from venue) select * from venuecopy order by 1 limit 10;
Copy
venueid | venuename | venuecity | venuestate | venueseats ---------+----------------------------+-----------------+------------+------------ 1 | Toyota Park | Bridgeview | IL | 0 2 | Columbus Crew Stadium | Columbus | OH | 0 3 | RFK Stadium | Washington | DC | 0 4 | CommunityAmerica Ballpark | Kansas City | KS | 0 5 | Gillette Stadium | Foxborough | MA | 68756 6 | New York Giants Stadium | East Rutherford | NJ | 80242 7 | BMO Field | Toronto | ON | 0 8 | The Home Depot Center | Carson | CA | 0 9 | Dick's Sporting Goods Park | Commerce City | CO | 0 v 10 | Pizza Hut Park | Frisco | TX | 0 (10 rows)

次の例では、VENUE_SALES と TOP_VENUES という名前の 2 つのテーブルを生成する WITH 句を示します。2 番目の WITH 句テーブルは最初のテーブルから選択します。次に、メインクエリブロックの WHERE 句には、TOP_VENUES テーブルを制約するサブクエリが含まれています。

Copy
with venue_sales as (select venuename, venuecity, sum(pricepaid) as venuename_sales from sales, venue, event where venue.venueid=event.venueid and event.eventid=sales.eventid group by venuename, venuecity), top_venues as (select venuename from venue_sales where venuename_sales > 800000) select venuename, venuecity, venuestate, sum(qtysold) as venue_qty, sum(pricepaid) as venue_sales from sales, venue, event where venue.venueid=event.venueid and event.eventid=sales.eventid and venuename in(select venuename from top_venues) group by venuename, venuecity, venuestate order by venuename;
Copy
venuename | venuecity | venuestate | venue_qty | venue_sales ------------------------+---------------+------------+-----------+------------- August Wilson Theatre | New York City | NY | 3187 | 1032156.00 Biltmore Theatre | New York City | NY | 2629 | 828981.00 Charles Playhouse | Boston | MA | 2502 | 857031.00 Ethel Barrymore Theatre | New York City | NY | 2828 | 891172.00 Eugene O'Neill Theatre | New York City | NY | 2488 | 828950.00 Greek Theatre | Los Angeles | CA | 2445 | 838918.00 Helen Hayes Theatre | New York City | NY | 2948 | 978765.00 Hilton Theatre | New York City | NY | 2999 | 885686.00 Imperial Theatre | New York City | NY | 2702 | 877993.00 Lunt-Fontanne Theatre | New York City | NY | 3326 | 1115182.00 Majestic Theatre | New York City | NY | 2549 | 894275.00 Nederlander Theatre | New York City | NY | 2934 | 936312.00 Pasadena Playhouse | Pasadena | CA | 2739 | 820435.00 Winter Garden Theatre | New York City | NY | 2838 | 939257.00 (14 rows)

次の 2 つの例は、WITH 句サブクエリに基づいた、テーブル参照の範囲に関するルールをデモンストレーションしています。最初のクエリは実行されますが、2 番目のクエリは予想どおりのエラーが発生して失敗します。最初のクエリには、メインクエリの SELECT リスト内に WITH 句サブクエリが存在します。WITH 句によって定義されるテーブル (HOLIDAYS) は、SELECT リストのサブクエリの FROM 句で参照されます。

Copy
select caldate, sum(pricepaid) as daysales, (with holidays as (select * from date where holiday ='t') select sum(pricepaid) from sales join holidays on sales.dateid=holidays.dateid where caldate='2008-12-25') as dec25sales from sales join date on sales.dateid=date.dateid where caldate in('2008-12-25','2008-12-31') group by caldate order by caldate; caldate | daysales | dec25sales -----------+----------+------------ 2008-12-25 | 70402.00 | 70402.00 2008-12-31 | 12678.00 | 70402.00 (2 rows)

2 番目のクエリは SELECT リストのサブクエリ内だけでなく、メインクエリ内の HOLIDAYS テーブルを参照しようとしたため、失敗しました。メインクエリの参照は範囲外です。

Copy
select caldate, sum(pricepaid) as daysales, (with holidays as (select * from date where holiday ='t') select sum(pricepaid) from sales join holidays on sales.dateid=holidays.dateid where caldate='2008-12-25') as dec25sales from sales join holidays on sales.dateid=holidays.dateid where caldate in('2008-12-25','2008-12-31') group by caldate order by caldate; ERROR: relation "holidays" does not exist