【SQL】指定した日時から日時までランダムなデータを挿入する

はじめに

SQL学習でABEMAのようなデータベースを作る際に、三週間分の番組スケジュールを挿入する必要がありました。その時に試してみたことを備忘録として残します。

ER図はこんな感じ

データの挿入をする箇所はprogram_schedulesです。

手動でやるなら

INSERT INTO program_schedules (channel_id, episode_id, start_time, end_time)
SELECT 
    channel.id AS channel_id,
    episode.id AS episode_id,
    start_time,
    DATE_ADD(start_time, INTERVAL duration MINUTE) AS end_time
FROM
    (
        SELECT '2024-03-01 00:00:00' AS start_time
        UNION ALL
        SELECT '2024-03-01 00:30:00'
        UNION ALL
        SELECT '2024-03-01 01:00:00'
        UNION ALL
        SELECT '2024-03-01 01:30:00'
        UNION ALL

番組表(program_schedules)の中にチャンネルと番組、開始時間と終了時間を3月1日から30分間隔で挿入しようと試みます。

UNION ALL演算子は、複数のSELECT文の結果を結合し、それぞれのSELECT文から返されるすべての行を含む結果セットを作成します。ここでのUNION ALLは、特定の放送開始時間を表す一連の固定値を生成するために使っています。

INTERVAL duration MINUTE は、duration の値を分単位の時間間隔として指定しています。
INTERVAL の後に指定する時間単位には、SECOND, MINUTE, HOUR, DAY, MONTH, YEAR などがあり、状況に応じて使い分けます。

とはいえ量が半端ないので、他の方法を探しました。

再帰的なCTEを使う

-- program_schedulesテーブル
-- 一時テーブルを作成
CREATE TEMPORARY TABLE temp_datetime_range (
    start_time DATETIME
);

-- 再帰的なCTEで日時を生成し、一時テーブルに挿入
INSERT INTO temp_datetime_range (start_time)
WITH RECURSIVE datetime_range AS (
    SELECT '2024-03-01 00:00:00' AS start_time
    UNION ALL
    SELECT DATE_ADD(start_time, INTERVAL 30 MINUTE)
    FROM datetime_range
    WHERE start_time < '2024-03-22 00:00:00' -- 3週間分の日時を生成
)
SELECT start_time FROM datetime_range;

-- 全チャンネルで24時間放送されるようにデータを挿入
INSERT INTO program_schedules (channel_id, episode_id, start_time, end_time)
SELECT 
    channel.id AS channel_id,
    episode.id AS episode_id,
    temp_datetime_range.start_time,
    DATE_ADD(temp_datetime_range.start_time, INTERVAL episode.duration MINUTE) AS end_time
FROM
    temp_datetime_range
    CROSS JOIN (SELECT * FROM channels) AS channel
    JOIN (SELECT id, duration FROM episodes ORDER BY RAND()) AS episode
    ON MOD(TIMESTAMPDIFF(MINUTE, '2024-03-01 00:00:00', temp_datetime_range.start_time) DIV 30 + channel.id, (SELECT COUNT(*) FROM episodes)) = episode.id - 1;

-- 一時テーブルを削除
DROP TEMPORARY TABLE temp_datetime_range;

複雑なので一つ一つ説明していきます。

一時テーブルを作成

CREATE TEMPORARY TABLE temp_datetime_range (
    start_time DATETIME
);

temp_datetime_rangeという一時テーブルを作成し、start_timeというDATETIME型のカラムを持たせます。このテーブルは日時の範囲を一時的に格納するために使います。

再帰的なCTEで日時を生成し、一時テーブルに挿入

INSERT INTO temp_datetime_range (start_time)
WITH RECURSIVE datetime_range AS (
    SELECT '2024-03-01 00:00:00' AS start_time
    UNION ALL
    SELECT DATE_ADD(start_time, INTERVAL 30 MINUTE)
    FROM datetime_range
    WHERE start_time < '2024-03-22 00:00:00'
)
SELECT start_time FROM datetime_range;

次に、再帰的な共通テーブル式（CTE）を使用して、2024年3月1日から3週間分の日時を30分間隔で生成し、これらをtemp_datetime_rangeテーブルに挿入します。このCTEは、指定された開始日時から30分ごとに日時を加算し、指定した日時に達するまで繰り返します。

再帰的なCTE（共通テーブル式）は、自分自身を参照するCTEのことを指します。CTEは、SQLクエリ内で一時的に定義される名前付きの結果セットです。

通常のCTEは、クエリ内で一度だけ実行され、その結果を後続のクエリで使用することができます。しかし、再帰的なCTEは、自分自身を参照することで、複数回実行されます。

全チャンネルで24時間放送されるようにデータを挿入

INSERT INTO program_schedules (channel_id, episode_id, start_time, end_time)
SELECT 
    channel.id AS channel_id,
    episode.id AS episode_id,
    temp_datetime_range.start_time,
    DATE_ADD(temp_datetime_range.start_time, INTERVAL episode.duration MINUTE) AS end_time
FROM
    temp_datetime_range
    CROSS JOIN (SELECT * FROM channels) AS channel
    JOIN (SELECT id, duration FROM episodes ORDER BY RAND()) AS episode
    ON MOD(TIMESTAMPDIFF(MINUTE, '2024-03-01 00:00:00', temp_datetime_range.start_time) DIV 30 + channel.id, (SELECT COUNT(*) FROM episodes)) = episode.id - 1;

ここでは、生成した各放送開始時刻に対して、全チャンネルとエピソードを組み合わせてprogram_schedulesテーブルにスケジュール情報を挿入しています。CROSS JOINを用いて全チャンネルと組み合わせ、JOINを用いてランダムに選択されたエピソードと結合します。DATE_ADD関数で各エピソードの持続時間を加算し、放送終了時刻を計算。このステップにより、指定された期間中、各チャンネルで24時間番組が放送されるスケジュールを作ることができました。

一時テーブルの削除

DROP TEMPORARY TABLE temp_datetime_range;

最後に、temp_datetime_range一時テーブルを削除し、リソースを解放します。

今回、データを自動で挿入したい、という一つのことのために他の様々な書き方や機能を知ることができました。これからも沢山学び＆改善を日々続け、良い物を作れるようコツコツ頑張りたいと思います。