oracle 网址分析

悬赏园豆：20 [已解决问题] 解决于 2010-11-29 11:46

要查询结果如下

2010-11-01 到2010-11-28

2010-11-01 网址访问总数，访问量最高的时间段（如11点）

2010-11-02

……

2010-11-28

---现在不知道怎么把时间加上去

select to_char(receivetime,'yyyy-mm-dd hh24') rt,count(*) times from customeraction
where receivetime between to_date('2010-11-1'||' 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2010-11-29 23:59:59','yyyy-mm-dd hh24:mi:ss')
group by to_char(receivetime,'yyyy-mm-dd hh24')

"rownum","RECEIVETIME","TERMINALID","ACTIONTYPE","URL","URLTYPE"
"1","2010-11-22 12:52:00","2010209943","http","www.google.com","0001"
"2","2010-10-18 14:16:15","2010209943","http","www.google.com.hk","0001"
"3","2010-10-18 14:16:24","2010209943","http","www.google.com.hk","0001"
"4","2010-10-18 14:16:26","2010209943","http","www.google.com.hk","0001"
"5","2010-10-18 14:16:40","2010209943","http","www.baidu.com","0002"
"6","2010-10-18 14:16:42","2010209944","http","www.baidu.com","0002"
"7","2010-10-18 14:16:51","2010209945","http","www.baidu.com","0002"
"8","2010-10-19 14:31:12","2010209943","http","www.pconline.com.cn","0002"
"9","2010-10-19 15:06:39","2010209946","http","www.google.hk","0001"
"10","2010-10-19 15:08:35","2010209948","http","www.google.hk","0001"
"11","2010-10-19 15:14:08","2010209948","http","baike.baidu.com","0001"
"12","2010-10-19 15:18:27","2010209947","http","baike.baidu.com","0001"
"13","2010-10-19 15:20:18","2010209948","http","map.baidu.com","0002"
"14","2010-10-19 15:24:53","2010209948","http","map.baidu.com","0002"
"15","2010-10-20 15:56:25","2010209948","http","www.baidu.com","0002"
"16","2010-10-20 15:57:34","2010209948","http","www.163.com","0003"
"17","2010-10-20 15:58:03","2010209948","http","www.sina.com.cn","0004"
"18","2010-10-20 16:08:37","2010209948","http","www.178.com","0014"
"19","2010-10-20 16:11:23","2010209948","http","www.178.com","0014"
"20","2010-10-21 17:26:45","2010209946","http","hn.house.163.com","0001"
"21","2010-11-13 17:31:40","2010209946","http","www.baidu.com","0002"
"22","2010-10-21 17:29:01","2010209946","http","dkp.178.com","0014"
"23","2010-11-8 10:03:00","2010209946","http","www.178.com","0014"
"24","2010-11-8 10:07:03","2010209946","http","www.178.com","0014"
"25","2010-11-8 10:11:01","2010209946","http","www.baidu.com","0002"
"26","2010-11-8 10:19:05","2010209946","http","www.163.com","0003"
"27","2010-11-8 10:25:14","2010209946","http","product.tech.163.com","0012"
"28","2010-11-8 11:24:20","2010209946","http","www.163.com","0003"
"29","2010-11-19 13:35:03","m0001","http","www.google.com.hk","0001"
"30","2010-11-19 13:35:55","2010209946","http","www.google.com.hk","0001"
"31","2010-11-19 13:37:55","2010209946","http","www.google.com.hk","0002"
"32","2010-11-19 13:38:12","2010209946","http","www.baidu.com","0002"
"33","2010-11-19 1:10:00","2010209946","http","www.baidu.com","0002"
"34","2010-11-19 2:00:00","2010209946","http","www.baidu.com","0002"
"35","2010-11-19 12:02:00","2010209946","http","www.baidu.com","0002"
"36","2010-11-19 12:52:00","2010209946","http","www.baidu.com","0002"
"37","2010-11-20 12:52:00","2010209946","http","www.baidu.com","0002"
"38","2010-11-21 12:52:00","2010209946","http","www.baidu.com","0002"
"39","2010-11-11 1:16:40","201020991","http","www.google.com.hk","0001"
"40","2010-11-11 2:16:40","201020992","http","www.google.com.hk","0001"
"41","2010-11-11 3:16:40","201020993","http","www.google.com.hk","0001"
"42","2010-11-11 4:16:40","201020994","http","www.google.com.hk","0001"
"43","2010-11-11 5:16:40","201020995","http","www.google.com.hk","0001"
"44","2010-11-11 6:16:40","201020996","http","www.google.com.hk","0001"
"45","2010-11-11 7:16:40","201020997","http","www.google.com.hk","0001"
"46","2010-11-11 8:16:40","201020998","http","www.google.com.hk","0001"
"47","2010-11-11 9:16:40","201020999","http","www.google.com.hk","0001"
"48","2010-11-11 10:16:40","2010209910","http","www.google.com.hk","0001"
"49","2010-11-11 11:16:40","2010209911","http","www.google.com.hk","0001"
"50","2010-11-11 12:16:40","2010209912","http","www.google.com.hk","0001"
"51","2010-11-11 13:16:40","2010209913","http","www.google.com.hk","0001"
"52","2010-11-11 14:16:40","2010209914","http","www.google.com.hk","0001"
"53","2010-11-11 15:16:40","2010209915","http","www.google.com.hk","0001"
"54","2010-11-11 16:16:40","2010209916","http","www.google.com.hk","0001"
"55","2010-11-11 17:16:40","2010209917","http","www.google.com.hk","0001"
"56","2010-11-11 18:16:40","2010209918","http","www.google.com.hk","0001"
"57","2010-11-11 19:16:40","2010209919","http","www.google.com.hk","0001"
"58","2010-11-11 20:16:40","2010209920","http","www.google.com.hk","0001"
"59","2010-11-5 5:08:40","201020995","http","www.google.com.hk","0001"
"60","2010-11-6 6:08:40","201020996","http","www.google.com.hk","0001"
"61","2010-11-7 7:08:40","201020997","http","www.google.com.hk","0001"
"62","2010-11-8 8:08:40","201020998","http","www.google.com.hk","0001"
"63","2010-11-9 9:08:40","201020999","http","www.google.com.hk","0001"
"64","2010-11-10 10:08:40","2010209910","http","www.google.com.hk","0001"
"65","2010-11-11 11:08:40","2010209911","http","www.google.com.hk","0001"
"66","2010-11-12 12:08:40","2010209912","http","www.google.com.hk","0001"
"67","2010-11-13 13:08:40","2010209913","http","www.google.com.hk","0001"
"68","2010-11-14 14:08:40","2010209914","http","www.google.com.hk","0001"
"69","2010-11-15 15:08:40","2010209915","http","www.google.com.hk","0001"
"70","2010-11-16 16:08:40","2010209916","http","www.google.com.hk","0001"
"71","2010-11-17 17:08:40","2010209917","http","www.google.com.hk","0001"
"72","2010-11-18 18:08:40","2010209918","http","www.google.com.hk","0001"
"73","2010-11-19 19:08:40","2010209919","http","www.google.com.hk","0001"
"74","2010-11-20 20:08:40","2010209920","http","www.google.com.hk","0001"
"75","2010-5-15 18:08:40","201020995","http","www.google.com.hk","0001"
"76","2010-6-15 18:08:40","201020996","http","www.google.com.hk","0001"
"77","2010-7-15 23:08:40","201020997","http","www.google.com.hk","0001"
"78","2010-8-15 18:08:40","201020998","http","www.google.com.hk","0001"
"79","2010-9-15 18:08:40","201020999","http","www.google.com.hk","0001"
"80","2010-10-15 18:08:40","2010209910","http","www.google.com.hk","0001"
"81","2010-11-15 18:08:40","2010209911","http","www.google.com.hk","0001"
"82","2010-1-15 18:08:40","201020991","http","www.google.com.hk","0001"
"83","2010-2-15 18:08:40","201020992","http","www.google.com.hk","0001"
"84","2010-3-15 18:08:40","201020993","http","www.google.com.hk","0001"
"85","2010-4-15 18:08:40","201020994","http","www.google.com.hk","0001"
"86","2010-1-13 18:08:40","201020991","http","www.baidu.com","0002"
"87","2010-2-13 18:08:40","201020992","http","www.baidu.com","0002"
"88","2010-3-13 18:08:40","201020993","http","www.baidu.com","0002"
"89","2010-4-13 18:08:40","201020994","http","www.baidu.com","0002"
"90","2010-12-10 11:08:40","2010209011","http","www.baidu.com","0002"
"91","2010-12-10 11:08:40","2010209012","http","www.baidu.com","0002"
"92","2010-12-10 11:08:40","2010209013","http","www.baidu.com","0002"
"93","2010-11-23 15:25:44","2010100001","http","www.yahoo.com","0010"
"94","2010-11-1 15:00:00","201020994u","http","www.google","0001"
"95","2010-11-2 15:00:00","201020994u","http","www.google","0001"
"96","2010-11-3 15:00:00","201020994u","http","www.google","0001"
"97","2010-11-4 15:00:00","201020994u","http","www.google","0001"
"98","2010-11-5 15:00:00","201020994u","http","www.google","0001"
"99","2010-11-6 15:00:00","201020994u","http","www.google","0001"
"100","2010-11-7 15:00:00","201020994u","http","www.google","0001"
"101","2010-11-8 15:00:00","201020994u","http","www.google","0001"
"102","2010-11-9 15:00:00","201020994u","http","www.google","0001"
"103","2010-11-10 15:00:00","201020994u","http","www.google","0001"
"104","2010-11-6 15:00:22","201020994","http","www.google","0001"
"105","2010-11-7 15:00:22","201020994","http","www.google","0001"
"106","2010-11-8 15:00:22","201020994","http","www.google","0001"
"107","2010-11-9 15:00:22","201020994","http","www.google","0001"
"108","2010-11-10 15:00:22","201020994","http","www.google","0001"
"109","2010-11-11 15:00:22","201020994","http","www.google","0001"
"110","2010-11-12 15:00:22","201020994","http","www.google","0001"
"111","2010-11-13 15:00:22","201020994","http","www.google","0001"
"112","2010-11-14 15:00:22","201020994","http","www.google","0001"
"113","2010-11-15 15:00:22","201020994","http","www.google","0001"

