1、數(shù)據(jù)說明

計算留存率只需要2個字段：用戶ID (user_id) 和 登錄日期 (login_time)

• t_user_login：表名

• user_id: 用戶id，也可用設備ID等

• login_time：登錄日期時間，例如：2020-05-25 16:03:05

2、實現(xiàn)步驟：

• 步驟一：從數(shù)據(jù)庫中提取user_id和login_time, 并計算 first_day, 用于存儲每個用戶ID最早登錄日期（最小日期）；

• 步驟二：用登錄日期-最早登錄日期，得到每個登錄日期距離最早登錄日期的時間間隔，即留存日期；

• 步驟三：對不同留存日期的user_id進行匯總就是留存人數(shù)，除以首日登錄人數(shù)，就得到了不同留存時間的留存率。

3、SQL實現(xiàn)

SELECT
	log_day '日期',
	count( user_id_day0 ) '新增數(shù)量',
	count( user_id_day1 ) / count( user_id_day0 ) '次日留存率',
	count( user_id_day2 ) / count( user_id_day0 ) '3日留存率',
	count( user_id_day7 ) / count( user_id_day0 ) '7日留存率',
	count( user_id_day30 ) / count( user_id_day0 ) '30日留存率' FROM
	(
	SELECT DISTINCT
		log_day,
		a.user_id_day0,
		b.user_id AS user_id_day1,
		c.user_id AS user_id_day3,
		d.user_id AS user_id_day7,
		e.user_id AS user_id_day30 
	FROM
		( SELECT DISTINCT 
				Date( login_time ) AS log_day, 
				user_id AS user_id_day0 
				FROM 
				t_user_login 
				GROUP BY user_id 
				ORDER BY log_day 
				) a
		LEFT JOIN t_user_login b ON DATEDIFF( DATE( b.login_time ), a.log_day ) = 1 
		AND a.user_id_day0 = b.user_id
		LEFT JOIN t_user_login c ON DATEDIFF( date( c.login_time ), a.log_day ) = 2 
		AND a.user_id_day0 = c.user_id
		LEFT JOIN t_user_login d ON datediff( date( d.login_time ), a.log_day ) = 6 
		AND a.user_id_day0 = d.user_id
		LEFT JOIN t_user_login e ON datediff( date( e.login_time ), a.log_day ) = 29 
		AND a.user_id_day0 = e.user_id 
	) temp GROUP BY
	log_day