一般テーブル式

一般テーブル式（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）を定義できますが、各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では、固定レベルの学生を1人だけ選択することになります。