常规的数据取数过程中,难免会遇到一些留存数据,针对一次性取一段的留存数据做简单的介绍
例如需求需要取day1 到day2之间的留存数据:
select
t1.day_num
count(t1.id) as active_num ---当日活跃用户数
,count(t2.id) as retained_num ---当日活跃用户在次日留存
from
(
select
day_num
,id
from table_name
where between '${day1}' and '${day2}'
group by day_num,id
)t1
left join
(
select
date_add(from_unixtime(unix_timestamp(day_num,'yyyyMMdd'),'yyyy-MM-dd'),-1) as day_num
,id
from table_name
where date_add(from_unixtime(unix_timestamp('${day1}','yyyyMMdd'),'yyyy-MM-dd'),1) and date_add(from_unixtime(unix_timestamp('${day2}','yyyyMMdd'),'yyyy-MM-dd'),1)
group by date_add(from_unixtime(unix_timestamp(day_num,'yyyyMMdd'),'yyyy-MM-dd'),-1),id
)t2
on t1.id=t2.id and t1.day_num = t2.day_num
group by t1.day_num
通过日期的加减达到日期的同步,通过日期的增加取次日留存数据