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

機能紹介

• CTE は、単一の DML 文の実行範囲内で定義された一時的な結果セットです。派生テーブルに似ていますが、オブジェクトとして保存されず、クエリの期間だけ保持されます。CTE は SQL 文の可読性を高め、複雑なクエリのメンテナンスを簡素化します。

• CTE は文レベルの句であり、WITH で始まり、その後に式名が続きます。次の 2 つのタイプがあります。

  • 非再帰 CTE: このタイプには、再帰や反復的な自己アクセスは含まれません。

  • 再帰 CTE: このタイプでは、自己参照と反復アクセスが可能になり、SQL での再帰クエリが可能になります。多くの場合、階層データの走査に使用されます。

• マテリアライズド CTE のサポート: CTE を定義する場合、SELECT 文で materialize ヒントを使用して、CTE の計算結果を一時テーブルにキャッシュできます。これにより、CTE への後続のアクセスでキャッシュから直接読み取ることができ、複雑な CTE ネストシナリオでのメモリ超過の問題を防ぎ、CTE 文のパフォーマンスを向上させることができます。

非再帰 CTE

コマンド形式

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

パラメータの説明

使用例

次のコードについて考えてみます。

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;

両側の最上位レベルの UNION は JOIN であり、JOIN の左側のテーブルは同じクエリ文です。CTE がないと、このコードを繰り返す必要があります。

以下のコマンド例に示すように、共通テーブル式 (CTE) を使用して文を書き直します。

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;

a に関連付けられたサブクエリは 1 回だけ定義すればよく、その後再利用できます。CTE の WITH 句内では複数のサブクエリを指定でき、文全体で再利用できる変数のように機能するため、反復的なネストが不要になります。

再帰 CTE

コマンド形式

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

パラメータの説明

制限事項

• 再帰 CTE は、IN/EXISTS/SCALAR サブクエリでは使用できません。

• 再帰 CTE のデフォルトの再帰制限は 10 回の反復です。odps.sql.rcte.max.iterate.num を設定することで調整できます。上限は 100 です。

• 再帰 CTE は、反復計算中の 中間結果の記録をサポートしていません。障害が発生した場合、計算は最初から再開されます。再帰回数を制御し、長い計算には一時テーブルの使用を検討することをお勧めします。

• 再帰 CTE は、クエリ高速化 (MCQA) ではサポートされていません。MCQA モードで使用した場合、interactive_auto_rerun=true の設定に応じて、タスクは実行のために一般モードにフォールバックするか、失敗します。

使用例

• 例 1: cte_name という名前の再帰 CTE を定義します。

  -- 方法 1: a と b という名前の 2 つの出力列を持つ cte_name という名前の再帰 CTE を定義する
  WITH RECURSIVE cte_name(a, b) AS (       
    SELECT 1L, 1L                                 -- initial_part: 反復 0 データセット
    UNION ALL                                      -- CTE の initial_part と recursive_part を UNION ALL で接続する
    SELECT a+1, b+1 FROM cte_name WHERE a+1 <= 5) -- recursive_part: cte_name は前の反復の計算結果を指す
  SELECT * FROM cte_name ORDER BY a LIMIT 100;    -- 再帰 CTE 結果を出力する。つまり、すべての反復のすべてのデータを結合する
  
  -- 方法 2: 出力列名を明示的に指定せずに cte_name という名前の再帰 CTE を定義する
  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;    

  説明

  • 再帰部分では、無限ループを防ぐために、反復終了条件を設定します。この例では、WHERE a + 1 <= 5 が終了条件として機能します。WHERE 条件が満たされない場合、反復は空のデータセットを生成し、終了します。

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

  結果は次のとおりです。

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

