SQLクックブック 第2版 ―データベースエキスパート、データサイエンティストのための実践レシピ集

付録B共通テーブル式

本書に示したクエリの多くは、特に集約関数やウィンドウ関数に関してデータベースで通常利用できるようなテーブルを使ってできることを超えた処理を行っています。そのため、一部のクエリでは派生テーブル（サブクエリや共通テーブル式、CTE：Common Table Expression）を作成する必要があります。

B.1　サブクエリ

ウィンドウ関数や集約関数に対してクエリを実行できる仮想テーブルを作成する最も簡単な方法は、おそらくサブクエリでしょう。ここで必要なのは、必要なクエリを丸括弧内に書き、そのクエリを使う2つ目のクエリを書くことだけです。以下のテーブルは、簡単な「二重集約」のサブクエリを使う例を示しています。職種ごとの従業員数だけでなく最高値を求めたいけれども、標準的なクエリでは集約関数を直接ネストすることはできません。

1つの落とし穴は、サブクエリテーブルやエイリアスが必要なベンダもあれば、必要のないベンダもあることです。次に挙げる例は、エイリアスを必要とするMySQLで書かれています。ここでのエイリアスは、閉じ括弧の後のHEAD_COUNT_TABです。

他にPostgreSQLとSQL Serverはエイリアスが必要ですが、Oracleは必要ありません。

select max(HeadCount) as HighestJobHeadCount from
(select job,count(empno) as HeadCount
from emp
group by job) head_count_tab

B.2　共通テーブル式

CTEはサブクエリの制約の一部を克服することを目的としています。SQL内で再帰クエリを使えることで最もよく知られているでしょう。実際に、SQLで再帰を可能にすることが、CTEの主なきっかけでした。 ...