[MySQL] 日時のデータをもとに日別のデータを集計する

MySQLで、日時のデータがdatetime型のカラムに保存されているデータベースから、日別のデータを集計する方法のメモです。

こういうデータですね。

id | name | datetime
 1 |    A | 2012-09-11 05:12:56
 2 |    B | 2012-09-11 21:16:56
 3 |    A | 2012-09-12 12:55:10

これをそのままDATETIMEカラムでグループ化したのでは秒単位で集計されてしまいます。

SELECT COUNT(id) FROM table GROUP BY datetime;//秒単位で集計されてしまう…

はじめからdate型のデータに保存された日別データや、datetime型でも時分秒は00:00:00のみのデータベースを集計するなら話は簡単ですが、秒単位のデータが詰まったものから１日ごとのデータを集計する場合、いったん日時を日に加工する作業が必要になります。

そこで、日付と時刻を指定の形式にフォーマットしてくれるMySQL関数のDATE_FORMATを使い、datetimeカラムのデータを日付に整形し、それをGROUP化することで対応します。

SELECT
  *,COUNT(id)
FROM
  table
GROUP BY
  DATE_FORMAT(datetime,'%Y%m%d')//日時データを日付に加工

GROUP BY DATE_FORMAT(datetime,'%Y%m%d')で時刻までが含まれたデータを日時に加工してからグループ化することで、１日あたりのデータを集計しています。

日付の文字列も必要な場合、SELECT句にDATE_FORMATを書く以下の例の方が良いかもしれません。

SELECT
  *,
  DATE_FORMAT(datetime,'%Y%m%d') AS date//日時に加工し"date"として保持する
  COUNT(id)
FROM
  table
GROUP BY
  date//3行目で加工したdateをグループ化

この場合、GROUP BY句ではSELECT句で加工したデータを指定します。