hive 用户行为路径分析
需求
已知用户行为表 temp_actions_log, 大概字段有:(user_id 用户编号, op_id 操作编号, op_time 操作时间)
要求
- 统计每天符合以下条件的用户数:A操作之后是B操作,AB操作必须相邻。
- 统计用户行为序列为A-B-D的用户数
其中:A-B之间可以有任何其他浏览记录(如C,E等),B-D之间除了C记录可以有任何其他浏览记录(如A,E等)
数据准备
create table temp_actions_log ( user_id int, op_id string, op_time string ) row format delimited fields terminated by '\t' stored as orc tblproperties ("orc.compress" = "snappy");; insert into temp_actions_log(user_id, op_id, op_time) values (1, 'A', '2020-1-1 12:01:03'), (2, 'A', '2020-1-1 12:01:04'), (3, 'A', '2020-1-1 12:01:05'), (1, 'B', '2020-1-1 12:03:03'), (1, 'A', '2020-1-1 12:04:03'), (1, 'C', '2020-1-1 12:06:03'), (1, 'D', '2020-1-1 12:11:03'), (2, 'A', '2020-1-1 12:07:04'), (3, 'C', '2020-1-1 12:02:05'), (2, 'C', '2020-1-1 12:09:03'), (2, 'A', '2020-1-1 12:10:03'), (4, 'A', '2020-1-1 12:01:03'), (4, 'C', '2020-1-1 12:11:05'), (4, 'D', '2020-1-1 12:15:05'), (1, 'A', '2020-1-2 12:01:03'), (2, 'A', '2020-1-2 12:01:04'), (3, 'A', '2020-1-2 12:01:05'), (1, 'B', '2020-1-2 12:03:03'), (1, 'A', '2020-1-2 12:04:03'), (1, 'C', '2020-1-2 12:06:03'), (2, 'A', '2020-1-2 12:07:04'), (3, 'B', '2020-1-2 12:08:05'), (3, 'E', '2020-1-2 12:09:05'), (3, 'D', '2020-1-2 12:11:05'), (2, 'C', '2020-1-2 12:09:03'), (4, 'E', '2020-1-2 12:05:03'), (4, 'B', '2020-1-2 12:06:03'), (4, 'E', '2020-1-2 12:07:03'), (2, 'A', '2020-1-2 12:10:03');
需求一实现
先计算 前后的动作ID
select user_id, op_id, collect_set(op_id) over (partition by user_id,to_date(op_time) order by op_time) op_before, collect_set(op_id) over (partition by user_id,to_date(op_time) order by op_time desc ) op_after, to_date(op_time) as dt from temp_actions_log;
user_id
|
op_id
|
op_before
|
op_after
|
dt
|
1
|
C
|
["A","B","C"]
|
["C"]
|
2020/1/2
|
1
|
A
|
["A","B"]
|
["C","A"]
|
2020/1/2
|
1
|
B
|
["A","B"]
|
["C","A","B"]
|
2020/1/2
|
1
|
A
|
["A"]
|
["C","A","B"]
|
2020/1/2
|
2
|
A
|
["A","C"]
|
["A"]
|
2020/1/1
|
2
|
C
|
["A","C"]
|
["A","C"]
|
2020/1/1
|
2
|
A
|
["A"]
|
["A","C"]
|
2020/1/1
|
2
|
A
|
["A"]
|
["A","C"]
|
2020/1/1
|
3
|
C
|
["A","C"]
|
["C"]
|
2020/1/1
|
3
|
A
|
["A"]
|
["C","A"]
|
2020/1/1
|
4
|
E
|
["E","B"]
|
["E"]
|
2020/1/2
|
4
|
B
|
["E","B"]
|
["E","B"]
|
2020/1/2
|
4
|
E
|
["E"]
|
["E","B"]
|
2020/1/2
|
1
|
D
|
["A","B","C","D"]
|
["D"]
|
2020/1/1
|
1
|
C
|
["A","B","C"]
|
["D","C"]
|
2020/1/1
|
1
|
A
|
["A","B"]
|
["D","C","A"]
|
2020/1/1
|
1
|
B
|
["A","B"]
|
["D","C","A","B"]
|
2020/1/1
|
1
|
A
|
["A"]
|
["D","C","A","B"]
|
2020/1/1
|
2
|
A
|
["A","C"]
|
["A"]
|
2020/1/2
|
2
|
C
|
["A","C"]
|
["A","C"]
|
2020/1/2
|
2
|
A
|
["A"]
|
["A","C"]
|
2020/1/2
|
2
|
A
|
["A"]
|
["A","C"]
|
2020/1/2
|
3
|
D
|
["A","B","E","D"]
|
["D"]
|
2020/1/2
|
3
|
E
|
["A","B","E"]
|
["D","E"]
|
2020/1/2
|
3
|
B
|
["A","B"]
|
["D","E","B"]
|
2020/1/2
|
3
|
A
|
["A"]
|
["D","E","B","A"]
|
2020/1/2
|
4
|
D
|
["A","C","D"]
|
["D"]
|
2020/1/1
|
4
|
C
|
["A","C"]
|
["D","C"]
|
2020/1/1
|
4
|
A
|
["A"]
|
["D","C","A"]
|
2020/1/1
|
根据需求要求过滤结果
select user_id, dt from (select user_id, op_id, collect_set(op_id) over (partition by user_id,to_date(op_time) order by op_time) op_before, collect_set(op_id) over (partition by user_id,to_date(op_time) order by op_time desc ) op_after, to_date(op_time) as dt from temp_actions_log) i where op_id = 'B' and array_contains(op_before, 'A') and array_contains(op_after, 'D') and !array_contains(op_after, 'C')
user_id
|
dt
|
3
|
2020/1/2
|
计算结果
select dt, count(distinct user_id) cnt from (select user_id, dt from (select user_id, op_id, collect_set(op_id) over (partition by user_id,to_date(op_time) order by op_time) op_before, collect_set(op_id) over (partition by user_id,to_date(op_time) order by op_time desc ) op_after, to_date(op_time) as dt from temp_actions_log) i where op_id = 'B' and array_contains(op_before, 'A') and array_contains(op_after, 'D') and !array_contains(op_after, 'C')) i group by dt;
dt
|
cnt
|
2020/1/2
|
1
|
需求二实现
select user_id, op_id, lag(op_id, 1, op_id) over (partition by user_id, date_format(op_time, 'yyyy-MM-dd') order by op_time) as last_op_id, date_format(op_time, 'yyyy-MM-dd') as dt from temp_actions_log;
第一步
user_id
|
op_id
|
last_op_id
|
dt
|
2
|
A
|
A
|
2020/1/1
|
2
|
C
|
A
|
2020/1/1
|
2
|
A
|
C
|
2020/1/1
|
2
|
A
|
A
|
2020/1/2
|
2
|
A
|
A
|
2020/1/2
|
2
|
C
|
A
|
2020/1/2
|
2
|
A
|
C
|
2020/1/2
|
3
|
A
|
A
|
2020/1/1
|
3
|
C
|
A
|
2020/1/1
|
3
|
A
|
A
|
2020/1/2
|
3
|
B
|
A
|
2020/1/2
|
3
|
E
|
B
|
2020/1/2
|
3
|
D
|
E
|
2020/1/2
|
1
|
A
|
A
|
2020/1/1
|
1
|
B
|
A
|
2020/1/1
|
1
|
A
|
B
|
2020/1/1
|
1
|
C
|
A
|
2020/1/1
|
1
|
D
|
C
|
2020/1/1
|
1
|
A
|
A
|
2020/1/2
|
1
|
B
|
A
|
2020/1/2
|
1
|
A
|
B
|
2020/1/2
|
1
|
C
|
A
|
2020/1/2
|
4
|
A
|
A
|
2020/1/1
|
4
|
C
|
A
|
2020/1/1
|
4
|
D
|
C
|
2020/1/1
|
4
|
E
|
E
|
2020/1/2
|
4
|
B
|
E
|
2020/1/2
|
4
|
E
|
B
|
2020/1/2
|
第二步
select user_id, dt from (select user_id, op_id, lag(op_id, 1, op_id) over (partition by user_id, date_format(op_time, 'yyyy-MM-dd') order by op_time) as last_op, date_format(op_time, 'yyyy-MM-dd') as dt from temp_actions_log) t where op_id = 'B' and last_op = 'A';
user_id
|
dt
|
3
|
2020/1/2
|
1
|
2020/1/1
|
1
|
2020/1/2
|
第三步
select dt, count(distinct user_id) cnt from (select user_id, dt from (select user_id, op_id, lag(op_id, 1, op_id) over (partition by user_id, date_format(op_time, 'yyyy-MM-dd') order by op_time) as last_op, date_format(op_time, 'yyyy-MM-dd') as dt from temp_actions_log) o where op_id = 'B' and last_op = 'A') o group by dt;
dt
|
cnt
|
2020/1/1
|
1
|
2020/1/2
|
2
|