日付別にアクセス数を知りたいと思い調べていたら
参考:http://kokoromo.jugem.cc/?eid=204
このページを見つけました。
現在はこのページの通りに、datetime型のデータから日付別の集計を以下のように行っています。
こんなデータ。
mysql> select id,board,dat,w,s,datetime from accesslog limit 100,1;
+--------+----------+------------+------+------+---------------------+
| id | board | dat | w | s | datetime |
+--------+----------+------------+------+------+---------------------+
| 220362 | gamenews | 1134178950 | NULL | NULL | 2005-12-11 15:58:35 |
+--------+----------+------------+------+------+---------------------+
1 row in set (0.27 sec)
こんなクエリ。
select substring(datetime,1,10) as date,count(*) from accesslog group by date;
しかし、この方法だとアクセスログがない日付がある場合、その日付のデータは抜けてしまいます。
アクセスログがない日はcount(*)が0と出力し、データが1日分も抜け落ちなくする方法を教えてください。
datetime を基準にしている以上、SQL 文だけでは存在しない datetime を count することはできません。
代替策は以下の通り。
date型で連続する年月日のみを記録したテーブルを別途用意します。
このテーブルとご質問にあるテーブルの UNION をとります。
MySQL 5.x 以上が必要です。
REPEAT ステートメントを使い、変数 @dt DATE を1ずつインクリメントしながら、質問にあるような select 文を実行します。
ストアドプロシージャの代わりに、PHPで date を1ずつインクリメントしながら、質問にあるような select 文を実行します。
datetime を基準にしている以上、SQL 文だけでは存在しない datetime を count することはできません。
代替策は以下の通り。
date型で連続する年月日のみを記録したテーブルを別途用意します。
このテーブルとご質問にあるテーブルの UNION をとります。
MySQL 5.x 以上が必要です。
REPEAT ステートメントを使い、変数 @dt DATE を1ずつインクリメントしながら、質問にあるような select 文を実行します。
ストアドプロシージャの代わりに、PHPで date を1ずつインクリメントしながら、質問にあるような select 文を実行します。
ありがとうございました!
日付表を別途用意し、UNIONをとったら簡単にできました!
助かりました!
存在しない日付を検索することはできないので、予め日付を管理する表を作っておくか、クエリ中で無理矢理日付を作るといった方法になってしまいます。
「日付を管理する表」は、例えばシステム停止日や稼動日の管理などでも利用できるので、作成しておいて無駄にならないと思います。
日付を管理する表の定義&格納データ例
create table calendar (hiduke date); insert into calendar values ('2007-07-31'), ('2007-08-01'), ('2007-08-02'), ('2007-08-03'), ('2007-08-04'), ('2007-08-05');
日付の管理表を活用し、検索する例
select c.hiduke,count(al.`datetime`) from calendar as c left join accesslog as al on c.hiduke=substring(al.`datetime`,1,10) group by c.hiduke ;
なお、表名や列名に英単語をそのまま使用しているようですが、MySQLのバージョンアップ時に予約語と被ってSQLのシンタックスエラーや予期しない文法エラーになる可能性があります。
英単語そのままを利用するのをやめるか、「`」(バッククォート)で名前を囲むようにした方がいいでしょう。
ありがとうございました!
日付表を別途用意し、UNIONをとったら簡単にできました!
助かりました!