大数据项目之电商数仓（总结）（十）:用户行为数据仓库

问题导读：
1、如何设计每日新增设备明细表？
2、如何使用每日新增设备表？
3、如何设计用户留存主题表？
4、如何设计留存用户数表？

上一篇：大数据项目之电商数仓（总结）（九）:用户行为数据仓库

第7章需求二：用户新增主题

首次联网使用应用的用户。如果一个用户首次打开某APP，那这个用户定义为新增用户；卸载再安装的设备，不会被算作一次新增。新增用户包括日新增用户、周新增用户、月新增用户。

7.1 DWS层（每日新增设备明细表）

1）建表语句

hive (gmall)>
drop table if exists dws_new_mid_day;
create external table dws_new_mid_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 '创建时间' 
)  COMMENT '每日新增设备信息'
stored as parquet
location '/warehouse/gmall/dws/dws_new_mid_day/';
复制代码

2）导入数据
用每日活跃用户表Left Join每日新增设备表，关联的条件是mid_id相等。如果是每日新增的设备，则在每日新增设备表中为null。

hive (gmall)>
insert into table dws_new_mid_day
select  
    ud.mid_id,
    ud.user_id , 
    ud.version_code , 
    ud.version_name , 
    ud.lang , 
    ud.source, 
    ud.os, 
    ud.area, 
    ud.model, 
    ud.brand, 
    ud.sdk_version, 
    ud.gmail, 
    ud.height_width,
    ud.app_time,
    ud.network,
    ud.lng,
    ud.lat,
    '2020-10-14'
from dws_uv_detail_day ud left join dws_new_mid_day nm on ud.mid_id=nm.mid_id
where ud.dt='2020-10-14' and nm.mid_id is null;
复制代码

3）查询导入数据

hive (gmall)> select count(*) from dws_new_mid_day ;
复制代码

4）导入数据脚本

[kgg@hadoop102 bin]$ vi dws_new_log.sh
#!/bin/bash

# 定义变量方便修改
APP=gmall
hive=/opt/module/hive/bin/hive
hadoop=/opt/module/hadoop-2.7.2/bin/hadoop

# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$1" ] ;then
   do_date=$1
else 
   do_date=`date -d "-1 day" +%F`
fi

echo "===日志日期为 $do_date==="
sql="
insert into table "$APP".dws_new_mid_day
select  
    ud.mid_id,
    ud.user_id , 
    ud.version_code , 
    ud.version_name , 
    ud.lang , 
    ud.source, 
    ud.os, 
    ud.area, 
    ud.model, 
    ud.brand, 
    ud.sdk_version, 
    ud.gmail, 
    ud.height_width,
    ud.app_time,
    ud.network,
    ud.lng,
    ud.lat,
    '$do_date'
from "$APP".dws_uv_detail_day ud left join "$APP".dws_new_mid_day nm on ud.mid_id=nm.mid_id
where ud.dt='$do_date' and nm.mid_id is null;
"

$hive -e "$sql"
复制代码

7.2 ADS层（每日新增设备表）

1）建表语句

hive (gmall)>
drop table if exists ads_new_mid_count;
create external table ads_new_mid_count
(
    `create_date` string comment '创建时间',
    `new_mid_count` BIGINT comment '新增设备数量'
) COMMENT '每日新增设备信息数量'
row format delimited fields terminated by '\t'
location '/warehouse/gmall/ads/ads_new_mid_count/';
复制代码

2）导入数据

hive (gmall)>
insert into table ads_new_mid_count 
select
create_date,
count(*)
from dws_new_mid_day
where create_date='2020-10-14'
group by create_date;
复制代码

3）查询导入数据

hive (gmall)> select * from ads_new_mid_count;
复制代码

4）导入数据脚本

[kgg@hadoop102 bin]$ vim ads_new_log.sh
#!/bin/bash

# 定义变量方便修改
APP=gmall
hive=/opt/module/hive/bin/hive
hadoop=/opt/module/hadoop-2.7.2/bin/hadoop

# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$1" ] ;then
   do_date=$1
else 
   do_date=`date -d "-1 day" +%F`
fi

echo "===日志日期为 $do_date==="
sql="
insert into table "$APP".ads_new_mid_count 
select
create_date,
count(*)
from "$APP".dws_new_mid_day
where create_date='$do_date'
group by create_date;"

$hive -e "$sql"
复制代码

第8章需求三：用户留存主题
8.1 需求目标
8.1.1 用户留存概念

8.1.2 需求描述

8.2 DWS层
8.2.1 DWS层（每日留存用户明细表）

1）建表语句

hive (gmall)>
drop table if exists 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/';
复制代码

2）导入数据（每天计算前1天的新用户访问留存明细）

hive (gmall)>
insert overwrite table dws_user_retention_day
partition(dt="2020-10-15")
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-10-15' and nm.create_date=date_add('2020-10-15',-1);
复制代码

3）查询导入数据（每天计算前1天的新用户访问留存明细）

hive (gmall)> select count(*) from dws_user_retention_day;
复制代码

8.2.2 DWS层（1,2,3,n天留存用户明细表）
1）导入数据（每天计算前1,2,3，n天的新用户访问留存明细）

hive (gmall)>
insert overwrite table dws_user_retention_day
partition(dt="2019-02-11")
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='2019-02-11' and nm.create_date=date_add('2019-02-11',-1)

union all
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,
    2 retention_day 
from  dws_uv_detail_day ud join dws_new_mid_day nm   on ud.mid_id =nm.mid_id 
where ud.dt='2019-02-11' and nm.create_date=date_add('2019-02-11',-2)

union all
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,
    3 retention_day 
from  dws_uv_detail_day ud join dws_new_mid_day nm   on ud.mid_id =nm.mid_id 
where ud.dt='2019-02-11' and nm.create_date=date_add('2019-02-11',-3);
复制代码

2）查询导入数据（每天计算前1,2,3天的新用户访问留存明细）

hive (gmall)> select retention_day , count(*) from dws_user_retention_day group by retention_day;
复制代码

8.2.3 Union与Union all区别
1）准备两张表

tableA                              tableB
id　　name　　score             id　　name　　score
1　　　a　　　　80               1　　  d　　　　48
2　　  b　　　　79               2　　　e　　　　23
3　　  c　　  　68               3　　　c　　　　86
复制代码

2）采用union查询

select name from tableA　　　　　　　　　　　　　
union　　　　　　　　　　　　　　　　　　　　　　　　
select name from tableB　　　　　　　　　　　　　
查询结果
name
a
d
b
e
c
复制代码

3）采用union all查询

select name from tableA
union all
select name from tableB
查询结果
name
a
b
c
d
e
c
复制代码

4）总结
（1）union会将联合的结果集去重，效率较union all差
（2）union all不会对结果集去重，所以效率高

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='2020-10-15' 
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 
    '2020-10-14', 
    ur.create_date,
    ur.retention_day, 
    ur.retention_count, 
    nc.new_mid_count,
    ur.retention_count/nc.new_mid_count*100
from ads_user_retention_day_count ur join ads_new_mid_count nc
on nc.create_date=ur.create_date;
复制代码

3）查询导入数据

hive (gmall)>select * from ads_user_retention_day_rate;
复制代码

最新经典文章，欢迎关注公众号

图文精华

大数据项目之电商数仓（总结）（十）:用户行为数据仓库

本帖被以下淘专辑推荐:

最佳新人

热心会员

推荐 /2