mysql留存数据 |
您所在的位置:网站首页 › 安宁丸的功效与价格 › 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)=1 AND DATEDIFF(date2,date1)=1 AND DATEDIFF(date2,date1) |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |