【ITニュース解説】Understanding Subqueries and CTE's

システムエンジニアとして、データ分析の課題に取り組む際、基本的なSQLコマンドだけでは解決が難しい複雑な問題に直面することがある。例えば、会社全体の平均給与よりも高い給与を得ている従業員を特定したい場合、事前に平均給与が分からなければ、一度のクエリでは答えを導き出せない。まず平均給与を計算し、その結果を使って各従業員の給与と比較する必要がある。このような多段階の計算や条件設定が必要な場合に、SQLサブクエリとCommon Table Expressions（CTE）が非常に役立つ。これらは複雑な分析問題をより小さく、管理しやすいクエリに分解し、中間計算を行ったり、他のクエリの結果に基づいてレコードをフィルタリングしたり、SQLコードをより効果的に整理したりすることを可能にする。

サブクエリは「入れ子クエリ」や「内部クエリ」とも呼ばれ、別のSQLクエリの内部に記述されるSQLクエリである。内部クエリが生成する結果を、外部クエリが次の操作に使用する。これは、一つの質問に答え、その答えを同じSQL文内で別の質問を解決するために利用するようなものだ。サブクエリの基本的な構文は、SELECT column_name FROM table_name WHERE column_name operator (SELECT column_name FROM table_name); のように、括弧の中に別のSELECT文が含まれる形である。括弧内のクエリが内部クエリ、それを囲むクエリが外部クエリと呼ばれる。

具体的な例として、従業員テーブルから会社全体の平均給与を超える従業員を見つけるケースを考える。このテーブルには、従業員ID、氏名、部署、給与の情報が含まれるとする。まず、SELECT AVG(salary) AS average_salary FROM employees; というクエリで会社全体の平均給与が計算される。この結果を直接別のクエリに埋め込む代わりに、サブクエリとして利用できる。SELECT employee_name, department, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees); と記述することで、内部クエリが平均給与を計算し、その結果を外部クエリのWHERE句で使用し、平均給与よりも高い給与の従業員を抽出する。この方法は、データが変動しても常に最新の平均値に基づいて結果を生成するため、非常に実用的である。

サブクエリにはいくつかの種類がある。

1. スカラーサブクエリ: 単一の値を返すサブクエリで、先の平均給与の例がこれに該当する。最大価格や合計収益など、単一の集計値を返す場合に利用される。
2. 複数行サブクエリ: 複数の行を返すサブクエリで、IN、ANY、ALLといった演算子と組み合わせて使用されることが多い。例えば、特定の地域の部署に所属する従業員を特定する際に、内部クエリがその地域の部署名のリストを返し、外部クエリがそのリストに含まれる部署の従業員を選択する、といった使い方をする。
3. 相関サブクエリ: 外部クエリの列を参照するサブクエリである。これは独立したサブクエリとは異なり、外部クエリが供給する値に依存するため、外部クエリの各行に対してサブクエリが繰り返し実行される。各部門の平均給与より高い従業員を特定する例がこれに当たる。SELECT e.employee_name, e.department, e.salary FROM employees AS e WHERE e.salary > (SELECT AVG(e2.salary) FROM employees AS e2 WHERE e2.department = e.department); のように、内部クエリが外部クエリの現在の行の部署と一致する従業員の平均給与を計算し、それと比較する。これにより、会社全体ではなく、各部門内での比較が可能となる。

Common Table Expression（CTE）は、SQL文内でWITHキーワードを使って定義される一時的な名前付きの結果セットである。CTEは、クエリの結果に意味のある名前を付け、その名前をメインのSQL文で参照することを可能にする。複雑なクエリを、より小さく論理的なステップに分割して整理する一時的な作業テーブルとして考えることができる。CTEは恒久的なデータベースオブジェクトではなく、定義されたSQL文の実行中にのみ有効である。

CTEの基本的な構造は次のようになる。 WITH cte_name AS (SELECT column_name FROM table_name WHERE condition) SELECT * FROM cte_name; WITHキーワードでCTEの定義を開始し、cte_nameは括弧内のクエリが生成する結果に与える名前である。メインクエリはこのcte_nameを使ってCTEの結果を参照する。

