sql-ops

SQL パターン

よく使われる SQL パターンのクイックリファレンスです。

CTE（共通テーブル式）

WITH active_users AS (
    SELECT id, name, email
    FROM users
    WHERE status = 'active'
)
SELECT * FROM active_users WHERE created_at > '2024-01-01';

連鎖する CTE

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

ウィンドウ関数（クイックリファレンス）

関数	用途
ROW_NUMBER()	重複のない連番を付与
RANK()	順位を付与し、同順位の後に順位を飛ばします（1、2、2、4）
DENSE_RANK()	順位を付与し、同順位の後も順位を飛ばしません（1、2、2、3）
LAG(col, n)	前の行の値
LEAD(col, n)	次の行の値
SUM() OVER	累計
AVG() OVER	移動平均

SELECT
    date,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY date) as prev_day,
    SUM(revenue) OVER (ORDER BY date) as running_total
FROM daily_sales;

JOIN リファレンス

種類	返される行
INNER JOIN	一致する行のみ
LEFT JOIN	左側のすべての行と、一致する右側の行
RIGHT JOIN	右側のすべての行と、一致する左側の行
FULL JOIN	すべての行。一致しない場合は NULL

ページネーション

-- OFFSET/LIMIT (simple, slow for large offsets)
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 40;

-- Keyset (fast, scalable)
SELECT * FROM products WHERE id > 42 ORDER BY id LIMIT 20;

トランザクション分離レベル

レベル	ダーティリード	非反復読み取り	ファントムリード	用途
READ UNCOMMITTED	発生する可能性あり	発生する可能性あり	発生する可能性あり	まれ（PostgreSQL では READ COMMITTED として扱われます）
READ COMMITTED	なし	発生する可能性あり	発生する可能性あり	ほとんどのデータベースのデフォルト
REPEATABLE READ	なし	なし	発生する可能性あり*	複数のステートメントにわたる一貫した読み取り
SERIALIZABLE	なし	なし	なし	重要な不変条件（シリアライズ失敗時は再試行）

*PostgreSQL の REPEATABLE READ は、スナップショット分離によってファントムリードも防ぎます。

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- ... critical operations ...
COMMIT;  -- be prepared to retry on serialization failure

デフォルトの READ COMMITTED を全体に適用し、ロジック上必要な場合に限り、トランザクション単位でレベルを引き上げてください。

インデックスのクイックリファレンス

インデックスの種類	最適な用途
B-tree	範囲検索、ORDER BY
Hash	完全一致のみ
GIN	配列、JSONB、全文検索
カバリング	テーブル検索の回避

アンチパターン

間違い	修正方法
SELECT *	列を明示的に指定する
WHERE YEAR(date) = 2024	WHERE date >= '2024-01-01'
NULL を含む NOT IN	NOT EXISTS を使う
N+1 クエリ	JOIN またはバッチ処理を使う

追加リソース

詳しいパターンについては、以下を読み込んでください。

• ./references/window-functions.md - ウィンドウ関数パターンの完全なリファレンス
• ./references/indexing-strategies.md - インデックスの種類、カバリングインデックス、最適化