86 网站点击流数据分析案例（统计分析-访客分析）

1.独立访客

需求描述：按照时间维度比如小时来统计独立访客及其产生的pvCnts；

对于独立访客的识别，如果在原始日志中有用户标识，则根据用户标识即很好实现。

此处，由于原始日志中并没有用户标识，以访客IP来模拟，技术上是一样的，只是精确度相对较低。

时间维度：时

drop table dw_user_dstc_ip_h;
create table dw_user_dstc_ip_h(
remote_addr string,
pvs      bigint,
hour     string);

insert into table dw_user_dstc_ip_h 
select remote_addr,count(1) as pvs,concat(month,day,hour) as hour 
from ods_weblog_detail
Where datestr='2013-09-18'
group by concat(month,day,hour),remote_addr;

在此结果表之上，可以进一步统计出，每小时独立访客总数，每小时请求次数topn访客等

如每小时独立访客总数：

select count(1) as dstc_ip_cnts,hour from dw_user_dstc_ip_h group by hour;

时间维度：月

select remote_addr,count(1) as counts,month 
from ods_weblog_detail
group by month,remote_addr;

间维度：日

select remote_addr,count(1) as counts,concat(month,day) as day
from ods_weblog_detail
Where dd='18/Sep/2013'
group by concat(month,day),remote_addr;

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

2.每日新访客

需求描述：将每天的新访客统计出来。

实现思路：创建一个去重访客累积表，然后将每日访客对比累积表。

时间维度：日

--历日去重访客累积表
drop table dw_user_dsct_history;
create table dw_user_dsct_history(
day string,
ip string
) 
partitioned by(datestr string);

--每日新用户追加到累计表
drop table dw_user_dsct_history;
create table dw_user_dsct_history(
day string,
ip string
) 
partitioned by(datestr string);

--每日新用户追加到累计表
insert into table dw_user_dsct_history partition(datestr='2013-09-19')
select tmp.day as day,tmp.today_addr as new_ip from
(
select today.day as day,today.remote_addr as today_addr,old.ip as old_addr 
from 
(select distinct remote_addr as remote_addr,"2013-09-19" as day from ods_weblog_detail where datestr="2013-09-19") today
left outer join 
dw_user_dsct_history old
on today.remote_addr=old.ip
) tmp
where tmp.old_addr is null;

验证：

select count(distinct remote_addr) from ods_weblog_detail;
-- 1005

select count(1) from dw_user_dsct_history where prtflag_day='18/Sep/2013';
--845

select count(1) from dw_user_dsct_history where prtflag_day='19/Sep/2013';
--160

时间维度：月

类似日粒度算法