8.3 ADS层
8.3.1 留存用户数
1)建表语句
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/’;
2)导入数据
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=‘2019-02-11’
group by create_date,retention_day;
3)查询导入数据
hive (gmall)> select * from ads_user_retention_day_count;
8.3.2 留存用户比率
1)建表语句
hive (gmall)>
drop table if exists ads_user_retention_day_rate;
create external table ads_user_retention_day_rate
(
`stat_date` string comment '统计日期',
`create_date` string comment '设备新增日期',
`retention_day` int comment '截止当前日期留存天数',
`retention_count` bigint comment '留存数量',
`new_mid_count` bigint comment '当日设备新增数量',
`retention_ratio` decimal(10,2) comment '留存率'
) COMMENT ‘每日用户留存情况’
row format delimited fields terminated by ‘\t’
location ‘/warehouse/gmall/ads/ads_user_retention_day_rate/’;
2)导入数据
hive (gmall)>
insert into table ads_user_retention_day_rate
select
‘2019-02-11’,
ur.create_date,
ur.retention_day,
ur.retention_count,
nc.new_mid_count,
ur.retention_count/nc.new_mid_count100
from
(
select
create_date,
retention_day,
count() retention_count
from dws_user_retention_day
where dt=‘2019-02-11’
group by create_date,retention_day
) ur join ads_new_mid_count nc on nc.create_date=ur.create_date;
3)查询导入数据
hive (gmall)>select * from ads_user_retention_day_rate;