sql-expert

SQL エキスパートスキル

PostgreSQL、MySQL、SQLite、SQL Server における SQL データベースの作成、最適化、管理に関する専門的なガイダンスです。

主な機能

このスキルを使うと、次のことができます。

• 複雑な SQL クエリを作成する（JOIN、サブクエリ、CTE、ウィンドウ関数を使用）
• 遅いクエリを最適化する（EXPLAIN プランとインデックスの推奨を活用）
• データベーススキーマを設計する（適切な正規化を適用：1NF、2NF、3NF、BCNF）
• クエリのパフォーマンスを高める効果的なインデックスを作成する
• ロールバックに対応した安全なデータベースマイグレーションを作成する
• SQL エラーをデバッグし、エラーメッセージを理解する
• 適切な分離レベルでトランザクションを処理する
• JSON／JSONB データ型を扱う
• テスト用のサンプルデータを生成する
• データベースの方言を相互に変換する（PostgreSQL ↔ MySQL ↔ SQLite）

---

対応データベースシステム

PostgreSQL

最適な用途：複雑なクエリ、JSON データ、高度な機能、ACID 準拠

pip install psycopg2-binary sqlalchemy

MySQL/MariaDB

最適な用途：ウェブアプリケーション、WordPress、読み取りが多いワークロード

pip install mysql-connector-python sqlalchemy

SQLite

最適な用途：ローカル開発、組み込みデータベース、テスト

pip install sqlite3  # Built into Python

SQL Server

最適な用途：エンタープライズアプリケーション、Windows 環境

pip install pyodbc sqlalchemy

---

クエリの作成

JOIN を使った基本的な SELECT

-- Simple SELECT with filtering
SELECT
    column1,
    column2,
    column3
FROM
    table_name
WHERE
    condition = 'value'
    AND another_condition > 100
ORDER BY
    column1 DESC
LIMIT 10;

-- INNER JOIN
SELECT
    users.name,
    orders.order_date,
    orders.total_amount
FROM
    users
INNER JOIN
    orders ON users.id = orders.user_id
WHERE
    orders.status = 'completed';

-- LEFT JOIN (include all users, even without orders)
SELECT
    users.name,
    COUNT(orders.id) as order_count,
    COALESCE(SUM(orders.total_amount), 0) as total_spent
FROM
    users
LEFT JOIN
    orders ON users.id = orders.user_id
GROUP BY
    users.id, users.name;

サブクエリと CTE

-- Subquery in WHERE clause
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Common Table Expression (CTE)
WITH high_value_customers AS (
    SELECT
        user_id,
        SUM(total_amount) as lifetime_value
    FROM orders
    GROUP BY user_id
    HAVING SUM(total_amount) > 1000
)
SELECT
    users.name,
    users.email,
    hvc.lifetime_value
FROM users
INNER JOIN high_value_customers hvc ON users.id = hvc.user_id;

ウィンドウ関数

-- Ranking within groups
SELECT
    name,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as salary_rank
FROM
    employees;

-- Running totals
SELECT
    order_date,
    total_amount,
    SUM(total_amount) OVER (ORDER BY order_date) as running_total
FROM
    orders;

-- Moving averages
SELECT
    order_date,
    total_amount,
    AVG(total_amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) as moving_avg_7days
FROM
    daily_sales;

より高度なクエリパターンについては、examples/complex_queries.sql を参照してください。

---

クエリの最適化

EXPLAIN の使用

-- Analyze query performance
EXPLAIN ANALYZE
SELECT
    users.name,
    COUNT(orders.id) as order_count
FROM users
LEFT JOIN orders ON users.id = orders.user_id
GROUP BY users.id, users.name;

-- Look for:
-- - Seq Scan (bad) vs Index Scan (good)
-- - High cost numbers
-- - Large row counts being processed

最適化の簡単なヒント

-- BAD: Function on indexed column
SELECT * FROM users WHERE LOWER(email) = '[email protected]';

-- GOOD: Keep indexed column clean
SELECT * FROM users WHERE email = LOWER('[email protected]');

-- BAD: SELECT *
SELECT * FROM large_table WHERE id = 123;

-- GOOD: Select only needed columns
SELECT id, name, email FROM large_table WHERE id = 123;

包括的な最適化手法については、references/query-optimization.md を参照してください。

---

スキーマ設計

正規化の原則

第一正規形（1NF）：繰り返しグループを排除し、原子的な値を使用します

-- GOOD: Separate table for order items
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_name VARCHAR(100)
);

