Laravelで月別・日別で集計する方法

Laravelを使用していて、月別日別の集計を取りたい事があります。
今回の記事では、Laravelを使用して月別・日別の集計を撮る方法及び、データがない日や月の穴埋めをどのようにプログラムをしようして行うのか紹介します。

テーブル例

id INT
count INT
created_at TIMESTANP
update_at TIMESTANP

月別の集計

2019年の月毎に集計したい場合、まずはwhereYearで2019年を検索します。
groupByでformat(‘m’)にすると月別にまとまるので、最後にmapでsumします。

Logs::whereYear('start_at', 2019)
->orderBy('created_at')
->get()
->groupBy(function ($row) {
    return $row->created_at->format('m');
})
->map(function ($day) {
    return $day->sum('count');
});

日別の集計

日別の場合whereMonthで集計したい月を追加します。
groupByの部分を日のdに変更するだけです。
2019年9月を日別に集計したい場合は次のようになります。


Logs::whereYear('created_at', 2019)
->whereMonth('created_at', 9)
->orderBy('created_at')
->get()
->groupBy(function ($row) {
    return $row->created_at->format('d');
})
->map(function ($day) {
    return $day->sum('count');
});

[参考]:Laravelで月別や日別で集計する方法

今回の記事の本題はここではありません。
このようなクエリを流すとデータない月や日がクエリ結果として出てきません。
そのデータのない月や日に特定の値を与えるのにはどうしようかという一つの方法を紹介するために記事を書きました。

あくまで参考のプログラムなので、Carbon使えるところ使っていなかったりするのですが、以下のような方法があります。

   $period = new DatePeriod( new DateTime($from), new DateInterval('P1D'), new DateTime($to));
   $dbData = [];

      foreach($period as $date){
          $range[$date->format("Y-m-d")] = 0;
      }

      $data = DB::table($request['option'])
                ->select(DB::raw('DATE(created_at) as time'), DB::raw('count(*) as count'))
                ->whereDate('created_at', '>=', date($from).' 00:00:00')
                ->whereDate('created_at', '<=', date($to).' 00:00:00')
                ->groupBy('time')
                ->get();

      foreach($data as $val){
        $dbData[$val->time] = $val->count;
      }

      $data = array_replace($range, $dbData);
    }

    return json_encode($data);

この方法は、DatePeriodを用いて、その期間の0件の配列を作成しています。
そして、クエリを流した後のキーが一致したもの(データがあった部分)の配列のにうわがくという方法です。

今回、勉強で作成したみんなで作る電話帳のグラフの表示などでこのロジックを流用しました。

集計はできたけど、データがない日や月の情報はどうしたら良いのかという方は参考にしてください。

[参考]:Laravel – fill up missing dates and counts from database