MySQL/MariaDB, 検索クエリのSQLで日付リストを作る方法

MySQL/MariaDBの検索クエリ（SQL）で日付リストを作る方法です。日次、週次、月次、年次に対応できます。（時・分・秒も）。

クエリ結果で日付が飛び飛びになっているのを連続した日付のデータにするのに使います。

（自分がよく忘れるので備忘録でもある。）

最初に答えからいって、あとでSQLの解説をします。

まずは、テスト用テーブルを用意します。今回はテーブルをひとつだけ使います。

SELECT * FROM test_date;

日付はすでに列（column）にありますが、飛び飛びです。データのない日付が欲しいこともあります。

今回はこのテーブルを使って連続した日付リストを作ります。

年月日リスト

日付リストは、年月日、年月、年、週ごとにそれぞれ作れます。まずは年月日のリストから。

SELECT date_t.date
FROM
  (SELECT
     DATE_FORMAT(DATE_ADD(min_t.date, interval @num:=@num+1 day), '%Y-%m-%d') AS date
   FROM
     (SELECT table_name FROM information_schema.COLUMNS) AS t1,
     (SELECT MIN(date) AS date FROM test_date) AS min_t,
     (SELECT @num:=-1) AS dummy
  ) AS date_t,
  (SELECT MAX(date) AS date FROM test_date) AS max_t
WHERE
  date_t.date <= DATE_FORMAT(max_t.date, '%Y-%m-%d')

年月リスト

さっきのSQLのSELECT句を変更します。

/* 変更前 */
DATE_FORMAT(DATE_ADD(min_t.date, interval @num:=@num+1 day), '%Y-%m-%d') AS date
/* 変更後 */
DATE_FORMAT(DATE_ADD(min_t.date, interval @num:=@num+1 month), '%Y-%m') AS date

日付リストのインターバルをmonthに、フォーマットを'%Y-%m'に変えただけです。

年リスト

同じようにSELECT句を変更して、日付リストのインターバルをyearに、フォーマットを'%Y'に変えます。

/* 変更後 */
DATE_FORMAT(DATE_ADD(min_t.date, interval @num:=@num+1 year), '%Y') AS date

週リスト

週のリストも作れます。同じようにSELECT句を変更して、日付リストのインターバルをweekに、フォーマットを'%Y-%m-%d (%u)'に変えます。

週で表示する日付は、1週間の初日を表示するためにmin_t.dateからあらかじめ週インデックスを引きました。

（月曜日を表示する。）

/* 変更後 */
DATE_FORMAT(DATE_ADD(min_t.date - WEEKDAY(min_t.date), interval @num:=@num+1 week), '%Y-%m-%d (%u)') AS date

この日付リストを他のテーブルと結合するだけで検索クエリ結果に日付を追加できます。

次はSQLを分解してひとつずつ見ていきます。

検索に必要なデータは3つだけ

SELECT date_t.date
FROM
  (SELECT
     DATE_FORMAT(DATE_ADD(min_t.date, interval @num:=@num+1 day), '%Y-%m-%d') AS date
   FROM
     (SELECT table_name FROM information_schema.COLUMNS) AS t1,
     (SELECT MIN(date) AS date FROM test_date) AS min_t,
     (SELECT @num:=-1) AS dummy
  ) AS date_t,
  (SELECT MAX(date) AS date FROM test_date) AS max_t
WHERE
  date_t.date <= DATE_FORMAT(max_t.date, '%Y-%m-%d')

SQLを見るといろんなことをしているように見えます。でも、このSQLで必要なものは『たった3つ』。そして、2つのSELECT句だけを見てください。

SELECT DATE_FORMAT(DATE_ADD(min_t.date, interval @num:=@num+1 day), '%Y-%m-%d') AS date

SELECT MAX(date) AS date FROM test_date

この3つを作ることだけに集中すれば、SQLが何をしているのか、かんたんに見えてきます。

（というか今回のSQLはこの3つを作るためにいろいろやっている。）

なぜinformation_schema.COLUMNSを使う？

