用户行为路径分析（一）

用户行为路径 （一）

数据清洗

采用hive编写hql代码实现:

代码如下：

USE src_dat_zuma;
DROP TABLE 20170329_log_tycx;
CREATE TABLE IF NOT EXISTS 20170329_log_tycx (
SERVER_DT       string,
SP_CD                string,
EVENTS              string,
S_ID                     string,
U_ID                     string,
TEL                      string,
WEIXIN_ID           string,
EVENT_NM          string,
EVENT_PARA      string,
EXPLANATION     string,
UNIKEY                 string,
NET_TP                string,
UNIKEY_STAT      string,
VST_URL              string,
SRC_URL             string,
IP                           string,
Browser_INFO      string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS TEXTFILE;
load data inpath "oss://20170329_tycx.log" into table src_dat_zuma.20170329_log_tycx;
DROP TABLE tmp_mid_zuma.20170329_log_events;
CREATE TABLE IF NOT EXISTS tmp_mid_zuma.20170329_log_events (
SERVER_DT          string,
SP_CD                    string,
EVENTS                  string,
S_ID                        string,
EVENT_NM             string,
EVENT_PARA        string,
EXPLANATION       string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS TEXTFILE;
insert into table tmp_mid_zuma.20170329_log_events
select
t.SERVER_DT,
t.SP_CD,
t.EVENTS,
t.S_ID,
t.EVENT_NM,
t.EVENT_PARA,
t.EXPLANATION from src_dat_zuma.20170329_log_tycx t where t.SP_CD='02000500312' AND t.EVENTS='Event' or t.EVENTS='Interface' or t.EVENTS='Error'; 
DROP TABLE tmp_mid_zuma.20170329_log_pv;
CREATE TABLE IF NOT EXISTS tmp_mid_zuma.20170329_log_pv (
SERVER_DT         string,
SP_CD                   string,
EVENTS                 string,
S_ID                        string,
EVENT_NM             string,
EVENT_PARA        string,
EXPLANATION       string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS TEXTFILE;
insert into table tmp_mid_zuma.20170329_log_pv
select
t.SERVER_DT,
t.SP_CD,
t.EVENTS,
t.S_ID,
"pv",
t.EVENT_PARA,
t.EXPLANATION from src_dat_zuma.20170329_log_tycx t where t.SP_CD='02000500312' AND t.EVENTS='pv'; 
DROP TABLE tmp_mid_zuma.20170329_log_uv;
CREATE TABLE IF NOT EXISTS tmp_mid_zuma.20170329_log_uv (
SERVER_DT         string,
SP_CD             string,
EVENTS            string,
S_ID              string,
EVENT_NM          string,
EVENT_PARA        string,
EXPLANATION       string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS TEXTFILE;
insert into table tmp_mid_zuma.20170329_log_uv
select
t.SERVER_DT,
t.SP_CD,
t.EVENTS,
t.S_ID,
"uv",
t.EVENT_PARA,
t.EXPLANATION from src_dat_zuma.20170329_log_tycx t where t.SP_CD='02000500312' AND t.EVENTS='Visit'; 
DROP TABLE tmp_mid_zuma.event_all;
CREATE TABLE IF NOT EXISTS tmp_mid_zuma.event_all (
SERVER_DT         string,
SP_CD             string,
EVENTS            string,
S_ID              string,
EVENT_NM          string,
EVENT_PARA        string,
EXPLANATION       string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS TEXTFILE;
insert overwrite table tmp_mid_zuma.event_all
select * from(select a.SERVER_DT,a.SP_CD,a.EVENTS,a.S_ID,a.EVENT_NM,a.EVENT_PARA,a.EXPLANATION from tmp_mid_zuma.20170329_log_events a
union all
select b.SERVER_DT,b.SP_CD,b.EVENTS,b.S_ID,b.EVENT_NM,b.EVENT_PARA,b.EXPLANATION from tmp_mid_zuma.20170329_log_pv b
union all
select c.SERVER_DT,c.SP_CD,c.EVENTS,c.S_ID,c.EVENT_NM,c.EVENT_PARA,c.EXPLANATION from tmp_mid_zuma.20170329_log_uv c
)tt;
DROP TABLE  tmp_mid_zuma.row_number_test;
create table tmp_mid_zuma.row_number_test as select * from(
SELECT 
t.S_ID,
t.SERVER_DT,
t.EVENT_NM,
ROW_NUMBER() OVER(PARTITION BY t.S_ID,t.SERVER_DT,t.EVENT_NM ORDER BY t.SERVER_DT) AS rn
from tmp_mid_zuma.event_all t) tt;
USE tmp_mid_zuma;
drop table  tmp_mid_zuma.col2row;
create table tmp_mid_zuma.col2row as select * from(
select t.S_ID,concat_ws('_',collect_set(t.EVENT_NM))
from tmp_mid_zuma.row_number_test t group by t.S_ID ) tt;
select * from  tmp_mid_zuma.col2row limit 100;

效果图：