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

  • ---

    LINEで送る
  • -

    ブックマーク
  • -

    pocket
  • -

    rss
mysql, mariaDB logo
イラストダウンロードサイト【イラストAC】
の画像をもとに加工しています。

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

他のテーブルのクエリ結果に日付を追加したりするのに使います。

(自分がよく忘れるので備忘録でもある。)

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

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

SELECT * FROM test_date;
dateval1val2
2019/11/0810test1
2019/12/0920test2
2020/02/1030test3
2020/06/1140test4
2021/03/1250test5
2021/09/1360test6

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

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

年月日リスト

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

日付リスト作成のSQL
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')
date
2019-11-08
2019-11-09
2019-11-10
 ・
 ・
 ・
2021-09-12
2021-09-13

年月リスト

さっきの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'に変えただけです。

date
2019-11
2019-12
2020-01
 ・
 ・
 ・
2021-08
2021-09

年リスト

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

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

週リスト

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

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

(月曜日を表示する。)

min_t.date - WEEKDAY(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
date
2019-11-04 (45)
2019-11-11 (46)
2019-11-18 (47)
 ・
 ・
 ・
2019-12-23 (52)
2019-12-30 (53)
2020-01-06 (02)
 ・
 ・
 ・
2021-08-30 (35)
2021-09-06 (36)

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

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

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

日付リスト作成のSQL
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
min_t.date日付リストの開始日付。
データ内の一番古い日付。
(最小値)
max_t.date日付リストの終了日付。
データ内の一番新しい日付。
(最大値)
@num:=@num+1日付リストを作るのに必要なシーケンス番号

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

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

なぜinformation_schema.COLUMNSを使う?

日付リスト作成のSQL
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レコード)

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も実行時間は速いですが、一番いいのは

最小限の列(column)数のひとつのテーブルを使う。

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

日付リスト件数用テーブル
CREATE TABLE `tmp_date_list` (
  `val` int(10) DEFAULT NULL
)
val
1
2


10000
変更後
(SELECT * FROM tmp_date_list) t1,

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

(カスタマイズもかんたん。)

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

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

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

SELECT MIN(date) AS date FROM test_date
date
2019/11/08

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

SELECT * 
FROM
  (SELECT table_name FROM information_schema.COLUMNS) AS t1,
  (SELECT MIN(date) AS date FROM test_date) AS min_t
table_namedate
test_date2019/11/08
test_date2019/11/08
test_date2019/11/08




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

(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
table_namedate@num:=@num+1
test_date2019/11/080
test_date2019/11/081
test_date2019/11/082






wp_wpgdprc_log2019/11/083220

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

日付リスト作成

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

table_namedate@num:=@num+1
test_date2019/11/080
test_date2019/11/081
test_date2019/11/082






wp_wpgdprc_log2019/11/083220

(使うテーブル)

日付を作る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
dateseq
2019/11/080
2019/11/091
2019/11/102




2028/09/013220

(interval day。シーケンス番号は1日+)

週別

dateseq
2019-11-040
2019-11-111
2019-11-182




2081-07-213220

(interval week。シーケンス番号は1週+)

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

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

年月

dateseq
2019/11/080
2019/12/081
2020/01/082




2288/03/083220

(interval month。シーケンス番号は1ヶ月+)

dateseq
2019/11/080
2020/11/081
2021/11/082




5239/11/083220

(interval year。シーケンス番号は1年+)

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

シーケンス番号を0から始めるのは、開始日付を入れるためです。

シーケンス番号が1から始まると、『開始日付の1日後(や1ヶ月後など)』が最初の日付になります。

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

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

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

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だけで日付リストはできるのですがひとつ問題があります。

WHERE句はエイリアス(別名。AS句)が使えない。

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

のdateが使えません。

SQLエラー
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句にもっていって、そこで終了日付の条件をつけます。

最初のSQL
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の完成です。

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

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

最初の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,...)も変数にすればさらに使い回せる。)

インターバルと書式の変更で、時・分・秒にも対応できます。

前のページ
MySQL/MariaDB 検索クエリの結果に連番をつける方法
MySQL/MariaDB 検索クエリの結果にSQLで連続した日付をつける方法
次のページ
SNSでも記事を配信しています。
コメントを残す

*

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください

top
この記事を気に入ったらぜひシェアも!!