hive 计算连续登陆7天的用户

数据表

求：连续登陆7天的用户

思路：按用户、login_date分组，随后按用户对login_date 进行排序，若降序 则用login_date+排序序号 相同则为连续登陆，若升序则login_date-排序序号 相同则为连续登陆 

执行sql

select
user_id
,real_date
,count(1)
from
(
select
user_id
,login_date
,date_add(login_date,rn) as real_date
from
(--排序
select
user_id
,login_date
,row_number() over(partition by user_id order by login_date desc) as rn
from
(--user_idlogin_date分组
select
user_id
,login_date
from
(--模拟表数据
select
1 as user_id,'2022-06-28' login_date
union all
select
1 as user_id,'2022-06-29' login_date
union all
select
1 as user_id,'2022-06-27' login_date
union all
select
1 as user_id,'2022-06-26' login_date
union all
select
1 as user_id,'2022-06-25' login_date
union all
select
1 as user_id,'2022-06-24' login_date
union all
select
1 as user_id,'2022-06-23' login_date
union all
select
2 as user_id,'2022-06-28' login_date
union all
select
3 as user_id,'2022-06-28' login_date
union all
select
2 as user_id,'2022-06-29' login_date
union all
select
2 as user_id,'2022-06-30' login_date
union all
select
7 as user_id,'2022-06-28' login_date
)a
group by
user_id
,login_date
)a
)a
)a
group by
user_id
,real_date
having count(1) >= 7

执行结果

最后

以上就是明理蜜蜂为你收集整理的hive 计算连续登陆7天的用户的全部内容，希望文章能够帮你解决hive 计算连续登陆7天的用户所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。