MySQL/MariaDBの検索クエリ結果にデータがないものに日付だけ追加して、残りはデータ無しにする連続した日付をつける方法です。
テーブルの列(column)は追加せずテーブル構成は一切変更しません。SQLだけで行います。
ビューを作るときにも使えます。
(自分がよく忘れるので備忘録でもある。)


といったことがあります。
(集計やグラフを作るときには。)
データベースではよくあること。(というかそういうもの。)
『登録されてないデータ』がデータベースにあるはずがない。『登録されてないデータを登録する』という訳のわからないことをしないかぎり。
でも、集計をしたりグラフを作るときなどは『登録されてないデータ』が必要になる。
たとえば
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を分解して解説します。あとは
『登録されてないデータ』に初期値を入れる。
いろいろな集計をやってみる。
を行います。
『データベースに登録されてないデータ』をプログラムで追加することもあるでしょう。
それが大変なので、『SQLで足しちゃいましょ。』というのが今回の目的です。
日付リストのSQL
今回は、『日付リストを生成するSQL』が中心です。これに変更を加えます。
このSQLの解説はこちらをどうぞ。
日付リストにマージ
『登録されていないデータ』のレコードで、日付以外はNULLにするときはかんたんです。
日付リスト作成のSQLとマージしたいテーブルをLEFT JOINでつなぎます。
じっさいのSQLです。
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はこうなります。
date | val1 |
---|---|
2019 | 30 |
2020 | 70 |
2021 | 110 |
集計が合計(SUM())なので、『登録されてないデータ』のレコードのval1(NULL)は集計対象から外せました。でも初期値が0以外のとき使えません。
また、SQLで初期値が入っている方がプログラムとしては楽です。データベースのクエリ結果をそのまま使えるので。
今度は初期値を入れるパターンを見てみましょう。
初期値を入れて集計できるSQL
初期値を入れる。
初期値を集計に加える。
このようにするには、日付リストにval1, val2の初期値が必要です。そして、日付リストとテーブルの結合がLEFT JOINでは初期値が集計対象になりません。
そこでSQLを次のように変更します。
じっさいのSQLです。
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を集計してみます。日時データのクエリを少しだけ変更します。
合計(SUM())のときは、DATE_ADDのインターバルを変えるだけで日次、週次、月次、年次の集計がほぼ同じSQLでできます。
平均の集計など分母(レコード数)が関係する集計(週次、月次、年次)は日次データ(合計の集計)を使わないといけません。
(UNION ALLは同じ日付のレコードが複数できる。)