[MySQL] datetime型カラムを時間単位で集計する

Datetime型で記録されている日時の情報を、時間単位で集計する方法のまとめです。

具体的には、以下のようなテーブルから、13時は3件、14時は5件というように、時間区切りで件数の集計を行う方法です。

ID | item         | datetime 
 1 | オムライス   | 2013-05-29 13:15:54
 2 | もんじゃ焼き | 2013-05-29 13:28:06
 3 | カレーうどん | 2013-05-29 13:54:32
 4 | 焼き飯       | 2013-05-29 14:05:12
 5 | オムライス   | 2013-05-29 14:06:41
 6 | 焼き飯       | 2013-05-29 14:15:55
 7 | ハヤシライス | 2013-05-29 14:49:51
 8 | カツ丼       | 2013-05-29 14:52:30

1時間当たりの件数を集計する場合なら、CASE文とdate_format()関数を使うことで可能となります。

SELECT 
  SUM(CASE WHEN DATE_FORMAT(datetime,'%H') = 13 THEN 1 ELSE 0 END) AS h13,
  SUM(CASE WHEN DATE_FORMAT(datetime,'%H') = 14 THEN 1 ELSE 0 END) AS h14
FROM
  table_name

date_format()関数でdatetimeカラムの値を時刻のみの表示にし、1時間あたりの数を集計しています。DATE_FORMAT(datetime,'%H')とすることで、”2013-05-29 13:15:54″は”13″となります。DATE_FORMAT(datetime,'%H') = 13で集計を行うことで、分秒の部分を切り捨てて、13時でまとめることが可能になったということです。

指定日の1時間あたりの集計を行うなら以下のような感じでしょうか。

for($i=0;$i<24;$i++)
  $hash[] = "SUM(CASE WHEN DATE_FORMAT(datetime,'%H') = ".$i." THEN 1 ELSE 0 END) AS h".$i;

$sql = "SELECT ".implode(",", $hash) . "FROM table_name WHERE datetime >= '2013-05-29 00:00:00' AND datetime <= '2013-05-29 23:59:59'";

これで2013年5月29日の1時間ごとの件数を集計するSQL文が作成されます。

集計条件を変えたい場合はDATE_FORMAT()の部分を変更します。例えば10分単位で集計するなら、CASEの部分を以下のようにします。

SELECT 
  SUM(CASE WHEN DATE_FORMAT(datetime,'%H%i') >= 1300 AND DATE_FORMAT(datetime,'%H%i') < 1310 THEN 1 ELSE 0 END) AS h1300,
  SUM(CASE WHEN DATE_FORMAT(datetime,'%H%i') >= 1310 AND DATE_FORMAT(datetime,'%H%i') < 1320 THEN 1 ELSE 0 END) AS h1310
FROM
  table_name

DATE_FORMAT(datetime,'%H%i')とすることで、"2013-05-29 13:15:54"は1315となります。これを1310以上、1320未満の条件で抽出することで、10分あたりの件数を集計しています。

特定itemの1時間あたりの件数を集計する場合はwhere句に条件を追加します。オムライスの1時間あたりの件数を集計するなら以下のSQL文で可能となります。

SELECT 
  SUM(CASE WHEN DATE_FORMAT(datetime,'%H') = 13 THEN 1 ELSE 0 END) AS h13,
  SUM(CASE WHEN DATE_FORMAT(datetime,'%H') = 14 THEN 1 ELSE 0 END) AS h14
FROM
  table_name
WHERE
  item = 'オムライス'