LINEで送る
B! はてぶでブックマーク
Pocketでブックマーク
RSSフィード

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

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

MySQL/MariaDBの検索クエリ結果にSQLで連続した日付をつける方法です。テーブルの列(column)追加ではなくテーブル構成は一切変更しません。

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

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

エンジニア
検索クエリ結果に期間内の日付が全部あるわけじゃない。
エンジニア
日付だけのレコード(その他の列は0とかNULL)も欲しい

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

(集計やグラフを作るときには。)

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

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

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

たとえば

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

(test_dateテーブル)

の検索結果を

dateval1val2
2019-11-0810test1
2019-11-090NULL
2019-11-100NULL






2019-12-0920test2
2019-12-100NULL






2021-09-120NULL
2021-09-1360test6

(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の解説はこちらをどうぞ。

日付リストにマージ

『登録されていないデータ』のレコードで、日付以外は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
dateval1val2
2019-11-0810test1
2019-11-09NULLNULL


NULLNULL
2019-12-0920test2
2019-12-10NULLNULL


NULLNULL
2020-02-1030test3
2020-02-11NULLNULL


NULLNULL
2020-06-1140test4
2020-06-12NULLNULL


NULLNULL
2021-03-1250test5
2021-03-13NULLNULL


NULLNULL
2021-09-1360test6

年次別で集計してみよう

この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
dateval1
201930
202070
2021110

集計が合計(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
dateval1val2
2019-11-0810test1
2019-11-090none


0none
2019-12-0920test2
2019-12-100none


0none
2020-02-1030test3
2020-02-110none


0none
2020-06-1140test4
2020-06-120none


0none
2021-03-1250test5
2021-03-130none


0none
2021-09-1360test6

年次別で集計してみよう

今度は年次別で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で日付リストを作る方法

コメントを残す

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