15. 数据仓库分层之DWS层、ADS层--用户留存主题

留存用户：某段时间新增（活跃）用户隔段时间后再次使用。
留存率：留存用户占当时新增用户的比例。

1. 分析

2. DWS层创建每日留存用户表dws_user_retention_day

   create external table dws_user_retention_day 
   (
      `mid_id` string COMMENT '设备唯一标识',
      `user_id` string COMMENT '用户标识', 
      `version_code` string COMMENT '程序版本号', 
      `version_name` string COMMENT '程序版本名', 
      `lang` string COMMENT '系统语言', 
      `source` string COMMENT '渠道号', 
      `os` string COMMENT '安卓系统版本', 
      `area` string COMMENT '区域', 
      `model` string COMMENT '手机型号', 
      `brand` string COMMENT '手机品牌', 
      `sdk_version` string COMMENT 'sdkVersion', 
      `gmail` string COMMENT 'gmail', 
      `height_width` string COMMENT '屏幕宽高',
      `app_time` string COMMENT '客户端日志产生时的时间',
      `network` string COMMENT '网络模式',
      `lng` string COMMENT '经度',
      `lat` string COMMENT '纬度',
      `create_date`    string  comment '设备新增时间',
      `retention_day`  int comment '截止当前日期留存天数'
   )  COMMENT '每日用户留存情况'
   PARTITIONED BY (`dt` string)
   stored as parquet
   location '/warehouse/gmall/dws/dws_user_retention_day/';

3. 插入数据

   insert overwrite table dws_user_retention_day 
   partition(dt='2020-01-02')
   select
      nm.mid_id,
      nm.user_id , 
      nm.version_code , 
      nm.version_name , 
      nm.lang , 
      nm.source, 
      nm.os, 
      nm.area, 
      nm.model, 
      nm.brand, 
      nm.sdk_version, 
      nm.gmail, 
      nm.height_width,
      nm.app_time,
      nm.network,
      nm.lng,
      nm.lat,
      nm.create_date,
      1 retention_day
   from dws_uv_detail_day ud join dws_new_mid_day nm 
   on ud.mid_id=nm.mid_id
   where ud.dt='2020-01-02' and nm.create_date=date_add('2020-01-02',-1);

4. 查看结果

   hive (gmall)> select count(*) from dws_user_retention_day;

5. ADS层创建留存用户数表

   hive (gmall)>
   drop table if exists ads_user_retention_day_count;
   create external table ads_user_retention_day_count 
   (
     `create_date`       string  comment '设备新增日期',
     `retention_day`     int comment '截止当前日期留存天数',
     `retention_count`    bigint comment  '留存数量'
   )  COMMENT '每日用户留存情况'
   row format delimited fields terminated by '\t'
   location '/warehouse/gmall/ads/ads_user_retention_day_count/';

6. 插入数据

   hive (gmall)>
   insert into table ads_user_retention_day_count 
   select
      create_date,
      retention_day,
      count(*) retention_count
   from dws_user_retention_day
   where dt='2020-01-02' 
   group by create_date,retention_day;

7. 查询数据

   hive (gmall)> select * from ads_user_retention_day_count;