カテゴリ：: クエリ構文

WITH¶

WITH 句は、 SELECT ステートメントの本体の前にあるオプションの句であり、後ほどステートメントで使用できる1つ以上の CTEs （共通テーブル式）を定義します。たとえば、 FROM 句で CTEs を参照できます。

注釈

ストアドプロシージャに似た匿名プロシージャを作成して呼び出す場合は、 WITH 句を使用できます。この句は、 SELECT コマンドではなく CALL コマンドを変更します。詳細については、 CALL （匿名プロシージャの場合）をご参照ください。

WITH 句は、機械学習モデルオブジェクトで使用され、モデルの特定バージョンへのエイリアスを作成して、そのバージョンのメソッドを呼び出すために使用できます。 SQL でのモデルメソッドの呼び出しをご参照ください。

こちらもご参照ください。: CONNECT BY, モデルコマンド

構文¶

サブクエリ：

[ WITH
       <cte_name1> [ ( <cte_column_list> ) ] AS ( SELECT ...  )
   [ , <cte_name2> [ ( <cte_column_list> ) ] AS ( SELECT ...  ) ]
   [ , <cte_nameN> [ ( <cte_column_list> ) ] AS ( SELECT ...  ) ]
]
SELECT ...

Copy

再帰的 CTE：

[ WITH [ RECURSIVE ]
       <cte_name1> ( <cte_column_list> ) AS ( anchorClause UNION ALL recursiveClause )
   [ , <cte_name2> ( <cte_column_list> ) AS ( anchorClause UNION ALL recursiveClause ) ]
   [ , <cte_nameN> ( <cte_column_list> ) AS ( anchorClause UNION ALL recursiveClause ) ]
]
SELECT ...

Copy

条件:

anchorClause ::=
    SELECT <anchor_column_list> FROM ...

recursiveClause ::=
    SELECT <recursive_column_list> FROM ... [ JOIN ... ]

Copy

cte_name1、 cte_nameN: CTE 名は、ビューおよび類似のオブジェクト識別子のルールに従う必要があります。
cte_column_list: CTE （共通テーブル式）の列の名前。
anchor_column_list: 再帰的な CTE のアンカー句で使用される列。このリストの列は、 cte_column_list で定義されている列に対応する必要があります。
recursive_column_list: 再帰的 CTE の再帰的句で使用される列。このリストの列は、 cte_column_list で定義されている列に対応する必要があります。

詳細については、アンカー句および再帰句（このトピック）をご参照ください。アンカー句と再帰句がどのように連携するかに関する詳しい説明については、 CTEs（共通テーブル式）の使用をご参照ください。

使用上の注意¶

一般的な使用法¶

WITH 句は、それ自体、および同じ句の前にある他の CTEs を再帰的に参照できます。たとえば、 cte_name2 は、 cte_name1 およびそれ自体を参照できますが、 cte_name1 はそれ自体は参照できても cte_name2 は参照できません。
WITH 句には、再帰的および非再帰的（反復および非反復） CTE 句を混在させることができます。 CTE が別の CTE を参照する必要がある場合、参照される CTE がステートメントの前の方で定義されているように、 CTE 句を並べる必要があります。（例：2番目の CTE は最初の CTEを参照できますが、その逆はできません。）

CTEs は、再帰的かどうかに基づいて順番にリストする必要はありません。例えば、非再帰的な CTE はキーワード RECURSIVE の直後にリストでき、再帰的な CTE はその非再帰的な CTEの後に来ることができます。

