sql-advanced

SQL 高度コア知識

深い知識：包括的なドキュメントを参照するには、technology に sql を指定して mcp__documentation__fetch_docs を使用してください。

共通テーブル式（CTE）

基本的な CTE

WITH active_users AS (
    SELECT id, name, email
    FROM users
    WHERE status = 'active'
)
SELECT u.name, COUNT(o.id) as order_count
FROM active_users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name;

複数の CTE

WITH
active_users AS (
    SELECT id, name FROM users WHERE status = 'active'
),
user_orders AS (
    SELECT user_id, COUNT(*) as order_count, SUM(total) as total_spent
    FROM orders
    WHERE status = 'completed'
    GROUP BY user_id
),
high_value_users AS (
    SELECT u.*, uo.order_count, uo.total_spent
    FROM active_users u
    JOIN user_orders uo ON uo.user_id = u.id
    WHERE uo.total_spent > 10000
)
SELECT * FROM high_value_users ORDER BY total_spent DESC;

再帰 CTE

-- Hierarchical data (org chart, categories)
WITH RECURSIVE org_chart AS (
    -- Base case: top-level employees
    SELECT id, name, manager_id, 1 as level, ARRAY[name] as path
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive case
    SELECT e.id, e.name, e.manager_id, oc.level + 1, oc.path || e.name
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart ORDER BY path;

-- Generate series
WITH RECURSIVE numbers AS (
    SELECT 1 as n
    UNION ALL
    SELECT n + 1 FROM numbers WHERE n < 100
)
SELECT * FROM numbers;

-- Date range
WITH RECURSIVE dates AS (
    SELECT DATE '2024-01-01' as date
    UNION ALL
    SELECT date + INTERVAL '1 day' FROM dates WHERE date < '2024-12-31'
)
SELECT * FROM dates;

マテリアライズド CTE（PostgreSQL 12+）

-- Force CTE to be materialized (evaluated once)
WITH active_users AS MATERIALIZED (
    SELECT * FROM users WHERE status = 'active'
)
SELECT * FROM active_users WHERE id = 1
UNION ALL
SELECT * FROM active_users WHERE id = 2;

-- Force CTE to be inlined (not materialized)
WITH active_users AS NOT MATERIALIZED (
    SELECT * FROM users WHERE status = 'active'
)
SELECT * FROM active_users WHERE id = 1;

ウィンドウ関数の詳細

パーティションごとの計算

SELECT
    department,
    name,
    salary,
    -- Within department
    SUM(salary) OVER (PARTITION BY department) as dept_total,
    AVG(salary) OVER (PARTITION BY department) as dept_avg,
    salary - AVG(salary) OVER (PARTITION BY department) as diff_from_avg,
    -- Percentage of department total
    ROUND(100.0 * salary / SUM(salary) OVER (PARTITION BY department), 2) as pct_of_dept
FROM employees;

累積計算

SELECT
    date,
    amount,
    -- Running totals
    SUM(amount) OVER (ORDER BY date) as running_total,
    SUM(amount) OVER (ORDER BY date ROWS UNBOUNDED PRECEDING) as same_as_above,
    -- Moving averages
    AVG(amount) OVER (ORDER BY date ROWS 6 PRECEDING) as moving_avg_7d,
    AVG(amount) OVER (ORDER BY date ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING) as centered_avg,
    -- Cumulative stats
    COUNT(*) OVER (ORDER BY date) as cumulative_count,
    MIN(amount) OVER (ORDER BY date) as running_min,
    MAX(amount) OVER (ORDER BY date) as running_max
FROM daily_transactions;

ギャップと島の分析

-- Find consecutive sequences (islands)
WITH numbered AS (
    SELECT
        date,
        value,
        ROW_NUMBER() OVER (ORDER BY date) as rn,
        date - (ROW_NUMBER() OVER (ORDER BY date) * INTERVAL '1 day') as grp
    FROM daily_data
)
SELECT
    MIN(date) as island_start,
    MAX(date) as island_end,
    COUNT(*) as days_in_sequence
FROM numbered
GROUP BY grp
ORDER BY island_start;

-- Find gaps in sequence
SELECT
    id,
    LEAD(id) OVER (ORDER BY id) as next_id,
    LEAD(id) OVER (ORDER BY id) - id - 1 as gap_size
FROM items
WHERE LEAD(id) OVER (ORDER BY id) - id > 1;

グループ内の最初／最後

-- Get first and last values per group
SELECT DISTINCT ON (department)
    department,
    name as highest_paid,
    salary
FROM employees
ORDER BY department, salary DESC;

-- With window functions
SELECT DISTINCT
    department,
    FIRST_VALUE(name) OVER w as highest_paid,
    LAST_VALUE(name) OVER w as lowest_paid
FROM employees
WINDOW w AS (
    PARTITION BY department
    ORDER BY salary DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
);

クエリの最適化

EXPLAIN の基本

-- Show query plan
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

-- Show actual execution stats
EXPLAIN ANALYZE SELECT * FROM users WHERE email = '[email protected]';

-- More details
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM users WHERE email = '[email protected]';

-- JSON output for tools
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT * FROM users WHERE email = '[email protected]';

EXPLAIN 出力の読み方

Seq Scan on users  (cost=0.00..155.00 rows=1 width=100) (actual time=0.015..0.842 rows=1 loops=1)
  Filter: (email = '[email protected]'::text)
  Rows Removed by Filter: 4999

用語	意味
Seq Scan	テーブル全体のスキャン（多くの場合、望ましくありません）
Index Scan	インデックスを使用（良好）
Index Only Scan	カバリングインデックスを使用（最適）
Bitmap Index Scan	複数のインデックスを使用
cost=0.00..155.00	推定起動コスト..総コスト
rows=1	推定返却行数
actual time=0.015..0.842	実際の起動時間..合計時間（ms）
Rows Removed by Filter	読み込まれたが返却されなかった行

よくあるパフォーマンスの問題

インデックスの欠如

-- Problem: Seq Scan
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

-- Solution: Add index
CREATE INDEX idx_users_email ON users(email);

インデックスが使用されない

-- Problem: Function on column prevents index use
SELECT * FROM users WHERE LOWER(email) = '[email protected]';

-- Solution: Expression index
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

N+1 クエリ問題

-- Problem: Querying in a loop (application code)
-- For each user: SELECT * FROM orders WHERE user_id = ?

-- Solution: Single query with JOIN
SELECT u.*, o.*
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.id IN (1, 2, 3, 4, 5);

インデックス戦略

-- Composite index (column order matters!)
-- Good for: WHERE a = ? AND b = ?
-- Good for: WHERE a = ? ORDER BY b
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);

