第6章 SQL応用

1. 集約関数

1.1 COUNT・SUM・AVG

集約関数（Aggregate Function）は、複数の行から単一の値を計算する関数である。

Table 1. 主要な集約関数

-- 基本的な集約関数
SELECT 
    COUNT(*) AS total_employees,
    COUNT(department_id) AS employees_with_dept,
    SUM(salary) AS total_salary,
    AVG(salary) AS average_salary,
    MAX(salary) AS max_salary,
    MIN(salary) AS min_salary
FROM employees;

-- DISTINCT と組み合わせ
SELECT COUNT(DISTINCT department_id) AS dept_count FROM employees;

-- 条件付きカウント（CASE式）
SELECT 
    COUNT(*) AS total,
    COUNT(CASE WHEN salary >= 500000 THEN 1 END) AS high_salary_count,
    COUNT(CASE WHEN salary < 500000 THEN 1 END) AS low_salary_count
FROM employees;

1.2 GROUP BY・HAVING

GROUP BY句は、指定した列の値ごとにグループ化し、各グループに対して集約関数を適用する。HAVING句は、グループ化後の条件指定に使用する。

-- 部署ごとの社員数と平均給与
SELECT 
    department_id,
    COUNT(*) AS employee_count,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

-- HAVING句でグループを絞り込み
-- 社員数が5人以上の部署のみ表示
SELECT 
    department_id,
    COUNT(*) AS employee_count,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING COUNT(*) >= 5;

-- WHERE と HAVING の違い
-- WHERE: GROUP BY 前の行の絞り込み
-- HAVING: GROUP BY 後のグループの絞り込み
SELECT 
    department_id,
    AVG(salary) AS avg_salary
FROM employees
WHERE salary > 300000           -- 個々の行に対する条件
GROUP BY department_id
HAVING AVG(salary) > 450000;    -- グループに対する条件

Table 2. WHERE と HAVING の比較

-- 部署ごとの平均給与を高い順に表示
SELECT 
    department_id,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
ORDER BY avg_salary DESC;

2. サブクエリ

サブクエリ（副問い合わせ）は、SQL文の中に埋め込まれた別のSELECT文である。外側のクエリから参照される内側のクエリとして機能する。

Table 3. サブクエリの種類

-- スカラサブクエリ（単一値を返す）
-- 平均給与より高い給与の社員を取得
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- IN を使ったサブクエリ（複数値）
-- 営業部門の社員を取得
SELECT name, salary
FROM employees
WHERE department_id IN (
    SELECT id FROM departments WHERE name LIKE '%営業%'
);

-- FROM句でのサブクエリ（派生テーブル）
SELECT dept_avg.department_id, dept_avg.avg_salary
FROM (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
) AS dept_avg
WHERE dept_avg.avg_salary > 450000;

-- 相関サブクエリ
-- 各部署で最高給与の社員を取得
SELECT e1.name, e1.salary, e1.department_id
FROM employees e1
WHERE e1.salary = (
    SELECT MAX(e2.salary)
    FROM employees e2
    WHERE e2.department_id = e1.department_id
);

-- EXISTS / NOT EXISTS
-- 注文がある顧客のみ表示
SELECT c.name
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

3. ビュー

ビュー（View）は、SELECT文の結果を仮想的なテーブルとして保存したものである。データは実際には格納されず、参照時にSELECT文が実行される。

Table 4. ビューの利点

-- ビューの作成
CREATE VIEW employee_summary AS
SELECT 
    e.id,
    e.name,
    e.salary,
    d.name AS department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

-- ビューの使用（テーブルと同様に参照）
SELECT * FROM employee_summary WHERE salary > 500000;

-- ビューの置き換え（すでに存在する場合は上書き）
CREATE OR REPLACE VIEW employee_summary AS
SELECT 
    e.id,
    e.name,
    e.salary,
    d.name AS department_name,
    e.hire_date
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

-- ビューの削除
DROP VIEW employee_summary;

-- 更新可能ビュー（単一テーブル、集約なし）
CREATE VIEW active_employees AS
SELECT id, name, salary, department_id
FROM employees
WHERE status = 'active'
WITH CHECK OPTION;  -- status='active'以外のINSERT/UPDATEを禁止

-- ビューへのINSERT（更新可能な場合）
INSERT INTO active_employees (id, name, salary, department_id)
VALUES (100, '新入社員', 350000, 1);

4. インデックス

インデックス（Index）は、テーブル内のデータを高速に検索するためのデータ構造である。書籍の索引と同様の役割を果たす。

4.1 B-Tree

B-Tree（Balanced Tree）は、最も一般的なインデックス構造である。データを木構造で管理し、ルートから目的のデータまで効率的に探索できる。

Table 5. インデックスの種類

Table 6. インデックスの効果

4.2 作成と削除

-- 単一列インデックスの作成
CREATE INDEX idx_employees_name ON employees(name);

-- 複合インデックス（複数列）
CREATE INDEX idx_employees_dept_salary 
ON employees(department_id, salary);

-- ユニークインデックス（重複不可）
CREATE UNIQUE INDEX idx_employees_email ON employees(email);

-- インデックスの削除
DROP INDEX idx_employees_name;

-- インデックスの確認（PostgreSQL）
\di employees*

-- インデックスの確認（MySQL）
SHOW INDEX FROM employees;

Table 7. インデックス作成の指針

5. まとめ

Table 8. 第6章のまとめ