MySQL实现用户7日留存率.docx
5墨值下载
计算 7 日内留存率
#1.计算 7 日内留存率
-- 1.理清楚业务逻辑：
假如某日新增了 100 个用户，第二天登录了 50 个，则次日留存率为 50 / 100 = 50 %，第三
天登录了 30 个，则第二日留存率为 30 / 100 = 30 %，以此类推，第 7 天登录了 10 个用户，
则 7 日留存率就是 10 / 100 = 10 %。
#2.计算每个用户的最早登录日期
#用登录时间-最早登录时间得到一列 by_day
SELECT
a.MOBILE_PHONE,
UPDATE_TIME,
first_login,
DATEDIFF( UPDATE_TIME, first_login ) AS by_day
FROM
(
( SELECT MOBILE_PHONE, UPDATE_TIME FROM t_user_login_info
WHERE UPDATE_TIME BETWEEN '2018-03-01' AND '2018-03-30' GROUP BY
MOBILE_PHONE, UPDATE_TIME ) a
LEFT JOIN ( SELECT MOBILE_PHONE, min( UPDATE_TIME ) AS
first_login FROM t_user_login_info WHERE UPDATE_TIME BETWEEN '2018-03-01'
AND '2018-03-30' GROUP BY MOBILE_PHONE ) AS b ON a.MOBILE_PHONE =
b.MOBILE_PHONE
)
ORDER BY
by_day DESC
#3.计算间隔 7 日登录过的用户
SELECT
e.MOBILE_PHONE,
e.first_login,
sum( CASE WHEN by_day = 0 THEN 1 ELSE 0 END ) day_0,
sum( CASE WHEN by_day = 1 THEN 1 ELSE 0 END ) day_1,
sum( CASE WHEN by_day = 2 THEN 1 ELSE 0 END ) day_2,
sum( CASE WHEN by_day = 3 THEN 1 ELSE 0 END ) day_3,
sum( CASE WHEN by_day = 4 THEN 1 ELSE 0 END ) day_4,
sum( CASE WHEN by_day = 5 THEN 1 ELSE 0 END ) day_5,
sum( CASE WHEN by_day = 6 THEN 1 ELSE 0 END ) day_6,
sum( CASE WHEN by_day = 7 THEN 1 ELSE 0 END ) day_7,
sum( CASE WHEN by_day >= 8 THEN 1 ELSE 0 END ) day_8plus
FROM
(
SELECT
a.MOBILE_PHONE,
UPDATE_TIME,
first_login,
DATEDIFF( UPDATE_TIME, first_login ) AS by_day
FROM
计算 7 日内留存率
(
( SELECT MOBILE_PHONE, UPDATE_TIME FROM
t_user_login_info WHERE UPDATE_TIME BETWEEN '2018-03-01' AND '2018-03-30'
GROUP BY MOBILE_PHONE, UPDATE_TIME ) a
LEFT JOIN ( SELECT MOBILE_PHONE,
min( UPDATE_TIME ) AS first_login FROM t_user_login_info WHERE
UPDATE_TIME BETWEEN '2018-03-01' AND '2018-03-30' GROUP BY
MOBILE_PHONE ) AS b ON a.MOBILE_PHONE = b.MOBILE_PHONE
)
ORDER BY
by_day DESC
) e where e.mobile_phone='18788551017' group by
e.MOBILE_PHONE,e.first_login
二、自连接实现方式
#--最早登录日期和最早用户
select min(UPDATE_TIME) as first_login ,MOBILE_PHONE as user_id_day0 from
t_user_login_info group by MOBILE_PHONE
-- 登录日期间隔
select
tt.first_login,
tt.user_id_day0,
count(tt.user_id_day1) as '1 日留存',
count(tt.user_id_day2) as '7 日留存',
count(tt.user_id_day3) as '8 日留存'
from (
SELECT
a.user_id_day0,a.first_login ,b.MOBILE_PHONE as
user_id_day1,c.MOBILE_PHONE as user_id_day2,d.MOBILE_PHONE as
user_id_day3
FROM
(
( SELECT min( UPDATE_TIME ) AS first_login, MOBILE_PHONE AS
user_id_day0 FROM t_user_login_info WHERE UPDATE_TIME BETWEEN '2018-03-
01' AND '2018-03-30' GROUP BY MOBILE_PHONE ) a
LEFT JOIN t_user_login_info b ON (DATEDIFF( b.UPDATE_TIME,
a.first_login ) = 1 and b.MOBILE_PHONE=a.user_id_day0 and b.UPDATE_TIME
BETWEEN '2018-03-01' AND '2018-03-30')
LEFT JOIN t_user_login_info c ON (DATEDIFF( c.UPDATE_TIME,
a.first_login ) = 7 and c.MOBILE_PHONE=a.user_id_day0 and c.UPDATE_TIME
BETWEEN '2018-03-01' AND '2018-03-30')
LEFT JOIN t_user_login_info d ON (DATEDIFF( d.UPDATE_TIME,
of 3
5墨值下载
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文档的来源（墨天轮），文档链接，文档作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
下载排行榜
评论