FROM句のサブクエリ
集計してできた列は、WHERE では使えない
部署ごとの人数を数えて、そのうち 10 人以上の部署だけを出したいとします。素直に書くと、こうなります。
SQL クエリ
SELECT dept, COUNT(*) AS headcount
FROM employees
WHERE headcount >= 10 -- ここで止まる
GROUP BY dept;column "headcount" does not exist というエラーで落ちます。WHERE は 1 行ずつを見て残すか捨てるかを決める段階で、まだグループにまとまっていません。headcount という列は、この時点ではどこにも存在しないのです。
集計した結果を、テーブルとして置き直す
集計が終わった後の状態で絞りたいなら、集計するクエリを丸括弧で囲んで、FROM に置きます。
SQL クエリ
SELECT dept, headcount
FROM (
SELECT dept, COUNT(*) AS headcount
FROM employees
GROUP BY dept
) AS d
WHERE headcount >= 10
ORDER BY headcount DESC;外側から見ると、丸括弧の中身は dept と headcount の 2 列を持つ普通のテーブルです。実在するテーブルと同じように WHERE で絞れますし、ORDER BY で並べ替えもできます。この置き方を派生テーブル (derived table) と呼びます。
読み方のコツは、内側から外へ順に見ることです。「まず部署ごとに数える」「できた表から 10 人以上を残す」という 2 段階が、そのまま入れ子の形になっています。
同じ絞り込みは HAVING でも書けます。1 段で終わるなら HAVING のほうが短く、集計した結果をさらに別の計算に使ったり、順位を付けてから絞ったりするなら FROM に置くほうが手順が見えます。
別名を付け忘れると、そこで止まる
FROM に置いたサブクエリには、必ず別名が要ります。
SQL クエリ
-- 落ちる subquery in FROM must have an alias
SELECT dept FROM (SELECT dept FROM employees GROUP BY dept);
-- 通る
SELECT dept FROM (SELECT dept FROM employees GROUP BY dept) AS d;外側から「どの表の列か」を指せないと困るので、名前が必須になっています。慣れないうちは付け忘れが必ず起きますが、エラーメッセージがそのまま alias と言ってくれるので、見たらすぐ気付けます。
もう 1 つ、内側で付けた別名 (COUNT(*) AS headcount の headcount) を使えるのは外側だけです。同じ丸括弧の中でその名前を参照しようとしても、先ほどと同じ理由で見つかりません。
テーブル構造
CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(40),
category VARCHAR(30),
price INT
);
INSERT INTO products VALUES
(1, 'シャープペン', '文具', 200),
(2, 'ノート', '文具', 350),
(3, '万年筆', '文具', 5000),
(4, 'マグカップ', '雑貨', 1200),
(5, 'タンブラー', '雑貨', 2500),
(6, 'ワイヤレスマウス', '家電', 3200),
(7, 'キーボード', '家電', 6800),
(8, 'スピーカー', '家電', 4000);期待される出力
| category | avg_price |
|---|---|
| 家電 | 4666.6667 |
| 文具 | 1850.0000 |
| 雑貨 | 1850.0000 |