CREATE TABLE order_items (
    order_item_id INT PRIMARY KEY,
    order_id INT REFERENCES orders(order_id),
    product_name VARCHAR(100)
);

第二正規形（2NF）：すべての非キー属性が主キー全体に依存するようにします

-- GOOD: Separate product information
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    product_price DECIMAL(10, 2)
);

CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

第三正規形（3NF）：推移的な依存関係がないようにします

一般的なスキーマパターン

一対多：

CREATE TABLE authors (
    author_id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100) UNIQUE
);

CREATE TABLE books (
    book_id INT PRIMARY KEY,
    title VARCHAR(200),
    author_id INT NOT NULL,
    published_date DATE,
    FOREIGN KEY (author_id) REFERENCES authors(author_id)
);

多対多：

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100)
);

-- Junction table
CREATE TABLE enrollments (
    enrollment_id INT PRIMARY KEY,
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    enrollment_date DATE,
    grade CHAR(2),
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id),
    UNIQUE (student_id, course_id)
);

その他のスキーマパターンについては、examples/schema_examples.sql を参照してください。

---

インデックスとパフォーマンス

インデックスの作成

-- Single column index
CREATE INDEX idx_users_email ON users(email);

-- Composite index (order matters!)
CREATE INDEX idx_orders_user_date ON orders(user_id, order_date);

-- Unique index
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- Partial index (PostgreSQL)
CREATE INDEX idx_active_users ON users(email) WHERE status = 'active';

インデックスのガイドライン

インデックスを作成する場合：

• ✅ WHERE 句で使用する列
• ✅ JOIN 条件で使用する列
• ✅ ORDER BY で使用する列
• ✅ 外部キー列

インデックスを作成しない場合：

• ❌ 小規模なテーブル（1000 行未満）
• ❌ 選択性の低い列（ブール型のフィールド）
• ❌ 頻繁に更新される列

詳細なインデックス戦略については、references/indexes-performance.md を参照してください。

---

マイグレーション

安全なマイグレーションパターン

-- Step 1: Add column as nullable
ALTER TABLE users ADD COLUMN status VARCHAR(20);

-- Step 2: Populate existing rows
UPDATE users SET status = 'active' WHERE status IS NULL;

-- Step 3: Make it NOT NULL
ALTER TABLE users ALTER COLUMN status SET NOT NULL;

-- Step 4: Add default for new rows
ALTER TABLE users ALTER COLUMN status SET DEFAULT 'active';

-- Rollback plan
ALTER TABLE users DROP COLUMN status;

ゼロダウンタイムマイグレーション

-- GOOD: Add column as nullable first, then backfill
ALTER TABLE large_table ADD COLUMN new_column VARCHAR(100);

-- Backfill in batches
UPDATE large_table SET new_column = 'value' WHERE new_column IS NULL LIMIT 1000;
-- Repeat until complete

-- Then make it NOT NULL
ALTER TABLE large_table ALTER COLUMN new_column SET NOT NULL;

その他のマイグレーションパターンについては、examples/migrations.sql を参照してください。

---

高度なパターン

UPSERT（挿入または更新）

-- PostgreSQL
INSERT INTO users (user_id, name, email, updated_at)
VALUES (1, 'John Doe', '[email protected]', NOW())
ON CONFLICT (user_id)
DO UPDATE SET
    name = EXCLUDED.name,
    email = EXCLUDED.email,
    updated_at = NOW();

-- MySQL
INSERT INTO users (user_id, name, email, updated_at)
VALUES (1, 'John Doe', '[email protected]', NOW())
ON DUPLICATE KEY UPDATE
    name = VALUES(name),
    email = VALUES(email),
    updated_at = NOW();

再帰 CTE

