4.数据分析(明白命令的意思)

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;