Hive-SQL查询连续活跃登录用户思路详解
发布日期：2022-02-02 08:12 | 文章来源：源码之家
连续活跃登陆的用户指至少连续2天都活跃登录的用户
解决类似场景的问题
创建数据
CREATE TABLE test5active(
dt string,
user_id string,
age int)
ROW format delimited fields terminated BY ',';
INSERT INTO TABLE test5active VALUES
('2019-02-11','user_1',23),('2019-02-11','user_2',19),
('2019-02-11','user_3',39),('2019-02-11','user_1',23),
('2019-02-11','user_3',39),('2019-02-11','user_1',23),
('2019-02-12','user_2',19),('2019-02-13','user_1',23),
('2019-02-15','user_2',19),('2019-02-16','user_2',19);
思路一:
1、因为每天用户登录次数可能不止一次，所以需要先将用户每天的登录日期去重。
2、再用row_number() over(partition by _ order by _)函数将用户id分组，按照登陆时间进行排序。
3、计算登录日期减去第二步骤得到的结果值，用户连续登陆情况下，每次相减的结果都相同。
4、按照id和日期分组并求和，筛选大于等于2的即为连续活跃登陆的用户。
第一步：用户登录日期去重
select DISTINCT dt,user_id from test5active;
第二步：用row_number() over()函数计数
select t1.user_id,t1.dt, row_number() over(partition by t1.user_id order by t1.dt) day_rank from ( select DISTINCT dt,user_id from test5active )t1;
第三步：日期减去计数值得到结果
select t2.user_id,t2.dt,date_sub(t2.dt,t2.day_rank) as dis from ( select t1.user_id,t1.dt, row_number() over(partition by t1.user_id order by t1.dt) day_rank from ( select DISTINCT dt,user_id from test5active )t1)t2;
第四步：根据id和结果分组并计算总和，大于等于2的即为连续登陆的用户，得到 用户id，开始日期，结束日期，连续登录天数
select t3.user_id,min(t3.dt),max(t3.dt),count(1) from ( select t2.user_id,t2.dt,date_sub(t2.dt,t2.day_rank) as dis from ( select t1.user_id,t1.dt, row_number() over(partition by t1.user_id order by t1.dt) day_rank from ( select DISTINCT dt,user_id from test5active )t1 )t2 )t3 group by t3.user_id,t3.dis having count(1)>1;
用户id 开始日期 结束日期 连续登录天数
最后：连续登陆的用户
select distinct t4.user_id from ( select t3.user_id,min(t3.dt),max(t3.dt),count(1) from ( select t2.user_id,t2.dt,date_sub(t2.dt,t2.day_rank) as dis from ( select t1.user_id,t1.dt, row_number() over(partition by t1.user_id order by t1.dt) day_rank from ( select DISTINCT dt,user_id from test5active )t1 )t2 )t3 group by t3.user_id,t3.dis having count(1)>1 )t4;
思路二：使用lag(向后)或者lead(向前)
select user_id,t1.dt, lead(t1.dt) over(partition by user_id order by t1.dt) as last_date_id from ( select DISTINCT dt,user_id from test5active )t1;
select distinct t2.user_id from ( select user_id,t1.dt, lead(t1.dt) over(partition by user_id order by t1.dt) as last_date_id from ( select DISTINCT dt,user_id from test5active )t1 )t2 where datediff(last_date_id,t2.dt)=1;
参考：
2020年大厂面试题-数据仓库篇
SQL 查询连续登陆7天以上的用户
到此这篇关于Hive-SQL查询连续活跃登陆的用户的文章就介绍到这了,更多相关SQL查询连续登陆的用户内容请搜索本站以前的文章或继续浏览下面的相关文章希望大家以后多多支持本站！
版权声明：本站文章来源标注为YINGSOO的内容版权均为本站所有，欢迎引用、转载，请保持原文完整并注明来源及原文链接。禁止复制或仿造本网站，禁止在非www.yingsoo.com所属的服务器上建立镜像，否则将依法追究法律责任。本站部分内容来源于网友推荐、互联网收集整理而来，仅供学习参考，不代表本站立场，如有内容涉嫌侵权，请联系alex-e#qq.com处理。
相关文章
关注官方微信