离线数仓——（新增用户业务指标，每日新增用户明细表，留存用户业务指标，用户留存明细表）

新增用户业务指标

1. 留存用户：指某段时间的新增用户，经过一段时间后，仍继续使用应用认为是留存用户
2. 新增会员：第一次使用应用的用户，定义为新增会员；卸载再次安装的设备，不会被算作一次新增。
3. 计算关系：先计算新增会员 => 再计算留存用户
4. 在DWD用户每日启动明细表中，新增用户数+旧的所有用户信息=新的所有用户信息，此时的计算关系是，先计算新增用户，然后更新所有用户信息。
5. 计算每日新增用户数：在所有的用户信息中增加时间列，表示这个用户是那一天成为新增用户的，此时，只需要一张表，所有用户信息（id，dt），然后将新增用户插入所有用户表中

每日新增用户明细表

创建DWS层每日新增用户明细表：【dws_load_member_add_day.sh】

create table dws.dws_member_add_day
(
`device_id` string,
`uid` string,
`app_v` string,
`os_type` string,
`language` string,
`channel` string,
`area` string,
`brand` string,
`dt` string
) COMMENT '每日新增用户明细'
stored as parquet;

从DWS层的用户启动日志明细表【dws_member_start_day】中加载DWS层数据到【dws_load_member_add_day】表中，dws_load_member_add_day.sh脚本如下：

#!/bin/bash
source /etc/profile
if [ -n "$1" ]
then
	do_date=$1
else
	do_date=`date -d "-1 day" +%F`
fi

sql="
insert into table dws.dws_member_add_day
select t1.device_id,
t1.uid,
t1.app_v,
t1.os_type,
t1.language,
t1.channel,
t1.area,
t1.brand,
'$do_date'
	from dws.dws_member_start_day t1 left join
dws.dws_member_add_day t2
	on t1.device_id=t2.device_id
 where t1.dt='$do_date'
	and t2.device_id is null;
"
hive -e "$sql"

此时，每日新增用户明细表中的数据如下：

hive (dws)> select * from dws_member_add_day limit 5;
OK
dws_member_add_day.device_id	dws_member_add_day.uid	dws_member_add_day.app_v	dws_member_add_day.os_type	dws_member_add_day.language dws_member_add_day.channel	dws_member_add_day.area	dws_member_add_day.brand	dws_member_add_day.dt
1FB872-9A100100001	2F10092A100001	1.1.18	4.30	chinese	AQ	营口	iphone-5	2020-07-31
1FB872-9A100100002	2F10092A100002	1.1.1	0.08	chinese	NN	淮安	iphone-2	2020-07-31
1FB872-9A100100003	2F10092A100003	1.1.1	0.07	chinese	OV	阳江	iphone-2	2020-07-31
1FB872-9A100100004	2F10092A100004	1.1.4	0.0.1	chinese	YF	杭州	Huawei-2	2020-07-31
1FB872-9A100100005	2F10092A100005	1.1.19	8.02	chinese	XY	拉萨	xiaomi-0	2020-07-31

留存用户业务指标

1. 留存用户与留存率：某段时间的新增用户，经过一段时间后，仍继续使用应用认为是留存用户；这部分用户占当时新增用户的比例为留存率。
2. 留存用户需求：可计算1日，2日，3日的用户留存数和用户留存率
   

1号的10W新会员，在2号留存3W，这3W在2号的启动日志明细表中【dws_member_start_day】

用户留存明细表

创建DWS层用户留存明细表：【dws_member_retention_day】

create table dws.dws_member_retention_day
(
`device_id` string,
`uid` string,
`app_v` string,
`os_type` string,
`language` string,
`channel` string,
`area` string,
`brand` string,
`add_date` string comment '用户新增时间',
`retention_date` int comment '留存天数'
)COMMENT '每日用户留存明细'
PARTITIONED BY (`dt` string)
stored as parquet;

从DWS层dws_member_start_day以及dws_member_add_day中加载数据，脚本如下:

#!/bin/bash

source /etc/profile

if [ -n "$1" ] ;then
	do_date=$1
else
	do_date=`date -d "-1 day" +%F`
fi

sql="
drop table if exists tmp.tmp_member_retention;
create table tmp.tmp_member_retention as
(
 select t2.device_id,
		t2.uid,
		t2.app_v,
		t2.os_type,
		t2.language,
		t2.channel,
		t2.area,
		t2.brand,
		t2.dt add_date,
		1
	from dws.dws_member_start_day t1 join dws.dws_member_add_day t2 on t1.device_id=t2.device_id
  where t2.dt=date_add('$do_date', -1)
    and t1.dt='$do_date'
 union all

 select t2.device_id,
		t2.uid,
		t2.app_v,
		t2.os_type,
		t2.language,
		t2.channel,
		t2.area,
		t2.brand,
		t2.dt add_date,
		2
	from dws.dws_member_start_day t1 join dws.dws_member_add_day t2 on t1.device_id=t2.device_id
 where t2.dt=date_add('$do_date', -2)
   and t1.dt='$do_date'
union all

 select t2.device_id,
		t2.uid,
		t2.app_v,
		t2.os_type,
		t2.language,
		t2.channel,
		t2.area,
		t2.brand,
		t2.dt add_date,
		3
   from dws.dws_member_start_day t1 join dws.dws_member_add_day
t2 on t1.device_id=t2.device_id
  where t2.dt=date_add('$do_date', -3)
    and t1.dt='$do_date'
);

insert overwrite table dws.dws_member_retention_day partition(dt='$do_date') select * from tmp.tmp_member_retention;
" 

hive -e "$sql"

创建ADS层用户留存数明细表【ads_member_retention_count】，用户留存率明细表【ads_member_retention_rate】

create table ads.ads_member_retention_count
(
`add_date` string comment '新增日期',
`retention_day` int comment '截止当前日期留存天数',
`retention_count` bigint comment '留存数'
) COMMENT '用户留存数'
partitioned by(dt string)
row format delimited fields terminated by ',';

create table ads.ads_member_retention_rate
(
`add_date` string comment '新增日期',
`retention_day` int comment '截止当前日期留存天数',
`retention_count` bigint comment '留存数',
`new_mid_count` bigint comment '当日用户新增数',
`retention_ratio` decimal(10,2) comment '留存率'
) COMMENT '用户留存率'
partitioned by(dt string)
row format delimited fields terminated by ',';

加载ADS层用户留存数明细表【ads_member_retention_count】，用户留存率明细表【ads_member_retention_rate】数据

#!/bin/bash

source /etc/profile

if [ -n "$1" ] ;then
	do_date=$1
else
	do_date=`date -d "-1 day" +%F`
fi

sql="
insert overwrite table ads.ads_member_retention_count
partition (dt='$do_date')
select add_date, retention_date,
	   count(*) retention_count
  from dws.dws_member_retention_day
 where dt='$do_date'
group by add_date, retention_date;

insert overwrite table ads.ads_member_retention_rate
partition (dt='$do_date')
select t1.add_date,
	   t1.retention_day,
	   t1.retention_count,
	   t2.cnt,
	   t1.retention_count/t2.cnt*100
  from ads.ads_member_retention_count t1 join
ads.ads_new_member_cnt t2 on t1.dt=t2.dt
  where t1.dt='$do_date';
"
hive -e "$sql"