集合で捉えるSQL

以前読んで非常にためになったSQLの参考書、「SQL実践入門」と「達人に学ぶSQL徹底指南書 第2版」で学んだことをここにメモしておこうと思います。

本記事では、提供された資料に基づき、SQLの基本的な考え方と設計のコツを解説します。

---

1. 「ぐるぐる系」から「ガツン系」への脱却

何も考えずにデータを取得しようとすると、データを1行ずつ取得してループ処理（手続き型）を行いたくなりますが、これは書籍の中で 「ぐるぐる系」 と呼ばれ、一般的に避けるべき手法とされています。

• ぐるぐる系（ループ処理）: 単純なSQLを何度も発行するため、ネットワークのオーバーヘッドが蓄積し、データ量に比例して遅くなります。また、データベースの進化による高速化の恩恵も受けにくいのが難点です。
• ガツン系（一括処理）: SQLで複雑な集計や計算を一括で行います。オーバーヘッドが少なく、データ量が増えても処理時間が正比例して増えないため、比較的高速です。

SQLの設計においては、まず「集合として一括で処理できないか」を考えることが重要です。

2. 「文」から「式」へ：CASE式の活用

書籍の中で非常に重要な格言として、　「WHERE句で条件分岐させるのは素人のやること。プロはSELECT句（CASE式）で分岐させる」 という言葉が紹介されていました。

条件分岐をWHEREで行い、かつUNION（和集合）でつなげて記述すると、同じテーブルに何度もアクセスすることになり、I/Oコストが増大します。これをCASE式に書き換えることで、I/Oコストを1回に抑え、パフォーマンスを向上させることが可能です。SQLを単なる「命令文」ではなく、値を返す 「式」 として捉える意識が大切です。

3. 「行」ではなく「集合」に条件を出す（HAVING句）

SQLの設計において、WHEREとHAVINGの使い分けもポイントです。

• WHERE句: 個別の レコード（行） に対して条件を指定します。
• HAVING句: レコードの集合に対して条件を指定します。

例えば、「特定の学部の生徒が全員課題を提出しているか」や「必要な材料がすべて揃っている拠点」を探す場合、個別の行を見るのではなく、GROUP BYでまとめた集合の数と、全体の数を比較する（COUNT(*) = COUNT(列名)など）といった集合的なアプローチが有効です。

4. 複雑な問題への設計アプローチ

複雑な要件をSQLで実装する際、いきなりコードを書き始めるのは挫折の元です。書籍では以下のステップが推奨されていました。

1. 必ず図に書く: 問題を視覚化し、集合の関係性を整理します。一気にSQLを組み立てるのは難しいので、まずはどの情報とどの情報が関連していて、抽出をしたらいいのかを図にかいてみましょう。
2. 問題を細かく分割する: 個人的に一番大事だと思っている箇所です。いきなり全てのSQLを組むことはできないので、パーツにわけて組み立てていきましょう。困難は分割し、まずは小さな単位（サブクエリやSELECT句）で正しく動作するか確認します。
3. 細部から組み立てる: 小さなパーツを組み合わせて最終的なクエリを構成します。
4. 具体値で試す: 変数が複雑な場合は、まず具体的な値を入れてイメージを掴みます。

---

実践例：具体的なテーブルとSQLサンプル

サンプルSQL

テーブル定義

-- 社員テーブル
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10, 2),
    age INT
);

サンプルデータ

INSERT INTO employees VALUES
(1, '田中太郎', '営業部', 450000, 28),
(2, '佐藤花子', '開発部', 550000, 32),
(3, '鈴木一郎', '営業部', 380000, 25),
(4, '高橋美咲', '開発部', 620000, 35),
(5, '伊藤健太', '人事部', 400000, 30),
(6, '山田涼子', '開発部', 480000, 27);

❌ UNION を使った方法（悪い例）

-- 部署ごとの給与ランクを集計（UNION使用）
SELECT 
    '営業部' AS department,
    COUNT(*) AS employee_count
FROM employees
WHERE department = '営業部' AND salary >= 400000
UNION ALL
SELECT 
    '開発部' AS department,
    COUNT(*) AS employee_count
FROM employees
WHERE department = '開発部' AND salary >= 500000
UNION ALL
SELECT 
    '人事部' AS department,
    COUNT(*) AS employee_count
FROM employees
WHERE department = '人事部' AND salary >= 400000;

問題点:

• テーブルに3回アクセスする（I/Oコスト×3）
• 部署が増えるたびにUNIONを追加する必要がある
• 実行計画が複雑になる

✅ CASE式を使った方法（良い例）

-- 部署ごとの給与ランクを集計（CASE式使用）
SELECT 
    department,
    SUM(CASE WHEN salary >= 500000 THEN 1 ELSE 0 END) AS high_salary_count,
    SUM(CASE WHEN salary >= 400000 AND salary < 500000 THEN 1 ELSE 0 END) AS mid_salary_count,
    SUM(CASE WHEN salary < 400000 THEN 1 ELSE 0 END) AS low_salary_count,
    COUNT(*) AS total_count
