数据仓库
| 阿里云国内75折 回扣 微信号：monov8 |
| 阿里云国际，腾讯云国际，低至75折。AWS 93折 免费开户实名账号 代冲值 优惠多多 微信号：monov8 飞机：@monov6 |
今天面试问到了一个问题假设我们的用户信息是天级别统计的那么如果计算多天的留存与回访就需要扫描多个分区这样计算资源比较多如何进行优化。
首先要介绍一下留存回访lt 这3个基本概念
基本概念
n日留存 n日后是否访问app
n日回访 n日活是否访问某个业务模块app中的某一板块当这个板块为app时留存==回访类似于留存
lt : 用户的生命周期相当于整体的活跃天数。 详细参考 用户全生命周期价值LTV指标如何计算 - 传播蛙
思路
我们需要构建map存储用户的历史活跃天数然后利用日期作为key进行快速索引
构建历史全量活跃天数表
with all_act_tmp as (
select
device_id,
map('${date}',1) as act_date_map
from test_dwd.dwd_user_device_active_di
where date = '${date}'
union all
select
device_id,
act_date_map
from test_dwd.user_dwd_device_active_df
where date = '${date-1}'
)
insert overwrite table test_dwd.user_dwd_device_active_df partition(date='${date}')
select
device_id,
union_map(act_date_map)
from
all_act_tmp
group by device_id 计算相关指标
select
device_id,
map_keys(act_date_map) as act_date_list
size(act_date_map) as all_lt_cnt,
if(act_date_map['${date}']=1,1,0) as is_act,
if(act_date_map['${date-1}'=1,1,0]) as is_act_1d,
if(act_date_map['${date-3}'=1,1,0]) as is_act_3d,
if(act_date_map['${date-7}'=1,1,0]) as is_act_7d,
...
size(sub_map(act_date_map,'${date-2}','${date}')) as lt_3d,
size(sub_map(act_date_map,'${date-6}','${date}')) as lt_7d,
size(sub_map(act_date_map,'${date-14}','${date}')) as lt_15d
...
from
test_dwd.user_dwd_device_active_df 相关功能函数
sub_map 函数 UDF
sub_map(map,start,end,byKey)
- Returns a sub map from mao, in which key(or value) is between start and end