📝
tips : SQL でデカルト積のテストデータを作成する

2025/10/18に公開
 対象読者SQL を多少実務で使ったことがある人
≒ SQL にあまり詳しくない人

テストデータを効率よく作成したい人
SQL でデカルト積の組み合わせでデータ作れるんだ、と最近気づいたので tips として共有

 こんな課題ないですか例えば飲食店サイトの検索 API のテストを記述する場合で考えてみます。
簡単な「不動産サイト」のつもりで、下記のような ERD で考えてみます
PROPERTY : 物件マスターテーブル について、検索 API を実装し、テストしたいとします。

テストするには、テスト対象データが十分に豊かでないといけません。
ここでいう十分に豊かとは
データのパターンが網羅されているか
です。
今回であれば、物件情報は
位置情報 * 構造情報 * 販売ステータス * ペット可否
の組み合わせで決まります。ここで、それぞれの要素のパターン数を
位置情報 : 大阪・東京・福岡 の3つ
構造情報 : 木造・鉄筋 の2つ
販売ステータス : 販売中・商談中・確約済み　の3つ
ペット可否 : 可・不可 の２つ
とすると、組み合わせ数は 3 * 2 * 3 * 2 = 36 通りになります。
これを手書きでは書いてらんないですね
この組み合わせのデータを、SQL で簡単に生成できます。

 実現例 (postgresql)1. テーブル作成
-- 1. 位置情報マスタ
CREATE TABLE LOCATION (
    location_id SERIAL PRIMARY KEY,
    prefecture VARCHAR(50) NOT NULL
);

-- 2. 構造情報マスタ
CREATE TABLE STRUCTURE (
    structure_id SERIAL PRIMARY KEY,
    structure_name VARCHAR(50) NOT NULL,
    abbreviation VARCHAR(10)
);

-- 3. 販売ステータスマスタ
CREATE TABLE SALES_STATUS (
    status_id SERIAL PRIMARY KEY,
    status_name VARCHAR(50) NOT NULL,
    display_order INTEGER
);

-- 4. 物件マスターテーブル
CREATE TABLE PROPERTY (
    property_id SERIAL PRIMARY KEY,
    location_id INTEGER NOT NULL REFERENCES LOCATION(location_id),
    structure_id INTEGER NOT NULL REFERENCES STRUCTURE(structure_id),
    status_id INTEGER NOT NULL REFERENCES SALES_STATUS(status_id),
    name VARCHAR(255) NOT NULL,
    is_pet_allowed BOOLEAN NOT NULL
);
2. 各属性のマスタデータ作成
-- LOCATION: 位置情報 (3パターン)
INSERT INTO LOCATION (location_id, prefecture) VALUES
(1, '大阪'),
(2, '東京'),
(3, '福岡');

-- STRUCTURE: 構造情報 (2パターン)
INSERT INTO STRUCTURE (structure_id, structure_name, abbreviation) VALUES
(1, '木造', 'M'),
(2, '鉄筋', 'R');

-- SALES_STATUS: 販売ステータス (3パターン)
INSERT INTO SALES_STATUS (status_id, status_name, display_order) VALUES
(1, '販売中', 1),
(2, '商談中', 2),
(3, '確約済み', 3);
3. デカルト積でマスターデータ作成
-- PROPERTY テーブルに全36パターンのテストデータを挿入
INSERT INTO PROPERTY (
    location_id,
    structure_id,
    status_id,
    is_pet_allowed,
    name
)
SELECT
    l.location_id,
    s.structure_id,
    ss.status_id,
    pet.is_pet_allowed,
    -- テストで判別しやすいように、組み合わせを反映した物件名を生成
    l.prefecture || 'の' || s.structure_name || ' (' || 
    CASE WHEN pet.is_pet_allowed THEN 'ペット可' ELSE 'ペット不可' END || 
    '、' || ss.status_name || ')' AS name
FROM
    LOCATION l
CROSS JOIN
    STRUCTURE s
CROSS JOIN
    SALES_STATUS ss
CROSS JOIN
    -- ペット可否のフラグを一時的に作成
    (VALUES (TRUE), (FALSE)) AS pet(is_pet_allowed);
動作確認用
select property_id, name from property;
 property_id |                    name                     
-------------+--------------------------------------------------
           1 | 大阪の木造 (ペット可、販売中)
           2 | 東京の木造 (ペット可、販売中)
           3 | 福岡の木造 (ペット可、販売中)
...
          34 | 大阪の鉄筋 (ペット不可、確約済み)
          35 | 東京の鉄筋 (ペット不可、確約済み)
          36 | 福岡の鉄筋 (ペット不可、確約済み)

 SQL だけで実現することの嬉しいポイントSQL だけで実現する方法を提示しましたが、もちろんプログラミング言語を用いて SQL を複数回投げるスクリプトを書いて実現することもできます。

ORM が優秀なので、スクリプトで実現してもメンテはそんなに苦しくならないでしょう。
ただ、ダミーデータを1度の SQL で実現できるということは、
それだけ通信回数が減るので処理効率が高くなります。ダミーデータ作成は CI などで頻繁に実行するので、処理時間が短いに越したことはないですよね。
実行環境に依存しなくなります。アプリケーションサーバーが node だろうが java だろうが動きます
データベース特有の機能も活用しやすい
ですね！

 まとめ効率よくテスト用のダミーデータを作成しましょう

他にも「ダミーデータ作成する上で、こんな方法もいいよ！」などあればコメントください！
Let's 組み合わせ爆発
対象読者

こんな課題ないですか

実現例 (postgresql)

SQL だけで実現することの嬉しいポイント

まとめ

Discussion