-- Covering index (all needed columns in index)
CREATE INDEX idx_orders_user_covering ON orders(user_id) INCLUDE (status, total);

-- Partial index (filtered)
CREATE INDEX idx_active_orders ON orders(user_id) WHERE status = 'active';

-- Expression index
CREATE INDEX idx_orders_year ON orders(EXTRACT(YEAR FROM created_at));

クエリの書き換え

-- Avoid: Subquery in SELECT
SELECT
    name,
    (SELECT COUNT(*) FROM orders WHERE user_id = users.id) as order_count
FROM users;

-- Better: JOIN with aggregation
SELECT u.name, COALESCE(o.order_count, 0) as order_count
FROM users u
LEFT JOIN (
    SELECT user_id, COUNT(*) as order_count
    FROM orders GROUP BY user_id
) o ON o.user_id = u.id;

-- Avoid: OR conditions on different columns
SELECT * FROM users WHERE email = '[email protected]' OR phone = '123';

-- Better: UNION (can use indexes)
SELECT * FROM users WHERE email = '[email protected]'
UNION
SELECT * FROM users WHERE phone = '123';

-- Avoid: NOT IN with NULLs (tricky behavior)
SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM banned);

-- Better: NOT EXISTS
SELECT * FROM users u WHERE NOT EXISTS (
    SELECT 1 FROM banned b WHERE b.user_id = u.id
);

