大数据分析复杂案例——网站日志流量深入分析

案例网站部分日志数据，根据以下数据字典

在hvie中写sql语句分析以下数据

UV： 每天的独立访客数，guid去重 统计个数

PV：网页浏览数 url不能为空，访问的条目数

登录人数：endUserId，当用户登录网站之后才算

游客人数：除去登录的人数

平均访问时长：访问时长  ,第一个页面 到最后一个页面

 会话访问时长（Seesion） sessionId,Max(trackTime) -min(trackTime)

二跳率：用户进入网站以后，点击的页面数 >=2 占比

                     会话（session）： sessionId
                     会话PV >2 /会话的总数

独立IP： ip去重

第一步：建立表，存放需要分析的数据

create table if not exists db_yhd.session_info(
    session_id STRING COMMENT '会话ID',
    guid STRING COMMENT '用户全局唯一ID', 
    trackU STRING COMMENT '访问渠道ID',
    landing_url STRING COMMENT '着陆页，会话中第一个访问URL',
    landing_url_ref STRING COMMENT '着陆页之前REF页面',
    user_id STRING COMMENT '用户登录网站ID',
    pv STRING COMMENT '会话中网站浏览量',
    stay_time STRING COMMENT '会话访问时长',
    min_trackTime STRING COMMENT '会话中第一个访问页面的时间',
    ip STRING COMMENT '会话IP地址',
    province_id STRING COMMENT '会话中省份ID'
)
PARTITIONED BY (date_str STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' 
LINES TERMINATED BY '\n'
STORED AS PARQUET;

第二步：向办法为第一步中的表插入数据

2.1：分步查询需要插入数据

    trackU STRING COMMENT '访问渠道ID',
    landing_url STRING COMMENT '着陆页，会话中第一个访问URL',
    landing_url_ref STRING COMMENT '着陆页之前REF页面',

很明显：上面三个数据无法直接原始数据中获取，需要提取出来查询

   --trackU 
   --landing_url 
   --landing_url_ref ,
--这3个字段其实，就是第一条记录的3个值  
drop table if exists db_yhd.track_url_tmp;
create table db_yhd.track_url_tmp
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
AS 
select
    sessionId as session_id,
    trackTime,
    url,
    referer,
    trackerU
from
    db_yhd.track_log
where 
    date_str="20150828"

2.2：获取剩余分析数据

drop table if exists db_yhd.session_info_tmp;
create table db_yhd.session_info_tmp
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
AS 
select 
   sessionId as session_id,
   MAX(guid) as guid,
   MAX(endUserId) as user_id,
   count(url) as pv,
   (unix_timestamp(Max(trackTime)) - unix_timestamp(MIN(trackTime))) as stay_time,
   MIN(trackTime) as min_trackTime,
   MAX(ip) as ip,
   MAX(provinceId) as province_id
from 
   db_yhd.track_log
where 
    date_str="20150828"
group by
    sessionId

2.3：将上面的表与2.2步骤中的表join即可获取所有需要分析的数据

INSERT INTO TABLE session_info PARTITION ( date_str="20150828"  ) 
select
   a.session_id,
   a.guid,
   b.trackerU as trackU, 
   b.url as landing_url,
   b.referer as landing_url_ref,
   a.user_id,
   a.pv,
   a.stay_time,
   a.min_trackTime,
   a.ip,
   a.province_id
from
   session_info_tmp a
left join
   track_url_tmp b
on 
  a.session_id = b.session_id and a.min_trackTime = b.trackTime

第三步：分析第一步中的数据表，得出需求数据

select
   date_str,
   count(distinct guid) as uv,
   sum(pv) as pv,
   count(distinct case when length(trim(user_id))>0 THEN user_id ELSE NULL END ) as  login_users,
   count(distinct case when user_id IS NULL or length(trim(user_id))=0 THEN guid ELSE NULL END ) as  visit_users,
   ROUND(avg(stay_time),2) as avg_stay_time,
   ROUND( count(case when pv>=2 THEN session_id ELSE NULL END) / count(session_id),2) as second_rate,
   count(distinct ip) as ip_num
from
   db_yhd.session_info
where 
   date_str="20150828"
group by 
   date_str

总结：

本案例中的sql语句并不复杂，就是简单的sql语句和函数的简单应用

难点在如何获取需要数据

要有分部获取数据的思想

有些数据不可能直接就获取到的，需要进过多次处理，才能获取到想要的数据

还有就是要能理解了要分析的参数的意义，明白这个数据代表的是什么意思

才能明确的得到分析数据