MySQLで連続した日付を作成する方法

2019-07-23

MySQLで連続した日付を作成する方法

MySQLで連続した日付を作成する方法。
時系列データを扱う際に、休日や非稼働日はデータが欠損していることがある。
そのようなデータ欠損の位置を確認する為、連続した日付データを結合してみたくなる。
今回はその『連番の日付』をMySQLで作るやり方を紹介する。

鍵は変数

連続した日付を作る為の鍵は @ で始まるMySQL変数である。
特定の日付からスタートして、次の行では +1。その次の行では +2 ... と加算して連続した日付を作成する。
データは使わないが取得する行数を確保するために information_schema.COLUMNS をfromで指定する。

@seq_no という変数を用意

１つ目のSELECT文で変数を初期化する

２つ目のSELECT文で初期化した変数のインクリメントの処理を行う

その際にデータ件数を確保するために、MySQLで最初に用意されているinformation_schema.COLUMNSを参照する（中身のデータは実際には使わない）

LIMITで取得したいデータ件数を指定する

MySQLで連番の仮想表を作る - Hack Your Design!

実際にSQLを書くと以下のようになる。

SELECT '2019-01-01' + INTERVAL seq_no DAY AS date
FROM (
    SELECT @seq_no := 0 AS seq_no
    UNION
    SELECT @seq_no := @seq_no + 1 AS seq_no FROM information_schema.COLUMNS
    LIMIT 10
) tmp

結果

date
2019-01-01
2019-01-02
2019-01-03
2019-01-04
2019-01-05
2019-01-06
2019-01-07
2019-01-08
2019-01-09
2019-01-10

件数制限に注意

information_schema.COLUMNS をfromに指定した理由は、先の説明で「行数を確保する為」としていた。
つまり行数を確保できれば他のテーブルを指定しても良い。

逆に言えば大量の行数が必要な場合は information_schema.COLUMNS では足りないので注意。

以下のSQLでは LIMIT 1000000 で100万行指定しているが、 information_schema.COLUMNS は1940行しか返せないので行数は1940となっている。

select count(1) FROM (
    SELECT '2019-01-01' + INTERVAL seq_no DAY AS date
    FROM (
        SELECT @seq_no := 0 AS seq_no
        UNION
        SELECT @seq_no := @seq_no + 1 AS seq_no FROM information_schema.COLUMNS
        LIMIT 1000000
    ) tmp
) x

結果

count(1)
1940

information_schema.COLUMNS の代わりにレコード数の多いテーブルを指定することでより多くの連続した日付を取得できる。

select count(1) FROM (
    SELECT '2019-01-01' + INTERVAL seq_no DAY AS date
    FROM (
        SELECT @seq_no := 0 AS seq_no
        UNION
        SELECT @seq_no := @seq_no + 1 AS seq_no FROM hoge
        LIMIT 1000000
    ) tmp
) x

結果