留存率既:
留存率=登录用户数/新增用户总数 * 100%
登录用户数:在统计的时间登陆过的用户
新增用户总数:新创建的用户(第一次登录的用户)
思路:
因为我们现实生产中求的不只是次日留存,还有7日留存或30日留存
1.首先我们统计出每个用户最早登录时间
2.统计出每日新增人数
3.在统计出每日活跃用户
4.将两个查询结果进行join形成新表 id atime(每日登录) btime(最早登录)
5.通过TIMESTAMPDIFF(DAY,atime,btime)函数,求出两个日期之间相差的天数
SELECT 日期,COUNT(DISTINCT 用户ID AS 活跃用户数,
COUNT(DISTINCT case when 时间间隔=1 then `用户ID`
ELSE NULL
END) AS 次日留存数/COUNT(DISTINCT 用户ID) AS 次日留存率
COUNT(DISTINCT case when 时间间隔=3 then `用户ID`
ELSE NULL
END) AS 次日留存数/COUNT(DISTINCT 用户ID) AS 三日留存率
COUNT(DISTINCT case when 时间间隔=7 then `用户ID`
ELSE NULL
END) AS 次日留存数/COUNT(DISTINCT 用户ID) AS 七日留存率
FROM( SELECT `用户ID`,TIMESTAMPDIFF(DAY,atime,btime)AS 时间间隔
FROM (
SELECT a.用户ID,a.日期 as atime,b.日期 as btime
FROM userbehavior as a LEFT JOIN userbehavior as b
ON a.用户ID=b.`用户ID`
WHERE a.`日期`>'2021-5-1'AND a.`日期` < '2021-5-14'
) AS c
)AS d