分単位でログ集計するためカウントする方法
アクセスログの分析のため月ごとで、データをlog_siteというテーブルに入れ込んでいます。(データ量は約4500万件)
※実際はもっと項目(リクエストURL、HTTPステータス、UAなど)がありますが、
ここでは省略しています。
+----+-----------------+--------------------+----------+----+------+
+ No | IP | DATETIME | YYYYMMDD | HH | HHMM |
+----+-----------------+--------------------+----------+----+------+
+ 1 | XXX.XXX.XXX.XXX | 2012-11-14 18:50:11| 20121114 | 18 | 1850 |
+ 2 | XXX.XXX.XXX.XXX | 2012-11-14 11:44:04| 20121114 | 11 | 1144 |
+ 3 | XXX.XXX.XXX.XXX | 2012-11-14 17:16:06| 20121114 | 17 | 1716 |
+ 4 | XXX.XXX.XXX.XXX | 2012-11-03 16:47:01| 20121103 | 16 | 1647 |
+ 5 | XXX.XXX.XXX.XXX | 2012-11-05 19:35:18| 20121105 | 19 | 1935 |
+ 6 | XXX.XXX.XXX.XXX | 2012-11-03 16:52:39| 20121103 | 16 | 1652 |
+ 7 | XXX.XXX.XXX.XXX | 2012-11-04 15:19:40| 20121104 | 15 | 1519 |
+ 8 | XXX.XXX.XXX.XXX | 2012-11-12 12:34:58| 20121112 | 12 | 1234 |
+ 9 | XXX.XXX.XXX.XXX | 2012-11-09 09:50:53| 20121109 | 09 | 0950 |
+ 10 | XXX.XXX.XXX.XXX | 2012-11-09 13:08:53| 20121109 | 13 | 1308 |
+ 11 | XXX.XXX.XXX.XXX | 2012-11-08 06:57:31| 20121108 | 06 | 0657 |
+ 12 | XXX.XXX.XXX.XXX | 2012-11-13 11:12:14| 20121113 | 11 | 1112 |
+ 13 | XXX.XXX.XXX.XXX | 2012-11-05 10:22:42| 20121105 | 10 | 1022 |
+----+-----------------+--------------------+----------+----+------+
各項目は以下のように定義しています。
`No` int(11) NOT NULL AUTO_INCREMENT,
`IP` varchar(15) DEFAULT NULL,
`DATETIME` datetime DEFAULT NULL,
`YYYYMMDD` varchar(8) DEFAULT NULL,
`HH` varchar(2) DEFAULT NULL,
`HHMM` varchar(4) DEFAULT NULL,
やりたいこととしては、1分または1時間単位でアクセスを集計しグラフを書こうとしています。
グラフに対しては、43,56,35,・・,5といった状態で渡せばグラフを書いてくれるのですが、
そのデータを取り出すため以下のようなSQLを書いて、各1分単位でそのカウントをして見ました。
select HHMM, count(IP) from log_site
where YYYYMMDD='20121101'and (
(HHMM='0000')or(HHMM='0001')or(HHMM='0002')・・・・(HHMM='2359')
)group by HHMM;
+------+-------+
+ HHMM | count |
+------+-------+
+ 0001 | 43 |
+ 0002 | 56 |
+ 0003 | 35 |
(----省略----)
+ 2359 | 5 |
+------+-------+
アクセス時間については、DATETIMEの項目にデータを入れているのですが
1分ずつ、1時間ずつ集計するのに日時や秒の情報が邪魔だったので
YYYYMMDD、HH、HHMMといった内容で情報を持たせています。
実行した結果、取得できるのに300-400秒必要なのですが、
1日を取得するだけで、5-7分要するのは、なんとかならないでしょうか？
最低でも3日、最高で7日間ぐらいのデータは取得したいと考えているため、
この時間はかかりすぎなのです。
やはりログデータ量が約4500万件あるという多さから予め日ごとで集計された
数字を別テーブルに持たせることを考えていたのですが、
引っ掛けたいリクエストURLや、HTTPステータス、UAなど他の項目でも
アクセス傾向が知りたいため、予め日ごとで集計された結果を別テーブルに持つ
ということは出来ないので、予め集計された結果を持っておく訳にも行かず困っています。
もし今の状況でどのようなＳＱＬだったら、データをスムースに取得できるのでしょうか？
またスムーズにデータを取得するためにデータ構造の見直しが必要としたら
どのように変えて、かつどのようにｓｑｌを組んで取得すればよいのでしょうか？
（仮に、日ごとでテーブルを作った場合は1日あたり150万程度になりますが
日をまたぐ場合は、どのようなSQLになるのでしょうか？）
もっといい方法があれば教えていただければと思います。
よろしくお願いいたします。
![その他([技術者向] コンピューター) イメージ](https://gazo.okwave.jp/okwave/images/related_qa/c205_4_thumbnail_img_sm.png)
![その他([技術者向] コンピューター) イメージ](https://gazo.okwave.jp/okwave/images/related_qa/c205_2_thumbnail_img_sm.png)
