hive 的数据案例 统计网站的数据信息

---

• 根据hive的案例一增加需求

---

一： 增加案例需求：

统计 pv , uv , 登录人数 , 游客人数 , 平均访问时长 , 二跳率 , 独立IP 
用一张表去处理

---

1.1 查看track_log的分区

show partitions track_log ;

1.2 建立一张会话信息表（session）：

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' ;

1.3 创建临时表 session_info_tmp1

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 ;

1.4 创建临时表session_info_tmp2

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' ;

1.5 导入数据处理：

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 ;

1.6 生成最后所需的表：

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 ;

1.7 查询结果：

select * from vistor_users_info;

转载于:https://blog.51cto.com/flyfish225/2097296