以前找过多个不同版本的留存SQL命令,大多无法让人满意,现提供一个真正实用的版本
说明:
role_login_back:表名字
event_time:登录日期时间,例如:2019-12-06 10:02:08
device_id:设备ID
数据库表role_login_back的字段:
/*计算某日新增登录设备的次日、3日、7日、14日、30日、90日留存率*/
SELECT
log_day '日期',
count(user_id_d0) '新增数量',
count(user_id_d1) / count(user_id_d0) '次日留存',
count(user_id_d3) / count(user_id_d0) '3日留存',
count(user_id_d7) / count(user_id_d0) '7日留存',
count(user_id_d14) / count(user_id_d0) '14日留存',
count(user_id_d30) / count(user_id_d0) '30日留存',
count(user_id_d90) / count(user_id_d0) '90日留存'
FROM
(
SELECT DISTINCT
log_day,
a.user_id_d0,
b.device_id AS user_id_d1,
c.device_id AS user_id_d3,
d.device_id AS user_id_d7,
e.device_id AS user_id_d14,
f.device_id AS user_id_d30,
g.device_id AS user_id_d90
FROM
(
SELECT DISTINCT
Date(event_time) AS log_day,
device_id AS user_id_d0
FROM
role_login_back
GROUP BY
device_id
ORDER BY
log_day
) a
LEFT JOIN role_login_back b ON DATEDIFF(DATE(b.event_time),a.log_day) = 1
AND a.user_id_d0 = b.device_id
LEFT JOIN role_login_back c ON DATEDIFF(DATE(c.event_time),a.log_day) = 2
AND a.user_id_d0 = c.device_id
LEFT JOIN role_login_back d ON DATEDIFF(DATE(d.event_time),a.log_day) = 6
AND a.user_id_d0 = d.device_id
LEFT JOIN role_login_back e ON DATEDIFF(DATE(e.event_time),a.log_day) = 13
AND a.user_id_d0 = e.device_id
LEFT JOIN role_login_back f ON DATEDIFF(DATE(f.event_time),a.log_day) = 29
AND a.user_id_d0 = f.device_id
LEFT JOIN role_login_back g ON DATEDIFF(DATE(g.event_time),a.log_day) = 89
AND a.user_id_d0 = g.device_id
) AS temp
GROUP BY
log_day
生成结果如下:
一些定义如下:
次日留存:当日登录后,第二天也登录了,比如12.10登录过,12.11登录的算作次日留存
三日留存:当日登录后,第三天也登录了,比如12.10登录过,12.12登录的算作三日留存
七日留存:当日登录后,第七天也登录了,比如12.10登录过,12.16登录的算作三日留存
以此类推···
如果要计算账户留存,则批量替换device_id为account即可。