电商数据仓库开发-实现需求二

（一）需求二： 每日活跃用户主活相关

活跃用户的定义是指只要当天使用过 APP 就算是活跃用户，使用 这种操作属于主动操作APP，所以这种活跃我们也会称为主动活跃，简称主活
针对这个需求统计的指标和新增用户的指标类似
1：每日主活用户量
2：每日主活用户量的日环比和周同比

• 首先看第一个指标：每日主活用户量
  主活的概念和定义我们知道了，其实就是统计每天使用过 app的用户，所以我们可以直接使用dws 层的这个表 dws_user_active_history
  直接求和即可 获 取 到 当 日 的 主 活 用 户 量 ， 将 最 终 的 结 果 保 存 到 app层 的app_user_active_count 表中
• 接着看第二个指标：每日主活用户量的日环比和周同比
  这个指标直接基于每日主活用户量的表进行计算即可，把最终的结果 (app_user_active_count)
  保存到app 层的 app_user_active_count_ratio 表中

（1）APP层

1. app_user_active_count(每日主活用户量)

2. app_user_active_count_ratio( 每日主活用户量及日环比和
   周同比)

create external table if not exists app_mall.app_user_active_count(
    num    int
)partitioned by(dt string)
 row format delimited  
 fields terminated by '\t'
 location 'hdfs://bigdata01:9000/data/app/user_active_count';
 
insert overwrite table app_mall.app_user_active_count partition(dt='20260201') select
count(*) as num
from dws_mall.dws_user_active_history
where dt = '20260201';
 
 

create external table if not exists app_mall.app_user_active_count_ratio(
    num    int,
	day_ratio    double,
	week_ratio    double
)partitioned by(dt string)
 row format delimited  
 fields terminated by '\t'
 location 'hdfs://bigdata01:9000/data/app/user_active_count_ratio';


insert overwrite table app_mall.app_user_active_count_ratio partition(dt='20260201') select
num,
(num-num_1)/num_1 as day_ratio,
(num-num_7)/num_7 as week_ratio
from(
    select
    dt,
    num,
    lead(num,1) over(order by dt desc) as num_1,
    lead(num,7) over(order by dt desc) as num_7
    from app_mall.app_user_active_count
	where dt >=regexp_replace(date_add('2026-02-01',-7),'-','')
) as t
where dt = '20260201';

（2）开发脚本

针对 app层抽取脚本
1 ：表初始化脚本 ( 初始化执行一次 )
app_mall_init_table_2.sh

#!/bin/bash
# 需求二：每日活跃用户 ( 主活 ) 相关指标
# app层数据库和表初始化脚本，只需要执行一次即可
hive -e "
create database if not exists app_mall;
create external table if not exists app_mall.app_user_active_count(
num int
)partitioned by(dt string)
row format delimited
fields terminated by '\t'
location 'hdfs://bigdata01:9000/data/app/user_active_count';
create external table if not exists app_mall.app_user_active_count_ratio(
num int,
day_ratio double,
week_ratio double
)partitioned by(dt string)
row format delimited
fields terminated by '\t'
location 'hdfs://bigdata01:9000/data/app/user_active_count_ratio';
"

2 ：添加分区数据脚本 ( 每天执行一次 )
app_mall_add_partition_2.sh

#!/bin/bash
# ( ) 需求二：每日活跃用户 主活 相关指标
# 每天凌晨执行一次
 # 默认获取昨天的日期，也支持传参指定一个日期
if [ "z$1" = "z" ]
then
dt=`date +%Y%m%d --date="1 days ago"`
else
dt=$1
fi
# 20260201 2026-02-01 转换日期格式， 改为
dt_new=`date +%Y-%m-%d --date="${dt}"`
hive -e "
insert overwrite table app_mall.app_user_active_count partition(dt='${dt}') select
count(*) as num
from dws_mall.dws_user_active_history
where dt = '${dt}';
insert overwrite table app_mall.app_user_active_count_ratio partition(dt='${dt}') selec
num,
(num-num_1)/num_1 as day_ratio,
(num-num_7)/num_7 as week_ratio
from
(
select
dt,
num,
lead(num,1) over(order by dt desc) as num_1,
lead(num,7) over(order by dt desc) as num_7
from app_mall.app_user_active_count
where dt >=regexp_replace(date_add('${dt_new}',-7),'-','')
) as t
where dt = '${dt}';
"

（3）验证结果

由于日环比最起码要有两天的数据，周环比最起码要有一周的数据
所以在这我们需要至少计算一周的数据
计算 2026-02-01~2026-02-02-09
注意：这里依赖的基础表在需求一中已经生成过了，在这可以直接使用。

sh app_mall_add_partition_2.sh 20260201
 sh app_mall_add_partition_2.sh 20260202
sh app_mall_add_partition_2.sh 20260203
sh app_mall_add_partition_2.sh 20260204
sh app_mall_add_partition_2.sh 20260205
sh app_mall_add_partition_2.sh 20260206
sh app_mall_add_partition_2.sh 20260207
sh app_mall_add_partition_2.sh 20260208
sh app_mall_add_partition_2.sh 20260209

查看表 app_mall.app_user_active_count 和 app_mall.app_user_active_count_ratio 中的数据