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

实现思路

与衡量新增用户数相关的数据存储分别在ODS层的ods_app_open表和DWD层的dwd_app_open表，但ODS层的表基本上不会用到。

• 实现每日新增的用户数统计的步骤。

  • 首先，创建历史表dws_app_open_history，它包含有did字段，并且针对每天的数据对did进行去重。

  • 然后，产生一个临时表dws_app_open_yyyyMMdd_tmp（yyyyMMdd表示每天的日期）。

  • 之后，将临时表和历史表通过did字段执行LEFT JOIN，此时dws_app_open_history中did字段为null的用户（也就是没关联到的）就是当日新增用户。

  • 接着，将计算出来的每日新增用户数据保存到dws_user_daily_increment（按天分区）。

  • 最后，对dws_user_daily_increment表中的数据进行聚合，将结果保存到app_user_daily_increment表中。

  注意：完成计算后，还需要将临时表dws_app_open_yyyyMMdd_tmp中的数据保存到dws_app_open_history表，然后记得删除临时表。

• 实现每日新增用户数的环/同比统计的步骤。

  • 日环比：（当天数据 - 前一天数据） / 前一天数据。

  • 周同比：（当天数据 - 前一周数据） / 前一周数据。

  • 环比和同比都基于app_user_daily_increment表进行聚合统计。

  • 环比和同比的计算结果保存到app_user_daily_increment_count表。

基于以上实现思路，最终需要创建五张表。

• DWS层历史表dws_app_open_history。

• DWS层结果表dws_user_daily_increment。

• DWS层临时表dws_app_open_yyyyMMdd_tmp。

• APP层聚合表app_user_daily_increment。

• APP层聚合表app_user_daily_increment_count。

DWS层开发

先创建历史表dws_app_open_history和结果表dws_user_daily_increment，还是通过脚本的方式进行初始化。

> cd /home/work/warehouse_user_action
> vi dws_shopmall_requirement01_init.sh

#!/bin/bash
# 需求一：新增用户相关指标
# dws层数据库和表初始化，只需要执行一次

hive -e "
create database if not exists dws_shopmall;

-- num表示当天did的记录条数
create external table if not exists dws_shopmall.dws_app_open_history (
    did    string,
    num    int
) partitioned by(dt string)
  row format delimited  
  fields terminated by '\t'
  location 'hdfs://server01:9000/data/dws/app_open_history';

create external table if not exists dws_shopmall.dws_user_daily_increment (
    did    string
) partitioned by(dt string)
  row format delimited  
  fields terminated by '\t'
  location 'hdfs://server01:9000/data/dws/user_daily_increment';
"

然后再通过脚本创建需要每天都生成的临时表dws_app_open_yyyyMMdd_tmp，并且同时执行LEFT JOIN操作。

> cd /home/work/warehouse_user_action
> vi dws_shopmall_requirement01_add_partition.sh

#!/bin/bash
# 需求一：新增用户相关指标
# 每天凌晨执行一次
if [ "d$1" = "d" ]
then
dt=`date +%Y%m%d --date="1 days ago"`
else
dt=$1
fi

hive -e "
-- 因为这个表会每天创建后再删除，所以选择把它放到添加分区的脚本中
create table if not exists dws_shopmall.dws_app_open_${dt}_tmp (
    did   string,
    num   int
);

insert overwrite table dws_shopmall.dws_app_open_${dt}_tmp select
did,
count(*) as num
from dwd_shopmall.dwd_app_open
where dt = '${dt}'
group by did;

-- 考虑到脚本重新运行，所以每次执行时都要先删除历史表 dws_app_open_history 中的分区数据
-- 这是因为计算新增用户时要和 dws_app_open_history 进行 left join 关联查询
alter table dws_shopmall.dws_app_open_history drop partition(dt='${dt}');

insert overwrite table dws_shopmall.dws_user_daily_increment partition(dt='${dt}') select
daot.did
from dws_shopmall.dws_app_open_${dt}_tmp daot
left join (select did from dws_shopmall.dws_app_open_history group by did) daoh
on daot.did = daoh.did
where daoh.did is null;

