MaxCompute:共通テーブル式 (CTE)

構文

WITH
  <cte_name> [(col_name [, col_name] ...)] AS (
    <cte_query>
  )
  [, <cte_name> [(col_name [, col_name] ...)] AS (
    <cte_query2>
  )
  , ...]

パラメーター

例

次のクエリでは、UNION ALL を使用して 2 つの JOIN 操作を結合します。両方の JOIN は同じ左側のサブクエリを共有しており、CTE を使用しない場合は、これを複製する必要があります。

INSERT OVERWRITE TABLE srcp PARTITION (p='abc')
SELECT * FROM (
    SELECT a.key, b.value
    FROM (
        SELECT * FROM src WHERE key IS NOT NULL) a
    JOIN (
        SELECT * FROM src2 WHERE value > 0) b
    ON a.key = b.key
) c
UNION ALL
SELECT * FROM (
    SELECT a.key, b.value
    FROM (
        SELECT * FROM src WHERE key IS NOT NULL) a
    LEFT OUTER JOIN (
        SELECT * FROM src3 WHERE value > 0) b
    ON a.key = b.key AND b.key IS NOT NULL
) d;

CTE で書き換えることで、重複が排除されます。サブクエリ a は一度定義され、両方の join で再利用されます：

WITH
  a AS (SELECT * FROM src WHERE key IS NOT NULL),
  b AS (SELECT * FROM src2 WHERE value > 0),
  c AS (SELECT * FROM src3 WHERE value > 0),
  d AS (SELECT a.key, b.value FROM a JOIN b ON a.key = b.key),
  e AS (SELECT a.key, c.value FROM a LEFT OUTER JOIN c ON a.key = c.key AND c.key IS NOT NULL)
INSERT OVERWRITE TABLE srcp PARTITION (p='abc')
SELECT * FROM d UNION ALL SELECT * FROM e;

構文

WITH RECURSIVE <cte_name> [(col_name [, col_name] ...)] AS (
  <initial_part> UNION ALL <recursive_part>
)
SELECT ... FROM ...;

パラメーター

制限事項

• 再帰 CTE は、IN、EXISTS、またはスカラサブクエリ内では使用できません。

• デフォルトの最大反復回数は 10 です。odps.sql.rcte.max.iterate.num を設定して上限を増やします (最大値: 100)。

• 中間結果はイテレーション間で保存されません。タスクが失敗した場合、実行は最初から再開されます。長時間実行される再帰計算の場合は、イテレーション数を制限するか、中間結果を一時テーブルに保存してください。

• 再帰 CTE は、Query Acceleration (MaxQA/MCQA) モードではサポートされていません。

  • MCQA モードでは、interactive_auto_rerun=true が設定されている場合、タスクは通常モードにフォールバックします。それ以外の場合、タスクは失敗します。

  • MaxQA モードでは、自動フォールバックはサポートされていません。タスクは直接失敗するため、手動でバッチ処理クォータグループに再サブミットして再試行する必要があります。

例

• 例1：cte_name という名前の再帰 CTE を定義

  -- 方法1：出力列名を明示的に指定
  WITH RECURSIVE cte_name(a, b) AS (
    SELECT 1L, 1L                                   -- initial_part：イテレーション0
    UNION ALL
    SELECT a+1, b+1 FROM cte_name WHERE a+1 <= 5    -- recursive_part：前のイテレーションを参照
  )
  SELECT * FROM cte_name ORDER BY a LIMIT 100;
  
  -- 方法2：initial_part から列名を推測
  WITH RECURSIVE cte_name AS (
    SELECT 1L AS a, 1L AS b
    UNION ALL
    SELECT a+1, b+1 FROM cte_name WHERE a+1 <= 5
  )
  SELECT * FROM cte_name ORDER BY a LIMIT 100;

  説明

  • recursive_part では、無限ループを回避するために終了条件を設定します。この例では、WHERE a + 1 <= 5 が終了条件となります。WHERE 条件が満たされない場合、現在のイテレーションで生成されたデータセットは空になり、イテレーションは停止します。

  • 出力列名が明示的に指定されていない場合、システムは自動推論をサポートします。たとえば、方法 2 では、initial_part の出力列名が再帰 CTE の出力列名として使用されます。

  結果：

  +------------+------------+
  | a          | b          |
  +------------+------------+
  | 1          | 1          |
  | 2          | 2          |
  | 3          | 3          |
  | 4          | 4          |
  | 5          | 5          |
  +------------+------------+

• 例2：サブクエリ内の再帰 CTE (コンパイルエラー)

  再帰 CTE は、IN、EXISTS、またはスカラサブクエリ内では許可されていません。次のクエリはコンパイルに失敗します。

  WITH RECURSIVE cte_name(a, b) AS (
    SELECT 1L, 1L
    UNION ALL
    SELECT a+1, b+1 FROM cte_name WHERE a+1 <= 5)
  SELECT x, x IN (SELECT a FROM cte_name) FROM VALUES (1L), (2L) AS t(x);

  エラー：

  FAILED: ODPS-0130071:[5,31] Semantic analysis exception - using Recursive-CTE cte_name in scalar/in/exists sub-query is not allowed, please check your query, the query text location is from [line 5, column 13] to [line 5, column 40]

