WITH句（CTE）の活用

内側から読まないと、何をしているのか分からない

サブクエリを 1 段重ねただけなら、まだ追えます。

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 を使うと、処理の段に名前を付けてから使えます。

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 を参照できるのが、丸括弧の入れ子には無い強みです。

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 が返している中身をそのまま目で確認できます。

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 だけを直します。長いクエリが動かないときに、どこから疑えばいいかで迷わなくなります。

1 行の全体合計を、各行に添える

カテゴリ別の集計に全体合計を添える場面でも、CTE を使えます。ここでは演習とは別に、図書館の分館別貸出件数を扱います。

WITH branch_total AS (
  SELECT branch, SUM(book_count) AS total
  FROM library_loans
  GROUP BY branch
),
all_branches AS (
  SELECT SUM(total) AS grand_total FROM branch_total
)
SELECT b.branch, b.total, a.grand_total
FROM branch_total b
CROSS JOIN all_branches a
ORDER BY b.branch;

SUM は合計を求めます。2 つ目の CTE は 1 つ目の合計をさらに足し、GROUP BY を付けずに全体合計を 1 行で返します。

CROSS JOIN は左右の行のすべての組み合わせを作る結合です。右側が 1 行なら、左側の各行に同じ全体合計が添えられます。分館が 3 行なら結果も 3 行です。b と a は表の別名で、b.total は分館別の合計、a.grand_total は全体合計を指します。

割合は、各行の合計を全体合計で割って 100 倍すると求められます。小数の計算にするため 100.0 を使い、小数第 1 位まで丸めるなら ROUND(値, 1) と書きます。たとえば SELECT ROUND(3 * 100.0 / 8, 1) AS pct; の結果は 37.5 です。全体合計が 0 なら割れないため、この演習では合計が正のデータを使います。