广州网站优化网站建设,做网站赚钱不,云商城官网,科技助手注#xff1a;参考文档#xff1a;
SQL之用户行为路径分析--HQL面试题46【拼多多面试题】_路径分析 sql-CSDN博客文章浏览阅读2k次#xff0c;点赞6次#xff0c;收藏19次。目录0 问题描述1 数据分析2 小结0 问题描述已知用户行为表 tracking_log#xff0c; 大概字段有参考文档
SQL之用户行为路径分析--HQL面试题46【拼多多面试题】_路径分析 sql-CSDN博客文章浏览阅读2k次点赞6次收藏19次。目录0 问题描述1 数据分析2 小结0 问题描述已知用户行为表 tracking_log 大概字段有user_id 用户编号, op_id 操作编号, op_time 操作时间要求1统计每天符合以下条件的用户数A操作之后是B操作AB操作必须相邻。 2统计用户行为序列为A-B-D的用户数其中:A-B之间可以有任何其他浏览记录(如C,E等),B-D之间除了C记录可以有任何其他浏览记录(如A,E等)1 数据分析1数据生成......_路径分析 sqlhttps://blog.csdn.net/godlovedaniel/article/details/119856344
0 问题描述 有一张用户行为表 tracking_log包括字段user_id 用户编号, op_id 操作编号, op_time 操作时间。2个需求 统计每天符合以下条件的用户数A操作之后是B操作AB操作必须相邻统计用户行为序列为A-B-D的用户数 其中 A-B之间可以有任何其他浏览记录(如C,E等),B-D之间除了C记录可以有任何其他浏览记录(如A,E等) 1 数据准备
create table tracking_log(user_id int ,op_id string,op_time string)row format delimited fields terminated by \t;insert overwrite table tracking_log 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);
2 数据分析
需求一统计每天符合以下条件的用户数A操作之后是B操作AB操作必须相邻
step1: 将路径分析转换成字符串序列分析采用函数concat_ws(,, collect_set())
selectuser_id,op_id,op_time,collect_set(op_id) over (partition by user_id order by op_time) cs,--用户行为轨迹--collect_set 及collect_list属于高级的聚合窗口函数当over()中有order by但是省略窗口子句时候窗口计算范围orws between unbounded preceding and current rowconcat_ws(,, collect_set(op_id) over (partition by user_id order by op_time)) as op_id_str
from tracking_log
order by user_id, op_time 上述代码涉及到的函数 collect_list : 收集并形成list集合结果不去重 高级聚合函数 语法collect_list(col) 返回值array说明在hive中是把一个key的多个信息收集起来合成一个不去重举例select avg(score) from table collect_set收集并形成set集合结果去重高级聚合函数 语法collect_set(col)返回值array说明在hive中是把一个key的多个信息收集起来去重举例select avg(score) from table concat_ws(带分隔符的字符串连接函数) 语法concat_ws(string SEP, string A ,string B.......)返回值string说明返回输入字符串连接后的结果,SEP表示各个字符串的分隔符举例select concat_ws(|,ad,cv,op) ;--- ad|cv|op step2: 利用函数 locate()判断序列 A,B 是否在字符串op_id_str 中存在存在则返回该位置的索引where locate(A,B, op_id_str) 0
selectdate_format(op_time, yyyy-MM-dd) as dt,count(distinct user_id) cnt
from (selectuser_id,op_id,op_time,collect_set(op_id) over (partition by user_id order by op_time) cs,--用户行为轨迹concat_ws(,, collect_set(op_id) over (partition by user_id order by op_time)) as op_id_strfrom tracking_logorder by user_id, op_time) t
where locate(A,B, op_id_str) 0
group by date_format(op_time, yyyy-MM-dd)
上述代码涉及到的函数 locate第一次出现的位置 语法 locate( string substr, string str [, int pos] )返回值 int说明查找字符串substr第一次出现的位置 举例select locate(ad,aadbedfaad); --- 2 select locate(A,B,A,B,C,D); --- 1 需求二需要匹配A-B-D的路径但A,B之间可以有任何其他浏览记录B-D之间除了C记录可以有任何其他浏览记录所以使用字符串的正则匹配like来求解。代码片段 where op_id_str like %A%B%D and op_id_str not like %A%B%C%D
selectdate_format(op_time, yyyy-MM-dd) as dt,count(distinct user_id) as cnt
from (selectuser_id,op_id,op_time,collect_set(op_id) over (partition by user_id order by op_time) cs,--用户行为轨迹concat_ws(,, collect_set(op_id) over (partition by user_id order by op_time)) as op_id_strfrom tracking_logorder by user_id, op_time) t
where op_id_str like %A%B%D and op_id_str not like %A%B%C%D
group by date_format(op_time, yyyy-MM-dd); 3 小结 上述案例阐述用户行为路径的解决方法主要思路是将用户路径转换为字符串序列进行分析并利用like方法进行路径的模糊匹配。字符”%”表示任意数量的字符。 Hive的like正则表达式见Hive正则表达式-CSDN博客文章浏览阅读382次点赞13次收藏5次。Hive正则表达式https://blog.csdn.net/SHWAITME/article/details/136094446?spm1001.2014.3001.5502
