MySQL/MariaDB, 検索クエリの結果にSQLで連続した日付をつける方法

MySQL/MariaDBの検索クエリ結果にデータがないものに日付だけ追加して、残りはデータ無しにする連続した日付をつける方法です。

テーブルの列（column）は追加せずテーブル構成は一切変更しません。SQLだけで行います。

ビューを作るときにも使えます。

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

検索クエリ結果に期間内の日付が全部あるわけじゃない。

日付だけのレコード（その他の列は0とかNULL）も欲しい

といったことがあります。

（集計やグラフを作るときには。）

データベースではよくあること。（というかそういうもの。）

『登録されてないデータ』がデータベースにあるはずがない。『登録されてないデータを登録する』という訳のわからないことをしないかぎり。

でも、集計をしたりグラフを作るときなどは『登録されてないデータ』が必要になる。

たとえば

date	val1	val2
2019/11/08	10	test1
2019/12/09	20	test2
2020/02/10	30	test3
2020/06/11	40	test4
2021/03/12	50	test5
2021/09/13	60	test6

（test_dateテーブル）

の検索結果を

date	val1	val2
2019-11-08	10	test1
2019-11-09	0	NULL
2019-11-10	0	NULL
・・・	・・・	・・・
2019-12-09	20	test2
2019-12-10	0	NULL
・・・	・・・	・・・
2021-09-12	0	NULL
2021-09-13	60	test6

（SQLでなんやかんやした結果）

にしたり。これがSQLだけできます。テーブル構成は一切変えずに。

実行したSQLです。

SQLだけで日付のみ（あとは初期値）のレコード追加

SELECT date, SUM(val1) AS val1, val2
FROM
  (
     SELECT * FROM test_date

     UNION ALL

     SELECT date_t.date AS date, 0 AS val1, NULL AS val2
     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')
  ) AS fix_t
  
GROUP BY date
ORDER BY date

次からは、SQLを分解して解説します。あとは

『登録されてないデータ』に初期値を入れる。

いろいろな集計をやってみる。

を行います。

『データベースに登録されてないデータ』をプログラムで追加することもあるでしょう。

それが大変なので、『SQLで足しちゃいましょ。』というのが今回の目的です。

日付リストのSQL

今回は、『日付リストを生成するSQL』が中心です。これに変更を加えます。

日付リスト作成の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の解説はこちらをどうぞ。

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

日付リストにマージ

『登録されていないデータ』のレコードで、日付以外はNULLにするときはかんたんです。

日付リスト作成のSQLとマージしたいテーブルをLEFT JOINでつなぎます。

SQLイメージ

SELECT *
FROM
  (/* 日付リストのSQL */) AS date_list
  LEFT JOIN
  /* 対象テーブル */
  test_date
  ON date_list.date = test_date.date

じっさいのSQLです。

初期値なしのSQL

SELECT date_list.date AS date, val1, val2
FROM
  (
    SELECT date_t.date AS 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')
  ) AS date_list
  
  LEFT JOIN
  
  test_date
  
  ON date_list.date = test_date.date

ORDER BY date_list.date

date	val1	val2
2019-11-08	10	test1
2019-11-09	NULL	NULL
・・・	NULL	NULL
2019-12-09	20	test2
2019-12-10	NULL	NULL
・・・	NULL	NULL
2020-02-10	30	test3
2020-02-11	NULL	NULL
・・・	NULL	NULL
2020-06-11	40	test4
2020-06-12	NULL	NULL
・・・	NULL	NULL
2021-03-12	50	test5
2021-03-13	NULL	NULL
・・・	NULL	NULL
2021-09-13	60	test6

年次別で集計してみよう

このSQLでは日時データが作られます。これを、年次別データに変えてval1を集計してみましょう。

SQLはこうなります。

SELECT date_list.date AS date, SUM(val1) as val1
FROM
  (
    SELECT date_t.date AS date
    FROM
      (SELECT
         DATE_FORMAT(DATE_ADD(min_t.date, interval @num:=@num+1 year), '%Y') 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')
  ) AS date_list
  
  LEFT JOIN
  
  (SELECT  DATE_FORMAT(date, '%Y') AS date, val1, val2 FROM test_date) AS test_date2
  
  ON date_list.date = test_date2.date

GROUP BY date_list.date
ORDER BY date_list.date

/* 変更前 */
SELECT date_list.date AS date, val1, val2
/* 変更後 */
SELECT date_list.date AS date, SUM(val1) as val1

/* 変更前 */
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 year), '%Y') AS date