• 例3：組織階層の走査

  employees テーブルを作成し、データを挿入します:

  CREATE TABLE employees(name STRING, boss_name STRING);
  INSERT INTO TABLE employees VALUES
    ('zhang_3', null),
    ('li_4',    'zhang_3'),
    ('wang_5',  'zhang_3'),
    ('zhao_6',  'li_4'),
    ('qian_7',  'wang_5');

  従業員の名前、マネージャーの名前、階層レベルの 3 つの出力列を持つ、company_hierarchy という名前の再帰 CTE を定義します:

  WITH RECURSIVE company_hierarchy(name, boss_name, level) AS (
      SELECT name, boss_name, 0L FROM employees WHERE boss_name IS NULL
      UNION ALL
      SELECT e.name, e.boss_name, h.level + 1
        FROM employees e
        JOIN company_hierarchy h ON e.boss_name = h.name
  )
  SELECT * FROM company_hierarchy ORDER BY level, boss_name, name LIMIT 1000;

  実行は次のように進みます。

  • イテレーション 0 (initial_part): boss_name IS NULL の従業員を選択し、 level = 0 を割り当てます。結果: ('zhang_3', NULL, 0)。

  • イテレーション 1 (recursive_part) では、employees を作業テーブル (イテレーション 0) と結合します。条件 e.boss_name = h.name は zhang_3 が管理する従業員を検索し、結果として li_4 と wang_5 が level = 1 の従業員として返されます。

  • イテレーション 2: li_4 または wang_5 が管理する従業員を検索します。結果: zhao_6 と qian_7 (level = 2)。

  • イテレーション 3: マネージャーが zhao_6 または qian_7 である従業員はいません。作業テーブルは空になり、イテレーションは停止します。

  結果：

  +---------+-----------+------------+
  | name    | boss_name | level      |
  +---------+-----------+------------+
  | zhang_3 | NULL      | 0          |
  | li_4    | zhang_3   | 1          |
  | wang_5  | zhang_3   | 1          |
  | zhao_6  | li_4      | 2          |
  | qian_7  | wang_5    | 2          |
  +---------+-----------+------------+

• 例4：循環データによる無限ループ

  例 3 の employees テーブルに、従業員が自身の上司であるレコードを挿入します。

  INSERT INTO TABLE employees VALUES('qian_7', 'qian_7');

  このレコードは、qian_7 のマネージャーが qian_7 自身であることを宣言しています。前述の再帰 CTE を実行すると無限ループが発生します。システムには最大反復回数の制限があるため、クエリは最終的に失敗します。

  エラー：

  FAILED: ODPS-0010000:System internal error - recursive-cte: company_hierarchy exceed max iterate number 10

使用例

非再帰 CTE のトップレベルの SELECT に /*+ MATERIALIZE */ ヒントを追加すると、その結果が一時テーブルにキャッシュされます。これにより、後続の参照ではクエリが再実行されるのではなく、キャッシュから読み取りが行われます。

WITH v1 AS (SELECT /*+ MATERIALIZE */ SIN(1.0) AS a)
SELECT a FROM v1 UNION ALL SELECT a FROM v1;

-- Result:
+------------+
| a          |
+------------+
| 0.8414709848078965 |
| 0.8414709848078965 |
+------------+

ヒントが有効な場合、LogView の [Job Details] タブに複数の Fuxi ジョブが表示され、中間結果が保存されたことを確認できます。

制限事項

• MATERIALIZE ヒントは、非再帰 CTE の最上位の SELECT 文に適用する必要があります。再帰 CTE には MATERIALIZE ヒントは不要です。

  • 誤った例：次の CTE では、最上位の文は SELECT ではなく UNION であるため、MATERIALIZE ヒントは有効になりません。

    WITH v1 AS (
      SELECT /*+ MATERIALIZE */ SIN(1.0) AS a
      UNION ALL
      SELECT /*+ MATERIALIZE */ SIN(1.0) AS a)
    SELECT a FROM v1 UNION ALL SELECT a FROM v1;

  • 正しい例：上記の誤った例をサブクエリでラップして書き直します。

    WITH 
      v1 AS (SELECT /*+ MATERIALIZE */ * FROM 
               (SELECT SIN(1.0) AS a
                UNION ALL 
                SELECT SIN(1.0) AS a)
            ) 
    SELECT a FROM v1 UNION ALL SELECT a FROM v1;

• CTE が RAND や非決定性の Java/Python UDF などの非決定性関数を使用する場合、CTE をマテリアライズすると関数の評価が 1 回だけキャッシュされ、後続の参照ではキャッシュされた値が返されるため、呼び出しごとに独立したランダムな値を期待するクエリのセマンティックな動作が変更されます。

• マテリアライズド CTE は MCQA (クエリ高速化 1.0) - 非推奨ではサポートされていません。タスクが interactive_auto_rerun=true で MCQA モードで実行される場合、通常モードにフォールバックします。それ以外の場合、タスクは失敗します。