MySQLのWITH句 使用例

はじめに

MySQLのWITH句を使用する機会があったので、使用例をご紹介致します。
※WITH句はMySQL8.0からの新機能となります。

環境

MySQL:8.0.29

やりたいこと

旧テーブルから新テーブルにデータ移行する。
旧テーブルは日付をFrom、Toでそれぞれ保持している。
新テーブルではFrom-Toの日付範囲を日単位に分割して保持したい。

以下のイメージです。

旧テーブル

SELECT sample_id, sample_date_from, sample_date_to FROM migration_source; 
+-----------+------------------+----------------+ 
| sample_id | sample_date_from | sample_date_to | 
+-----------+------------------+----------------+ 
|         1 | 2022-06-13       | 2022-06-17     | 
|         2 | 2022-06-20       | 2022-06-21     | 
|         3 | 2022-06-22       | 2022-06-22     | 
+-----------+------------------+----------------+

新テーブル

SELECT sample_id, sample_date FROM migration_target;
+-----------+-------------+
| sample_id | sample_date |
+-----------+-------------+
|         1 | 2022-06-13  | 
|         1 | 2022-06-14  | 
|         1 | 2022-06-15  | 
|         1 | 2022-06-16  | 
|         1 | 2022-06-17  | 
|         2 | 2022-06-20  | 
|         2 | 2022-06-21  | 
|         3 | 2022-06-22  | 
+-----------+-------------+

旧テーブルのFrom、Toの日付範囲を日単位に分割

旧テーブルのFrom、Toの日付範囲を日単位に分割した一時テーブルをWITH句で作成しています。
WITH句で作成した一時テーブルをSELECTすると、日単位に分割した結果が取得できます。
再帰のあるWITH句のため、RECURSIVEキーワードを使用しています。

WITH RECURSIVE with_migration_source_split AS (
    SELECT
        sample_id
        , sample_date_from
        , sample_date_to
    FROM
        migration_source

    UNION ALL
    
    SELECT
        sample_id
        , DATE_ADD(sample_date_from, INTERVAL 1 DAY) AS sample_date_from
        , sample_date_to
    FROM
        with_migration_source_split
    WHERE
        DATE_ADD(sample_date_from, INTERVAL 1 DAY) <= sample_date_to
)

SELECT
    sample_id
    , sample_date_from AS sample_date
FROM
    with_migration_source_split
ORDER BY
    sample_id;

+-----------+-------------+
| sample_id | sample_date |
+-----------+-------------+
|         1 | 2022-06-13  |
|         1 | 2022-06-14  |
|         1 | 2022-06-15  |
|         1 | 2022-06-16  |
|         1 | 2022-06-17  |
|         2 | 2022-06-20  |
|         2 | 2022-06-21  |
|         3 | 2022-06-22  |
+-----------+-------------+

新テーブルへINSERT

先ほどのWITH句を使用したSQL結果を新テーブルにINSERTします。

INSERT INTO migration_target (
    sample_id
    , sample_date
)

WITH RECURSIVE with_migration_source_split AS (
    SELECT
        sample_id
        , sample_date_from
        , sample_date_to
    FROM
        migration_source

    UNION ALL
    
    SELECT
        sample_id
        , DATE_ADD(sample_date_from, INTERVAL 1 DAY) AS sample_date_from
        , sample_date_to
    FROM
        with_migration_source_split
    WHERE
        DATE_ADD(sample_date_from, INTERVAL 1 DAY) <= sample_date_to
)

SELECT
    sample_id
    , sample_date_from AS sample_date
FROM
    with_migration_source_split
ORDER BY
    sample_id;

おわりに

WITH句の使用例でした。
別途python等を使用してデータ移行することも可能なはずですが、SQLで完結する対応となります。


--------------------------
システム開発のご要望・ご相談はこちらから

コメントを残す

メールアドレスが公開されることはありません。