MySQLで日次・週次・月次集計する

なにこれ

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