コモンテーブル式

コモンテーブル式（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 Listから取得されます。

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を使用する場合、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は固定レベルの学生のみを選択します。