[假装有目录][TOC]
引言
本人惭愧,在国企工作接近4年了,基本待废了,才开始认真的考虑跳槽找工作的事情,刚工作时没有考虑清楚想做什么,稀里糊涂做了数据运营,内心抗拒,但四顾茫然,浑浑噩噩,抬头发现已沧海桑田,唯有从头开始,及时止损,才能不辜负韶华和生命的意义。在牛客学习到的知识,都分享给各位,希望能对大家有些许微博的帮助。
本帖是为了汇总牛客网站上的数据分析的面试题,主要是社招方向的,难度稍微大一点,不会讲太多的基础知识,尽量以干活为主吧。
数据平台:hadoop+hive/spark
数据语言:HQL(非msql,语法结构大体相同)
1. 连续7日用户提取
泰科电子数据分析工程师社招面经
里面讲到一道题,是提取多少时间内的连续X天用户的问题,此类问题的核心解法都是:先对每天的用户去重(distinct day,user_id),然后开窗排序(row_numer()over(partition by user_id order by day) rn),最关键的一点来了,是用日期减去排序的序号(day-rn),如果是连续的日期,那么序号必然也是按顺序+1的。自然(day-rn)的值是一样的(相当于将用户的连续访问的时间段做了一个标记,一个时间段用一个数值来代表)。最后再统计一下每个用户差值的计数数量,就能筛选出大于X天的用户了。
day rn 20210301 1 20210302 2 20210303 3 20210305 4 20210306 5 #上面的例子里,3月1号-3月3号都是连续的,(day-rn)的差值都是20210300,计数项为3,3月4号-3月5号的(day-rn)的差值都是20210302,计数项为2
因此,参考代码为:
#求30天内的连续7天访问用户 select id,count(*) from (select *,day-rn as result from (select *,row_number() over(PARTITION by user_id order by day ) as rn from (select DISTINCT day ,user_id from tb_visit)a )b )c GROUP BY user_id,result having count(*)>=7
问题扩展:
感谢@牛客800931114号 的提问:
当统计的时间范围大于1个月时,上述代码确实会遇到一个问题,就是跨月时间的连续访问判定问题。如果统计的范围很大,超过一个月,需要将日期转换为时间戳格式进行运算才能保证结果的正确性,因此更为严谨的完整版为:
select id,count(*) from (select *,day-rn*60*60*24 as result from (select *, to_unix_timestamp(day,'yyyy-MM-dd') day_stamp, row_number() over(PARTITION by user_id order by day ) as rn from (select DISTINCT day_stamp day ,user_id from tb_visit)a )b )c GROUP BY user_id,result having count(*)>=7
当数据为如下格式时:
day rn 20210331 1 20210401 2 #通过 to_unix_timestamp('2021-03-31','yyyy-MM-dd') 将日期转换为unix时间戳:1617120000,然后多一天,就相当于多了86400秒,通过day-rn*60*60*24来保证连续时间的结果一致
顺便提一句,mysql8.0版本已经支持了诸如 row_number(),rank()等各种开窗函数,感觉已经将hive SQL中亮眼的功能都实现了,优秀了
2. 每日新增用户的次日留存率
感谢评论区网友的分享,让我找到了新的思路,真是惭愧啊,就算天天接触到指标统计,用的一直是笨的办法,如果后续大家有这方面的问题也可以留言,我慢慢补充这个帖子的问题和答案。
我都是用土办法先把每天的新增用户捞出来,再从每天的用户表里去匹配前一日的新增用户来算次日留存,评论区老兄的办法很好,先给每条记录打上一个最早访问时间的标签,再以该时间作为聚合字段,也就是将一个月内用户的所有活跃数据,都放到了第一次访问时间对应的块中,进行统计,然后在块中判断用户是否有相较于第一次访问时间的X天的记录,有就计数一次,没有就0次。
这个方法高明的地方就在于,用户是按第一次访问时间分类的,直接按天找到了新增用户,然后通过遍历一遍用户的访问数据,计算与第一次访问时间的时间差,就能知道是否有次日留存,而不需要多次关联变筛选数据后统计结果,虽然性能上不一定最优,但是解法思路上很高级,只不过要注意的是,遍历的是时候一定要先将用户的单条访问记录按天先汇总一下,不然遍历计算次日留存的时候,分母是去重用户数,分子就变成单条访问记录数了,结果完全不对。
with tmp as ( select user_key,dt, min(dt) over(partition by user_key) as fst_log from Activity where dt between 20210301 and 20210331 --假设为月表 group by user_key,dt ) select fst_log as install_dt, count(distinct user_key) as installs, round(sum(if((dt-fst_log)=1,1,0))/count(distinct user_key),2) as new_1Day_retention from tmp group by fst_log
我跑了下某app的数据,每日新增是150w,次日留存是8-12%,当然大家可以吧判断语句的数值改下,就可以延伸为每日新增用户的X日留存率(如3日,5日,7日)。
3. 牛客练习题-订单分析5
题目看着有点绕,我总结一下,就是先从2025-10-15这个时间段后,选择一批用户的结果集(订购成功且不少于两次,课程为限定为JAVA、Python、和C++),将这些用户的第一次订购时间、第二次订购时间、以及订购成功的次数都展示出来,看代码顺理思路:
--先将结果集target_user 搞出来,但是我在这一步并没有对订单次数做过滤 with target_user as ( select user_id,date,count(1) over(partition by user_id) user_cnt, row_number() over(partition by user_id order by date asc )rn from order_info where unix_timestamp(date) >unix_timestamp('2025-10-15') and product_name in ('C++','Python','Java') and status='completed' )--接下来搞了两个子集,第一次订购结果集和第二次订购结果集 ,first_buy as ( select user_id,date first_buy_date,user_cnt from target_user where user_cnt>=2 and rn=1 ) ,second_buy as ( select user_id,date second_buy_date,user_cnt from target_user where user_cnt>=2 and rn=2 ) select f.user_id,f.first_buy_date,s.second_buy_date,f.user_cnt from first_buy f left join second_buy s on f.user_id=s.user_id order by f.user_id
代码虽然长,但是逻辑还算清晰,后续维护和编辑也简单,但是中间搞了两个结果集确实有点不好看,于是我看到了别人的版本,对上述代码进行优化:
--先将结果集target_user 搞出来,但是我在这一步并没有对订单次数做过滤 with target_user as ( select user_id,date,count(1) over(partition by user_id) user_cnt, row_number() over(partition by user_id order by date asc )rn from order_info where unix_timestamp(date) >unix_timestamp('2025-10-15') and product_name in ('C++','Python','Java') and status='completed' ) --只筛选出两个记录,分别用最大最小时间来分开第一次和第二次记录,如果再多来一次就又要开窗来排序了 select t.user_id, min(t.date ) first_buy_date, max(t.date ) second_buy_date, t.user_cnt from target_user t where t.user_cnt>=2 and t.rn<=2 group by t.user_id,t.user_cnt--第一次提交的时候没有加group by,导致只出现了一条数据,没有加的话,min和max都取的是所有记录的最大值最小值,并且只显示一条记录,差点没看出来 order by t.user_id
3. 牛客练习题-考试分数(5)
此问题最大的难点在于中位数的定义:
- 当元素的总个数是偶数时(如N=4),MOD(N/2)=0,中位数选取的时候,将总数+1再除2,即5/2=2.5,要取靠近2.5的两边的两个数,即取第二个和第三个
- 当元素的总个数是奇数时(如N=3),MOD(N/2)=1,中位数要取2,即取第二个即可
解法如下:
#先分类别排序,排序的序号也方便后续拿出来,后取每个类别中序号最大的为元素个数做运算 select re.id,re.job,re.score,re.rn from (select t.*, max(rn) over(partition by t.job ) max_rn from (select g.*, row_number() over(partition by g.job order by g.score desc)rn from grade g)t ) re # where (rn=ceil((re.max_rn+1)/2) or rn=floor((re.max_rn+1)/2)) where abs(rn-(re.max_rn+1)/2) <1 order by re.id
where条件有两个方式过滤;
- 方案1:(re.max_rn+1)/2的表达式下,元素个数是偶数,则ceil()和floor()计算的数据是一样的(N为奇数,选一个就行),选的都是同一个;元素个数是奇数,则ceil()和floor()计算的数据是不一样的(N为偶数,or的条件会把两个中位数都选出来),选出来的结果有两个
- 方案2:(rn-(re.max_rn+1)/2)的结果与上面一致,用了另外一个方式实现,大家稍微想想应该能明白
全部评论
(8) 回帖