insert overwrite table dws_shopmall.dws_app_open_history partition(dt='${dt}') select
did, num from dws_shopmall.dws_app_open_${dt}_tmp;

-- 删除临时表
drop table dws_shopmall.dws_app_open_${dt}_tmp
"

可以通过定时任务来执行它。

> crontab -e

# 每天凌晨0点1分执行
1 0 * * * /home/work/warehouse_user_action/dws_shopmall_requirement01_add_partition.sh

APP层开发

再来开发APP层的运行脚本，相同的套路，先初始化需要的聚合表app_user_daily_increment和app_user_daily_increment_count。

> cd /home/work/warehouse_user_action
> vi app_shopmall_requirement01_init.sh

#!/bin/bash
# 需求一：新增用户相关指标
# app层数据库和表初始化，只需要执行一次

hive -e "
create database if not exists app_shopmall;

-- 每日新增用户数
create external table if not exists app_shopmall.app_user_daily_increment(
    num    int
) partitioned by(dt string)
  row format delimited  
  fields terminated by '\t'
  location 'hdfs://server01:9000/data/app/user_daily_increment';

-- 日环比和周同比
create external table if not exists app_shopmall.app_user_daily_increment_count(
    num    int,
    day    double,
    week   double
) partitioned by(dt string)
  row format delimited  
  fields terminated by '\t'
  location 'hdfs://server01:9000/data/app/user_daily_increment_count';
"

然后再通过脚本每天给app_user_daily_increment和app_user_daily_increment_count插入数据。

> cd /home/work/warehouse_user_action
> vi app_shopmall_requirement01_add_partition.sh

#!/bin/bash
# 需求一：新增用户相关指标
# 每天凌晨执行一次
if [ "d$1" = "d" ]
then
dt=`date +%Y%m%d --date="1 days ago"`
else
dt=$1
fi

# 日期格式转换，将 20240101 转换为 2024-01-01
dt_new=`date +%Y-%m-%d --date="${dt}"`

hive -e "
insert overwrite table app_shopmall.app_user_daily_increment partition(dt='${dt}') select
count(*) as num
from dws_shopmall.dws_user_daily_increment
where dt = '${dt}';

insert overwrite table app_shopmall.app_user_daily_increment_count partition(dt='${dt}') select
num,
(num - num_yesterday) / num_yesterday as day,
(num - num_last7days) / num_last7days as week
from (
    select
    dt,
    num,
    lead(num, 1) over(order by dt desc) as num_yesterday,
    lead(num, 7) over(order by dt desc) as num_last7days
    from app_shopmall.app_user_daily_increment
    where dt >= regexp_replace(date_add('${dt_new}', -7), '-', '')
) as t
where dt = '${dt}';
"

可以通过定时任务来执行它。

> crontab -e

# 每天凌晨0点1分执行
1 0 * * * /home/work/warehouse_user_action/app_shopmall_requirement01_add_partition.sh

填充数据

为了验证效果，可以往DWS层和APP层的表中填充测试数据。

#!/bin/bash
# 往dws层填充数据
for((i=1;i<=31;i++))
do
    if [ $i -lt 10 ]
    then
        dt="2024010"$i
    else
        dt="202401"$i
    fi
    echo "dws_shopmall_requirement01_add_partition.sh" ${dt}
    sh dws_shopmall_requirement01_add_partition.sh ${dt}
done

同理，往APP层中填充数据也是一样。

#!/bin/bash
# 往app层填充数据
for((i=1;i<=31;i++))
do
    if [ $i -lt 10 ]
	then
        dt="2024010"$i
	else
        dt="202401"$i
    fi
    echo "app_shopmall_requirement01_add_partition.sh" ${dt}
    sh app_shopmall_requirement01_add_partition.sh ${dt}
done

---

感谢支持

更多内容，请移步《超级个体》。