----表结构

create table CUSTOMERACTION
(
RECEIVETIME DATE not null,
TERMINALID VARCHAR2(10) not null,
ACTIONTYPE CHAR(4),
URL VARCHAR2(100),
URLTYPE VARCHAR2(20)
)

-- Create/Recreate primary, unique and foreign key constraints
alter table CUSTOMERACTION
add constraint PK_CUSTOMERACTION primary key (RECEIVETIME, TERMINALID)

数据库 Oracle

window5549-accp | 初学一级 | 园豆：117
提问于：2010-11-26 15:22

< >

最佳答案

with temp as(
      select to_char(receivetime,'yyyy-mm-dd') temp_day, to_char(receivetime,'hh24') temp_hour,count(*) times
      from customeraction 
      where receivetime between to_date('2010-11-1 00:00:00','yyyy-mm-dd hh24:mi:ss') 
                        and to_date('2010-11-29 23:59:59','yyyy-mm-dd hh24:mi:ss')
      group by to_char(receivetime,'yyyy-mm-dd'), to_char(receivetime,'hh24')                        
)
select a.temp_day,a.temp_total,b.temp_hour temp_max_hour
from (
       select temp_day,sum(times) temp_total,max(times) temp_max_times
       from temp
       group by temp_day
     ) a
left join temp b  
on a.temp_day=b.temp_day  and
   a.temp_max_times=b.times

收获园豆：20

wgz | 小虾三级 |园豆：1254 | 2010-11-26 16:40

多谢！

window5549-accp | 园豆：117 (初学一级) | 2010-11-29 11:46

清除回答草稿

您需要登录以后才能回答，未注册用户请先注册。

欢迎，请先 登录 或者 注册 。

oracle 网址分析

欢迎，请先登录或者注册。