平均給与を超える従業員を見つける問題をCTEで解決することもできる。 WITH average_salary AS (SELECT AVG(salary) AS avg_salary FROM employees) SELECT e.employee_name, e.department, e.salary FROM employees AS e CROSS JOIN average_salary AS a WHERE e.salary > a.avg_salary; ここでは、average_salaryというCTEが平均給与を計算し、その結果にavg_salaryという名前を付ける。メインクエリはこのaverage_salaryCTEとemployeesテーブルをCROSS JOINし、従業員の給与とCTEで計算された平均給与を比較する。サブクエリが平均計算をWHERE条件内に直接配置するのに対し、CTEは最初に平均を計算して名前を付け、その結果をメインクエリで参照する形をとる。

CTEは、中間計算を行ってからさらに分析を進める場合に特に有用である。例えば、顧客ごとの合計支出を計算し、その合計支出が特定の金額を超える顧客を特定するような場合だ。 WITH customer_spending AS (SELECT customer_id, SUM(amount) AS total_spent FROM sales GROUP BY customer_id) SELECT customer_id, total_spent FROM customer_spending WHERE total_spent > 30000; このCTEは、まずcustomer_spendingとして顧客ごとの合計支出を計算し、その結果をメインクエリでフィルタリングして、合計支出が30,000を超える顧客を特定する。このように、CTEは分析のステップを明確にし、結果に意味のある名前を付けることで、コードの可読性を高める。

複数のCTEを同じSQL文内で定義することも可能である。これらはカンマで区切って記述し、後続のCTEが先行するCTEの結果を参照することもできる。これにより、複数段階の複雑な分析を、一連の論理的なステップとして表現できる。例えば、「顧客ごとの合計支出の計算」「顧客の平均支出の計算」「平均支出を超える顧客の特定」といった複数のステップを、それぞれのCTEで表現し、最終的にメインクエリでこれらを結合して結果を出す、といったことが可能である。

サブクエリとCTEはどちらも中間結果を生成できるが、そのクエリの組織化方法に違いがある。サブクエリは単純な計算や一度しか必要ない中間結果に適している一方で、CTEは複雑なロジックをより読みやすく、管理しやすいステップに分割するのに優れている。特に、同じ中間結果を複数回参照する必要がある場合や、深く入れ子になったサブクエリが読みにくくなった場合にCTEは大きな利点を発揮する。また、階層データや再帰クエリを扱う際にもCTEが活用される。

性能については、CTEが常にサブクエリよりも高速であるとは限らない。最新のデータベースエンジンにはクエリオプティマイザがあり、SQL文がどのように実行されるかを決定するため、実際の性能はデータベースシステム、インデックス、テーブルサイズ、結合条件など多くの要因に依存する。したがって、CTEは主にコードの明瞭性と組織化のために選択すべきであり、性能向上が保証されるわけではないことに留意する必要がある。

サブクエリは、中間計算が比較的単純で一度だけ必要な場合に特に有効である。例えば、商品の平均価格より高い商品を抽出するような単純なフィルタリングである。これに対し、CTEは、クエリが複数の論理ステップを含み、同じ中間結果を複数回参照する必要がある場合、入れ子になったサブクエリが理解しにくい場合、中間計算に意味のある名前を付けたい場合、あるいは階層データや再帰的なデータ処理を行う場合に特に役立つ。customer_spendingやmonthly_revenueのような具体的な名前をCTEに与えることで、クエリの各部分が何を表しているのかが直感的に理解できるようになる。

サブクエリやCTEを使用する際の一般的な間違いとして、単一の値を返すことが期待される場所で複数の値を返してしまうケースがある。例えば、=演算子で単一値が期待されるのに、サブクエリが複数行を返すとエラーになる。この場合、IN演算子など、複数行を受け入れられる演算子を使用する必要がある。また、サブクエリの入れ子が深くなりすぎると、コードの可読性が著しく低下するため、そのような場合はCTEへの再構築を検討すべきである。CTEは一時的なオブジェクトであり、定義されたSQL文の実行が終了すると消滅することも忘れてはならない。別のSQL文で再利用するには、再度定義する必要がある。そして、CTEが常に性能を向上させると仮定しないことが重要である。

結局のところ、サブクエリとCTEはどちらも、「一つのクエリの結果を、より大きな分析の一部としてどのように利用するか」という根本的な問題を解決するためのツールである。サブクエリはロジックを「入れ子」にし、CTEはロジックに「名前を付けて整理」する。どちらのアプローチが優れているという普遍的な答えはなく、解決する問題の複雑さや、中間結果の利用方法に応じて適切な方を選択するスキルが求められる。これらを習得することで、システムエンジニアはより高度なSQL技術、例えばウィンドウ関数や再帰CTE、ランキング、多段階の分析クエリなどを使いこなすための基礎を築くことができる。