再帰的な CTE 内では、アンカー句または再帰的句のいずれか（または両方）が別の CTE を参照できます。
再帰 CTEs の場合は、 cte_column_list が必要です。
非再帰 CTEs の場合、 cte_column_list はオプションです。
再帰的な CTE では、 UNION ではなく UNION ALL を使用してください。
キーワード RECURSIVE はオプションです。
- CTEs は、 RECURSIVE が指定されたかどうかにかかわらず、再帰的になります。
- CTEs が再帰的でない場合でも、キーワード RECURSIVE を使用できます。
- RECURSIVE を使用する場合、複数の CTE が再帰的な場合でも、使用は1回に制限する必要があります。
SQL ステートメントは、、キーワード RECURSIVE の有無にかかわらず適切に機能しますが、このキーワードを適切に使用すると、コードの理解と保守が容易になります。Snowflakeは、1つ以上の CTEs が再帰的である場合にはキーワード RECURSIVE の使用を推奨し、 CTEs が再帰的でない場合にはキーワードの省略を強く推奨します。

注意

再帰的 CTE を使用する場合は、クエリが無限ループに入り、クエリに成功するか、クエリがタイムアウトするか（例: STATEMENT_TIMEOUT_IN_SECONDS パラメーターで指定された秒数を超過）、クエリをキャンセルするまで、クレジットを消費し続けるクエリを作成する可能性があります。

無限ループが発生する可能性のある仕組みと、この問題を回避する方法のガイドラインについては、再帰的 CTE のトラブルシューティングをご参照ください。

たとえば、反復回数を10回未満に制限するには、次のようにします。

WITH cte AS (
  SELECT ..., 1 as level ...

  UNION ALL

  SELECT ..., cte.level + 1 as level
   FROM cte ...
   WHERE ... level < 10
) ...

Copy

制限事項¶

Snowflakeの再帰 CTEs の実装では、他のシステムがサポートする次のキーワードをサポートしていません。
- SEARCH DEPTH FIRST BY ...
- CYCLE ... SET ...

アンカー句¶

再帰的な CTE のアンカー句は SELECT ステートメントです。

アンカー句は、埋め込まれているステートメントの実行中に 1回実行されます。再帰句の前に実行され、再帰的 CTE から最初の行セットを生成します。これらの行は、クエリの出力に含まれるだけでなく、再帰句によっても参照されます。

アンカー句には、 SELECT 句で許可されている SQL 構造を含めることができます。ただし、アンカー句は cte_name1 を参照できません。再帰句のみが cte_name1 を参照できます。

通常、アンカー句は再帰句と同じテーブルから選択しますが、これは必須ではありません。アンカー句は、別のテーブル、ビュー、 UDTF、または定数値といった、テーブル状のデータソースから選択できます。

アンカー句は、階層の単一「レベル」、通常は最上位レベル、または関心のある最高レベルを選択します。たとえば、クエリの目的が自動車の「部品爆発」を表示することである場合、アンカー句は最高レベルのコンポーネント、つまり自動車自体を返します。

アンカー句からの出力は階層の1つの層を表し、この層は再帰句の最初の反復でアクセスされる「ビュー」のコンテンツとして保存されます。

再帰句¶

再帰句は SELECT ステートメントです。この SELECT は、投影、フィルター、および結合（再帰結合が外部結合の保存側にある内部結合および外部結合）に制限されています。再帰句には以下を含めることはできません。

集計またはウィンドウ関数、
GROUP BY、 ORDER BY、 LIMIT、または DISTINCT。

再帰句は、 CTE がテーブルまたはビューであるかのように cte_name1 を参照できます（通常は参照します）。

通常、再帰句には、アンカー句で使用されたテーブルを CTE に結合する JOIN が含まれます。ただし、 JOIN は複数のテーブル、またはテーブルのようなデータソース（ビューなど）を結合できます。

再帰句の最初の反復は、アンカー句のデータから始まります。そのデータは、再帰句の FROM 句で他のテーブルに結合されます。

後続の各反復は、前の反復のデータから始まります。

CTE 句または「ビュー」は、コンテンツを結合できるように前の反復からのコンテンツを保持していると考えることができます。1回の反復中 CTE には、前回の反復からのコンテンツのみが含まれ、以前の反復すべての累積結果が含まれるのではないことに注意してください。累積結果（アンカー句からの結果を含む）は別の場所に保存されます。

再帰的 CTE の列リスト¶

