WITH 句

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

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

構文

[ WITH  common_table_expression [, common_table_expression , ...] ]

common_table_expression は、非再帰的にすることもできます。非再帰形式は次のとおりです。

CTE_table_name AS ( query )

パラメータ

common_table_expression

FROM 句 で参照できる一時テーブルを定義し、それが属するクエリの実行中にのみ使用されます。

CTE_table_name

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

query

が AWS Clean Rooms サポートするすべての SELECT クエリ。SELECT を参照してください。

使用に関する注意事項

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

• SELECT、WITH、UNION、INTERSECT、および EXCEPT

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

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

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

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

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

例

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

with venuecopy as (select * from venue)
select * from venuecopy order by 1 limit 10;

 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 テーブルを制約するサブクエリが含まれています。

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;

        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 句で参照されます。

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 テーブルを参照しようとしたため、失敗しました。メインクエリの参照は範囲外です。

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