Hack Your Design!

[MySQL]日付が歯抜けのデータを結合して日付毎に整理されたテーブルを作る

(image)[MySQL]日付が歯抜けのデータを結合して日付毎に整理されたテーブルを作る

やりたいこと

下記の日付が歯抜けになっている2つのテーブル(テーブル1, テーブル2)のデータを日付毎に整理してテーブル3のようなデータを生成したい。

テーブル1

日付 商品販売数
2018-01-02 10
2018-01-04 8
2018-01-05 3
2018-01-07 4
2018-01-08 5
2018-01-10 1

テーブル2

日付 商品入荷数
2018-01-03 20
2018-01-05 30
2018-01-10 10

テーブル3

日付 商品入荷数 商品販売数
2018-01-01 0 0
2018-01-02 0 10
2018-01-03 20 0
2018-01-04 0 8
2018-01-05 30 3
2018-01-06 0 0
2018-01-07 0 4
2018-01-08 0 5
2018-01-09 0 0
2018-01-10 10 1

前提環境

  • MySQL 5.7

方法

以前紹介した連番の仮想表を作るテクニックを駆使すればやりたいことが実現可能です。

MySQLで連番の仮想表を作る

日付だけのテーブルを作る

対象期間、つまり2018年1月1日〜2018年1月10日までのデータを生成します。

SELECT '2018-01-01' + INTERVAL seq_no DAY AS date
FROM (SELECT @seq_no := 0 AS seq_no
      UNION
      SELECT @seq_no := @seq_no + 1 AS seq_no FROM information_schema.COLUMNS
      LIMIT 10) tmp;
date
2018-01-01
2018-01-02
2018-01-03
2018-01-04
2018-01-05
2018-01-06
2018-01-07
2018-01-08
2018-01-09
2018-01-10

日付テーブルにデータをJOINする

上記の日付テーブルに実データをJOINしてやればうまくデータが生成できます。

SELECT
    date_table.date AS '日付',
    IFNULL(table2.商品入荷数, 0) AS '入荷数',
    IFNULL(table1.商品販売数, 0) AS '販売数'
FROM (SELECT '2018-01-01' + INTERVAL seq_no DAY AS date
      FROM (SELECT @seq_no := 0 AS seq_no
            UNION
            SELECT @seq_no := @seq_no + 1 AS seq_no FROM information_schema.COLUMNS
            LIMIT 10) tmp) date_table
LEFT JOIN table1 ON table1.日付 = date_table.date
LEFT JOIN table2 ON table2.日付 = date_table.date

これで無事、得たかったデータを得ることができました。

日付 入荷数 販売数
2018-01-01 0 0
2018-01-02 0 10
2018-01-03 20 0
2018-01-04 0 8
2018-01-05 30 3
2018-01-06 0 0
2018-01-07 0 4
2018-01-08 0 5
2018-01-09 0 0
2018-01-10 10 1

利用シーン

日付毎にデータをグラフにプロットして可視化したいときにこのテクニックを使うと便利。

  • このエントリーをはてなブックマークに追加