-- Hierarchical data traversal
WITH RECURSIVE employee_hierarchy AS (
    -- Anchor: top-level employees
    SELECT id, name, manager_id, 1 as level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive: employees reporting to previous level
    SELECT e.id, e.name, e.manager_id, eh.level + 1
    FROM employees e
    INNER JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy ORDER BY level, name;

ピボットテーブル、JSON 操作、一括操作など、さらに高度なパターンについては、references/advanced-patterns.md を参照してください。

---

ベストプラクティス

重要なガイドライン

1. SQL インジェクションを防ぐため、必ずパラメーター化クエリを使用します
2. 原子性を確保するため、関連する操作にはトランザクションを使用します
3. 適切な制約を追加します（PRIMARY KEY、FOREIGN KEY、NOT NULL、CHECK）
4. テーブルにタイムスタンプ（created_at、updated_at）を含めます
5. テーブルと列には意味のある名前を使用します
6. SELECT * を避けます。必要な列のみを指定してください
7. JOIN のパフォーマンスを高めるため、外部キーにインデックスを作成します
8. 可変長文字列にはCHAR ではなく VARCHAR を使用します
9. IS NULL／IS NOT NULL を使ってNULL 値を適切に処理します
10. 適切なデータ型を使用します（金額には FLOAT ではなく DECIMAL）

複数のベストプラクティスを適用した例：

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT NOT NULL,
    order_date DATE NOT NULL DEFAULT CURRENT_DATE,
    total_amount DECIMAL(10, 2) CHECK (total_amount >= 0),
    status VARCHAR(20) CHECK (status IN ('pending', 'completed', 'cancelled')),
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);

包括的なベストプラクティスについては、references/best-practices.md を参照してください。

---

よくある落とし穴

次のようなよくある問題に注意してください。

1. N+1 クエリ問題 - クエリを伴うループではなく、JOIN を使用します
2. 大きなテーブルに対する調査用クエリで LIMIT を使用しない
3. 暗黙的な型変換によってインデックスが使用されない
4. EXISTS で十分な場合に COUNT(*) を使用する
5. NULL を適切に扱わない（NULL = NULL は常に NULL であり、TRUE ではありません）
6. クエリを修正せずに、応急処置として SELECT DISTINCT を使用する
7. 関連する操作でトランザクションを忘れる
8. インデックス付きの列に関数を使用することで、インデックスが使用されない

例 - N+1 の回避：

# BAD: N+1 queries
users = db.query("SELECT * FROM users")
for user in users:
    orders = db.query("SELECT * FROM orders WHERE user_id = ?", user.id)

# GOOD: Single query with JOIN
result = db.query("""
    SELECT users.*, orders.*
    FROM users
    LEFT JOIN orders ON users.id = orders.user_id
""")

落とし穴と解決策の完全な一覧については、references/common-pitfalls.md を参照してください。

---

ヘルパースクリプトと例

利用可能なリソース

ヘルパースクリプト（scripts/）：

• sql_helper.py - クエリ構築、スキーマのイントロスペクション、インデックス分析、マイグレーション用ヘルパーのユーティリティ関数

例（examples/）：

• complex_queries.sql - CTE、ウィンドウ関数、サブクエリを使った高度なクエリパターン
• schema_examples.sql - さまざまなユースケースに対応する完全なスキーマ設計例
• migrations.sql - 安全なマイグレーションパターンとゼロダウンタイムの手法

リファレンス（references/）：

• query-optimization.md - 包括的なクエリ最適化手法と EXPLAIN 分析
• indexes-performance.md - 詳細なインデックス戦略、メンテナンス、モニタリング
• advanced-patterns.md - UPSERT、一括操作、ピボットテーブル、JSON 操作、再帰クエリ
• best-practices.md - SQL のベストプラクティス完全ガイド
• common-pitfalls.md - よくある間違いとその回避方法

クイックスタート

1. 基本的なクエリには、上記のパターンを使用します
2. 最適化するには、まず EXPLAIN を使用し、references/query-optimization.md を確認します
3. スキーマ設計については、正規化パターンを確認し、examples/schema_examples.sql を参照します
4. 複雑なケースについては、references/advanced-patterns.md を確認します
5. ユーティリティには、scripts/sql_helper.py を使用します

---

ワークフロー

SQL データベースを扱う場合：

1. 要件を把握する - クエリで取得または保存する必要があるデータは何ですか？
2. スキーマを設計する - 正規化を適用し、適切なデータ型を選択します
3. インデックスを作成する - 外部キーと、頻繁にクエリされる列にインデックスを設定します
4. クエリを作成する - シンプルなものから始め、必要に応じて複雑にしていきます
5. 最適化する - EXPLAIN を使用してボトルネックを特定します
6. テストする - サンプルデータとエッジケースで検証します
7. ドキュメントを作成する - 複雑なクエリにはコメントを追加します

マイグレーションの場合：

1. 変更を計画する - 影響を受けるテーブルと依存関係を特定します
2. マイグレーションを作成する - up と down の両方のマイグレーションを作成します
3. コピーでテストする - まず開発データベースでテストします
4. バックアップする - マイグレーションの実行前に必ずバックアップします
5. 実行する - トラフィックの少ない時間帯にマイグレーションを実行します
6. 検証する - マイグレーション後にデータの整合性を確認します