日毎のデータをMetabaseで確認する際にデータがない日付もグラフの軸に表示するようにしてみた

はじめに
やってみた
感想

はじめに

自分は日々の学習記録を以下の自作アプリで記録しております。
https://github.com/nakajima97/laravel-study-record-app-v2

このアプリは学習記録を登録することはできるのですが、
学習記録を可視化する機能はないです。

なので、Metabaseを使って可視化を行っています。
直近２週間の日毎の学習時間を可視化したいという際に素直にcreated_atでgroup by + 学習時間をsumで合算という方法でやると学習記録をつけていない日は0になります。

0である日も表示したかったのでその方法を備忘録としてブログに残します。

やってみた

学習記録を保存するテーブルは以下のようなカラムを持ちます。

カラム名	概要
id	主キー
category_id	categoriesテーブルの外部キー
minute	勉強した時間
note	備考欄
created_at	データが作成された日時
updated_at	データが更新された日時

minuteを日ごとに合計することで日ごとの勉強時間を取得することができます。
そして、それを素直にやると以下のようなSQLになるかと思います。

SELECT
  date_format(records.created_at, '%Y-%m-%d'),
  SUM(records.minute) AS total_minute
FROM
  records
WHERE
  records.created_at >= DATE_SUB(NOW(), INTERVAL 2 WEEk)
GROUP BY
  date_format(records.created_at, '%Y-%m-%d');

これをMetabaseで表示すると以下のようになります。

ブログ更新している今日は学習記録を付けていないので表示されていないです。
（10/10もデータないのですがグラフの軸には表示されています。原因は不明です。。。）
SQLの結果で見ると以下の通りで、10/10は出力されていないことが分かります。

これは表示されていない日付は勉強していないことを意識しないと正確にグラフを見れないので
あまりよくないです。
なので、勉強していない日付もグラフには勉強時間0で表示したいです。
それで作成したSQLが以下です。

SELECT
  date_format(c.date, '%Y-%m-%d'),
  COALESCE(SUM(r.minute), 0) AS total_minute
FROM
  records r
  RIGHT JOIN (
    SELECT
      CURDATE() AS DATE
    UNION
    SELECT
      CURDATE() - INTERVAL 1 DAY
    UNION
    SELECT
      CURDATE() - INTERVAL 2 DAY
    UNION
    SELECT
      CURDATE() - INTERVAL 3 DAY
    UNION
    SELECT
      CURDATE() - INTERVAL 4 DAY
    UNION
    SELECT
      CURDATE() - INTERVAL 5 DAY
    UNION
    SELECT
      CURDATE() - INTERVAL 6 DAY
    UNION
    SELECT
      CURDATE() - INTERVAL 7 DAY
    UNION
    SELECT
      CURDATE() - INTERVAL 8 DAY
    UNION
    SELECT
      CURDATE() - INTERVAL 9 DAY
    UNION
    SELECT
      CURDATE() - INTERVAL 10 DAY
    UNION
    SELECT
      CURDATE() - INTERVAL 11 DAY
    UNION
    SELECT
      CURDATE() - INTERVAL 12 DAY
    UNION
    SELECT
      CURDATE() - INTERVAL 13 DAY
    UNION
    SELECT
      CURDATE() - INTERVAL 14 DAY
  ) AS `c` ON date_format(r.created_at, '%Y-%m-%d') = c.date
GROUP BY
  date_format(c.date, '%Y-%m-%d');

直近2週間の日付データを用意してそれと外部結合することで実現しました。
これをMetabaseで表示すると以下のようになります。

勉強時間が0の時もグラフの軸に日付が表示されるようになりました！
SQLの結果でも確認できます。

感想

あくまで学習記録データを保存するためのDBなので分析用のテーブルを作るのはどうかな？と思ったので今回はこの方法にしました。
システムの動作には不要なテーブルをシステムのためのDBに作成するのは違う気がしたので。
DWHを構築しておりそこから参照する方法であれば日付を保存しただけのテーブルを用意してそこと結合して表示する方法をとったと思います。

ココらへんの考えは良いのかな？
やったこととしては小さなことでしたが、学習することたくさんあるし、まだまだ頑張らないとと思える良い機会でした！

Follow me!