再帰的 CTE には3つの列リストがあります。

cte_column_list
anchor_column_list （アンカー句内）
recursive_column_list （再帰句内）

再帰的な CTE には他の列リスト（例: サブクエリ内）を含めることができますが、これらの3つの列リストが必要です。

これらの3つの列リストはすべて互いに対応している必要があります。

擬似コードでは、これは次のようになります。

WITH RECURSIVE cte_name (X, Y) AS
(
  SELECT related_to_X, related_to_Y FROM table1
  UNION ALL
  SELECT also_related_to_X, also_related_to_Y
    FROM table1 JOIN cte_name ON <join_condition>
)
SELECT ... FROM ...

Copy

列 X と related_to_X は対応する必要があります。アンカー句は CTE が表す「ビュー」の初期「コンテンツ」を生成するため、アンカー句の各列（例: 列 related_to_x）は、 CTE の対応する列に属する出力を生成する必要があります（例: 列 X）。

列 also_related_to_X と X は対応している必要があります。再帰句の反復ごとに、その句の出力が次の反復の CTE/ビューの新しいコンテンツになります。

また、列 related_to_X および also_related_to_X は対応する必要があります。これは、それらが各 UNION ALL 演算子の片側にあり、 UNION ALL 演算子の各側の列が対応しなければならないためです。

例¶

非再帰的の例¶

このセクションでは、サンプルクエリとサンプル出力を提供します。例を簡略化するために、コードではテーブルを作成してロードするステートメントを省略しています。

この最初の例では、単純な WITH 句をビューとして使用して、データのサブセット（この場合は1976年にリリースされた音楽アルバム）を抽出します。この小さなデータベースの場合、クエリ出力はアルバム「Amigos」と「Look Into The Future」であり、両方とも1976年のものです。

with
  albums_1976 as (select * from music_albums where album_year = 1976)
select album_name from albums_1976 order by album_name;
+----------------------+
| ALBUM_NAME           |
|----------------------|
| Amigos               |
| Look Into The Future |
+----------------------+

Copy

次の例では、以前の WITH 句とともに WITH 句を使用しています。 journey_album_info_1976 という名前の CTE は、 album_info_1976 という名前の CTE を使用します。出力は、バンドの名前を持つアルバム「Look Into The Future」です。

with
   album_info_1976 as (select m.album_ID, m.album_name, b.band_name
      from music_albums as m inner join music_bands as b
      where m.band_id = b.band_id and album_year = 1976),
   Journey_album_info_1976 as (select *
      from album_info_1976 
      where band_name = 'Journey')
select album_name, band_name 
   from Journey_album_info_1976;
+----------------------+-----------+
| ALBUM_NAME           | BAND_NAME |
|----------------------+-----------|
| Look Into The Future | Journey   |
+----------------------+-----------+

Copy

この例では、サンタナのアルバムとジャーニーのアルバムで演奏したミュージシャンをリストします。この例では、 WITH 句を使用していません。このクエリ（および同じクエリを実行する同等の方法である次のいくつかのクエリ）の場合、出力は IDs およびミュージシャンの名前であり、両者ともサンタナのアルバムとジャーニーのアルバムで演奏しています。

select distinct musicians.musician_id, musician_name
 from musicians inner join musicians_and_albums inner join music_albums inner join music_bands
 where musicians.musician_ID = musicians_and_albums.musician_ID
   and musicians_and_albums.album_ID = music_albums.album_ID
   and music_albums.band_ID = music_bands.band_ID
   and music_bands.band_name = 'Santana'
intersect
select distinct musicians.musician_id, musician_name
 from musicians inner join musicians_and_albums inner join music_albums inner join music_bands
 where musicians.musician_ID = musicians_and_albums.musician_ID
   and musicians_and_albums.album_ID = music_albums.album_ID
   and music_albums.band_ID = music_bands.band_ID
   and music_bands.band_name = 'Journey'
