84 网站点击流数据分析案例（统计分析-PV统计）

1.多维度统计PV总量

1.时间维度

--计算指定的某个小时pvs
select count(*),month,day,hour from dw_click.ods_weblog_detail group by month,day,hour;


--计算该处理批次（一天）中的各小时pvs
drop table dw_pvs_hour;
create table dw_pvs_hour(month string,day string,hour string,pvs bigint) partitioned by(datestr string);

insert into table dw_pvs_hour partition(datestr='2016-03-18')
select a.month as month,a.day as day,a.hour as hour,count(1) as pvs from ods_weblog_detail a
where  a.datestr='2016-03-18'  group by a.month,a.day,a.hour;

或者用时间维表关联

维度：日

drop table dw_pvs_day;
create table dw_pvs_day(pvs bigint,month string,day string);

insert into table dw_pvs_day
select count(1) as pvs,a.month as month,a.day as day  from dim_time a
join ods_weblog_detail b 
on b.dd='18/Sep/2013' and a.month=b.month and a.day=b.day
group by a.month,a.day;

--或者，从之前算好的小时结果中统计
Insert into table dw_pvs_day
Select sum(pvs) as pvs,month,day from dw_pvs_hour group by month,day having day='18';

结果如下：

维度：月

drop table t_display_pv_month;

create table t_display_pv_month (pvs bigint,month string);

insert into table t_display_pv_month

select count(*) as pvs,a.month from t_dim_time a
join t_ods_detail_prt b on a.month=b.month group by a.month;

2. 按终端维度统计pv总量

注：探索数据中的终端类型

select distinct(http_user_agent) from ods_weblog_detail where http_user_agent like '%Mozilla%' limit 200;

终端维度：uc

drop table t_display_pv_terminal_uc;
create table t_display_pv_ terminal_uc (pvs bigint,mm string,dd string,hh string);

终端维度：chrome

drop table t_display_pv_terminal_chrome;
create table t_display_pv_ terminal_ chrome (pvs bigint,mm string,dd string,hh string);

终端维度：safari

drop table t_display_pv_terminal_safari;
create table t_display_pv_ terminal_ safari (pvs bigint,mm string,dd string,hh string);

3.按栏目维度统计pv总量

栏目维度：job
栏目维度：news
栏目维度：bargin
栏目维度：lane

2.人均浏览页数

需求描述：比如，今日所有来访者，平均请求的页面数

–总页面请求数/去重总人数

drop table dw_avgpv_user_d;
create table dw_avgpv_user_d(
day string,
avgpv string);

insert into table dw_avgpv_user_d
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;

3.按referer维度统计pv总量

需求：按照来源及时间维度统计PVS，并按照PV大小倒序排序

– 按照小时粒度统计，查询结果存入：( “dw_pvs_referer_h” )

drop table dw_pvs_referer_h;
create table dw_pvs_referer_h(referer_url string,referer_host string,month string,day string,hour string,pv_referer_cnt bigint) partitioned by(datestr string);

insert into table dw_pvs_referer_h partition(datestr='2016-03-18')
select http_referer,ref_host,month,day,hour,count(1) as pv_referer_cnt
from ods_weblog_detail 
group by http_referer,ref_host,month,day,hour 
having ref_host is not null
order by hour asc,day asc,month asc,pv_referer_cnt desc;

按天粒度统计各来访域名的访问次数并排序

drop table dw_ref_host_visit_cnts_h;
create table dw_ref_host_visit_cnts_h(ref_host string,month string,day string,hour string,ref_host_cnts bigint) partitioned by(datestr string);

insert into table dw_ref_host_visit_cnts_h partition(datestr='2016-03-18')
select ref_host,month,day,hour,count(1) as ref_host_cnts
from ods_weblog_detail 
group by ref_host,month,day,hour 
having ref_host is not null
order by hour asc,day asc,month asc,ref_host_cnts desc;

注：还可以按来源地域维度、访客终端维度等计算

4.统计pv总量最大的来源TOPN

需求描述：按照时间维度，比如，统计一天内产生最多pvs的来源topN

需要用到row_number函数
以下语句对每个小时内的来访host次数倒序排序标号，

select ref_host,ref_host_cnts,concat(month,hour,day),
row_number() over (partition by concat(month,hour,day) order by ref_host_cnts desc) as od 
from dw_ref_host_visit_cnts_h

效果如下：

根据上述row_number的功能，可编写Hql取各小时的ref_host访问次数topn

drop table dw_pvs_refhost_topn_h;
create table dw_pvs_refhost_topn_h(
hour string,
toporder string,
ref_host string,
ref_host_cnts string
) partitioned by(datestr string);

insert into table zs.dw_pvs_refhost_topn_h partition(datestr='2016-03-18')
select t.hour,t.od,t.ref_host,t.ref_host_cnts from
 (select ref_host,ref_host_cnts,concat(month,day,hour) as hour,
row_number() over (partition by concat(month,day,hour) order by ref_host_cnts desc) as od 
from zs.dw_ref_host_visit_cnts_h) t where od<=3;

结果如下：

注：还可以按来源地域维度、访客终端维度等计算