如何实现网站流量设计

需求：某网站每天都有大量访问,所以需要统计网站流量。表有两个字段,分别是访问时间字段：log_Time,访问IP字段：IP)

问题1：请统计每个小时(0-1,1-2....-24)的访问次数。

问题2：哪个时段访问是最高峰。

select t.*, t.rowid from t_access t

--方法：得到要统计的原始信息(假设今天是2008-03-02)

select to_char(t.log_time,'hh24') as hour,ip

from t_access t

where to_char(t.log_time,'yyyy-mm-dd')='2008-03-02'

--(必须记住)日期格式：yyyy-mm-dd hh24:mi:ss

--统计

select to_char(t.log_time,'hh24') as hour,count(ip) as access_count

from t_access t

where to_char(t.log_time,'yyyy-mm-dd')='2008-03-02'

group by to_char(t.log_time,'hh24')

--问题2：哪个时段访问是最高峰。

--说明：这个问题与求人数最多的班级一样,要分析出来得分两步才能求出来。

--第一步：最高峰是?次

select  max(count(ip)) as access_count

from t_access t

where to_char(t.log_time,'yyyy-mm-dd')='2008-03-02'

group by to_char(t.log_time,'hh24')

--第二步：哪个时段=?次

select to_char(t.log_time,'hh24') as hour,count(ip) as access_count

from t_access t

where to_char(t.log_time,'yyyy-mm-dd')='2008-03-02'

group by to_char(t.log_time,'hh24')

having(count(ip)=(

    select  max(count(ip)) as access_count

    from t_access t

    where to_char(t.log_time,'yyyy-mm-dd')='2008-03-02'

    group by to_char(t.log_time,'hh24')

    )

)

--其它：按客户要求的格式显示：

select to_char(t.log_time,'hh24')||'-'||(to_char(t.log_time,'hh24')+1) as hour,

       count(ip) as access_count

from t_access t

where to_char(t.log_time,'yyyy-mm-dd')='2008-03-02'

group by to_char(t.log_time,'hh24')

-----------参考脚本------------

prompt PL/SQL Developer import file

prompt Created on 2008年3月3日 by Administrator

set feedback off

set define off

prompt Dropping T_ACCESS...

drop table T_ACCESS cascade constraints;

prompt Creating T_ACCESS...

create table T_ACCESS

(

  LOG_TIME DATE,

  IP       CHAR(17)

)

;

prompt Disabling triggers for T_ACCESS...

alter table T_ACCESS disable all triggers;

prompt Loading T_ACCESS...

insert into T_ACCESS (LOG_TIME, IP)

values (to_date('01-03-2008 00:00:12', 'dd-mm-yyyy hh24:mi:ss'), '192.169.1.190    ');

insert into T_ACCESS (LOG_TIME, IP)

values (to_date('02-03-2008 00:00:30', 'dd-mm-yyyy hh24:mi:ss'), '130.169.12.78    ');

insert into T_ACCESS (LOG_TIME, IP)

values (to_date('02-03-2008 01:00:30', 'dd-mm-yyyy hh24:mi:ss'), '192.169.13.192   ');

insert into T_ACCESS (LOG_TIME, IP)

values (to_date('02-03-2008 02:02:00', 'dd-mm-yyyy hh24:mi:ss'), '192.169.1.80     ');

insert into T_ACCESS (LOG_TIME, IP)

values (to_date('02-03-2008 02:02:30', 'dd-mm-yyyy hh24:mi:ss'), '202.169.1.80     ');

commit;

prompt 5 records loaded

prompt Enabling triggers for T_ACCESS...

alter table T_ACCESS enable all triggers;

set feedback on

set define on

prompt Done.