FROM 
    employees
GROUP BY 
    department;

結果:

department	high_salary_count	mid_salary_count	low_salary_count	total_count
営業部	0	1	2	3
開発部	2	1	0	3
人事部	0	1	0	1

メリット:

• テーブルへのアクセスは1回のみ（I/Oコスト最小）
• 複数の集計を同時に取得できる
• 実行計画がシンプル

さらに応用：年齢層別の平均給与

-- CASE式で年齢層を分類して平均給与を算出
SELECT 
    department,
    AVG(CASE WHEN age < 30 THEN salary END) AS avg_salary_under30,
    AVG(CASE WHEN age >= 30 AND age < 35 THEN salary END) AS avg_salary_30to34,
    AVG(CASE WHEN age >= 35 THEN salary END) AS avg_salary_over35
FROM 
    employees
GROUP BY 
    department;

---

例3: GROUP BY、HAVINGを活用した集合操作

テーブル定義

-- 学生テーブル
CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50)
);

-- 課題提出テーブル
CREATE TABLE submissions (
    submission_id INT PRIMARY KEY,
    student_id INT,
    assignment_name VARCHAR(100),
    submitted_date DATE,
    FOREIGN KEY (student_id) REFERENCES students(student_id)
);

-- 必須課題マスタ
CREATE TABLE required_assignments (
    assignment_name VARCHAR(100) PRIMARY KEY
);

サンプルデータ

INSERT INTO students VALUES
(1, '山田太郎', '情報学部'),
(2, '佐藤花子', '情報学部'),
(3, '鈴木一郎', '情報学部'),
(4, '田中美咲', '経済学部'),
(5, '高橋健太', '経済学部');

INSERT INTO required_assignments VALUES
('課題A'),
('課題B'),
('課題C');

INSERT INTO submissions VALUES
(1, 1, '課題A', '2024-01-10'),
(2, 1, '課題B', '2024-01-12'),
(3, 1, '課題C', '2024-01-15'),
(4, 2, '課題A', '2024-01-11'),
(5, 2, '課題B', '2024-01-13'),
-- 学生2は課題Cを未提出
(6, 3, '課題A', '2024-01-09'),
-- 学生3は課題BとCを未提出
(7, 4, '課題A', '2024-01-10'),
(8, 4, '課題B', '2024-01-11'),
(9, 4, '課題C', '2024-01-14'),
(10, 5, '課題A', '2024-01-12');
-- 学生5は課題BとCを未提出

問題1: 全ての必須課題を提出した学生を見つける

❌ 行単位で考える方法（悪い例）

-- 各課題ごとに提出したかチェック（非効率）
SELECT DISTINCT s.student_id, s.name
FROM students s
WHERE EXISTS (SELECT 1 FROM submissions sub WHERE sub.student_id = s.student_id AND sub.assignment_name = '課題A')
  AND EXISTS (SELECT 1 FROM submissions sub WHERE sub.student_id = s.student_id AND sub.assignment_name = '課題B')
  AND EXISTS (SELECT 1 FROM submissions sub WHERE sub.student_id = s.student_id AND sub.assignment_name = '課題C');

問題点:

• 課題が増えるたびにEXISTS句を追加する必要がある
• 保守性が悪い
• 「集合」として考えていない

✅ 集合で考える方法（良い例）

-- 必須課題数と提出課題数を比較（HAVING句使用）
SELECT 
    s.student_id,
    s.name,
    s.department,
    COUNT(DISTINCT sub.assignment_name) AS submitted_count
FROM 
    students s
    INNER JOIN submissions sub ON s.student_id = sub.student_id
    INNER JOIN required_assignments ra ON sub.assignment_name = ra.assignment_name
GROUP BY 
    s.student_id, s.name, s.department
HAVING 
    COUNT(DISTINCT sub.assignment_name) = (SELECT COUNT(*) FROM required_assignments);

結果:

student_id	name	department	submitted_count
1	山田太郎	情報学部	3
4	田中美咲	経済学部	3

メリット:

• 「集合」の数を比較する発想
• 課題が増えても自動的に対応
• HAVING句で集合に対して条件を適用

---

まとめ：集合志向の3つのポイント

1. ぐるぐる系からガツン系へ: ループで1件ずつ処理するのではなく、JOINと集約関数で一括処理
2. WHERE句からCASE式へ: 条件分岐はUNIONではなくCASE式で。テーブルアクセスは1回に
3. 行ではなく集合へ: HAVING句を使って集合に対する条件を記述。COUNT比較で「全て」を表現

これらのテクニックを使うことで、パフォーマンスが向上し、保守性の高いSQLが書けるようになります。