留存率指标定义
第n日留存率 = (第一日新增的,且第n日仍在使用的用户数) / 第一日新增用户数
留存率数据
表名: mm
字段名:login_day 登录日期
userid 用户
userid对于每一个用户而言是唯一的
Mysql实现计算单日留存率(次日,3日,7日):方法一
select aday,
count(distinct buser)/count(distinct auser) as '次日留存',
count(distinct cuser)/count(distinct auser) as '3日留存',
count(distinct duser)/count(distinct auser) as '7日留存',
from
# 拼接表
select a.login_day as aday, a.userid as auser, b.userid as buser, c.userid as cuser, d.userid as duser from
# 先计算某一日新增用户数
(select distinct userid from mm where login_day = '2017-03-05'
and userid not in (select distinct(userid) from mm where login_day <'2017-03-05') a
left join mm b on a.userid=b.userid and b.login_day -a.login_day =1
left join mm c on a.userid