人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

PHP5+MySQL4.0.27でアクセスログをとっています。

日付別にアクセス数を知りたいと思い調べていたら
参考: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日分も抜け落ちなくする方法を教えてください。


●質問者: serimaryo
●カテゴリ:コンピュータ ウェブ制作
✍キーワード:2005-12-11 as dat GROUP MySQL
○ 状態 :終了
└ 回答数 : 2/2件

▽最新の回答へ

1 ● pahoo
●60ポイント ベストアンサー

datetime を基準にしている以上、SQL 文だけでは存在しない datetime を count することはできません。

代替策は以下の通り。


日付表を別途用意する

date型で連続する年月日のみを記録したテーブルを別途用意します。

このテーブルとご質問にあるテーブルの UNION をとります。


ストアドプロシージャを用いる

MySQL 5.x 以上が必要です。

REPEAT ステートメントを使い、変数 @dt DATE を1ずつインクリメントしながら、質問にあるような select 文を実行します。


PHPを用いる

ストアドプロシージャの代わりに、PHPで date を1ずつインクリメントしながら、質問にあるような select 文を実行します。

◎質問者からの返答

ありがとうございました!

日付表を別途用意し、UNIONをとったら簡単にできました!

助かりました!


2 ● chuken_kenkou
●10ポイント

存在しない日付を検索することはできないので、予め日付を管理する表を作っておくか、クエリ中で無理矢理日付を作るといった方法になってしまいます。

「日付を管理する表」は、例えばシステム停止日や稼動日の管理などでも利用できるので、作成しておいて無駄にならないと思います。

日付を管理する表の定義&格納データ例

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のシンタックスエラーや予期しない文法エラーになる可能性があります。

英単語そのままを利用するのをやめるか、「`」(バッククォート)で名前を囲むようにした方がいいでしょう。

関連質問


●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