Hadoop大数据技术与项目实战 实训指导书——4.6.5 用户留存率数据表设计及数据加载.pdf

山西职业技术学院《Hadoop大数据技术与项目实战》

用户留存率数据表设计及数据加载实训指导书

一、任务描述

创建ADS层留存率数据表，包含统计日期、设备新增日期、留存天数、留存数量、

设备新增数量、留存率等指标。加载用户登录信息数据，统计留存用户占当时新增用户

的比例。

二、能力目标

1.掌握sum聚合函数的计数累加操作

2.会正确使用sum、IF函数以及unionall操作

三、任务准备

1.启动HDFS及YARN服务

2.启动Hive元数据服务及客户端

四、任务实施

1.创建ADS层留存率数据表ads_user_retention_day_rate

切换到uzest数据库，执行删除同名表操作。

hive(default)useuzest;

hive(uzest)DROPTABLEIFEXISTSads_user_retention_day_rate;

下面创建用户留存率表ads_user_retention_day_rate。在本表中，包含有6个字段，

分别是统计日期、设备新增日期、留存天数、留存数量、设备新增数量、留存率等指标。

执行如下建表语句。

hive(uzest)createtableifnotexistsads_user_retention_day_rate(

`stat_date`stringCOMMENT统计日期,

1

山西职业技术学院《Hadoop大数据技术与项目实战》

`create_date`stringCOMMENT设备新增日期,

`retention_day`INTCOMMENT截止当前日期留存天数,

`retention_count`BIGINTCOMMENT留存数量,

`new_mid_count`BIGINTCOMMENT设备新增数量,

`retention_ratio`DECIMAL(10,2)COMMENT留存率

)COMMENT每日用户留存情况

ROWformatdelimited

FIELDSTERMINATEDBY\t

location/project/offlineDataWarehouse/uzest.db/ads/ads_user_retenti

on_day_rate/;

2.加载ads_user_retention_day_rate表数据

在DWT层的设备主题宽表dwt_uv_topic中，以设备号为单位，统计出了每一个设

备的首次登陆时间、末次登录时间以及累积登录天数，本节，基于这张表按照设定的规

则，统计用户留存率。

统计数据源是2021-04-04全天的用户登录信息。首先计算2021-04-01的3日留存

率，然后使用sum聚合函数来对所有的计数进行累加，求得4月1日的新增数量。接

下来，查询2021-04-01的设备新增数量，然后使用sum聚合函数来对所有的计数进行

累加，求得4月1日的新增数量。将查询出的日新增数量与留存数量相除，即可求得

2021-04-01的3日后留存率。同理，计算出4月2日的两天后用户留存率，以及4月

3日的1天后用户留存率。

最后，使用unionall将4月2日的两天后用户留存率，以及4月3日的1天后用户

留存率进行拼接，追加到该表中，执行如下HQL语句。

hive(uzest)INSERToverwriteTABLEads_user_retention_day_rate

Select*

Fromads_user_retention_day_rate

2

山西职业技术学院