用户行为路径分析（一）

爱被打了一巴掌 2022-06-16 09:47 264阅读 0赞

用户行为路径 （一）

数据清洗

采用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;

效果图：

![Image 1][]

[Image 1]: