🔎
SQLでテストクエリを書いてみよう

2025/12/11に公開
 はじめに筆者はSQLのレビュワーとしての業務経験があります。レビュワーといっても、エンジニアではないビジネス職のメンバーが業務オペレーションの一つとして作成するものや、分析に用いるクエリの品質を担保するという役割です。
その中でテストクエリを記述してきましたので、ナレッジを共有させていただきます。

 SQLレビューの4つの観点SQLレビューでは、以下の4つの観点でチェックを行ってきました。

 1. 要件適合性そもそも求められている結果を返すか
ビジネス要件との整合性

 2. 正確性データの正しさ（結合漏れ、計算ミス、境界値など）
エッジケースへの対応

 3. パフォーマンスコスト効率（クラウドDWHの場合）

 4. 可読性メンテナンス性
チームでの理解しやすさ
本記事では特に「正確性」に焦点を当て、実務で使っている検証SQLパターンを紹介します。

 実践：検証SQLパターン集レビューで「正確性」を担保するために、検証用のSQLを書いて実際にデータを確認することが重要です。以下、実務で頻繁に使うパターンを紹介します。

 テストSQLの基本方針期待値と実際の値を比較する検証SQLを書く
-- テストの基本パターン
SELECT
  actual_value,
  expected_value,
  actual_value = expected_value AS is_valid
FROM result_table
WHERE actual_value != expected_value;  -- 差分があるレコードのみ抽出
差分があればテスト失敗、差分がなければテスト成功という明確な基準でSQLの正しさを検証します。

 パターン1：結合テスト - NULLの発生を検証するLEFT JOINを使った場合、想定外のNULLが発生していないかをテストします。
-- 結合漏れ検証テスト
WITH joined_data AS (
  SELECT
    u.user_id,
    u.registered_date,
    o.first_order_date,
    CASE 
      WHEN o.user_id IS NULL THEN 1 
      ELSE 0 
    END AS is_null
  FROM users u
  LEFT JOIN orders o ON u.user_id = o.user_id
),
test_result AS (
  SELECT
    COUNT(*) AS null_count,
    -- 期待値：登録から1年以上経過したユーザーの約20%は未注文と想定
    FLOOR(COUNT(*) * 0.2 * 0.8) AS expected_min,  -- 期待値の下限（-20%許容）
    FLOOR(COUNT(*) * 0.2 * 1.2) AS expected_max   -- 期待値の上限（+20%許容）
  FROM joined_data
  WHERE is_null = 1
    AND DATE_DIFF(CURRENT_DATE(), registered_date, DAY) >= 365
)
SELECT
  null_count AS actual,
  expected_min,
  expected_max,
  CASE
    WHEN null_count BETWEEN expected_min AND expected_max THEN 'PASS'
    ELSE 'FAIL'
  END AS test_status
FROM test_result;
テストのポイント
NULLの発生件数が想定範囲内か
ビジネスロジックとして妥当な結合結果か

 パターン2：計算ロジックテスト - 複雑な計算式を検証する複雑な計算式が仕様通りに実装されているかをテストします。
-- 計算ロジック検証テスト
WITH calculation_test AS (
  SELECT
    user_id,
    profit,
    -profit AS loss,
    FLOOR(total_deposit * 0.2) AS twenty_percent,
    -- 期待値：「損失額」「預金残高の20%」「500円」のうち最小値
    LEAST(-profit, FLOOR(total_deposit * 0.2), 500) AS expected_compensation,
    actual_compensation
  FROM result_table
)
SELECT
  user_id,
  expected_compensation,
  actual_compensation,
  expected_compensation - actual_compensation AS diff,
  CASE
    WHEN expected_compensation = actual_compensation THEN 'PASS'
    ELSE 'FAIL'
  END AS test_status
FROM calculation_test
WHERE expected_compensation != actual_compensation;  -- 差分があるレコードのみ
テストのポイント
期待値と実際の値の差分がゼロか
計算の各ステップを可視化して検証
差分があるレコードが1件でも返ってきたら、計算ロジックに問題があることを意味します。

 パターン3：データ鮮度テスト - テーブルの更新状況を検証する使用しているテーブルが最新データで更新されているかをテストします。
-- テーブル更新状況検証テスト
WITH table_freshness AS (
  SELECT
    'table_a' as table_name,
    MAX(updated_at) AS last_update_datetime,
    DATE_DIFF(CURRENT_DATE(), DATE(MAX(updated_at)), DAY) AS days_since_last_update,
    1 AS expected_max_days  -- 期待値：1日以内の更新
  FROM `project.dataset.table_a`

  UNION ALL

  SELECT
    'table_b' as table_name,
    MAX(updated_at) AS last_update_datetime,
    DATE_DIFF(CURRENT_DATE(), DATE(MAX(updated_at)), DAY) AS days_since_last_update,
    1 AS expected_max_days
  FROM `project.dataset.table_b`
)
SELECT
  table_name,
  last_update_datetime,
  days_since_last_update AS actual_days,
  expected_max_days,
  CASE
    WHEN days_since_last_update <= expected_max_days THEN 'PASS'
    ELSE 'FAIL'
  END AS test_status
FROM table_freshness
WHERE days_since_last_update > expected_max_days;  -- 期待値を超えているテーブルのみ
テストのポイント
最終更新日時が想定範囲内か
日次更新が止まっていないか
テストが失敗する場合、データパイプラインに問題がある可能性を示唆します。

 パターン4：条件網羅性テスト - WHERE句の条件を検証するWHERE句の条件が意図通りに機能し、論理的に整合しているかをテストします。
-- 条件の整合性検証テスト
WITH category_check AS (
  SELECT
    category,
    sub_category,
    COUNT(*) AS cnt,
    CASE
      -- 期待値：electronicsならsmartphone/tabletのみ許容
      WHEN category = 'electronics' AND sub_category IN ('smartphone', 'tablet') THEN 'VALID'
      -- 期待値：appliancesならrefrigerator/washing_machineのみ許容
      WHEN category = 'appliances' AND sub_category IN ('refrigerator', 'washing_machine') THEN 'VALID'
      ELSE 'INVALID'
    END AS validation_status
  FROM `project.dataset.product_master`
  WHERE update_date >= '2025-01-01'
    AND category IN ('electronics', 'appliances')
    AND (sub_category = 'smartphone' OR sub_category = 'tablet')
)
SELECT
  category,
  sub_category,
  cnt,
  validation_status,
  CASE
    WHEN validation_status = 'VALID' THEN 'PASS'
    ELSE 'FAIL'
  END AS test_status
FROM category_check
WHERE validation_status = 'INVALID';  -- 不整合なデータのみ
テストのポイント
複数の条件の組み合わせが論理的に正しいか
想定外のデータパターンが混入していないか
マスタデータの不整合を早期発見できます。

 パターン5：境界値テスト - 閾値判定を検証する金額や数量の閾値判定が正しく機能しているかをテストします。
-- 境界値検証テスト
WITH boundary_test AS (
  SELECT
    o.order_id,
    o.user_id,
    o.total_amount,
    o.shipping_fee,
    o.point_granted,
    -- 期待値の計算
    CASE WHEN o.total_amount >= 1000 THEN 0 ELSE 500 END AS expected_shipping_fee,
    CASE WHEN o.total_amount >= 5000 THEN FLOOR(o.total_amount * 0.05) ELSE 0 END AS expected_point,
    CASE
      WHEN o.total_amount = 999 THEN 'boundary_999'
      WHEN o.total_amount = 1000 THEN 'boundary_1000'
      WHEN o.total_amount = 1001 THEN 'boundary_1001'
      WHEN o.total_amount = 4999 THEN 'boundary_4999'
      WHEN o.total_amount = 5000 THEN 'boundary_5000'
      WHEN o.total_amount = 5001 THEN 'boundary_5001'
      ELSE NULL
    END AS boundary_type
  FROM `project.dataset.orders` o
  WHERE o.order_date >= '2025-11-01'
    AND o.order_date < '2025-12-01'
    AND o.status = 'COMPLETED'
)
SELECT
  boundary_type,
  total_amount,
  expected_shipping_fee,
  shipping_fee AS actual_shipping_fee,
  expected_point,
  point_granted AS actual_point,
  CASE
    WHEN shipping_fee = expected_shipping_fee AND point_granted = expected_point THEN 'PASS'
    ELSE 'FAIL'
  END AS test_status
