なにこれ
MySQLでtimestamp型から日次(daily)・週次(weekly)・月次(monthly)で集計するための変換方法です。
日次・月次はto_char関数で'YYYY-MM-DD'や'YYYY-MM'を出力すればいいのですが、週次が少し面倒です。
週次は、週番号とMODであまりを計算することで、簡単な月曜起点以外の曜日起点も集計可能です。
日次
DATE_FORMATで時間以下のデータを切り捨てて集計しています。
SELECT DATE_FORMAT(created_at, '%Y-%m-%d'), count(*) FROM table GROUP BY 1
月次
DATE_FORMATで月以下のデータを切り捨てて集計しています。
SELECT DATE_FORMAT(created_at, '%Y-01-01'), count(*) FROM table GROUP BY 1
週次
月曜起点
WEEKDAYを使うと月曜が0、火曜が1...日曜が6となる週番号を得ることができます。
この週番号分の日付を戻すことで月曜起点で集計できます。
SELECT DATE_FORMAT(created_at - INTERVAL WEEKDAY(created_at) DAY, '%Y-%m-%d'), count(*) FROM table GROUP BY 1
月曜起点以外
月曜起点以外は、いい感じに日付を戻せるように週番号を得られるよう調整します。
それをまとめると以下のようになります。
月 | 火 | 水 | 木 | 金 | 土 | 日 | 行う処理 | |
---|---|---|---|---|---|---|---|---|
月曜起点で欲しい週番号 | 0 | 1 | 2 | 3 | 4 | 5 | 6 | WEEKDAYで週番号を得る |
火曜起点で欲しい週番号 | 6 | 0 | 1 | 2 | 3 | 4 | 5 | WEEKDAYの週番号に6を足して、7で割った余りを計算する |
水曜起点で欲しい週番号 | 5 | 6 | 0 | 1 | 2 | 3 | 4 | WEEKDAYの週番号に6を足して、7で割った余りを計算する |
木曜起点で欲しい週番号 | 4 | 5 | 6 | 0 | 1 | 2 | 3 | WEEKDAYの週番号に4を足して、7で割った余りを計算する |
金曜起点で欲しい週番号 | 3 | 4 | 5 | 6 | 0 | 1 | 2 | WEEKDAYの週番号に3を足して、7で割った余りを計算する |
土曜起点で欲しい週番号 | 2 | 3 | 4 | 5 | 6 | 0 | 1 | WEEKDAYの週番号に2を足して、7で割った余りを計算する |
日曜起点で欲しい週番号 | 1 | 2 | 3 | 4 | 5 | 6 | 0 | WEEKDAYの週番号に1を足して、7で割った余りを計算する |
金曜起点で計算したい場合は、以下のようなクエリになります。
SELECT DATE_FORMAT(created_at - INTERVAL MOD(WEEKDAY(created_at)+3, 7) DAY, '%Y-%m-%d'), count(*) FROM table GROUP BY 1