MySQLで集計するときに使える小技

MySQL

期間を分けて集計

過去7日間とその前の7日間を比較するといった場合は、CASE文が使えます。

クエリ

SELECT CASE
           WHEN (DATE(m.created_at) >= DATE_ADD(CURRENT_DATE(), INTERVAL - 7 DAY)) THEN "過去7日間"
           ELSE "比較期間"
       END AS period,
       COUNT(DISTINCT m.id) AS count
FROM members AS m
WHERE DATE(m.created_at) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 14 DAY) AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
GROUP BY period

期間毎に集計する

対象期間を、7日間毎や30分毎に分けて集計したい場合は、TRANCATE()が使えます。 以下は、過去28日間の登録者数を7日間毎に集計するという例です。

クエリ

SELECT if(TRUNCATE((DATEDIFF(CURRENT_DATE(), DATE(m.created_at)) - 1) / 7, 0) >= 1, "比較期間", "過去7日間") AS period,
       COUNT(DISTINCT m.id) AS count
FROM members AS m
WHERE DATE(m.created_at) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY) AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
GROUP BY TRUNCATE((DATEDIFF(CURRENT_DATE(), DATE(m.created_at)) - 1) / 7, 0)

(DATEDIFF(CURRENT_DATE(), DATE(m.created_at)) - 1)で、今日とm.created_atの差を出します。 -1しているので、差は0~13になります。

この差を7で割り、少数点以下をTRANCATE()で切り捨てることで、0~3のグループに分けることができます。