MySql计算用户留存率
定义，每日新增的10个用户，在次日再次登陆有5个记为留存数5个，次日留存率为50%，在第七日还有3个，记为留存数3个，次日留存率为30%；
表1：hlj_user_base_info（用户注册表，记录用户注册信息）；关键字段，user_id，device_id，register_time
表2：hlj_user_click_url_point （用户点击表，记录用户点击行为）；关键字段，user_id，device_id，create_time
1 SELECT 2 create_date 3 , 新增用户数 4 , concat(CAST(ROUND((100 * 次日留存) / 新增用户数,2) AS char), '%') 次日留存率 5 , concat(CAST(ROUND((100 * 2日留存) / 新增用户数,2) AS char), '%') 2日留存率 6 , concat(CAST(ROUND((100 * 3日留存) / 新增用户数,2) AS char), '%') 3日留存率 7 , concat(CAST(ROUND((100 * 4日留存) / 新增用户数,2) AS char), '%') 4日留存率 8 , concat(CAST(ROUND((100 * 5日留存) / 新增用户数,2) AS char), '%') 5日留存率 9 , concat(CAST(ROUND((100 * 6日留存) / 新增用户数,2) AS char), '%') 6日留存率 10 , concat(CAST(ROUND((100 * 7日留存) / 新增用户数,2) AS char), '%') 7日留存率 11 FROM 12 ( 13 SELECT 14 create_date 15 , count((CASE WHEN (day_diff = 0) THEN device_id END)) 新增用户数 16 , count((CASE WHEN (day_diff = 1) THEN device_id END)) 次日留存 17 , count((CASE WHEN (day_diff = 2) THEN device_id END)) 2日留存 18 , count((CASE WHEN (day_diff = 3) THEN device_id END)) 3日留存 19 , count((CASE WHEN (day_diff = 4) THEN device_id END)) 4日留存 20 , count((CASE WHEN (day_diff = 5) THEN device_id END)) 5日留存 21 , count((CASE WHEN (day_diff = 6) THEN device_id END)) 6日留存 22 , count((CASE WHEN (day_diff = 7) THEN device_id END)) 7日留存 23 FROM 24 ( 25 SELECT 26 login_log.device_id 27 , create_date 28 , DATEDIFF(login_date, create_date) day_diff 29 FROM 30 (( 31 SELECT DISTINCT 32 STR_TO_DATE(create_time, '%Y-%m-%d') login_date 33 , device_id 34 FROM 35 hlj_user_click_url_point 36 ) login_log 37 INNER JOIN ( 38 SELECT DISTINCT 39 STR_TO_DATE(register_time, '%Y-%m-%d') create_date 40 , device_id 41 FROM 42 hlj_user_base_info 43 ) create_log ON (login_log.device_id = create_log.device_id)) 44 ) temp_1 45 GROUP BY create_date 46 ) temp_2 47 ORDER BY create_date ASC
输出结果：