SELECT date_t.date
FROM
  (SELECT
     DATE_FORMAT(DATE_ADD(min_t.date, interval @num:=@num+1 day), '%Y-%m-%d') AS date
   FROM
     (SELECT table_name FROM information_schema.COLUMNS) AS t1,
     (SELECT MIN(date) AS date FROM test_date) AS min_t,
     (SELECT @num:=-1) AS dummy
  ) AS date_t,
  (SELECT MAX(date) AS date FROM test_date) AS max_t
WHERE
  date_t.date <= DATE_FORMAT(max_t.date, '%Y-%m-%d')

SQLはFROM句から実行するので、まずはそこから見ましょう。

作成する日付リストの件数は、information_schema.COLUMNSテーブルの件数になります。

imfomationデータベースは、MySQL / MariaDBのDBMS（データベース・マネージメント・システム。DBソフトウェア）で使うデータベースで、もちろんCOLUMNSテーブルもソフトウェアが使うテーブルです。

なぜこれを使うか？ というと、ひとつは、

もうひとつは、

3,200なら年月日リストで9年弱分くらいです。

information_schemaはシステムのデータベースなのでそうかんたんに変更されません。

最初からボリュームがあって、変更がほぼないので使いやすいのでしょう。個人的には使いやすいなら他のテーブルでもいいと思います。

足りなくなったら？

足りなくなってきたらUNION ALLを使って増やします。

(SELECT table_name FROM information_schema.COLUMNS) t1,

(
SELECT table_name FROM information_schema.COLUMNS
UNION ALL
SELECT table_name FROM information_schema.COLUMNS
) t1,

まちがっても、クロス結合はいけません。

(
SELECT table_name
FROM
  information_schema.COLUMNS sub1,
  information_schema.COLUMNS sub2
) t1,

たしかに、レコード数を一気に増やせますが、SQLの実行に時間がかかってしまいます。

（確かめたところ30秒以上かかる。強制終了させたのでどこまでかかるか不明。）

一番パフォーマンスがいいのは専用テーブルを作る

UNION ALLも実行時間は速いですが、一番いいのは、

かんたんなのでテーブルを作っちゃいましょう。

CREATE TABLE `tmp_date_list` (
  `val` int(10) DEFAULT NULL
)

(SELECT * FROM tmp_date_list) t1,

10,000レコードあったとしてもこれが一番速く、ボリュームを増やしたければレコードを追加するだけです。

（カスタマイズもかんたん。）

これが一番のオススメです。

開始年月日に登録日付を使う

データベースに登録されている日付より前の日付をもつ必要はないので登録日付の一番古いものを開始日付に使います。

SELECT MIN(date) AS date FROM test_date

日付リストの件数に使うクエリと結合してこうなります。

SELECT * 
FROM
  (SELECT table_name FROM information_schema.COLUMNS) AS t1,
  (SELECT MIN(date) AS date FROM test_date) AS min_t

日付リストを作るクエリ結果に最小値の日付が追加されました。

（table_nameの中身はどうでもいい。）

シーケンス番号の初期化

SELECT @num:=-1 AS dummy

この文法まちがってない？

と思いがちですが、MySQL/MariaDBではOKです。

シーケンス番号についてはこちらにまとめました。

ここでのポイントは、@numを0から始めたいので初期値は-1です。

（あとで説明）

日付リストを作るクエリにシーケンス番号を追加します。

SELECT t1.*, min_t.*, @num:=@num+1
FROM
  (SELECT table_name FROM information_schema.COLUMNS) AS t1,
  (SELECT MIN(date) AS date FROM test_date) AS min_t,
  (SELECT @num:=-1) AS dummy

シーケンス番号の列（columns）が追加されました。

日付リスト作成

データが揃ったので日付リストを作ります。

日付を作るSELECT句はこうなります。

SELECT DATE_FORMAT(DATE_ADD(min_t.date, interval @num:=@num+1 day), '%Y-%m-%d') AS date

DATE_ADD()のパラメータで、開始日付と連番（シーケンス番号）を渡せば、残りのパラメータの間隔指定（day, week, month, year）で勝手に開始日付からリストを作ってくれます。

