1.流量分析
、、、基于weblog数据库、、、
(1)创建表
create table dw_pvs_everyday(pvs bigint,month string,day string);
(2)插入数据
insert into table dw_pvs_everyday select count(*) as pvs,owd.month as month,owd.day as day from ods_weblog_detail owd group by owd.month,owd.day;
(3)查看插入的数据
select * from dw_pvs_everyday;
2、人均流量分析
(1)创建维度表
create table dw_avpv_user_everyday (day string,avgpv string);
(2)往维度表插入数据
insert into table dw_avpv_user_everyday select ‘2013-09-18’,sum(b.pvs)/count(b.remote_addr) from (select remote_addr,count(1) as pvs from ods_weblog_detail where datestr=‘2013-09-18’ group by remote_addr) b;
insert into table dw_avpv_user_everyday select ‘2013-09-19’,sum(b.pvs)/count(b.remote_addr) from (select remote_addr,count(1) as pvs from ods_weblog_detail where datestr=‘2013-09-19’ group by remote_addr) b;
insert into table dw_avpv_user_everyday select ‘2013-09-20’,sum(b.pvs)/count(b.remote_addr) from (select remote_addr,count(1) as pvs from ods_weblog_detail where datestr=‘2013-09-20’ group by remote_addr) b;
insert into table dw_avpv_user_everyday select ‘2013-09-21’,sum(b.pvs)/count(b.remote_addr) from (select remote_addr,count(1) as pvs from ods_weblog_detail where datestr=‘2013-09-21’ group by remote_addr) b;
insert into table dw_avpv_user_everyday select ‘2013-09-22’,sum(b.pvs)/count(b.remote_addr) from (select remote_addr,count(1) as pvs from ods_weblog_detail where datestr=‘2013-09-22’ group by remote_addr) b;
insert into table dw_avpv_user_everyday select ‘2013-09-23’,sum(b.pvs)/count(b.remote_addr) from (select remote_addr,count(1) as pvs from ods_weblog_detail where datestr=‘2013-09-23’ group by remote_addr) b;
insert into table dw_avpv_user_everyday select ‘2013-09-24’,sum(b.pvs)/count(b.remote_addr) from (select remote_addr,count(1) as pvs from ods_weblog_detail where datestr=‘2013-09-24’ group by remote_addr) b;