order by musician_ID;
+-------------+---------------+
| MUSICIAN_ID | MUSICIAN_NAME |
|-------------+---------------|
|         305 | Gregg Rolie   |
|         306 | Neal Schon    |
+-------------+---------------+

Copy

ご覧のとおり、前のクエリには重複したコードが含まれています。次のいくつかの例では、1つ以上の明示的なビューを使用してこのクエリを簡素化し、 CTEs を使用して単純化する方法を示します。

このクエリは、ビューを使用して、前の例の重複と複雑さを軽減する方法を示しています（前の例と同様、これは WITH 句を使用しません）。

create or replace view view_musicians_in_bands AS
  select distinct musicians.musician_id, musician_name, band_name
    from musicians inner join musicians_and_albums inner join music_albums inner join music_bands
    where musicians.musician_ID = musicians_and_albums.musician_ID
      and musicians_and_albums.album_ID = music_albums.album_ID
      and music_albums.band_ID = music_bands.band_ID;

Copy

このビューを使用すると、元のクエリを次のように書き換えることができます。

select musician_id, musician_name from view_musicians_in_bands where band_name = 'Santana'
intersect
select musician_id, musician_name from view_musicians_in_bands where band_name = 'Journey'
order by musician_ID;
+-------------+---------------+
| MUSICIAN_ID | MUSICIAN_NAME |
|-------------+---------------|
|         305 | Gregg Rolie   |
|         306 | Neal Schon    |
+-------------+---------------+

Copy

この例では、 WITH 句を使用して、前述のクエリと同等の処理を実行します。

with
  musicians_in_bands as (
     select distinct musicians.musician_id, musician_name, band_name
      from musicians inner join musicians_and_albums inner join music_albums inner join music_bands
      where musicians.musician_ID = musicians_and_albums.musician_ID
        and musicians_and_albums.album_ID = music_albums.album_ID
        and music_albums.band_ID = music_bands.band_ID)
select musician_ID, musician_name from musicians_in_bands where band_name = 'Santana'
intersect
select musician_ID, musician_name from musicians_in_bands where band_name = 'Journey'
order by musician_ID
  ;
+-------------+---------------+
| MUSICIAN_ID | MUSICIAN_NAME |
|-------------+---------------|
|         305 | Gregg Rolie   |
|         306 | Neal Schon    |
+-------------+---------------+

Copy

これらのステートメントは、より詳細なビューを作成します（この例では WITH 句を使用しません）。

特定のバンドごとにアルバムをリストします。

create or replace view view_album_IDs_by_bands AS
 select album_ID, music_bands.band_id, band_name
  from music_albums inner join music_bands
  where music_albums.band_id = music_bands.band_ID;

Copy

アルバムで演奏したミュージシャンをリストします。

create or replace view view_musicians_in_bands AS
 select distinct musicians.musician_id, musician_name, band_name
  from musicians inner join musicians_and_albums inner join view_album_IDs_by_bands
  where musicians.musician_ID = musicians_and_albums.musician_ID
    and musicians_and_albums.album_ID = view_album_IDS_by_bands.album_ID;

Copy

次に、これらのビューを使用して、サンタナとジャーニーの両方のアルバムで演奏したミュージシャンにクエリを実行します。

select musician_id, musician_name from view_musicians_in_bands where band_name = 'Santana'
intersect
select musician_id, musician_name from view_musicians_in_bands where band_name = 'Journey'
order by musician_ID;
+-------------+---------------+
| MUSICIAN_ID | MUSICIAN_NAME |
|-------------+---------------|
|         305 | Gregg Rolie   |
|         306 | Neal Schon    |
+-------------+---------------+

Copy

これらのステートメントは、より詳細で暗黙的なビューを作成します（この例では WITH 句を使用しています）。

