6.1 离线数仓—ADS层用户主题需求的实现

---

前言

前面完成了DWS层流量主题需求的设计和开发，下面进行ADS层用户主题的设计和开发。

一、用户变动统计

1.需求说明和分析

1）需求说明
该需求包括两个指标，分别为流失用户数和回流用户数，以下为对两个指标的解释说明。

需求编号	统计周期	指标	说明
需求1	最近1日	流失用户数	之前活跃过的用户，最近一段时间未活跃，就称为流失用户。此处要求统计7日前（只包含7日前当天）活跃，但最近7日未活跃的用户总数。
需求2	最近1日	回流用户数	之前的活跃用户，一段时间未活跃（流失），今日又活跃了，就称为回流用户。此处要求统计回流用户总数。

2）需求分析
需求1：要求7日前活跃但最近7日未活跃的用户总数，可以通过用户的最后登录时间来判断是否符合条件。
需求2：找到当天登录的用户，且7天内未登录的用户。

2.建表语句

根据需求分析，可以知道，该表中应该包含的字段有：日期、流失用户数、回流用户数，所以建表语句如下：

DROP TABLE IF EXISTS ads_user_change;
CREATE EXTERNAL TABLE ads_user_change
(
    `dt`               STRING COMMENT '统计日期',
    `user_churn_count` BIGINT COMMENT '流失用户数',
    `user_back_count`  BIGINT COMMENT '回流用户数'
) COMMENT '用户变动统计'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
    LOCATION '/warehouse/gmall/ads/ads_user_change/';

3.数据装载

如何求流失用户数：这里求的是每日新增的流失用户数，所以只要确定用户的最后登录日期在8天前的当天即可。
语句如下：

	select
        '2020-06-14' dt,
    	count(*) user_churn_count
    from dws_user_user_login_td
    where dt='2020-06-14'
    and login_date_last=date_add('2020-06-14',-7)

如何求回流用户数：先找到当天登录了的用户，再找到这些用户在这次登录前最后一次登录的时间（其实这个时间就是td表前一天分区里的时间），时间差大于等于8就代表是回流用户。
语句如下：

select
        '2020-06-14' dt,
        count(*) user_back_count
    from
    (
        select
            user_id,
            login_date_last
        from dws_user_user_login_td
        where dt='2020-06-14'
    )t1
    join
    (
        select
            user_id,
            login_date_last login_date_previous
        from dws_user_user_login_td
        where dt=date_add('2020-06-14',-1)
    )t2
    on t1.user_id=t2.user_id
    where datediff(login_date_last,login_date_previous)>=8

将两个字段的值join起来就得到了最终的数据：

insert overwrite table ads_user_change
select * from ads_user_change
union
select
    churn.dt,
    user_churn_count,
    user_back_count
from
(
    select
        '2020-06-14' dt,
        count(*) user_churn_count
    from dws_user_user_login_td
    where dt='2020-06-14'
    and login_date_last=date_add('2020-06-14',-7)
)churn
join
(
    select
        '2020-06-14' dt,
        count(*) user_back_count
    from
    (
        select
            user_id,
            login_date_last
        from dws_user_user_login_td
        where dt='2020-06-14'
    )t1
    join
    (
        select
            user_id,
            login_date_last login_date_previous
        from dws_user_user_login_td
        where dt=date_add('2020-06-14',-1)
    )t2
    on t1.user_id=t2.user_id
    where datediff(login_date_last,login_date_previous)>=8
)back
on churn.dt=back.dt;

二、用户留存率

1.需求说明和分析

1）需求说明
       留存分析一般包含新增留存和活跃留存分析。
       新增留存分析是分析某天的新增用户中，有多少人有后续的活跃行为。活跃留存分析是分析某天的活跃用户中，有多少人有后续的活跃行为。
       留存分析是衡量产品对用户价值高低的重要指标。
       此处要求统计新增留存率，新增留存率具体是指留存用户数与新增用户数的比值，例如2020-06-14新增100个用户，1日之后（2020-06-15）这100人中有80个人活跃了，那2020-06-14的1日留存数则为80，2020-06-14的1日留存率则为80%。
       要求统计每天的1至7日留存率，如下图所示。

2）需求分析
根据需求可知，要包含以下字段：统计日期、新增用户日期、从新增到今天的天数、留存用户数量、新增用户数量、留存率

2.建表语句

DROP TABLE IF EXISTS ads_user_retention;
CREATE EXTERNAL TABLE ads_user_retention
(
    `dt`              STRING COMMENT '统计日期',
    `create_date`     STRING COMMENT '用户新增日期',
    `retention_day`   INT COMMENT '截至当前日期留存天数',
    `retention_count` BIGINT COMMENT '留存用户数量',
    `new_user_count`  BIGINT COMMENT '新增用户数量',
    `retention_rate`  DECIMAL(16, 2) COMMENT '留存率'
) COMMENT '用户留存率'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
    LOCATION '/warehouse/gmall/ads/ads_user_retention/';

3.数据装载

3.1我的思路

假如拿到的是2020-06-14日的数据，那么可以计算2020-06-13、2020-06-12…等7天的留存率，先计算2020-6-13日的留存率。
1）先拿到2020-06-14日的活跃用户

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

2）再拿到2020-06-13日的注册用户

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

3）两张表中共有的用户是2020-06-13日新增用户在2020-06-14日的留存用户，将两张表关联起来，算出2020-06-13日的留存率

select
  '2020-06-14',
  '2020-06-13',
  1,
  sum(if(t2.user_id is not null,1,0)),
  count(*),
  sum(if(t2.user_id is not null,1,0))/count(*)
from
(
  select
    inc_dt,
    user_id
  from dwd_user_register_inc
  where dt='2020-06-13'
)t1
left join
(
  select
    user_id,
    dt
  from dwd_user_login_inc
  where dt='2020-06-14'
)t2
on t1.user_id=t2.user_id

拿到2020-06-13所有的注册用户跟2020-6-14登录的用户左连接，右边不为null则代表是留存用户
4）同理，2020-06-12的留存率如下：

select
  '2020-06-14',
  '2020-06-12',
  2,
  sum(if(t2.user_id is not null,1,0)),
  count(*),
  sum(if(t2.user_id is not null,1,0))/count(*)