ラテラル派生テーブル

本記事では、ラテラル派生テーブル（Lateral Derived Table）の基本概念、構文、および関連する例を紹介します。

ラテラル派生テーブルの紹介

派生テーブル（Derived Table）は、FROM 句で使用されるサブクエリであり、このサブクエリによって生成された結果は、一時的に外部クエリでテーブルとして使用されます。派生テーブルは通常、データをグループ化および集約するため、または特定の条件を満たすデータセットを作成するために使用され、主クエリでさらに使用されます。

ラテラル派生テーブル（Lateral Derived Table）は、特殊なタイプの派生テーブルであり、LATERAL キーワードを使用することで、同じ FROM 句内で先に定義された他のテーブルや派生テーブルの列を参照できるようにします。これにより、派生テーブル内のサブクエリは同じ FROM 句で定義されたテーブルに依存できるようになり、これらのテーブルの列値にアクセスできます。

ラテラル派生テーブルの主な特徴は、それらが FROM 句で定義されたテーブルの列を参照できることです。通常の派生テーブルはこの機能がありません。

LATERALキーワードの使用方法

SELECT select_list
FROM table_name1, 
LATERAL (SELECT select_list 
         FROM table_name2 
         WHERE table_name2.col_name = table_name1.col_name) AS lateral_derived_table_name
[...];

パラメータの説明：

例

テストテーブルの作成とテストデータの追加

1. テーブル students を作成します。

   CREATE TABLE students (
     id NUMBER PRIMARY KEY,
     name VARCHAR2(50) NOT NULL,
     age NUMBER
   );
   

2. students テーブルに3件のデータを挿入します。

   INSERT INTO students 
     VALUES (1, 'name1', 20),
     (2, 'name2', 22),
     (3, 'name3', 21);
   

3. テーブル scores を作成します。

   CREATE TABLE scores (
     id NUMBER PRIMARY KEY,
     student_id NUMBER,
     subject VARCHAR2(50) NOT NULL,
     score DECIMAL(5, 2),
     FOREIGN KEY (student_id) REFERENCES students(id)
   );
   

4. テーブル scores に9件のデータを挿入します。

   INSERT INTO scores
     VALUES (1, 1, 'A', 86.5),
     (2, 1, 'B', 90.0),
     (3, 1, 'C', 91.5),
     (4, 2, 'A', 86.0),
     (5, 2, 'B', 92.0),
     (6, 2, 'C', 89.5),
     (7, 3, 'A', 93.0),
     (8, 3, 'B', 92.5),
     (9, 3, 'C', 85.0);
   

テストデータのクエリ

students と scores の2つのテーブルから、各学生の名前、平均スコア、および最高スコアを取得します。

Q1：複数のサブクエリを使用して必要なデータを取得します。scores テーブルに対して2回の独立したグループ集約操作を実行し、scores テーブルを2回スキャンして平均スコアと最高スコアをそれぞれ計算します。その後、WHERE 句を使用して結果を students テーブルと結合します。

SELECT st.name, sc.avg_score, scs.max_score
FROM students st, 
  (SELECT student_id, AVG(score) avg_score 
   FROM scores
   GROUP BY student_id) sc,
   (SELECT student_id, MAX(score) max_score 
   FROM scores
   GROUP BY student_id) scs
WHERE sc.student_id = st.id
AND scs.student_id = st.id;

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

+-------+-------------------------------------------+-----------+
| NAME  | AVG_SCORE                                 | MAX_SCORE |
+-------+-------------------------------------------+-----------+
| name1 | 89.33333333333333333333333333333333333333 |      91.5 |
| name2 | 89.16666666666666666666666666666666666667 |        92 |
| name3 | 90.16666666666666666666666666666666666667 |        93 |
+-------+-------------------------------------------+-----------+
3 rows in set

Q2：LATERAL キーワードを使用して、必要なデータを取得します。LATERAL キーワード（ラテラル派生テーブル）を使用して、1行のSQLステートメントで各学生の平均点と最高点を同時に計算し、各学生の ID に対して scores テーブルを1回だけスキャンします。

SELECT st.name, ld_tbl.avg_score, ld_tbl.max_score
FROM students st, 
  LATERAL (SELECT AVG(score) avg_score, MAX(score) max_score 
           FROM scores sc 
           WHERE sc.student_id = st.id) ld_tbl;

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

+-------+-------------------------------------------+-----------+
| NAME  | AVG_SCORE                                 | MAX_SCORE |
+-------+-------------------------------------------+-----------+
| name1 | 89.33333333333333333333333333333333333333 |      91.5 |
| name2 | 89.16666666666666666666666666666666666667 |        92 |
| name3 | 90.16666666666666666666666666666666666667 |        93 |
+-------+-------------------------------------------+-----------+
3 rows in set

Q2は、Q1と比較して scores テーブルへの重複スキャンを避けるため、大規模データセット時により高いパフォーマンスを示す可能性があります。Q2の書き方はより明確で簡潔であり、理解しやすく、保守も容易です。

関連ドキュメント

• SELECT

• クエリでの集約関数の使用