with
  album_IDs_by_bands as (select album_ID, music_bands.band_id, band_name
                          from music_albums inner join music_bands
                          where music_albums.band_id = music_bands.band_ID),
  musicians_in_bands as (select distinct musicians.musician_id, musician_name, band_name
                          from musicians inner join musicians_and_albums inner join album_IDs_by_bands
                          where musicians.musician_ID = musicians_and_albums.musician_ID
                            and musicians_and_albums.album_ID = album_IDS_by_bands.album_ID)
select musician_ID, musician_name from musicians_in_bands where band_name = 'Santana'
intersect
select musician_ID, musician_name from musicians_in_bands where band_name = 'Journey'
order by musician_ID
  ;
+-------------+---------------+
| MUSICIAN_ID | MUSICIAN_NAME |
|-------------+---------------|
|         305 | Gregg Rolie   |
|         306 | Neal Schon    |
+-------------+---------------+

Copy

再帰的な例¶

これは、再帰的な CTE を使用してフィボナッチ数列を生成する基本的な例です。

WITH RECURSIVE current_f (current_val, previous_val) AS
    (
    SELECT 0, 1
    UNION ALL 
    SELECT current_val + previous_val, current_val FROM current_f
      WHERE current_val + previous_val < 100
    )
  SELECT current_val FROM current_f ORDER BY current_val;
+-------------+
| CURRENT_VAL |
|-------------|
|           0 |
|           1 |
|           1 |
|           2 |
|           3 |
|           5 |
|           8 |
|          13 |
|          21 |
|          34 |
|          55 |
|          89 |
+-------------+

Copy

この例は、自動車の「部品爆発」を示す再帰的な CTE を使用したクエリです。

-- The components of a car.
CREATE TABLE components (
    description VARCHAR,
    component_ID INTEGER,
    quantity INTEGER,
    parent_component_ID INTEGER
    );

INSERT INTO components (description, quantity, component_ID, parent_component_ID) VALUES
    ('car', 1, 1, 0),
       ('wheel', 4, 11, 1),
          ('tire', 1, 111, 11),
          ('#112 bolt', 5, 112, 11),
          ('brake', 1, 113, 11),
             ('brake pad', 1, 1131, 113),
       ('engine', 1, 12, 1),
          ('piston', 4, 121, 12),
          ('cylinder block', 1, 122, 12),
          ('#112 bolt', 16, 112, 12)   -- Can use same type of bolt in multiple places
    ;

Copy

WITH RECURSIVE current_layer (indent, layer_ID, parent_component_ID, component_id, description, sort_key) AS (
  SELECT 
      '...', 
      1, 
      parent_component_ID, 
      component_id, 
      description, 
      '0001'
    FROM components WHERE component_id = 1
  UNION ALL
  SELECT indent || '...',
      layer_ID + 1,
      components.parent_component_ID,
      components.component_id, 
      components.description,
      sort_key || SUBSTRING('000' || components.component_ID, -4)
    FROM current_layer JOIN components 
      ON (components.parent_component_id = current_layer.component_id)
  )
SELECT
  -- The indentation gives us a sort of "side-ways tree" view, with
  -- sub-components indented under their respective components.
  indent || description AS description, 
  component_id,
  parent_component_ID
  -- The layer_ID and sort_key are useful for debugging, but not
  -- needed in the report.
--  , layer_ID, sort_key
  FROM current_layer
  ORDER BY sort_key;
+-------------------------+--------------+---------------------+
| DESCRIPTION             | COMPONENT_ID | PARENT_COMPONENT_ID |
|-------------------------+--------------+---------------------|
| ...car                  |            1 |                   0 |
| ......wheel             |           11 |                   1 |
| .........tire           |          111 |                  11 |
| .........#112 bolt      |          112 |                  11 |
| .........brake          |          113 |                  11 |
| ............brake pad   |         1131 |                 113 |
| ......engine            |           12 |                   1 |
| .........#112 bolt      |          112 |                  12 |
| .........piston         |          121 |                  12 |
| .........cylinder block |          122 |                  12 |
+-------------------------+--------------+---------------------+

Copy

その他の例については、 CTEs（共通テーブル式）の使用をご参照ください。