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

（一）需求三：用户日流失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 日流失用户。

例如：dws_user_active_history 表中有以下几条数据
xaid dt
a1 2026-02-01
a1 2026-02-05
b1 2026-02-01
b1 2026-02-02
c1 2026-02-03

针对这份数据，我们想要在 02-09 号统计用户 7 日流失量。那也就意味着要统计表里面在 02-02 使用过 APP ，但是在之后的 7 天内，一直到 02-09 号没有再使用过 app 的用户。

根据 xaid进行分组,获取里面最大的日期(最近一次使用 app的时间)
a1 2026-02-01,2026-02-05
b1 2026-02-01,2026-02-02
c1 2026-02-03

判断这个时间是否等于02-02 ，如果满足这个条件，就说明在 02-09 号之前的 7 天内没有使用过 app ，
这里的b1 满足条件，所以他就是 7日流失用户了。

依此类推，可以计算 14日流失 21日流失用户，针对流失的时间不同可以实现不同的策略 ,
给用户实现 push提醒，告诉用户他关注的商品降价了，或者给用户推荐他经常浏览的类似 商品，促进用户活跃，最终促进订单成交。

3. 将满足条件的 xaid数据保存到dws 层的 dws_user_lost_item表中
4. 对 dws_user_lost_item表中的数据进行聚合统计，统计用户 7日流失数据量，保存到 app层的 app_user_lost_count表中

（三）实现需求

（1）创建表

1） dws 层
dws_user_lost_item(7 日流失用户 xaid 数据)

create external table if not exists dws_mall.dws_user_lost_item(
    xaid    string
)partitioned by(dt string)
 row format delimited  
 fields terminated by '\t'
 location 'hdfs://bigdata01:9000/data/dws/user_lost_item';
 
insert overwrite table dws_mall.dws_user_lost_item partition(dt='20260201') select
xaid
from dws_mall.dws_user_active_history
where dt >= regexp_replace(date_add('2026-02-01',-7),'-','')
group by xaid
having max(dt) = regexp_replace(date_add('2026-02-01',-7),'-','');

2)app 层
app_user_lost_count(7 日流失用户量)

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

（2）开发脚本

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

#!/bin/bash
# 7 push 需求三：用户 日流失 提醒
# dws 层数据库和表初始化脚本，只需要执行一次即可
hive -e "
create database if not exists dws_mall;
create external table if not exists dws_mall.dws_user_lost_item(
xaid string
)partitioned by(dt string)
row format delimited
fields terminated by '\t'
location 'hdfs://bigdata01:9000/data/dws/user_lost_item';
"

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

#!/bin/bash
# 需求三：用户 7 日流失 push 提醒
# 每天凌晨执行一次
 # 默认获取昨天的日期，也支持传参指定一个日期
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 dws_mall.dws_user_lost_item partition(dt='${dt}') select
xaid
from dws_mall.dws_user_active_history
where dt>=regexp_replace(date_add('${dt_new}',-7),'-','')
group by xaid
having max(dt) = regexp_replace(date_add('${dt_new}',-7),'-','');
"

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

# 需求三：用户 7 日流失 push 提醒
# app 层数据库和表初始化脚本，只需要执行一次即可
hive -e "
create database if not exists app_mall;
create external table if not exists app_mall.app_user_lost_count(
num int
)partitioned by(dt string)
row format delimited
fields terminated by '\t'
 location 'hdfs://bigdata01:9000/data/app/user_lost_count';
"

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

#!/bin/bash
# 需求三：用户 7日流失 push提醒
# 每天凌晨执行一次
# 默认获取昨天的日期，也支持传参指定一个日期
if [ "z$1" = "z" ]
then
dt=`date +%Y%m%d --date="1 days ago"`
else
dt=$1
fi
hive -e "
insert overwrite table app_mall.app_user_lost_count partition(dt='${dt}') select
count(*) as num
from dws_mall.dws_user_lost_item
where dt = '${dt}';
"

（四）验证结果

注意：在执行的 SQL 的时候使用 20260209 这个日期
查询 app_mall.app_user_lost_count 表中的数据

hive (default)> select * from app_mall.app_user_lost_count;