夕蛙のなく頃に

データアナリストとして学んだことや趣味で勉強し始めたIoTをアウトプットする

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