select starttime,round(base_1_num/base_num,2) as lcl_1,
round(base_3_num/base_num,2) as lcl_3,
round(base_7_num/base_num,2) as lcl_7
from (
select starttime,count(distinct userid)as base_num
,count(distinct if(diff=1,userid,null)) as base_1_num
,count(distinct if(diff=3,userid,null)) as base_3_num
,count(distinct if(diff=7,userid,null)) as base_7_num
from (
select t1.userid,t1.time as starttime,t2.time as endtime,datediff(t2.time,t1.time) as diff
from (
(select userid,date_format(time,'%Y-%m-%d') as time
from user_log
group by userid, date_format(time,'%Y-%m-%d')) t1
left join
(select userid,date_format(time,'%Y-%m-%d') as time
from user_log
group by userid, date_format(time,'%Y-%m-%d')) t2
on t1.userid=t2.userid and t2.time>t1.time
)
)t3 group by starttime
)t4
留存率的计算
于 2024-01-17 17:18:27 首次发布
该篇文章详细描述了一种SQL查询方法,用于分析用户日志中不同时间间隔(如1天、3天和7天)的活跃用户数,以评估用户会话行为特征。通过分组和计数distinctuserid,作者展示了如何在数据集中检测和量化用户间的频繁互动。
摘要由CSDN通过智能技术生成