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

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

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

1）建表语句

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。

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）查询导入数据

select count(*) from dws_new_mid_day ;

4）导入数据脚本

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"

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

1）建表语句

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）导入数据

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）查询导入数据

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"

第13章需求三：用户留存主题

13.1 需求目标

13.1.1 用户留存概念

13.1.2 需求描述

13.2 DWS层

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

1）建表语句

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天的新用户访问留存明细）

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天的新用户访问留存明细）

select count(*) from dws_user_retention_day;

13.2.2 DWS层（1,2,3,n天留存用户明细表）

1）导入数据

（每天计算前1,2,3，n天的新用户访问留存明细）

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天的新用户访问留存明细）

select retention_day , count(*) from dws_user_retention_day group by retention_day;

13.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不会对结果集去重，所以效率高

13.3 ADS层

13.3.1 留存用户数

1）建表语句

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）导入数据

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）查询导入数据

 select * from ads_user_retention_day_count;

13.3.2 留存用户比率

1）建表语句

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）导入数据

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）查询导入数据

select * from ads_user_retention_day_rate;

posted on 2021-01-20 16:41 大码王阅读(378) 评论(0) 编辑收藏举报

刷新页面返回顶部

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

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

1）建表语句

2）导入数据

3）查询导入数据

4）导入数据脚本

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

1）建表语句

2）导入数据

3）查询导入数据

4）导入数据脚本

第13章 需求三：用户留存主题

13.1 需求目标

13.1.1 用户留存概念

13.1.2 需求描述

13.2 DWS层

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

1）建表语句

2）导入数据

3）查询导入数据

13.2.2 DWS层（1,2,3,n天留存用户明细表）

1）导入数据

2）查询导入数据

13.2.3 Union与Union all区别

1）准备两张表

2）采用union查询

3）采用union all查询

4）总结

13.3 ADS层

13.3.1 留存用户数

1）建表语句

2）导入数据

3）查询导入数据

13.3.2 留存用户比率

1）建表语句

2）导入数据

3）查询导入数据

公告

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

第13章需求三：用户留存主题