シーケンス番号と日付の関係を見てみましょう。

年月日

SELECT DATE_ADD(t2.date, interval t2.seq day) AS date, t2.seq AS seq
FROM
  (SELECT min_t.date AS date, @num:=@num+1 AS seq
   FROM
     (SELECT table_name FROM information_schema.COLUMNS) AS t1,
     (SELECT MIN(date) AS date FROM test_date) AS min_t,
     (SELECT @num:=-1) AS dummy
  ) AS t2

（interval day。シーケンス番号は1日+）

週別

（interval week。シーケンス番号は1週+）

週別リストは、週の最初の日（月曜日）にするため、WEEKDAY()を使って、開始日付から週番号を引きます。

SELECT DATE_ADD(t2.date - WEEKDAY(t2.date), interval t2.seq week) AS date, t2.seq AS seq

年月

（interval month。シーケンス番号は1ヶ月+）

年

（interval year。シーケンス番号は1年+）

指定したインターバルで日付リストができました。あとはDATE_ADD()の結果をDATE_FORMAT()で整形すればできあがりです。

ただひとつ問題が。年月、年リストは使うことはないだろう未来の日付が大量にできてしまいました。

これを条件をつけて消します。

終了年月日も登録日付を使う

SELECT
  DATE_FORMAT(DATE_ADD(min_t.date - WEEKDAY(min_t.date), interval @num:=@num+1 week), '%Y-%m-%d (%u)') AS date
FROM
  (SELECT table_name FROM information_schema.COLUMNS) AS t1,
  (SELECT MIN(date) AS date FROM test_date) AS min_t,
  (SELECT @num:=-1) AS dummy

このSQLだけで日付リストができるのですがひとつ問題が。

DATE_FORMAT(DATE_ADD(min_t.date - WEEKDAY(min_t.date), interval @num:=@num+1 week), '%Y-%m-%d (%u)') AS date

のdateが使えません。

SELECT
  DATE_FORMAT(DATE_ADD(min_t.date - WEEKDAY(min_t.date), interval @num:=@num+1 week), '%Y-%m-%d (%u)') AS date
FROM
  (SELECT table_name FROM information_schema.COLUMNS) AS t1,
  (SELECT MIN(date) AS date FROM test_date) AS min_t,
  (SELECT MAX(date) AS date FROM test_date) AS max_t,
  (SELECT @num:=-1) AS dummy
WHERE
  date <= DATE_FORMAT(max_t.date, '%Y-%m-%d');

ということで、日付リストを作るSQLをごっそりFROM句にもっていって、そこで終了日付の条件をつけます。

SELECT date_t.date
FROM
  /* 日付リストを作るSQL */
  (SELECT
     DATE_FORMAT(DATE_ADD(min_t.date, interval @num:=@num+1 day), '%Y-%m-%d') AS date
   FROM
     (SELECT table_name FROM information_schema.COLUMNS) AS t1,
     (SELECT MIN(date) AS date FROM test_date) AS min_t,
     (SELECT @num:=-1) AS dummy
  ) AS date_t,
  /* 登録データから最新日付取得 */
  (SELECT MAX(date) AS date FROM test_date) AS max_t
WHERE
  /* 終了日付でリストの範囲指定 */
  date_t.date <= DATE_FORMAT(max_t.date, '%Y-%m-%d')

これでSQLの完成です。

もっとシンプルにするなら

開始・終了日付をデータベースの登録日付を使わないのならもっとシンプルにできます。

SELECT date_t.date AS date
FROM
  (SELECT
     DATE_FORMAT(DATE_ADD('2019/01/01', interval @num:=@num+1 day), '%Y-%m-%d') AS date
   FROM
     (SELECT table_name FROM information_schema.COLUMNS) AS t1,
     (SELECT @num:=-1) AS dummy
  ) AS date_t
WHERE
  date_t.date <= DATE_FORMAT(CURRENT_DATE, '%Y-%m-%d')

開始・終了日付は、プログラムの変数で指定するなどして使い回せます。

（インターバル（day, week,...）も変数にすればさらに使い回せる。）