`
学会做人
  • 浏览: 121013 次
  • 性别: Icon_minigender_1
  • 来自: 成都
社区版块
存档分类
最新评论

如何实现网站流量设计

阅读更多

需求:某网站每天都有大量访问,所以需要统计网站流量。表有两个字段,分别是访问时间字段: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 200833 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. 

分享到:
评论

相关推荐

Global site tag (gtag.js) - Google Analytics