/* 変更前 */
LEFT JOIN
test_date
ON date_list.date = test_date.date
/* 変更後 */
LEFT JOIN
(SELECT  DATE_FORMAT(date, '%Y') AS date, val1, val2 FROM test_date) AS test_date2
ON date_list.date = test_date2.date

/* 変更前 */
ORDER BY date_list.date
/* 変更後 */
GROUP BY date_list.date
ORDER BY date_list.date

date	val1
2019	30
2020	70
2021	110

集計が合計（SUM()）なので、『登録されてないデータ』のレコードのval1（NULL）は集計対象から外せました。でも初期値が0以外のとき使えません。

また、SQLで初期値が入っている方がプログラムとしては楽です。データベースのクエリ結果をそのまま使えるので。

今度は初期値を入れるパターンを見てみましょう。

初期値を入れて集計できるSQL

初期値を入れる。

初期値を集計に加える。

このようにするには、日付リストにval1, val2の初期値が必要です。そして、日付リストとテーブルの結合がLEFT JOINでは初期値が集計対象になりません。

そこでSQLを次のように変更します。

SQLイメージ

SELECT date, sum(val1), val2
FROM
  (
    /* 対象テーブル */
    UNION ALL
    (/* 日付リストのSQL */)
  ) fix_t
GROUP BY date
ORDER BY date;

じっさいのSQLです。

初期値あり。集計可のSQL

SELECT date, SUM(val1) AS val1, val2
FROM
  (
     SELECT * FROM test_date

     UNION ALL

     SELECT date_t.date AS date, 0 AS val1, 'none' AS val2
     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')
  ) AS fix_t
  
GROUP BY date
ORDER BY date

date	val1	val2
2019-11-08	10	test1
2019-11-09	0	none
・・・	0	none
2019-12-09	20	test2
2019-12-10	0	none
・・・	0	none
2020-02-10	30	test3
2020-02-11	0	none
・・・	0	none
2020-06-11	40	test4
2020-06-12	0	none
・・・	0	none
2021-03-12	50	test5
2021-03-13	0	none
・・・	0	none
2021-09-13	60	test6

年次別で集計してみよう

今度は年次別でval1を集計してみます。日時データのクエリを少しだけ変更します。

年次集計SQL

SELECT date, SUM(val1) AS val1
FROM
  (
     SELECT DATE_FORMAT(date, '%Y') AS date, val1 FROM test_date

     UNION ALL

     SELECT date_t.date AS date, 0 AS val1
     FROM
       (SELECT
          DATE_FORMAT(DATE_ADD(min_t.date, interval @num:=@num+1 year), '%Y') 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')
  ) AS fix_t
  
GROUP BY date
ORDER BY date

/* 変更前 */
SELECT date, SUM(val1) AS val1, val2
/* 変更後 */
SELECT date, SUM(val1) AS val1

/* 変更前 */
SELECT * FROM test_date
UNION ALL
SELECT date_t.date AS date, 0 AS val1, 'none' AS val2
/* 変更後 */
SELECT DATE_FORMAT(date, '%Y') AS date, val1 FROM test_date
UNION ALL
SELECT date_t.date AS date, 0 AS val1

/* 変更前 */
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 year), '%Y') AS date

合計（SUM()）のときは、DATE_ADDのインターバルを変えるだけで日次、週次、月次、年次の集計がほぼ同じSQLでできます。

平均の集計など分母（レコード数）が関係する集計（週次、月次、年次）は日次データ（合計の集計）を使わないといけません。

（UNION ALLは同じ日付のレコードが複数できる。）

日次を使った年次の平均

SELECT YEAR(date) AS date, AVG(val1) AS val1
FROM
  (SELECT date, SUM(val1) AS val1, val2
   FROM
     (
        SELECT * FROM test_date

        UNION ALL

        SELECT date_t.date AS date, 0 AS val1, 'none' AS val2
        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')
     ) AS fix_t
     GROUP BY date
     ORDER BY date
    ) AS days_t
GROUP BY YEAR(date)
ORDER BY YEAR(date)

前の投稿

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

MariaDB, Linux(CentOS, Ubuntu)に最新版をインストールする

次の投稿

かんたんに超高速WordPressでサイトを作る、KUSANAGIが使える低価格クラウド・VPS・レンタルサーバ