定义,每日新增的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
输出结果:
标签:create,用户,day,留存,MySql,device,diff,id From: https://www.cnblogs.com/chig/p/18362395