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 のモードもとりあえず置いておきます。
PHP で先月の月初月末日付を取得する（ -1 month に関するどうでもいい話）
業務改善にあたり「先月の数字をほにゃらら」見たいな要件が出たので調べました。
例によってググって見ると、date('Y-m-t', strtotime(date('Y-m-01') . '-1 month'));みたいのが多かったのですが、PHP のドキュメントを読みに行ってみたら「0とか-1とか無くてこっちの方が好みだな」という書き方に辿り着いたので書いておきます。
DateTime で欲しいときは以下
<?php // 先月初日 new \DateTime('first day of midnight previous month'); // 先月末日 new \DateTime('last day of midnight previous month');
文字列で欲しいときは以下
<?php // 先月初日 echo date('Y-m-d', strtotime('first day of previous month')); // 先月末日 echo date('Y-m-d', strtotime('last day of previous month'));
参考：PHP: Relative Formats - Manual
どうでもいい話
PHP のドキュメントでも多少言及されていますが、〜 month 表記を「（進んだ or 戻った）月の同じ日付」という感覚で利用すると基準となる日付次第で問題が発生するケースがあります。「基準日から見て〜ヶ月（前 or 後）の初日や末日が欲しい」という要件に関しては、「基準月の1日を求めてから云々」等の解決方法を結構見かけましたが、そんなにゴニョゴニョしなくても明示的に first day of や last day of を記載する事で該当の問題が発生することを避けることが可能です。
月ごとに日数は異なるので、月末月初に関わらず「来月」という「日」の概念が若干ふんわりしている要件には注意してあたろうと改めて思いました。
<?php echo date('Y-m-d', strtotime('2016-01-31 next month')); // 2016-03-02 <- マジかよｗ echo date('Y-m-t', strtotime('2016-01-31 next month')); // 2016-03-31 <- 2月なんてなかった echo date('Y-m-d', strtotime('2016-01-31 last day of next month')); // 2016-02-29 <- 欲しかった結果
追伸：ぼくは先月分のデータ引っ張るなら 先月初日 ≦ x < 当月初日 が好きです。
33歳になった。
8月31日に33歳になりました。
変わらず元気ですが、誕生日のちょっと前に
iOS の（社内的な）エキスパートとして働いて居たと思ったら、営業・運用メンバーの業務改善チームに移動になった
という、割とアレな出来事がありました。名刺に刻まれた「あいおーえすあぷりけーしょんえんじにあ」って肩書きが切ないので変えたいです。
業務改善は「会社がデカくなったときとか、属人性に頼った生産性の向上に限界が見えてくると必要になるが、内部の積極的な協力を得るまでそこそこ時間がかかる仕事」というイメージを持っていましたが、まさか自分がやることになるとは思いませんでした。だってぼくが PHP 書くとろくな事にならないし、iOS アプリ以外も書けなくは無いことなんて皆忘れてると思っていたから。
お金稼いだり、残業しないのは好きなので、時間短縮したり経費削減したりする業務改善ってのは相性悪くないだろうし。個人的な目標は「（諸事情で減らしたく無い人も居るかもしれないが）他人の残業時間を減らす」で頑張っていこうと思います。
ということで、家では毎日 Swift 書いているのに会社では毎日コマンドを並べただけのシェルスクリプトと PHP と SQL を書く生活になりましたが、最後に PHP をちゃんと書いたのはもう数年前のお話。「PHP 7 新機能」とか「PHP フレームワーク オススメ」でググって必死にキャッチアップした次第です。（ちゃんと PHP のドキュメントも読みに行きました、マサカリを投げないで下さい）PHP 久しぶりだけど結構楽しいです。
そんなこんなで仕事も変わったし歳も変わったし、ここは一つブログも変えてポエムでも書こうかなと思い、新しくはてなブログ開設してみました。
追伸：弊社で人を募集しています。興味の有る方はお声がけください。
