WITH 句 - Amazon Redshift
構文パラメータ使用に関する注意事項再帰的な CTE例例: 再帰的な CTE

WITH 句

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

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

構文

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

ここで、common_table_expression は、非再帰的または再帰的のいずれかになります。非再帰形式は次のとおりです。

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

以下は、common_table_expression の再帰形式です。

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

パラメータ

RECURSIVE

クエリを再帰的な CTE として識別するキーワード。WITH 句で定義された common_table_expression が再帰的である場合、このキーワードは必須です。WITH 句に複数の再帰的な CTE が含まれている場合でも、WITH キーワードの直後に RECURSIVE キーワードを指定できるのは 1 回だけです。一般に、再帰的な CTE は、2 つの部分で構成される UNION ALL サブクエリです。

common_table_expression

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

CTE_table_name

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

column_name

WITH 句サブクエリの出力列名を、カンマで区切ったリスト。指定された列名の数は、サブクエリで定義した列数以下でなければなりません。非再帰的な CTE の場合、column_name 句はオプションです。再帰的な CTE の場合、column_name リストが必要です。

query

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

recursive_query

2 つの SELECT サブクエリから構成される UNION ALL クエリ。

  • 最初の SELECT サブクエリには、同じ CTE_table_name への再帰リファレンスがありません。再帰の最初のシードである結果セットを返します。この部分は、初期メンバーまたはシードメンバーと呼ばれます。

  • 2 番目の SELECT サブクエリは、FROM 句で同じ CTE_table_name を参照します。これは、再帰メンバーと呼びます。recursive_query には、recursive_query を終了するための WHERE 条件が含まれています。

使用に関する注意事項

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

  • SELECT

  • SELECT INTO

  • CREATE TABLE AS

  • CREATE VIEW

  • DECLARE

  • EXPLAIN

  • INSERT INTO...SELECT

  • PREPARE

  • UPDATE (WHERE 句のサブクエリ内。サブクエリで再帰的な CTE を定義することはできません。再帰的な CTE は、UPDATE 句の前に配置する必要があります)。

  • 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 句を使用することは可能です。

再帰的なテーブル共通式

再帰共通テーブル式 (CTE) はそれ自体を参照する CTE です。再帰的な CTE は、従業員とマネージャー間のレポート関係を示す組織図などの階層データのクエリに役立ちます。「例: 再帰的な CTE」を参照してください。

もう 1 つの一般的な用途は、製品が多くのコンポーネントで構成され、各コンポーネント自体も他のコンポーネントまたはサブアセンブリで構成されている場合のマルチレベルの部品表です。

再帰クエリの 2 番目の SELECT サブクエリに WHERE 句を含めることで、再帰の深さを制限する必要があります。例については、「例: 再帰的な CTE」を参照してください。この制限を行わない場合は、次のようなエラーが発生する可能性があります。

  • Recursive CTE out of working buffers.

  • Exceeded recursive CTE max rows limit, please add correct CTE termination predicates or change the max_recursion_rows parameter.

注記

max_recursion_rows は、無限再帰ループを防ぐために再帰 CTE が返すことができる最大行数を設定するパラメータです。これをデフォルトよりも大きな値に変更しないことをお勧めします。これにより、クエリの無限再帰の問題がクラスター内で過剰なスペースを占有することを防ぎます。

再帰的な CTE の結果に対するソート順と制限を指定できます。再帰的な CTE の最終結果に、group by オプションと distinct オプションを含めることができます。

サブクエリ内で、WITH RECURSIVE 句を指定することはできません。recursive_query メンバーには、order by 句または limit 句を含めることはできません。

例

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

例: 再帰的な CTE

次に示すのは、John に直接的または間接的に報告する従業員を返す再帰的な CTE の例です。再帰クエリには、再帰の深さを 4 レベル未満に制限する WHERE 句が含まれています。

--create and populate the sample table
  create table employee (
  id int,
  name varchar (20),
  manager_id int
  );
  
  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);
  
--run the recursive query
  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 as next_level
  from employee e, john_org j
  where e.manager_id = j.id and level < 4
  )
 select distinct id, name, manager_id from john_org order by manager_id;

以下は、クエリの結果です。

    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

以下は、John が所属する部門の組織図です。

John が所属する部門の組織図。