MySQLで一時間ごとのログを集計する
いま、ログから直近24時間の1時間ごとに何回のアクセスがあったかのサマリーをSQLで作ろうとしているのだがなかなか出来ない・・・
テーブルはアクセス時刻を示すtimeとログの内容を示すlogからなっているものと仮定
下記のようなSQLでは1時間に一度もアクセスがない場合にはログがそもそもはかれないため本来0と表示したい部分が表示出来ない
SELECT DATE_FORMAT(time,'%m/%d %H:00') AS DayTime, count(time) AS Accept FROM httplog WHERE year(time) = 2008 and log = 'Accept' GROUP BY date(time),hour(time) ORDER BY time DESC LIMIT 0, 24;
今が2008年01月02日0時10分として こんなテーブルを time log 2008-01-01 00:00:00 Accept 2008-01-01 01:10:00 Accept 2008-01-01 02:20:00 Accept 2008-01-01 02:40:00 Accept 2008-01-01 04:10:00 Accept 2008-01-01 05:00:00 Accept こうしたい ↓ DayTime Accept 2008-01-01 01:00 1 2008-01-01 02:00 2 2008-01-01 03:00 0 2008-01-01 04:00 1 2008-01-01 05:00 1 2008-01-01 06:00 0 ... 2008-01-01 23:00 0 2008-01-02 00:00 0
・・・何か良い方法はないものか
しかし、どうもSQLは時系列のデータというか0というか、存在しないデータを扱うにはめんどくさいな〜