SQLで指定した範囲の日付を穴埋めする（MySQL）

はじめに

日付の開始日と終了日だけで、間の日付を埋めたいときってありますよね？...はい、きっとあるはずです！

例えば「ある商品の売上トランザクションデータを持っており、これを〇年〇月〇日～〇年〇月〇日までの各日で何点ずつ売れたのか、売れなかった日も含めて一覧を作りたい」といった場合、このトランザクションデータとLEFT JOINできる日付のマスタがあると便利ですよね。

今回はSQLのみで、始点と終点の日付から、間の日付を穴埋めした日付データを作る方法を書いておきます。

検証バージョン

MySQL 5.6

DATE_ADD関数のINTERVALを使うことで実現する

基本的な考えはDATE_ADD関数を用いて+1ずつ日付を足していく

-- 例えば、DATE_ADD関数を用いて1日加算すると下記の結果が得られる
SELECT DATE_ADD('2020-10-01', INTERVAL 1 DAY) as DATE;
+--------------------+
| DATE               |
+--------------------+
| 2020-10-02         |
+--------------------+

これを繰り返し加算すればよいので、加算するための表を作成する

CREATE TABLE number (
    number smallint
)
;
-- [0-9]までのデータを入れる
insert into number values (0);
insert into number values (1);
insert into number values (2);
insert into number values (3);
insert into number values (4);
insert into number values (5);
insert into number values (6);
insert into number values (7);
insert into number values (8);
insert into number values (9);

上記の表からSELECTした結果をDATE_ADD関数のINTERVALにセットしていくSQLを書けばよい

SELECT DATE_ADD('2020-10-01', INTERVAL number DAY) as DATE FROM number;
+------------+
| DATE       |
+------------+
| 2020-10-01 |
| 2020-10-02 |
| 2020-10-03 |
| 2020-10-04 |
| 2020-10-05 |
| 2020-10-06 |
| 2020-10-07 |
| 2020-10-08 |
| 2020-10-09 |
| 2020-10-10 |
+------------+

最後にほしい日付の範囲に絞る

SELECT DATE_ADD('2020-10-01', INTERVAL number DAY) as DATE FROM number
WHERE  DATE_ADD('2020-10-01', INTERVAL number DAY) BETWEEN '2020-10-01' AND '2020-10-05';
+------------+
| DATE       |
+------------+
| 2020-10-01 |
| 2020-10-02 |
| 2020-10-03 |
| 2020-10-04 |
| 2020-10-05 |
+------------+

これで完成です！が、これだと日付の範囲が10日分しか作れないので、numberテーブルのデータを拡張するVIEWを作る

-- ひとまず[0-999]の1000行のVIEWを作成
CREATE VIEW vw_number AS
SELECT a.number + (b.number * 10) + (c.number * 100) AS number
FROM number a, number b, number c;

上記のVIEWには1000行のデータが入っているので3年近くは作れます（必要に応じて拡張してください）

SELECT DATE_ADD('2020-10-01', INTERVAL number DAY) as DATE FROM vw_number
WHERE  DATE_ADD('2020-10-01', INTERVAL number DAY) BETWEEN '2020-10-01' AND '2022-10-01';
+------------+
| DATE       |
+------------+
| 2020-10-01 |
| 2020-10-02 |
| 2020-10-03 |
| 2020-10-04 |

| 2022-09-28 |
| 2022-09-29 |
| 2022-09-30 |
| 2022-10-01 |
+------------+

その他、DATE_ADD関数のINTERVALを使っているので月単位に変更することも簡単

SELECT DATE_FORMAT(DATE_ADD('2020-10-01', INTERVAL number MONTH), '%Y-%m') as MONTH FROM vw_number
WHERE  DATE_ADD('2020-10-01', INTERVAL number MONTH) BETWEEN '2020-10-01' AND '2022-10-01';
+---------+
| MONTH   |
+---------+
| 2020-10 |
| 2020-11 |
| 2020-12 |
| 2021-01 |

| 2022-07 |
| 2022-08 |
| 2022-09 |
| 2022-10 |
+---------+

これならトランザクションデータにLEFT JOIN出来る日付マスタを簡単に作れますね！

おわり