Laravel で group by した時にグループ毎の avg （集計関数の結果）が欲しい場合

前回「前月の〜」みたいなブログを書いたんですが、それに伴って「前月の週毎の平均を〜」みたいな要件があって、Laravelでちょっとハマったので書いておきます。

Laravel のクエリビルダには DB::avg('COLUMN_NAME') という、該当カラムの数値平均を出すための function が事前に定義されています。しかしこの DB::avg() は group by を利用して group 毎の平均値を得ようとすると最初の1グループ目の平均値しか返してくれません。

SQL と PHP 弱者なので、中途半端に1グループ目の分だけ正しい値を返してくるのに加えてなんの警告もエラーも、ドキュメント上での言及も無いので「自分がどこか間違っているのでは無いか?」と色々と試して見ましたが、結果として今回のように「Laravel で group by した後 group 毎の集計関数の結果を取得したい」というケースでは以下のように*1書くのが正しいだろう、という所に落ち着きました。

SELECT
 avg(impressions)
 FROM `posts`
 WHERE
 YEARWEEK('2016-09-01',1) <= YEARWEEK(created_time,1)
 AND YEARWEEK('2016-09-30',1) > YEARWEEK(created_time,1)
 GROUP BY YEARWEEK(created_time,1)
 ORDER BY YEARWEEK(created_time,1)

<?php
// YEARWEEK(created_time,1)  をどうにかしろというツッコミは謹んでお受けします。
$firstDateOfPreviousMonthString = date('Y-m-d', strtotime('first day of previous month'));
$lastDateOfPreviousMonthString = date('Y-m-d', strtotime('last day of previous month'));
$impressionAvgs = DB::table("posts")

    ->select(DB::raw('avg(impressions) as value'))
 
    ->whereRaw('YEARWEEK(:startDate,1) <= YEARWEEK(created_time,1)',['startDate' => $firstDateOfPreviousMonthString])

    ->whereRaw('YEARWEEK(:endDate,1) > YEARWEEK(created_time,1)', ['endDate' => $lastDateOfPreviousMonthString])

    ->groupBy(DB::raw('YEARWEEK(created_time,1)'))

    ->orderBy(DB::raw('YEARWEEK(created_time,1)'))

    ->get();

というように、用意された DB::avg() を使わず select に DB::raw() で直接 avg(impressions) を指定することで該当月の週毎の平均値が配列で返ってくるようになります。DB::whereRaw() は and で纏めてしまっても良かったんですが、分けて書いても勝手に and で繋げてくれるので分けて書いてあります。

*1:テーブル名とか引数の日付は適当です。 YEARWEEK のモードもとりあえず置いておきます。