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