コモンテーブル式

コモンテーブル式（Common Table Expressions、CTE）とは、命名された一時的な結果セットであり、その適用範囲は現在のステートメントに限られます。実際にはオブジェクトとして保存されることはなく、クエリ実行時にのみ使用されます。派生テーブルとは異なり、CTEは自己参照可能であり、同一クエリ内で複数回参照することもできます。

利用シナリオ：

• CTEを使用することで、同じサブクエリを複数箇所で再利用でき、同じロジックを繰り返し記述する手間を省くことができます。
• 例えば、ツリー構造データを照会するような再帰クエリを簡略化するために使用できます。
• 複雑なクエリを複数の小さな部分に分割することで、クエリのロジックをより明確かつ分かりやすくすることができます。

OceanBaseデータベースは、非再帰CTEと再帰CTEの両方をサポートしています。

CTE構文

汎用テーブル式は、DMLステートメント構文のオプション部分であり、WITH 句を使用して定義します。WITH 句に複数の句が含まれる場合は、カンマで区切ることができます。各句は、結果セットを生成するためのサブクエリを提供し、サブクエリを名前に関連付けます。構文は以下のとおりです：

 WITH [RECURSIVE]
      cte_name [(column_name [, column_name] ...)] AS (subquery)
      [, cte_name [(column_name [, column_name] ...)] AS (subquery)] ...

パラメータの説明

CTE名の後に括弧内の名前リストがある場合、これらの名前は列名であり、その数はCTE内のSELECTステートメントの列数と同じでなければなりません。列名が指定されていない場合、列名はAS(subquery)部分の最初のSELECTリストから取得されます。

WITH句の適用シナリオ

次のシナリオでは、WITH 句を使用できます。

• SELECT ステートメントの先頭に記述します。

  WITH ... SELECT ...
  

• サブクエリ（派生テーブルサブクエリを含む）の先頭に記述します。

  SELECT ... WHERE id IN (WITH ... SELECT ...) ...
  SELECT * FROM (WITH ... SELECT ...) AS dt ...
  

• SELECT ステートメントを含むステートメントでは、SELECT の直前に記述します。

  INSERT ... WITH ... SELECT ...
  REPLACE ... WITH ... SELECT ...
  CREATE TABLE ... WITH ... SELECT ...
  CREATE VIEW ... WITH ... SELECT ...
  

同一レベルでは、WITH 句は1つのみ許可されます。WITH 句内に複数の句が含まれる場合は、カンマで区切ることができます。

WITH cte1 AS (...), cte2 AS (...) SELECT ...

WITH句は、1つまたは複数の汎用テーブル式を定義できますが、各CTE名はその句に対して一意でなければなりません。次の例は無効です。

WITH cte1 AS (...), cte1 AS (...) SELECT ...

再帰CTEの構造

再帰CTEは、以下の構造を持ちます：

• WITH 句内のCTEが自身を参照する場合、WITH 句は WITH RECURSIVE で始める必要があります。それ以外の場合、RECURSIVE を付ける必要はありません。

• 再帰CTEサブクエリには2つの部分があり、UNION [ALL | DISTINCT] で区切られています：

  説明

  • UNION または UNION DISTINCT：2つ以上の SELECT ステートメントの結果セットを1つのセットに統合し、重複する行を削除します。
  • UNION ALL：2つ以上の SELECT ステートメントの結果セットを1つのセットに統合し、重複する行を削除しません。

  SELECT ...      -- 初期行セットを返す
  UNION ALL
  SELECT ...      -- 追加の行セットを返す
  

  最初の SELECT はCTEに対して1つ以上の初期行を生成し、CTE名を参照しません。2番目の SELECT は追加の行を生成し、その FROM 句でCTE名を参照して再帰します。この部分が新しい行を生成しなくなった場合、再帰は終了します。したがって、再帰CTEは非再帰 SELECT 部分と再帰 SELECT 部分で構成されます。各 SELECT 部分は、複数の SELECT ステートメントの結合である可能性があります。

• CTEの結果列の型は、非再帰 SELECT 部分の列型からのみ推測され、すべての列はNULLにすることができます。型の決定では、再帰 SELECT 部分は無視されます。

• 再帰部分の各反復は、前回の反復で生成された行に対してのみ操作を行います。再帰部分に複数のクエリブロックがある場合、各クエリブロックの反復は指定されていない順序でスケジュールされ、各クエリブロックが操作する行は、前回の反復または前回の反復終了後に他のクエリブロックによって生成された行です。

