sql

SQL 開発

効率的な SQL クエリを作成し、スキーマを設計します。

使用する場面

• 複雑なクエリの作成
• クエリの最適化
• スキーマ設計
• インデックス戦略
• マイグレーション計画

クエリのパターン

ウィンドウ関数

-- Running totals
SELECT
    date,
    amount,
    SUM(amount) OVER (ORDER BY date) as running_total,
    AVG(amount) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as moving_avg_7d
FROM transactions;

-- Ranking
SELECT
    name,
    score,
    RANK() OVER (ORDER BY score DESC) as rank,
    DENSE_RANK() OVER (ORDER BY score DESC) as dense_rank,
    ROW_NUMBER() OVER (ORDER BY score DESC) as row_num
FROM players;

-- Partition by category
SELECT
    category,
    product,
    sales,
    sales * 100.0 / SUM(sales) OVER (PARTITION BY category) as pct_of_category
FROM products;

CTE（共通テーブル式）

WITH
monthly_sales AS (
    SELECT
        DATE_TRUNC('month', order_date) as month,
        SUM(amount) as total
    FROM orders
    GROUP BY 1
),
growth AS (
    SELECT
        month,
        total,
        LAG(total) OVER (ORDER BY month) as prev_month,
        (total - LAG(total) OVER (ORDER BY month)) / NULLIF(LAG(total) OVER (ORDER BY month), 0) * 100 as growth_pct
    FROM monthly_sales
)
SELECT * FROM growth WHERE growth_pct < 0;

再帰 CTE

-- Hierarchical data (org chart, categories)
WITH RECURSIVE subordinates AS (
    SELECT id, name, manager_id, 1 as level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT e.id, e.name, e.manager_id, s.level + 1
    FROM employees e
    JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates ORDER BY level, name;

クエリの最適化

インデックス戦略

-- Composite index for common queries
CREATE INDEX idx_orders_user_date ON orders(user_id, order_date DESC);

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

-- Check query plan
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123;

よくある問題

問題	症状	解決策
インデックスがない	Seq Scan	適切なインデックスを追加する
N+1 クエリ	小さなアクセスが多数	JOIN またはバッチ処理を使用する
SELECT *	低速化とメモリ消費	必要な列のみを選択する
LIMIT がない	大量の結果	ページネーションを追加する
列に関数を適用	インデックスが使われない	条件を書き換える

スキーマ設計

-- Normalized schema
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    total DECIMAL(10,2) NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_orders_user ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status) WHERE status != 'completed';

例

入力：「この遅いクエリを最適化して」 **アクション：**EXPLAIN を実行し、ボトルネックを特定して、インデックスを追加するかクエリを書き換えます

入力：「売上上位 10 社の顧客を取得して」 **アクション：**適切な結合、並べ替え、上限を指定して集計クエリを作成します