• 例 2: 再帰 CTE は、IN/EXISTS/SCALAR サブクエリでは使用できません。次のクエリはコンパイルエラーになります。

  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);

  結果は次のとおりです。

  -- エラーが返される。5 行目に in サブクエリがあり、サブクエリで再帰 CTE cte_name が参照されている
  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');

  company_hierarchy という名前の再帰 CTE を定義して、組織構造をマッピングします。再帰 CTE には、従業員の名前、マネージャーの名前、組織構造内のレベルの 3 つの出力フィールドが含まれます。

  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 WHERE e.boss_name = h.name
    )
  SELECT * FROM company_hierarchy ORDER BY level, boss_name, name LIMIT 1000;

  • 2 行目の initial_part は、boss_name が null の employees のレコードに対応し、これらのレコードにレベル 0 を割り当てます。

  • 4 行目の recursive_part は、employees と company_hierarchy の間で JOIN 操作を実行します。JOIN 条件は e.boss_name = h.name であり、前の反復の従業員レコードをマネージャーにリンクします。

  結果は次のとおりです。

  +------+-----------+------------+
  | 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          |
  +------+-----------+------------+

  計算プロセスは次のように展開されます。

  • 反復 0: boss_name IS NULL の employees レコードをフィルタリングして初期データセットを取得し、1 つのレコードを作成します。

    'zhang_3',   null,  0

  • 反復 1: 次のクエリを実行します。company_hierarchy は反復 0 の結果に対応します。

    SELECT e.name, e.boss_name, h.level+1 FROM employees e JOIN company_hierarchy h  WHERE e.boss_name = h.name

    これにより、zhang_3 によって管理される、レベル = 1 の 2 つのレコードが生成されます。

    'li_4',      'zhang_3', 1
    'wang_5',    'zhang_3',  1

  • 反復 2: 反復 1 と同様ですが、company_hierarchy は反復 1 の結果に対応します。これにより、li_4 または wang_5 によって管理される、level = 2 の 2 つのレコードが生成されます。

    'zhao_6',    'li_4'，   2
    'qian_7',    'wang_5'， 2

  • 反復 3: マネージャーが zhao_6 または qian_7 の従業員がいないため、空のセットが返され、再帰計算が終了します。

• 例 4: 例 2 の employees テーブルに追加のレコードを挿入すると、エラーが発生します。

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

  このレコードは、qian_7 を自分のマネージャーとして示しており、無限ループにつながります。システムには反復の上限があるため ( 制限事項 を参照)、クエリは最終的に失敗します。

  結果は次のとおりです。

  -- エラーが返される
  FAILED: ODPS-0010000:System internal error - recursive-cte: company_hierarchy exceed max iterate number 10

マテリアライズ CTE

WITH
  v1 AS (SELECT SIN(1.0) AS a) 
SELECT a FROM v1 UNION ALL SELECT a FROM v1;

+------------+
| a          |
+------------+
| 0.8414709848078965 |
| 0.8414709848078965 |
+------------+

SELECT a FROM (SELECT SIN(1.0) AS a) 
UNION ALL 
SELECT a FROM (SELECT SIN(1.0) AS a);

WITH
  v1 AS (SELECT 1L AS a, 2L AS b, 3L AS c),
  v2 AS (SELECT * FROM v1 UNION ALL SELECT * FROM v1 UNION ALL SELECT * FROM v1),
  v3 AS (SELECT * FROM v2 UNION ALL SELECT * FROM v2 UNION ALL SELECT * FROM v2),
  v4 AS (SELECT * FROM v3 UNION ALL SELECT * FROM v3 UNION ALL SELECT * FROM v3),
  v5 AS (SELECT * FROM v4 UNION ALL SELECT * FROM v4 UNION ALL SELECT * FROM v4),
  v6 AS (SELECT * FROM v5 UNION ALL SELECT * FROM v5 UNION ALL SELECT * FROM v5),
  v7 AS (SELECT * FROM v6 UNION ALL SELECT * FROM v6 UNION ALL SELECT * FROM v6),
  v8 AS (SELECT * FROM v7 UNION ALL SELECT * FROM v7 UNION ALL SELECT * FROM v7),
  v9 AS (SELECT * FROM v8 UNION ALL SELECT * FROM v8 UNION ALL SELECT * FROM v8)
SELECT * FROM v9;

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 |
+------------+