作业十九【Hive日志分析案例二】

北风网大数据培训

一) 需求

需求：需要统计网站的访问信息date，UV，PV，登录人数，游客人数，平均访问时长，二跳率(访问网页两次以上)， 独立IP。
首先对原始数据根据sessionid进行统计，统计到下表的数据，然后再对这张表进行统计。

二) 实现

2.1）建立目标表

#建立目标数据
create table session_info(
session_id string,
guid string,
trackerU string,
landing_url string,
landing_url_ref string,
user_id string,
pv string,
stay_time string,
min_trackTime string,
ip string,
provinceId string
)
partitioned by (date string)
row format delimited fields terminated by '\t' ;

2.2）分组统计session信息

create table session_info_tmp1 as select 
sessionId session_id,
max(guid) guid,
--trackerU string,
--landing_url string,
--landing_url_ref string,
max(endUserId) user_id,
count(url) pv,
(max(unix_timestamp(trackTime)) - min(unix_timestamp(trackTime)))  stay_time,
min(trackTime) min_trackTime ,
max(ip) ip,
max(provinceId) provinceId
from track_log where date='20150828'
group by sessionId ;

2.3）统计访问信息等

create table session_info_tmp2 as select
sessionId session_id,
trackTime trackTime,
trackeru trackerU,
url landing_url,
referer landing_url_ref
from track_log where date='20150828' ;
````
<div class="md-section-divider"></div>
##2.4）汇总数据
<div class="md-section-divider"></div>
```sql
insert overwrite table session_info partition (date='20150828')
select
a.session_id,
a.guid,
b.trackerU,
b.landing_url,
b.landing_url_ref,
a.user_id,
a.pv,
a.stay_time,
a.min_trackTime,
a.ip,
a.provinceId
from session_info_tmp1 a  join session_info_tmp2 b
on a.session_id=b.session_id 
and a.min_trackTime=b.trackTime ;

2.5）统计最终结果

create table vistor_users_info as
select
date,
count(distinct guid) UV,
sum(pv) PV,
count(case when user_id != '' then user_id else null end) login_users,
count(case when user_id = '' then user_id else null end) vistor_users,
avg(stay_time) avg_stay_time,
count(case when pv>=2 then session_id else null end)/count(session_id) sec_ratio,
count(distinct ip) ip
from session_info where date='20150828'
group by date ;

2.6）结果截图