需求分析

前面的两层中的表和需求一般没什么关系，就是把已有的数据接入进来，然后对数据进行清洗处理
但是后面的dws层和app层是和业务有关联的，所以在构建这两层中的表的时候，我们需要根据一些典型的业务场景来进行分析，在根据具体业务建表的时候尽可能把表设计的更加通用，可以满足后期一些类似业务需求
就是说我们在基于业务构建表的时候，不要直接一个SQL搞定，可以把一些复杂的SQL基于一些维度进行拆分，拆分出来一些中间表，再基于这些中间表统计最终的结果。
这样这个中间表里面的数据，我们后期针对一些类似的业务需求还是可以服用的。
需求一：每日新增用户相关指标
需求二：每日活跃用户相关指标
需求三：用户7日流失push提醒
需求四：每日启动App次数相关指标
需求五：操作系统活跃用户相关指标
需求六：APP崩溃相关指标

重新生成和采集用户数据

在计算这些需求的时候，为了保证大家在下面练习时计算的结果和我这边计算的结果保持一致，所以针对后面的测试数据就不再随机生成了，而是生成固定的数据，一共1个月的数据从 2026-02-01 到2026-02-28 的数据

运行GenerateUserActionDataV2，重新生成2026-02-01~2026-02-28的数据
执行上面的代码，此时数据就会被上传到HDFS上面。

写一个临时脚本，在脚本中写一个for循环，循环加载数据
tmp_load_ods_data.sh

#!/bin/bash
# 加载ods层的数据
for((i=1;i<=28;i++))
do
    if [ $i -lt 10 ]
    then
        dt="2026020"$i
    else
      dt="202602"$i    
    fi
    echo "ods_mall_add_partition.sh" ${dt}
    sh ods_mall_add_partition.sh ${dt}
done

tmp_load_dwd_data.sh

#!/bin/bash
# 加载dwd层的数据
for((i=1;i<=28;i++))
do
    if [ $i -lt 10 ]
    then
        dt="2026020"$i
    else
      dt="202602"$i    
    fi
    echo "dwd_mall_add_partition.sh" ${dt}
    sh dwd_mall_add_partition.sh ${dt}
done

删除ODS库

drop table ods_app_close;
drop table ods_click_good;
drop table ods_good_item;
drop table ods_good_list;
drop table ods_user_active;
drop schema ods_mall;

删除DWD库

drop table dwd_app_close;
drop table dwd_click_good;
drop table dwd_good_item;
drop table dwd_good_list;
drop table dwd_user_active;
drop schema dwd_mall;

生成ODS库

sh ods_mall_init_table.sh
sh tmp_load_ods_data.sh

生成DWD库

sh dwd_mall_init_table.sh
sh tmp_load_dwd_data.sh

需求一：每日新增用户相关指标

在统计新增用户时，用户是以设备标识（xaid字段）来判断的，每一个设备都有一个唯一设备码，因为会存在用户不登录的情况，以及多人共用一个账号的情况，所以根据用户id进行统计是不准确的。

新增用户是指第一次安装并且使用app的用户，后期卸载之后再使用就不算新用户了这个新增用户其实也可以称为新增设备，一个设备对应一个用户。

每日新增用户量

ods层的表名为：ods_user_active dwd层的表名为：dwd_user_active

第一步：我们基于清洗之后打开app上报的数据创建一个历史表（非全量），这个表里面包含的有xaid字段，针对每天的数据基于xaid进行去重
第二步：如果我们要计算2026年2月1日的新增用户量，就拿这一天上报的打开app的数据，和前面的历史表进行left join，使用xaid进行关联，关联不上的数据则为新增数据
举个例子：
(1) 第一步会产生一个历史表，dws_user_active_history，这个表中有一个xaid字段（初始时，这个表为空，通过tmp表insert）

xaid
a1
b1
c1
d1

(2) 第二步会产生一个临时表，表里面包含的是那一天上报的打开app的数据
dws_user_active_20260201_tmp

xaid
a1
b1
x1
y1
z1

(3) 对这两个表进行left join

dws_user_active_20260201_tmp	dws_user_active_history
xaid	xaid
a1	a1
b1	b1
x1	null
y1	null
z1	null

此时：dws_user_active_history.xaid 为null的数据条数即为当日新增用户数

第三步：将计算出来的每日新增用户信息保存到表dws_user_new_item表中，这个表按照天作为分区，便于后期其它需求使用这个表
第四步：基于dws_user_new_item对数据进行聚合，将计算出来的新增用户数量保存到结果表app_user_new_count

注意：在这里处理完之后，还需要将dws_user_active_20260201_tmp这个临时表中的数据insert到dws_user_active_history这个历史表中，最后删除这个临时表即可。

总结：
dws层需要创建三个表：dws_user_active_history、dws_user_active_20260201_tmp、dws_user_new_item
app层需要创建两个表：app_user_new_count和app_user_new_count_ratio
dws层：
1、dws_user_active_20260201_tmp

create table if not exists dws_mall.dws_user_active_20260201_tmp
(
    xaid  string,
    times int
);