高度なパターン

ピボット／アンピボット

-- Pivot: Rows to columns
SELECT
    product_id,
    SUM(CASE WHEN month = 1 THEN sales END) as jan,
    SUM(CASE WHEN month = 2 THEN sales END) as feb,
    SUM(CASE WHEN month = 3 THEN sales END) as mar
FROM monthly_sales
GROUP BY product_id;

-- PostgreSQL: crosstab
SELECT * FROM crosstab(
    'SELECT product_id, month, sales FROM monthly_sales ORDER BY 1,2'
) AS ct(product_id INT, jan INT, feb INT, mar INT);

-- Unpivot: Columns to rows (PostgreSQL)
SELECT product_id, month, sales
FROM products,
LATERAL (VALUES
    ('jan', jan_sales),
    ('feb', feb_sales),
    ('mar', mar_sales)
) AS t(month, sales);

重複排除

-- Keep first occurrence
DELETE FROM users a USING users b
WHERE a.id > b.id AND a.email = b.email;

-- With CTE (safer)
WITH duplicates AS (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at) as rn
    FROM users
)
DELETE FROM users WHERE id IN (
    SELECT id FROM duplicates WHERE rn > 1
);

時系列クエリ

-- Events active at specific time
SELECT * FROM events
WHERE start_time <= '2024-06-15 10:00:00'
  AND end_time > '2024-06-15 10:00:00';

-- Overlapping periods
SELECT a.*, b.*
FROM reservations a, reservations b
WHERE a.id < b.id
  AND a.room_id = b.room_id
  AND a.start_time < b.end_time
  AND a.end_time > b.start_time;

-- Fill gaps with generate_series
SELECT
    d.date,
    COALESCE(s.revenue, 0) as revenue
FROM generate_series(
    '2024-01-01'::date,
    '2024-12-31'::date,
    '1 day'
) d(date)
LEFT JOIN daily_sales s ON s.date = d.date;

このスキルを使用しない場合

• 基本的な SQL（SELECT、JOIN、INSERT） - sql-fundamentals スキルを使用してください
• PostgreSQL 固有の機能（配列、JSONB） - postgresql スキルを使用してください
• MySQL 固有の機能（ストアドプロシージャ） - mysql スキルを使用してください
• ORM クエリ - prisma、typeorm、または該当する ORM スキルを使用してください

アンチパターン

アンチパターン	問題	解決策
相関サブクエリ	パフォーマンスが低下し、N+1 が発生	JOIN またはウィンドウ関数を使用
インデックス付きカラムに対する関数	インデックスが使用されない	関数インデックスを使用
制限のない深い再帰	スタックオーバーフロー	再帰の深さに上限を設定
CTE に WHERE がない	不要なデータを処理	CTE 内で早めにフィルター
ウィンドウ関数の過剰な使用	メモリへの負荷	先に結果セットを制限
EXPLAIN 出力を分析しない	遅いクエリが見過ごされる	常に実行計画を確認

簡単なトラブルシューティング

問題	診断	修正
CTE の実行が遅い	EXPLAIN ANALYZE	MATERIALIZED ヒントを追加するか、書き換える
メモリ使用量が多い	ソート／ハッシュ操作を確認	work_mem を増やし、クエリを最適化
再帰の上限を超過	再帰の深さを確認	LIMIT を追加するか、クエリを再設計
ウィンドウ関数が遅い	PARTITION BY のカーディナリティを確認	パーティション列にインデックスを追加
クエリ計画が変化する	EXPLAIN の出力を比較	統計情報を更新し、計画を固定

リファレンスドキュメント

• CTE と再帰クエリ
• ウィンドウ関数
• 再帰クエリ
• クエリの最適化