--- 表user 用户ID uid , 登录时间 log_date
--- 先partition uid 按时间升序排序
--- 如果连续登录,则date_sub(log_date,rn)应该相同
--- 找出连续登录的最大天数
select max_continue_days,uid,start_day,end_day
from
(select uid
,date_sub(log_date,rn) as log_group
,count(log_date) as max_continue_days
,max(log_date) as end_day
,min(log_date) as start_day
from
(select uid,log_date
,row_number() over(partition by uid order by log_date) as rn
from xxx ) a
group by date_sub(log_date,rn),uid
) b
order by max_continue_days desc
用户连续登录的天数
最新推荐文章于 2024-08-03 23:56:47 发布