留存率分析

背景

留存率分析，在APP类数据中，属于比较常见的指标了，这里的话，就来分享一下我对留存率的实现。

留存率说明：假如今天新增了100名用户，第二天登陆了50名，
则次日留存率为50/100=50%，第三天登录了30名，则第二日留存率为30/100=30%,以此类推。

思路

1、取出user_id和用户login_time的表，获得新增用户登录时间表。（用户id和所有的登录时间）
2、根据user_id和login_time，增加一列first_day，此列存着每个用户最早登录时间。
3、有了最早登录时间和所有的登录时间，再增加一列by_day，这一列是用login_time - first_day ，得到0，1，2，3，4，5…，这就得到了某一天登录离第一次登录有多长时间。

select
		t2.user_id
		,t2.login_time
		,t2.first_day
		,timestampdiff(day,t2.first_day,t2.login_time) as by_day  ##利用timestampdiff 求出，当前登录时，距离第一次登录的时间
from
	(select
		t1.user_id
		,t1.login_time
		,min(login_time) over(partition by user_id)as first_day  ##利用开窗，求出第一次登录时间
	from
		(	
			select user_id,login_time  from log_day group by user_id,date(login_time)    ## 对登录次数进行去重，一天只留下一条记录
			
		)t1
	group by user_id,login_time)t2

4、然后从表中提取数据，找到从第一天开始。对应的by_day列中0有多少个，1有多少个，一直到7以上。

select
		t3.first_day as first_day
		## 通过case 去匹配 当前日期的与第一次登录日期为 1 - 7 的天数   然后sum聚合起来 得出 当前从当前日期到后面每天的登录人数
		,sum(case when t3.by_day = 0 then 1 else 0 end) as day_0
		,sum(case when t3.by_day = 1 then 1 else 0 end) as day_1
		,sum(case when t3.by_day = 2 then 1 else 0 end) as day_2
		,sum(case when t3.by_day = 3 then 1 else 0 end) as day_3
		,sum(case when t3.by_day = 4 then 1 else 0 end) as day_4
		,sum(case when t3.by_day = 5 then 1 else 0 end) as day_5
		,sum(case when t3.by_day = 6 then 1 else 0 end) as day_6
		,sum(case when t3.by_day = 7 then 1 else 0 end) as day_7
		,sum(case when t3.by_day = 8 then 1 else 0 end) as day_8
		,sum(case when t3.by_day = 9 then 1 else 0 end) as day_9
		,sum(case when t3.by_day = 10 then 1 else 0 end) as day_10
	
from
		(select
				t2.user_id
				,t2.login_time
				,t2.first_day
				,timestampdiff(day,t2.first_day,t2.login_time) as by_day  ##利用timestampdiff 求出，当前登录时，距离第一次登录的时间
		from
			(select
				t1.user_id
				,t1.login_time
				,min(login_time) over(partition by user_id)as first_day  ##利用开窗，求出第一次登录时间
			from
				(	
					## 对登录次数进行去重，一天只留下一条记录
					select user_id,date(login_time)as login_time  from log_day group by user_id,date(login_time)    
					
				)t1
			)t2)t3
group by t3.first_day	## 按照日期，统计每天的登录人数
order by t3.first_day   

这边因为模拟的数据量较少，所有看着不好看
有了这些数据，就可以求它的留存率了！
over!