高度なSQL

SQL

サブクエリ、CTE、ウィンドウ関数

サブクエリ

スカラー・インライン・相関サブクエリ

-- スカラーサブクエリ（1値を返す）
SELECT name, salary,
    (SELECT AVG(salary) FROM employees) AS avg_salary,
    salary - (SELECT AVG(salary) FROM employees) AS diff
FROM employees;

-- インラインビュー（FROM句のサブクエリ）
SELECT dept, avg_sal
FROM (
    SELECT department AS dept, AVG(salary) AS avg_sal
    FROM employees
    GROUP BY department
) AS dept_avg
WHERE avg_sal > 50000;

-- 相関サブクエリ（外側のクエリを参照）
-- 各部門で最高給与の社員を取得
SELECT e1.name, e1.department, e1.salary
FROM employees e1
WHERE e1.salary = (
    SELECT MAX(e2.salary)
    FROM employees e2
    WHERE e2.department = e1.department
);

-- EXISTS（相関サブクエリ、存在確認）
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.user_id = u.id AND o.total > 10000
);

-- NOT EXISTS（存在しない行）
SELECT * FROM users u
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

CTE（共通テーブル式）

WITH句、再帰CTE

-- 基本的なCTE（サブクエリより読みやすい）
WITH high_value_users AS (
    SELECT user_id, SUM(total) AS lifetime_value
    FROM orders
    GROUP BY user_id
    HAVING SUM(total) > 100000
)
SELECT u.name, u.email, h.lifetime_value
FROM users u
JOIN high_value_users h ON u.id = h.user_id
ORDER BY h.lifetime_value DESC;

-- 複数のCTEを連鎖
WITH
monthly_sales AS (
    SELECT DATE_TRUNC('month', created_at) AS month,
           SUM(total) AS revenue
    FROM orders
    GROUP BY 1
),
growth AS (
    SELECT month, revenue,
           LAG(revenue) OVER (ORDER BY month) AS prev_month,
           ROUND((revenue - LAG(revenue) OVER (ORDER BY month))
                 / LAG(revenue) OVER (ORDER BY month) * 100, 2) AS growth_pct
    FROM monthly_sales
)
SELECT * FROM growth WHERE growth_pct IS NOT NULL;

-- 再帰CTE（階層データ・ツリー構造）
WITH RECURSIVE org_tree AS (
    -- ベースケース: ルート（上司なし）
    SELECT id, name, manager_id, 0 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- 再帰ケース: 部下を追加
    SELECT e.id, e.name, e.manager_id, t.level + 1
    FROM employees e
    JOIN org_tree t ON e.manager_id = t.id
)
SELECT REPEAT('  ', level) || name AS hierarchy, level
FROM org_tree
ORDER BY level, name;

トランザクション

BEGIN、COMMIT、ROLLBACK、SAVEPOINT

-- 基本的なトランザクション
BEGIN;

    UPDATE accounts SET balance = balance - 5000 WHERE id = 1; -- 送金元
    UPDATE accounts SET balance = balance + 5000 WHERE id = 2; -- 送金先

    -- 残高チェック
    -- （アプリ側でチェックして ROLLBACK を判断）

COMMIT;  -- 全て成功した場合
-- ROLLBACK; -- 問題があれば取り消し

-- SAVEPOINT（部分ロールバック）
BEGIN;
    INSERT INTO orders (user_id, total) VALUES (1, 5000);

    SAVEPOINT after_order;

    INSERT INTO payments (order_id, amount) VALUES (LASTVAL(), 5000);
    -- もし支払い処理に失敗したら
    ROLLBACK TO SAVEPOINT after_order; -- 注文は残して支払いだけ取り消し
    -- または
    RELEASE SAVEPOINT after_order;    -- SAVEPOINTを解放

COMMIT;

-- 分離レベル
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;  -- デフォルト
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;  -- ファジーリードなし
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;     -- 最強（デッドロックに注意）

-- ロック
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;   -- 行ロック
SELECT * FROM accounts WHERE id = 1 FOR SHARE;    -- 共有ロック

ビュー・関数・ストアドプロシージャ

CREATE VIEW、関数、インデックス戦略

ビューと関数

-- ビュー（仮想テーブル）
CREATE VIEW active_users AS
SELECT id, name, email
FROM users
WHERE deleted_at IS NULL AND is_active = true;

-- マテリアライズドビュー（結果をキャッシュ）
CREATE MATERIALIZED VIEW monthly_revenue AS
SELECT DATE_TRUNC('month', created_at) AS month,
       SUM(total) AS revenue
FROM orders GROUP BY 1;

REFRESH MATERIALIZED VIEW monthly_revenue; -- データを再計算

-- ビュー削除
DROP VIEW IF EXISTS active_users;

-- 関数（PostgreSQL）
CREATE OR REPLACE FUNCTION get_user_total(p_user_id INT)
RETURNS DECIMAL AS $$
DECLARE
    v_total DECIMAL;
BEGIN
    SELECT COALESCE(SUM(total), 0)
    INTO v_total
    FROM orders WHERE user_id = p_user_id;
    RETURN v_total;
END;
$$ LANGUAGE plpgsql;

-- 使用
SELECT name, get_user_total(id) AS total_spent FROM users;

EXPLAINとクエリ最適化

-- 実行計画の確認
EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';

-- 実際の実行時間も計測
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1;

-- よくある最適化ポイント
-- ✅ Seq Scan → Index Scan になるようインデックスを追加
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- ✅ SELECT * を避け必要な列のみ取得
-- ✅ LIMIT を早めに適用
-- ✅ サブクエリより JOIN を優先
-- ✅ OR より IN / UNION を使う
-- ✅ 関数をWHEREの左辺に使わない（インデックスが効かない）

-- ❌ インデックスが効かない例
WHERE LOWER(email) = 'alice@example.com'
-- ✅ 代わりに関数インデックスを作成
CREATE INDEX idx_users_email_lower ON users(LOWER(email));
-- またはGenerated Column（MySQL 8+）

-- ページネーション最適化
-- ❌ OFFSETは遅い（大量ページで全行スキャン）
SELECT * FROM posts ORDER BY id LIMIT 10 OFFSET 10000;
-- ✅ カーソルベース（Keyset Pagination）
SELECT * FROM posts WHERE id > 10000 ORDER BY id LIMIT 10;

DML