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