はじめに
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で完結する対応となります。