例：

WITH RECURSIVE cte1 (n) AS
(
  SELECT 1                /*非再帰部分。単一の行を検索して初期行セットを生成します*/
  UNION ALL
  SELECT n + 2 FROM cte1 WHERE n < 10     /*再帰部分。前の行セットのn値よりも2大きい新しい値を生成し、nが10以上になるまで続けます*/
)
SELECT * FROM cte1;

使用制限

再帰CTEサブクエリには、以下の構文制約が適用されます：

• 再帰 SELECT 部分には、次の構造を含めることはできません：

  • 集計関数、例えば SUM()

  • ウィンドウ関数

  • GROUP BY

  • ORDER BY

  • DISTINCT

• 再帰 SELECT 部分は、その FROM 句内のサブクエリで1回しか参照されてはなりません。CTE以外のテーブルを参照してCTEと結合することができます。このような結合で使用される場合、CTEは LEFT JOIN の右側に配置してはなりません。

再帰CTEについて、EXPLAIN によって表示されるコスト見積もりは各反復のコストを表しており、総コストとは大幅に異なる場合があります。オプティマイザーは、WHERE 句がいつFalseになるか予測できないため、反復回数を予測することができません。

• 再帰 SELECT と非再帰 SELECT の間では、LIMIT 構造を含むことはサポートされていません。

例

以下は、学生のID、氏名、およびその指導教員のIDを含む学生ファイルテーブルを作成し、再帰CTEと非再帰CTEの違いを示す簡単な例です。

まず、学生ファイルテーブルを作成していくつかのデータを挿入します：

obclient> CREATE TABLE student (
    student_id INT PRIMARY KEY,
    name VARCHAR(100),
    mentor_id INT,
    FOREIGN KEY (mentor_id) REFERENCES student(student_id)
);
Query OK, 0 rows affected

obclient> INSERT INTO student (student_id, name, mentor_id) VALUES
(1, 'Alice', NULL),
(2, 'Bob', 1),
(3, 'Charlie', 1),
(4, 'David', 2),
(5, 'Eve', 3);
Query OK, 5 rows affected

このデータモデルでは、Aliceはトップレベルの学生であり、指導教員はいません。BobとCharlieはAliceの学生であり、DavidはBobの学生であり、EveはCharlieの学生です。

非再帰CTEの例

非再帰CTEは自身を参照しません。たとえば、Aliceのすべての直接の学生（つまり第一レベルの学生）を選択したい場合、次のような非再帰CTEを使用できます。

WITH Alice_Students AS (
    SELECT * FROM student WHERE mentor_id = 1
)
SELECT * FROM Alice_Students;

実行結果は次のとおりです。

+------------+---------+-----------+
| student_id | name    | mentor_id |
+------------+---------+-----------+
|          2 | Bob     |         1 |
|          3 | Charlie |         1 |
+------------+---------+-----------+
2 rows in set

再帰CTEの例

今回は、Aliceのすべての直接および間接の学生（つまりすべての学生レベル）を見つける必要があるため、再帰CTEを使用する必要があります。再帰CTEは自身を参照して、段階的に下方向へクエリを実行します。

WITH RECURSIVE Student_Hierarchy AS (
    -- アンカー要素：Aliceを開始点として選択
    SELECT student_id, name, mentor_id FROM student WHERE mentor_id IS NULL
    UNION ALL
    -- 再帰要素：前のレベルの学生のIDに基づいて直接の学生を選択
    SELECT s.student_id, s.name, s.mentor_id
    FROM student s
    INNER JOIN Student_Hierarchy sh ON s.mentor_id = sh.student_id
)
SELECT * FROM Student_Hierarchy;

この再帰CTEは、最初にAliceを開始点として選択し、それから再帰的に各学生の直接の学生を選択し、もはや学生がいないまで続けます。

実行結果は次のとおりです。

+------------+---------+-----------+
| student_id | name    | mentor_id |
+------------+---------+-----------+
|          1 | Alice   |      NULL |
|          3 | Charlie |         1 |
|          2 | Bob     |         1 |
|          5 | Eve     |         3 |
|          4 | David   |         2 |
+------------+---------+-----------+
5 rows in set

この例では、再帰CTEにより学生の階層構造をイテレーションできますが、非再帰CTEは固定されたレベルの学生のみを選択します。