統計日留存及周留存

有時為了檢視使用者的品質，次日留存，1周内的日留存，周留存率都是用得比較多的統計名額

例1：計算某日注冊使用者1周内的日留存資料

select CONVERT(varchar(10),targetdate,120),gap,count(distinct t02.uid1) as retentionnum from(

select RegTime as targetdate,Id as uid1 from t_user where DATEDIFF(day,RegTime,'2020-7-17')=0)t01 

left join

(select userid as uid1,datediff(day,'2020-7-17',InitTime)as gap from t_userlog where InitTime between 

'2020-7-17' and '2020-7-24')t02 on t01.uid1=t02.uid1 group by CONVERT(varchar(10),targetdate,120),gap

order by gap asc

例2：計算某周注冊使用者1月内的周留存資料