1、数据说明
计算留存率只需要2个字段:用户ID (user_id) 和 登录日期 (login_time)
t_user_login:表名
user_id: 用户id,也可用设备ID等
login_time:登录日期时间,例如:2020-05-25 16:03:05
2、实现步骤:
步骤一:从数据库中提取user_id和login_time, 并计算 first_day, 用于存储每个用户ID最早登录日期(最小日期);
步骤二:用登录日期-最早登录日期,得到每个登录日期距离最早登录日期的时间间隔,即留存日期;
步骤三:对不同留存日期的user_id进行汇总就是留存人数,除以首日登录人数,就得到了不同留存时间的留存率。
3、SQL实现
SELECT log_day '日期', count( user_id_day0 ) '新增数量', 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
在第一节基础上 更新了函数和执行顺序:实操了字符数函数、时间函数、字段截取函数、interval函数等,除最后顺序实操因未导入数据库,导致无法执行