使用当前日期往前推, 一至七日, 每日存留都会更新, 最新存留信息
推荐使用定时调度任务每日23:55 分执行, 这个查询数据量比较大
用户表里必须有两个字段, 用户创建时间create_time(类型为date,YMD), 最后登入时间last_login
SELECTIFNULL(((SELECT COUNT(*) FROM ylb_account WHERE create_date = DATE_ADD(DATE_FORMAT(NOW(),'%y-%m-%d'),INTERVAL -1 DAY) AND last_login >= DATE_FORMAT(NOW(),'%y-%m-%d'))/(SELECT COUNT(*) FROM ylb_account WHERE create_date = DATE_ADD(DATE_FORMAT(NOW(),'%y-%m-%d'),INTERVAL -1 DAY))),0) one_day,IFNULL(((SELECT COUNT(*) FROM ylb_account WHERE create_date = DATE_ADD(DATE_FORMAT(NOW(),'%y-%m-%d'),INTERVAL -2 DAY) AND last_login >= DATE_FORMAT(NOW(),'%y-%m-%d'))/(SELECT COUNT(*) FROM ylb_account WHERE create_date = DATE_ADD(DATE_FORMAT(NOW(),'%y-%m-%d'),INTERVAL -2 DAY))),0) tow_day,IFNULL(((SELECT COUNT(*) FROM ylb_account WHERE create_date = DATE_ADD(DATE_FORMAT(NOW(),'%y-%m-%d'),INTERVAL -3 DAY) AND last_login >= DATE_FORMAT(NOW(),'%y-%m-%d'))/(SELECT COUNT(*) FROM ylb_account WHERE create_date = DATE_ADD(DATE_FORMAT(NOW(),'%y-%m-%d'),INTERVAL -3 DAY))),0) three_day,IFNULL(((SELECT COUNT(*) FROM ylb_account WHERE create_date = DATE_ADD(DATE_FORMAT(NOW(),'%y-%m-%d'),INTERVAL -4 DAY) AND last_login >= DATE_FORMAT(NOW(),'%y-%m-%d'))/(SELECT COUNT(*) FROM ylb_account WHERE create_date = DATE_ADD(DATE_FORMAT(NOW(),'%y-%m-%d'),INTERVAL -4 DAY))),0) four_day,IFNULL(((SELECT COUNT(*) FROM ylb_account WHERE create_date = DATE_ADD(DATE_FORMAT(NOW(),'%y-%m-%d'),INTERVAL -5 DAY) AND last_login >= DATE_FORMAT(NOW(),'%y-%m-%d'))/(SELECT COUNT(*) FROM ylb_account WHERE create_date = DATE_ADD(DATE_FORMAT(NOW(),'%y-%m-%d'),INTERVAL -5 DAY))),0) five_day,IFNULL(((SELECT COUNT(*) FROM ylb_account WHERE create_date = DATE_ADD(DATE_FORMAT(NOW(),'%y-%m-%d'),INTERVAL -6 DAY) AND last_login >= DATE_FORMAT(NOW(),'%y-%m-%d'))/(SELECT COUNT(*) FROM ylb_account WHERE create_date = DATE_ADD(DATE_FORMAT(NOW(),'%y-%m-%d'),INTERVAL -6 DAY))),0) six_day,IFNULL(((SELECT COUNT(*) FROM ylb_account WHERE create_date = DATE_ADD(DATE_FORMAT(NOW(),'%y-%m-%d'),INTERVAL -7 DAY) AND last_login >= DATE_FORMAT(NOW(),'%y-%m-%d'))/(SELECT COUNT(*) FROM ylb_account WHERE create_date = DATE_ADD(DATE_FORMAT(NOW(),'%y-%m-%d'),INTERVAL -7 DAY))),0) seven_day
结果字段数据类型为fload, 乘100 即可转换成百分比
如果觉得《七日存留查询(MYSQL)》对你有帮助，请点赞、收藏，并留下你的观点哦！
