mysql留存数据

--次日留存率，基准日之后的第一天的用户的回访比例

--二日留存率，基准日之后的第二天的用户的回访比例

--三日留存率，基准日之后的第三天的用户的回访比例

--七日留存率，基准日之后的第七天的用户的回访比例

--二日内留存率，次日+第二日的活跃用户占比

--三日内留存率，次日+第二日+第三日的活跃用户占比**

--七日内留存率，七日内的活跃用户占比

image.png

【第一步】

将表格自己和自己做一个连接，目的是用来比较相间隔的天数，用来计算基于当日后续几天有活跃数据。SQL语句和结果如下↓

SELECT

lc1.id,

lc1.user_id,

date(lc1.time) date1,

date(lc2.time) date2,

DATEDIFF(date(lc1.time),date(lc2.time))

FROM

liucun as lc1

LEFT JOIN liucun as lc2 ON lc1.user_id = lc2.user_id

image.png

【第二步】

通过聚合函数(CASE WHEN)或者IF判断语句，判断日期相差的天数，用来判断用户时候后续指定天数有活跃。如果天数为1，代表次日是活跃的，以此类推。然后把满足条件的user_id取出来，因为考虑每天用户可能多次登录，所以需要用DISTINCT去重，然后对user_id进行计数就行了。SQL语句和结果如下↓

SELECT

date1,

count(DISTINCT user_id) usercount,

COUNT(DISTINCT if(DATEDIFF(date2,date1)=1,user_id,null)) if语法,

COUNT(DISTINCT CASE WHEN DATEDIFF(date2,date1)=1 THEN user_id ELSE NULL END) case语法

FROM

(SELECT

lc1.id,

lc1.user_id,

date(lc1.time) date1,

date(lc2.time) date2,

DATEDIFF(date(lc1.time),date(lc2.time))

FROM

liucun as lc1

LEFT JOIN liucun as lc2 ON lc1.user_id = lc2.user_id) temp

GROUP BY

date1

image.png

【第三步】

就可以计算留存率了，只需要相除就行了。还可以用用CANCAT函数做成百分位显示的效果，SQL语句和结果如下↓

SELECT

date1,

COUNT(DISTINCT user_id) 当日用户数,

COUNT(DISTINCT CASE WHEN DATEDIFF(date2,date1)=1 THEN user_id ELSE NULL END) 次日用户数,

CONCAT(ROUND(COUNT(DISTINCT CASE WHEN DATEDIFF(date2,date1)=1 THEN user_id ELSE NULL END)/COUNT(DISTINCT user_id)*100,2),"%") 次日留存率

FROM

(SELECT

lc1.id,

lc1.user_id,

date(lc1.time) date1,

date(lc2.time) date2,

DATEDIFF(date(lc1.time),date(lc2.time))

FROM

liucun as lc1

LEFT JOIN liucun as lc2 ON lc1.user_id = lc2.user_id) temp

GROUP BY

date1

image.png

【第四步】

最后我再增加一些数据，做一个完整的次日、第三日、第七日、第十日、三日内、七日内、十日内留存率效果，要看更长的，按规律增加时间就行了，SQL语句和结果如下↓

SELECT

date1,

COUNT(DISTINCT user_id) 当日用户数,

CONCAT(ROUND(COUNT(DISTINCT CASE WHEN DATEDIFF(date2,date1)=1 THEN user_id ELSE NULL END)/COUNT(DISTINCT user_id)*100,2),"%") 次日留存率,

CONCAT(ROUND(COUNT(DISTINCT CASE WHEN DATEDIFF(date2,date1)=2 THEN user_id ELSE NULL END)/COUNT(DISTINCT user_id)*100,2),"%") 第三日留存率,

CONCAT(ROUND(COUNT(DISTINCT CASE WHEN DATEDIFF(date2,date1)=6 THEN user_id ELSE NULL END)/COUNT(DISTINCT user_id)*100,2),"%") 第七日留存率,

CONCAT(ROUND(COUNT(DISTINCT CASE WHEN DATEDIFF(date2,date1)=9 THEN user_id ELSE NULL END)/COUNT(DISTINCT user_id)*100,2),"%") 第十日留存率,

CONCAT(ROUND(COUNT(DISTINCT CASE WHEN DATEDIFF(date2,date1)>=1 AND DATEDIFF(date2,date1)<=2 THEN user_id ELSE NULL END)/COUNT(DISTINCT user_id)*100,2),"%") 三日内留存率,

CONCAT(ROUND(COUNT(DISTINCT CASE WHEN DATEDIFF(date2,date1)>=1 AND DATEDIFF(date2,date1)<=6 THEN user_id ELSE NULL END)/COUNT(DISTINCT user_id)*100,2),"%") 七日内留存率,

CONCAT(ROUND(COUNT(DISTINCT CASE WHEN DATEDIFF(date2,date1)>=1 AND DATEDIFF(date2,date1)<=9 THEN user_id ELSE NULL END)/COUNT(DISTINCT user_id)*100,2),"%") 十日内留存率

FROM

(SELECT

lc1.id,

lc1.user_id,

date(lc1.time) date1,

date(lc2.time) date2,

DATEDIFF(date(lc1.time),date(lc2.time))

FROM

liucun as lc1

LEFT JOIN liucun as lc2 ON lc1.user_id = lc2.user_id) temp

GROUP BY

date1

image.png

转载于：https://baijiahao.baidu.com/s?id=1707892366222975766&wfr=spider&for=pc