今天我们来分享一个常见案例，用SQL来计算用户留存率。
目录：
1. 案例数据
2. 思路分析
3. 完整代码
1. 案例数据
这里我们一共两份日志数据，分别是用户账号创建的日志以及用户登录的日志。
账号创建日志
用户登录日志
2. 思路分析
所谓留存，就是指某日创建的账号在后续自然日登录的比例，比如3月1日新增账号创建数为100，在3月2日这部分用户登录数为51，那么3月1日新增用户的次日留存率为51/100=51%。
注意：我这里用的MYSQL环境
基于上述的理解，我们大概就有了以下思路：
考虑到用户每天登录的次数不一定只有一次，为了方面后续的数据处理，可以先对登录数据按照日期和用户id进行去重DISTINCT处理
SELECTDISTINCTSTR_TO_DATE($part_date,'%Y-%m-%d')login_date,role_idFROMrole_login
为了计算某条登录日志是该用户创建账号后的第几天登录，我们可以用用户登录日志和账号创建日志进行inner join（这里考虑到不在统计周期内的创建账号的用户数据也会记录在用户登录日志里，所以去掉）
SELECTlogin_log.role_id,create_date,login_dateFROM((SELECTDISTINCTSTR_TO_DATE($part_date,'%Y-%m-%d')login_date,role_idFROMrole_login)login_logINNERJOIN(SELECTDISTINCTSTR_TO_DATE($part_date,'%Y-%m-%d')create_date,role_idFROMrole_create)create_logON(login_log.role_id=create_log.role_id))
然后用登录日期字段和创建账户字段进行差值DATEDIFF获取第几天登录
SELECTlogin_log.role_id,create_date,DATEDIFF(login_date,create_date)day_diffFROM...
对于第0天登录的数据则可以理解为新增用户数，第N（≥1)天登录的数据则为这批新增用户后续有登录的用户数
SELECTcreate_date,count((CASEWHEN(day_diff=0)THENrole_idEND))新增用户数,count((CASEWHEN(day_diff=1)THENrole_idEND))次日留存,count((CASEWHEN(day_diff=2)THENrole_idEND))3日留存,count((CASEWHEN(day_diff=7)THENrole_idEND))7日留存FROMtemp_1GROUPBYcreate_date
用第N天登录的数据 / 新增用户数 就是对应第N天留存率
3. 完整代码
SELECTcreate_date,新增用户数,concat(CAST(ROUND((100*次日留存)/新增用户数,2)ASchar),'%')次日留存率,concat(CAST(ROUND((100*3日留存)/新增用户数,2)ASchar),'%')3日留存率,concat(CAST(ROUND((100*7日留存)/新增用户数,2)ASchar),'%')7日留存率FROM(SELECTcreate_date,count((CASEWHEN(day_diff=0)THENrole_idEND))新增用户数,count((CASEWHEN(day_diff=1)THENrole_idEND))次日留存,count((CASEWHEN(day_diff=2)THENrole_idEND))3日留存,count((CASEWHEN(day_diff=7)THENrole_idEND))7日留存FROM(SELECTlogin_log.role_id,create_date,DATEDIFF(login_date,create_date)day_diffFROM((SELECTDISTINCTSTR_TO_DATE($part_date,'%Y-%m-%d')login_date,role_idFROMrole_login)login_logINNERJOIN(SELECTDISTINCTSTR_TO_DATE($part_date,'%Y-%m-%d')create_date,role_idFROMrole_create)create_logON(login_log.role_id=create_log.role_id)))temp_1GROUPBYcreate_date)temp_2ORDERBYcreate_dateASC
以上就是本次全部内容，由于不同的sql环境语法存在些许差异，大家视情况而处理吧。
-END -
对比Excel系列图书累积销量达15w册，让你轻松掌握数据分析技能，可以在全网搜索书名进行了解选购：
如果觉得《用SQL进行用户留存率计算》对你有帮助，请点赞、收藏，并留下你的观点哦！
