WITH句（CTE）の活用
内側から読まないと、何をしているのか分からない
サブクエリを 1 段重ねただけなら、まだ追えます。
SQL クエリ
SELECT dept, total
FROM (
SELECT dept, SUM(salary) AS total
FROM employees
WHERE hired_at < '2024-01-01'
GROUP BY dept
) AS d
WHERE total >= 30000000
ORDER BY total DESC;このクエリを人に説明するとき、上から順には読めません。真ん中の丸括弧の中から読み始めて、外へ戻ってくる必要があります。段が 2 つ、3 つと重なると、今どの段の話をしているのかが分からなくなり、直すのが怖いクエリになります。
先に名前を付けてから、使う
WITH を使うと、処理の段に名前を付けてから使えます。
SQL クエリ
WITH dept_total AS (
SELECT dept, SUM(salary) AS total
FROM employees
WHERE hired_at < '2024-01-01'
GROUP BY dept
)
SELECT dept, total
FROM dept_total
WHERE total >= 30000000
ORDER BY total DESC;やっていることは先ほどと同じです。違うのは「まず dept_total という中間結果を作る」と宣言してから、最後の SELECT でそれを使っている点だけです。この WITH 名前 AS (...) を CTE (共通テーブル式) と呼びます。上から下へ、手順どおりに読めます。
名前は、いくつでも並べられる
カンマで区切れば、CTE はいくつでも書けます。後ろの CTE から前の CTE を参照できるのが、丸括弧の入れ子には無い強みです。
SQL クエリ
WITH dept_total AS (
SELECT dept, SUM(salary) AS total
FROM employees
GROUP BY dept
),
big_dept AS (
SELECT dept FROM dept_total WHERE total >= 30000000
)
SELECT e.name, e.dept, e.salary
FROM employees e
JOIN big_dept b ON b.dept = e.dept
ORDER BY e.dept, e.salary DESC;同じ中間結果を 2 か所以上で使いたいときも、CTE なら名前を書くだけです。サブクエリだと同じ丸括弧を書き写すことになり、片方だけ直して結果がずれる、という事故が起きます。
詰まったら、1 つずつ動かして確かめる
CTE のもう 1 つの利点は、途中を単独で見られることです。最後の SELECT を差し替えれば、その CTE が返している中身をそのまま目で確認できます。
SQL クエリ
WITH dept_total AS (
SELECT dept, SUM(salary) AS total
FROM employees
GROUP BY dept
)
SELECT dept, total FROM dept_total ORDER BY total DESC;思ったとおりの表になっていれば次の段に進み、違っていればその CTE だけを直します。長いクエリが動かないときに、どこから疑えばいいかで迷わなくなります。
テーブル構造
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 | total | pct |
|---|---|---|
| 家電 | 14000 | 60.2 |
| 文具 | 5550 | 23.9 |
| 雑貨 | 3700 | 15.9 |