【Spark数仓项目】需求五：用户活跃范围开发-拉链表

火之高兴

发布于 2024-07-25 15:42:25

520

发布于 2024-07-25 15:42:25

文章被收录于专栏：大数据应用技术

建表意义

通过建立用户的活跃时间段表，可以将用户的活跃时间段信息预先计算和存储起来，减少从dwd层重复查询的次数。这样可以在统计用户活跃需求时，直接从活跃时间段表中获取数据，提高查询的效率和性能，方便后续的数据分析和业务应用。

建表语句和测试数据

create table tmp.app_user_active_range(
  deviceid      string,     -- 设备编号
  first_login   string,     -- 首访日期
  start_dt      string,     -- 用户活跃区间起始时间
  end_dt        string      -- 用户活跃区间结束时间
)
partitioned by(dt string)
row format delimited
fields terminated by ','

create table tmp.app_user_active_day(
   deviceid   string
)partitioned by(dt string)
row format delimited 
fields terminated by ','

load data local inpath '/root/a.txt' 
into table tmp.app_user_active_range partition(dt='2022-11-03')

load data local inpath '/root/b.txt' 
into table tmp.app_user_active_day partition(dt='2022-11-04')

a).在root目录下创建a.txt和b.txt
[root@hadoop10 ~]# pwd
/root
[root@hadoop10 ~]# cat a.txt
deviceid1,2022-11-01,2022-11-01,9999-12-31
deviceid3,2022-11-02,2022-11-02,9999-12-31
deviceid4,2022-11-03,2022-11-03,9999-12-31
deviceid2,2022-11-01,2022-11-01,2022-11-02
deviceid6,2022-10-20,2022-10-20,2022-10-25
deviceid6,2022-10-20,2022-11-02,9999-12-31
[root@hadoop10 ~]# cat b.txt
deviceid1
deviceid2
deviceid5

连续活跃区间表的导入

Step 1

-- 全连接
select * from (
	select deviceid,dt from  tmp.app_user_active_day
) t1 
full join (
	select * from  tmp.app_user_active_range
) t2
on t1.deviceid = t2.deviceid

Step 2

在 Hive SQL 中，可以使用 NVL 函数来处理空值（NULL）。NVL 函数接受两个参数，如果第一个参数是 NULL，则返回第二个参数；如果第一个参数不是 NULL，则返回第一个参数。

NVL(expression, value)

根据Step 1全连接内容，增加第一列字段deviceid，如果t1.deviceid是空值，说明Step 1的全连接中，没有找到该用户，即这个deviceid是今天的dt中新出现的；如果t1.deviceid有值，说明在范围表中，根据以前的dt已经存在该用户设备，在Step 1中能够通过on条件全连接查找到，所以不需要添加在查找字段中出现该条用户。

结束时间9999-12-31表示到今天，即这个用户活跃并没有结束在今天，在今天仍然活跃。

select 
	nvl(t1.deviceid,t2.deviceid) as deviceid
from (
	select deviceid,dt from  tmp.app_user_active_day
) t1 
full join (
	select * from  tmp.app_user_active_range
) t2
on t1.deviceid = t2.deviceid

Step 3

第二个字段first_login，如果有 t2 表的数据,首选t2表中的first_login字段数据，如果没有的话，就固定写死，他的首访日期是今天，即2022-11-04号。

select 
	nvl(t1.deviceid,t2.deviceid) as deviceid,
	nvl(t2.first_login,'2022-11-04') as first_login
from (
	select deviceid,dt from  tmp.app_user_active_day
) t1 
full join (
	select * from  tmp.app_user_active_range
) t2
on t1.deviceid = t2.deviceid

Step 4

第三个字段start_dt 是求本次开始时间，我们要区分两种情况，一种是该用户第一次开始，即第一次活跃，那么此时start_dt就是今天的dt分区，可以写死为2022-11-04,另一种情况是t2表中已经存在，那么就直接使用t2.start_dt作为开始时间。本测试用例中，deviceid5符合第一种情况。

select 
	nvl(t1.deviceid,t2.deviceid) as deviceid,
	nvl(t2.first_login,'2022-11-04') as first_login,
	nvl(t2.start_dt,'2022-11-04') as start_dt
from (
	select deviceid,dt from  tmp.app_user_active_day
) t1 
full join (
	select * from  tmp.app_user_active_range
) t2
on t1.deviceid = t2.deviceid

Step 5

select 
	nvl(t1.deviceid,t2.deviceid) as deviceid,
	nvl(t2.first_login,'2022-11-04') as first_login,
	nvl(t2.start_dt,'2022-11-04') as start_dt,
 	case when t1.deviceid is null and t2.end_dt = '9999-12-31'
             then t2.dt
         when t1.deviceid is not null and t2.deviceid is null 
             then '9999-12-31' 
             else t2.end_dt 
         end as end_dt
from (
	select deviceid,dt from  tmp.app_user_active_day
) t1 
full join (
	select * from  tmp.app_user_active_range
) t2
on t1.deviceid = t2.deviceid

end_dt是求这个用户活跃的结束时间，根据Step 1全连接表的内容可得要分四种情况。

（1）如果t1.deviceid是空的，t2.end_dt是9999的年份：说明这个设备用户今天没有登录，所以今天的t1.deviceid是空的，而t2.end_dt是9999表示的是昨天这个用户登录了，而今天需要把9999改为昨天的dt日期；
（2）如果t1.deviceid 有值，t2.deviceid是空的：说明这个用户是今天新加入的，按9999处理为到今天活跃的用户即可；
（3）如果t1.deviceid 有值，t2.deviceid也有值：说明这个查询的end_dt结果是过去曾经写入好的年月，这个用户今天又登录了，过去的这条数据处理方式在Step 6中介绍，需要用union 。这里先处理这个用户另一条9999的end_dt即可；
（4）如果t1.deviceid是空的，t2.end_dt是2022...的过去的年月，说明这条是以前的数据，不用管它，直接取t2.end_dt即可。

Step 6

该步骤完善了Step 5中对于第三种end_dt情况的补充，需要再次拼接原表，拿到已经完成一个活跃周期的用户数据，即不是9999的日期就是过去完成的数据。

insert into tmp.app_user_active_range
partition(dt='2022-11-04')
select 
	nvl(t1.deviceid,t2.deviceid) as deviceid,
	nvl(t2.first_login,'2022-11-04') as first_login,
	nvl(t2.start_dt,'2022-11-04') as start_dt,
 	case when t1.deviceid is null and t2.end_dt = '9999-12-31'
             then t2.dt
         when t1.deviceid is not null and t2.deviceid is null 
             then '9999-12-31' else t2.end_dt end as end_dt
from (
	select deviceid,dt from  tmp.app_user_active_day where dt ='2022-11-04'
) t1 
full join (
	select * from  tmp.app_user_active_range where dt ='2022-11-03'
) t2
on t1.deviceid = t2.deviceid

UNION all

SELECT 
	t1.deviceid,t2.first_login,'2022-11-04' start_dt,'9999-12-31' end_dt
from (
	select deviceid,dt from  tmp.app_user_active_day where dt ='2022-11-04'
) t1 
INNER join (
	select deviceid,first_login from  tmp.app_user_active_range where dt ='2022-11-03'
	group by deviceid,first_login
	having max(end_dt ) != '9999-12-31'
) t2
on t1.deviceid = t2.deviceid


SELECT * from tmp.app_user_active_range