MaxCompute:Common Table Expression (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 は 1 回定義され、両方の 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;

-- 結果：
+------------+
| a          |
+------------+
| 0.8414709848078965 |
| 0.8414709848078965 |
+------------+

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

制限事項

• 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 は Query Acceleration (MCQA) ではサポートされていません。タスクが interactive_auto_rerun=true を設定した MCQA モードで実行される場合、通常モードにフォールバックします。それ以外の場合、タスクは失敗します。