HiveSQL用户留存率

留存分析一般包含新增留存和活跃留存分析。

新增留存分析是分析某天的新增用户中，有多少人有后续的活跃行为。活跃留存分析是分析某天的活跃用户中，有多少人有后续的活跃行为。

留存分析是衡量产品对用户价值高低的重要指标。

此处要求统计新增留存率，新增留存率具体是指留存用户数与新增用户数的比值，例如2020-06-14新增100个用户，1日之后（2020-06-15）这100人中有80个人活跃了，那2020-06-14的1日留存数则为80，2020-06-14的1日留存率则为80%。

要求统计每天的1至7日留存率，如下图所示。

1.表结构

 

2.数据来源

取用户末次登录

 

 取用户注册日期

实现：

1)获取13日所有新增用户

select
                user_id
      from
           dwd_user_register_inc
      where dt = '2020-06-13'

 2）获取留存用户（注册日期为13末次登录日期为14）

（1）获取截至14日所有用户的末次登录日期

 select
                user_id,
                login_date_last
         from
              dws_user_user_login_td
         where dt = '2020-06-14'

（2）新增用户join所有用户（14日所有用户内包含13日新增且留存的）

select count(*)
from
     (
         select
                user_id
      from
           dwd_user_register_inc
      where dt = '2020-06-13'
     ) t1
         join
     (
         select
                user_id,
                login_date_last
         from
              dws_user_user_login_td
         where dt = '2020-06-14')
         t2 on t1.user_id = t2.user_id

（3）判断13日注册的且14日活跃的

select count(*),
       sum(`if`(login_date_last = '2020-06-14', 1, 0))
from (
         select user_id
         from dwd_user_register_inc
         where dt = '2020-06-13'
     ) t1
         join
     (
         select user_id,
                login_date_last
         from dws_user_user_login_td
         where dt = '2020-06-14') t2 on t1.user_id = t2.user_id

 （4）字段补齐

select '2020-06-14'                                               dt,              --统计日期,
       '2020-06-13'                                               create_date,     --用户新增日期
       1                                                          retention_day,   --截至当前日期留存天数
       sum(`if`(login_date_last = '2020-06-14', 1, 0))            retention_count, --留存用户数量
       count(*)                                                   new_user_count,  --新增用户数量
       sum(`if`(login_date_last = '2020-06-14', 1, 0)) / count(*) retention_rate   --留存率
from (
         select user_id
         from dwd_user_register_inc
         where dt = '2020-06-13'
     ) t1
         join
     (
         select user_id,
                login_date_last
         from dws_user_user_login_td
         where dt = '2020-06-14') t2 on t1.user_id = t2.user_id

 2）获取12日两日留存

select '2020-06-14'                                               dt,              --统计日期,
       '2020-06-12'                                               create_date,     --用户新增日期
       2                                                          retention_day,   --截至当前日期留存天数
       sum(`if`(login_date_last = '2020-06-14', 1, 0))            retention_count, --留存用户数量
       count(*)                                                   new_user_count,  --新增用户数量
       sum(`if`(login_date_last = '2020-06-14', 1, 0)) / count(*) retention_rate   --留存率
from (
         select user_id
         from dwd_user_register_inc
         where dt = '2020-06-12'
     ) t1
         join
     (
         select user_id,
                login_date_last
         from dws_user_user_login_td
         where dt = '2020-06-14') t2 on t1.user_id = t2.user_id

 依次类推获取七日

每日聚合逻辑相同、计算内容不同

group by分组：对不同组的数据进行相同的聚合处理

1)获取7-13日所有新增用户的分区

 select user_id,create_time create_date
         from dwd_user_register_inc
         where dt >= date_add('2020-06-14', -7)
           and dt < '2020-06-14'

2）对注册日期进行分组

select sum(`if`(login_date_last = '2020-06-14', 1, 0))            retention_count, --留存用户数量
       count(*)                                                   new_user_count,  --新增用户数量
       sum(`if`(login_date_last = '2020-06-14', 1, 0)) / count(*) retention_rate   --留存率
from (
         select user_id, create_time create_date
         from dwd_user_register_inc
         where dt >= date_add('2020-06-14', -7)
           and dt < '2020-06-14'
     ) t1
         join
     (
         select user_id,
                login_date_last
         from dws_user_user_login_td
         where dt = '2020-06-14') t2 on t1.user_id = t2.user_id
group by create_date

3）字段补齐

select '2020-06-14'                                               dt,              --统计日期,
       create_date,                                                                --用户新增日期
       datediff('2020-06-14', create_date)                        retention_day,   --截至当前日期留存天数 当天日期-用户新增日期
       sum(`if`(login_date_last = '2020-06-14', 1, 0))            retention_count, --留存用户数量
       count(*)                                                   new_user_count,  --新增用户数量
       sum(`if`(login_date_last = '2020-06-14', 1, 0)) / count(*) retention_rate   --留存率
from (
         select user_id, dt create_date
         from dwd_user_register_inc
         where dt >= date_add('2020-06-14', -7)
           and dt < '2020-06-14'
     ) t1
         join
     (
         select user_id,
                login_date_last
         from dws_user_user_login_td
         where dt = '2020-06-14') t2 on t1.user_id = t2.user_id
group by create_date