FROM boundary_test
WHERE boundary_type IS NOT NULL
  AND (shipping_fee != expected_shipping_fee OR point_granted != expected_point);  -- 差分があるもののみ
テストのポイント
閾値の境界（999/1000、4999/5000など）で正しく判定されているか
「以上」「より大きい」などの条件が仕様通りか
境界値での挙動を実データで確認することで、仕様との齟齬を早期発見できます。

 テストパターンの体系化とLLMフィードバックループ
 テストパターンをナレッジ化するこれらのテストパターンを単にレビュー時に使うだけでなく、Markdownドキュメントとして体系化し、LLMを活用したフィードバックループに組み込むことで継続的な改善を実現することができます。
1. レビューで新たな問題パターン発見
   ↓
2. 検証SQLテストをMarkdownドキュメントに追記
   ↓
3. SQL生成時にClaude APIがテストパターンを参照
   ↓
4. テストを通過するSQLが生成される
   ↓
5. レビュー負荷軽減・同じミスが減る
   ↓
6. 新たなパターン発見（1に戻る）

 ナレッジ管理の実装Markdownによる文書化
## 境界値テスト

### 概要
閾値判定（1000円以上、5000円以上など）が正しく機能しているかを検証する

### テストSQL
[上記のSQLコード]

### チェックポイント
- 境界値±1での挙動確認
- 「以上」「より大きい」の条件が仕様通りか
Git管理
差分管理、変更履歴の追跡
チーム全体での更新・共有
レビューコメントとの紐付け
LLM連携
LLM APIに全ドキュメントを読み込ませる
依頼内容に応じて適切なテストパターンを適用したSQLを生成
生成されたSQLには検証SQLも含まれる

 SQL生成ツールの動作例依頼内容

「1000円以上で送料無料になる注文データを集計するSQLを書いて」
生成されるSQL
-- メインクエリ
SELECT
  order_date,
  COUNT(*) AS order_count,
  SUM(CASE WHEN total_amount >= 1000 THEN 1 ELSE 0 END) AS free_shipping_count
FROM `project.dataset.orders`
WHERE order_date >= '2025-11-01'
  AND status = 'COMPLETED'
GROUP BY order_date;

-- 検証SQL（境界値テスト）
WITH boundary_check AS (
  SELECT
    total_amount,
    shipping_fee,
    CASE WHEN total_amount >= 1000 THEN 0 ELSE 500 END AS expected_fee
  FROM `project.dataset.orders`
  WHERE order_date >= '2025-11-01'
    AND status = 'COMPLETED'
    AND total_amount IN (999, 1000, 1001)  -- 境界値のみ
)
SELECT *
FROM boundary_check
WHERE shipping_fee != expected_fee;  -- 差分があればFAIL
テストパターンがドキュメント化されているため、メインクエリとテストクエリがセットで生成されます。

 おわりに本記事で紹介したテスト駆動SQLの考え方は：
期待値と実際の値を明示的に比較する
差分があればFAIL、差分がなければPASSという明確な基準
テストパターンを体系化し、再利用可能にする
という汎用的な原則に基づいています。ぜひ自身の業務に合わせてカスタマイズし、活用してください。
また、LLMを活用したフィードバックループは、SQL生成に限らず様々な定型業務に応用できる考え方です。「人間が見つけた知見をLLMに還元し、次の作業に活かす」というサイクルを回すことで、継続的な業務改善が実現できます。
参考になった方は、ぜひいいねやコメントをいただけると嬉しいです。
はじめに

SQLレビューの4つの観点

1. 要件適合性

2. 正確性

3. パフォーマンス

4. 可読性

実践：検証SQLパターン集

テストSQLの基本方針

パターン1：結合テスト - NULLの発生を検証する

パターン2：計算ロジックテスト - 複雑な計算式を検証する

パターン3：データ鮮度テスト - テーブルの更新状況を検証する

パターン4：条件網羅性テスト - WHERE句の条件を検証する

パターン5：境界値テスト - 閾値判定を検証する

テストパターンの体系化とLLMフィードバックループ

テストパターンをナレッジ化する

ナレッジ管理の実装

SQL生成ツールの動作例

おわりに

Discussion
