DB

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

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

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


鍵は変数

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

  • @seq_no という変数を用意
  • 1つ目のSELECT文で変数を初期化する
  • 2つ目の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



結果

count(1)
65536


まとめ

  • MySQLで連続した日付を取得するには変数とインクリメントを用いる
  • 行数確保の為のテーブルは information_schema.COLUMNS でなくてもよい


KRSW

駆け出し機械学習エンジニア。機械学習、DB、WEBと浅く広い感じ。 Junior machine learning engineer. Not a specialist but a generalist who knows DB, WEB too.

役に立ったらシェアしてくれると励みになります。

にほんブログ村 IT技術ブログへ

-DB
-

Translate »

© 2021 ITips