【MySQL】 n分刻み範囲で集計する方法

　MySQLで時系列のログなりなんなりを集計する場合がしばしばあります。そういった時に使えるクエリの例を紹介します。例の区切りは次です。

SELECT
    -- UNIX_TIMESTAMP関数は日時をUNIXエポック秒に変換する関数です。
    -- UNIX_TIMESTAMP関数の結果を300で割った余りを引くことで300秒単位、つまり5分単位に丸めることができます。
    -- 丸めた秒をFROM_UNIXTIME関数で元の日時形式に戻します。これで5分単位の時間帯を得ることができます。
    FROM_UNIXTIME(UNIX_TIMESTAMP(datetime) - UNIX_TIMESTAMP(datetime) % 300) AS time_slot,
    -- 各5分単位の時間帯に存在するレコードの総数を得ます。
    COUNT(*) as total
FROM response_json_logs
-- 2023-08-22 13:00:00から2023-08-22 14:00:00の間のレコードのみを対象とします。
WHERE datetime >= '2023-08-22 13:00:00'
  AND datetime < '2023-08-22 14:00:00'
-- 5分単位の時間帯ごとに結果をまとめます。
GROUP BY time_slot
-- 時系列順に結果を並べます。
ORDER BY time_slot;

　このクエリの結果によって指定した範囲内での5分単位の時間帯ごとのレコード数を取得することができます。この時間帯で区切る手法は扱いやすく役立ちやすいです。

　またこのクエリは比較的簡単にカスタマイズすることができます。例えば10分単位や1時間単位に丸めるには、300の代わりに600や3600を使用します。集計対象のテーブルやカラム、期間も簡単に変更できるので様々なケースに応用できます。

　SQLで範囲を区切った集計といえばウィンドウ関数ですが、処理が重くなりやすかったりウィンドウ関数自体が比較的理解しがたい部類です。このあたりを避けつつ集計をするとなるとこのやり方は重宝します。