SQL / hive计算用户留存率

sql留存

1. 次日留存

from t1 left join t2 
on (t1.id=t2.id and t2.date = t1.date + interval '1' day)--t1、t2都是同一个表，只是分区使用不同
where t1.date=T1

2. 三日留存

from t1 left join t2 
on (t1.id=t2.id and t2.date = t1.date + interval '1' day)
left join t3 
on (t1.id=t3.id and t3.date = t1.date + interval '3' day)
where t1.date=T1

3. 每日次留

from t1 
left join t2 on (t1.id=t2.id and t2.date = t1.date + interval '1' day)
group by T1