WITH 句 - Amazon Redshift

翻訳は機械翻訳により提供されています。提供された翻訳内容と英語版の間で齟齬、不一致または矛盾がある場合、英語版が優先します。

WITH 句

WITH 句は、クエリ内の SELECT リストに先行するオプション句です。The WITH clause defines one or more common_table_expressions. Each common table expression (CTE) defines a temporary table, which is similar to a view definition. You can reference these temporary tables in the FROM clause. They're used only while the query they belong to runs. Each CTE in the WITH clause specifies a table name, an optional list of column names, and a query expression that evaluates to a table (a SELECT statement). When you reference the temporary table name in the FROM clause of the same query expression that defines it, the CTE is recursive.

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

Syntax

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

where common_table_expression can be either non-recursive or recursive. Following is the non-recursive form:

CTE_table_name [ ( column_name [, ...] ) ] AS ( query )

Following is the recursive form of common_table_expression:

CTE_table_name (column_name [, ...] ) AS ( recursive_query )

Parameters

RECURSIVE

Keyword that identifies the query as a recursive CTE. This keyword is required if any common_table_expression defined in the WITH clause is recursive. You can only specify the RECURSIVE keyword once, immediately following the WITH keyword, even when the WITH clause contains multiple recursive CTEs. In general, a recursive CTE is a UNION ALL subquery with two parts.

common_table_expression

Defines a temporary table that you can reference in the FROM 句 and is used only during the execution of the query to which it belongs.

CTE_table_name

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

column_name

A list of output column names for the WITH clause subquery, separated by commas. 指定された列名の数は、サブクエリで定義した列数以下でなければなりません。For a CTE that is non-recursive, the column_name clause is optional. For a recursive CTE, the column_name list is required.

query

Any SELECT query that Amazon Redshift supports. 「SELECT」を参照してください。

recursive_query

A UNION ALL query that consists of two SELECT subqueries:

  • The first SELECT subquery doesn't have a recursive reference to the same CTE_table_name. It returns a result set that is the initial seed of the recursion. This part is called the initial member or seed member.

  • The second SELECT subquery references the same CTE_table_name in its FROM clause. This is called the recursive member. The recursive_query contains a WHERE condition to end the recursive_query.

使用に関する注意事項

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

  • SELECT

  • SELECT INTO

  • CREATE TABLE AS

  • CREATE VIEW

  • DECLARE

  • EXPLAIN

  • INSERT INTO...SELECT

  • PREPARE

  • UPDATE (within a WHERE clause subquery. You can't define a recursive CTE in the subquery. The recursive CTE must precede the UPDATE clause.)

  • DELETE

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

再帰的なテーブル共通式

A recursive common table expression (CTE) is a CTE that references itself. A recursive CTE is useful in querying hierarchical data, such as organization charts that show reporting relationships between employees and managers. 「例: Recursive CTE」を参照してください。

Another common use is a multilevel bill of materials, when a product consists of many components and each component itself also consists of other components or subassemblies.

You can specify a sort order and limit on the result of the recursive CTE. You can include group by and distinct options on the final result of the recursive CTE.

You can't specify a WITH RECURSIVE clause inside a subquery. The recursive_query member can't include an order by or limit clause.

Examples

次の例では、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

例: Recursive CTE

The following is an example of a recursive CTE that returns the number of employees that report directly or indirectly to John.

with recursive john_org(id, name, manager_id, level) as ( select id, name, manager_id, 1 as level from employee where name = 'John' union all select e.id, e.name, e.manager_id, level + 1 from employee e, john_org j where e.manager_id = j.id and level < 4 ) select id, name, manager_id from john_org order by manager_id;

Following is the result of the query.

id name manager_id ------+-----------+-------------- 101 John 100 102 Jorge 101 103 Kwaku 101 110 Liu 101 201 Sofía 102 106 Mateo 102 110 Nikki 103 104 Paulo 103 105 Richard 103 120 Saanvi 104 200 Shirley 104 205 Zhang 104

Following is the table definition for this example.

create table employee ( id int, name varchar (20), manager_id int );

Following are the rows inserted into the table.

insert into employee(id, name, manager_id) values (100, 'Carlos', null), (101, 'John', 100), (102, 'Jorge', 101), (103, 'Kwaku', 101), (110, 'Liu', 101), (106, 'Mateo', 102), (110, 'Nikki', 103), (104, 'Paulo', 103), (105, 'Richard', 103), (120, 'Saanvi', 104), (200, 'Shirley', 104), (201, 'Sofía', 102), (205, 'Zhang', 104);

Following is an organization chart for John's department.