insert overwrite table dws_mall.dws_user_active_20260201_tmp
select xaid,
       count(*) as times
from dwd_mall.dwd_user_active
where dt = '20260201'
group by xaid;

2、dws_user_active_history

create external table if not exists dws_mall.dws_user_active_history(
    xaid    string,
    times   int
)partitioned by(dt string)
 row format delimited  
 fields terminated by '\t'
 location 'hdfs://bigdata1:9000/data/dws/user_active_history';

3、dws_user_new_item

create external table if not exists dws_mall.dws_user_new_item
(
    xaid string
) partitioned by (dt string)
    row format delimited
    fields terminated by '\t'
    location 'hdfs://bigdata1:9000/data/dws/user_new_item';

insert overwrite table dws_mall.dws_user_new_item partition (dt = '20260201')
select duat.xaid
from dws_mall.dws_user_active_20260201_tmp duat
         left join (select xaid from dws_mall.dws_user_active_history group by xaid) duah
                   on duat.xaid = duah.xaid
where duah.xaid is null;

insert overwrite table dws_mall.dws_user_active_history partition (dt = '20260201')
select xaid,
       times
from dws_mall.dws_user_active_20260201_tmp;

app层

create external table if not exists app_mall.app_user_new_count
(
    num int
) partitioned by (dt string)
    row format delimited
    fields terminated by '\t'
    location 'hdfs://bigdata1:9000/data/app/user_new_count';

insert overwrite table app_mall.app_user_new_count partition (dt = '20260201')
select count(*) as num
from dws_mall.dws_user_new_item
where dt = '20260201';

每日新增用户量的日环比和周同比

同比：一般是指本期统计数据和往年的同时期的统计数据比较
环比：一般是指本期统计数据和上一期的统计数据作比较

日环比=(本期的数据-上一期的数据)/上一期的数据
日环比中的时间单位是天

周同比=(本期的数据-上一期的数据)/上一期的数据
周同比中的时间单位是周(7天)

实现思路：
直接基于app_user_new_count进行统计即可，可以统计出来某一天的日环比和周同比
生成一个新表app_user_new_count_ratio
里面包含日期、新增用户量、日环比、周同比

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

# 日环比，周同比
insert overwrite table app_mall.app_user_new_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_new_count
    where dt >=regexp_replace(date_add('2026-02-01',-7),'-','')
) as t
where dt = '20260201';

需求二：每日活跃用户相关指标

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

需求三：用户7日流失push提醒

什么是流失呢？
假设这个用户在2026年2月2日是新增用户，如果他在后续的7天内，也就是在2月9日内没有再使用app,则认为是流失用户，具体多少天属于流失用户，这个是需要产品经理根据对应产品的特点来定的，一般业内使用比较多的是7天这个时间点。
push是什么意思呢
大家平时是不是深受各种app的提醒轰炸，我针对大部分的app都禁用了消息推送，要不然每天手机上会有各种各样的推送消息，很烦，这个其实就是软件给你push的消息。
实现思路：
1：基于dws_user_active_history表，获取表中最近8天的数据，根据xaid进行分组，这样可以获取xaid以及xaid对应的多个日期（dt）
2：接着需要对xaid对应的dt进行过滤，获取xaid中最大的dt,判断这个dt是否等于（当天日期-7）,如果满足条件，则说明这个用户最近7日内没有使用app,就认为他属于7日流失用户

需求四：每日启动App次数相关指标

这个需求就是对每日打开app上报的数据进行统计
针对这个需求我们需要统计两个指标
1：每日人均启动App次数
2：每日APP启动次数分布（1次，2次，3次及以上）

需求五：操作系统活跃用户相关指标

这个需求是统计一下我们产品的目前主要用户群体是使用什么类型的操作系统
因为我们产品的app是有Android端和ios端的
如果我们的用户80%以上使用的都是Android,那么我们肯定要针对Android端的APP做更
多的优化支持，这样可以保证大部分用户的使用体验。
还有就是获取用户使用的手机型号，分辨率信息，这样可以更好的做适配。
针对这个需求我们主要统计以下指标：
1：操作系统活跃用户分布（安卓、I0S）
2：安卓系统版本活跃用户分布
3： I0S系统版本活跃用户分布
4：设备品牌活跃用户分布
5：设备型号活跃用户分布
6：网络类型活跃用户分布
针对这些指标统一分析，其实可以看出来，他们是有相似之处的。都是基于用户使用app 时上报的数据相关的一些指标

需求六：APP崩溃相关指标

这个需求是统计在不同平台系统不同版本下APP崩溃的情况，统计这个数据可以方便排查定位问题，如果发现某一个版本的APP崩溃频繁，则需要及时修复问题，推送新版本，提升用户体验。
针对这个需求主要统计下面几个指标
1：每日操作系统崩溃总计（安卓、I0S）
2：每日安卓系统-不同APP版本崩溃量
3：每日I0S系统-不同APP版本崩溃量
这里面这三个指标是有关联的，第一个是总的统计，第二个和第三个是不同维度的统计

大数据

用户行为数仓（DWS和APP）