数仓-累计快照表应用

背景

随着数据量的增大，如何更好的管理和使用数据，并保证数据质量成为新的挑战，同时为了不浪费机器的计算资源，通过合理的数据建模，更快更方便的出业务所需要的统计数据成为数据治理的有效手段；其中累计快照表是常用的建模结果表。

系统中的数据都是有实体和实体之间的一系列事件产生的，比如，用户登陆APP，产生用户登陆数据表，用户下单购买商品，产生订单数据表，用户浏览页面参数，产生浏览事件表，用户点击页面，产生点击页面数据表等，根据 kibal 维度建模方法，将用户的这一系列原子的事件建立事件流水表，这些事件流水表有两部分数据组成， 即维度数据和事实数据。

维度表

-- 用户信息表
create table t_user{
Findex bigint  comment '主键',
Fuser_id int comment '用户ID',
Fname string comment '用户名称' ,
Fsex  int comment '性别' ,
....
}

-- 手机APP
create table t_app{
Findex bigint  comment '主键',
Fapp_id  int comment 'appID',
Fapp_namestring comment 'app名称' ,
....
}
-- 商品信息
create table t_app{
Findex bigint  comment '主键',
Fgoods_id string    comment '商品ID',
Fgoods_name string  comment '商品名称' ,
....
}

用户登陆手机APP 产生事件, 并产生事实数据，用户登陆事实表(Fuser_id,Fapp_id,Flogin_time)
用户下单参数订单事实数据表(Forder_id,Fuser_id,Fgoods_id,Foder_amount,Forder_time)
。。。。

create table t_user_login_detail{
Findex bigint comment '主键',
Fuser_id   int  comment '用户ID',   -- 维度数据
Fapp_id    string  comment 'appId' ,  -- 维度数据
Flogin_time timestamp  comment '登陆时间' , --  事实数据
....
}

这样系统中的数据是有一系列 维度表和事件事实表组成，同时为了数据统计方便，还需要建立一张累计快照表，一用户的累计事件快照表，就是记录用户在系统中的事件集，将每个事件类型的首次发生时间和最后一次发生时间记录在同一张表，并取每天快照；比如用户行为快照表，取每天用户的快照，记录用户的首次登陆时间，最后一次登陆时间

create table t_user_action_snap{
Fdate  bigint comment '主键',
Fuser_id   int  comment '用户ID',   
Fregist_time timestamp    comment '注册时间', 
Ffirst_login_time   timestamp   comment '首次登陆时间' ,   
Flast_login_time   timestamp  comment '最后一次登陆时间' , 
Ffirst_order_time timestamp comment '首次下单时间',
Ffirst_order_id string '首次下单ID',
Flast_order_time timestamp comment '最后一次下单时间',
Flast_order_time timestamp comment '最后一次下单ID'
....
} partitioned by (dt string comment '快照产生的时间') stored as orc

有了这张快照表，我们统计运营指标就很方便

比如常用的运营指标 日新增用户（Fnu_d）,日活跃用户(Fau_d),次日留存，2日留存，3日留存，七日留存，14日留存等。回流用户，留存率，转化用户

新增用户，首次登陆用户，最后一次登陆用户，日活用户(au)

-- 简单指标直接出数
select 
,count(if(Fregist_time=dt,Fuid,null) as Fnu_d -- 新用户
,count(if(Ffirst_login_time=dt,Fuid,null) as Flu_d_first -- 首次登陆用户
,count(if(Flast_login_time=dt,Fuid,null) as Fau_d  -- 最后一次登陆用户，也就是dt 日的日活用户，假设业务指标是登陆了算活跃用户
from t_user_action_snap where dt='2020-04-22' 

留存率的计算

--’2020-04-22‘ 为最新的一天的时间
create table t_user_ur_base_data{
Fcurrent date comment ’当前日期‘，
Ftarget  date comment ’目标日期‘，
Fdiff     int    comment ’当前日期距离目标日期的天数’，
Fur  date comment ’留存用户数‘，
}

---  取数逻辑
select s1.dt  as Fcurrent
,          s2.dt as Ftarget
,          datediff(s1.dt,s2.dt) as Fdiff
,          count(s1.Fuid) as Fur  
t_user_action_snap  s1 inner join t_user_action_snap s2 on s1.Fuser_id=s2.Fuser_id
and s1.dt='2020-04-22' ans s2.dt between date_sub('2020-04-22',90) and '2020-04-22'
and date(s1.Flast_login_time )=s1.dt and date(s2.Flast_login_time )=s2.dt

这样算出来Fcurrent 相对于Ftarget 时间的留存用户数

select  t1.Ftarget
,max(if(t1.Fdiff=0,Fur,0) as Fau  -- Ftarget 的活跃用户
,max(if(t1.Fdiff=1,t1.Fur,0) as Fur_1d -- Ftarget  天的次日留存率
,max(if(t1.Fdiff=2,t1.Fur,0) as Fur_2d -- Ftarget  天的2日留存率
,max(if(t1.Fdiff=3,t1.Fur,0) as Fur_3d -- Ftarget  天的3日留存率
,max(if(t1.Fdiff=4,t1.Fur,0) as Fur_4d -- Ftarget  天的4日留存率
,max(if(t1.Fdiff=5,t1.Fur,0) as Fur_5d -- Ftarget  天的5日留存率
,max(if(t1.Fdiff=7,t1.Fur,0) as Fur_w -- Ftarget  天的7日留存率
,max(if(t1.Fdiff=14,t1.Fur,0) as Fur_2w -- Ftarget  天的14日留存率
,max(if(t1.Fdiff=30,t1.Fur,0) as Fur_m -- Ftarget  天的30日留存率
,max(if(t1.Fdiff=60,t1.Fur,0) as Fur_2m -- Ftarget  天的60日留存率
,max(if(t1.Fdiff=90,t1.Fur,0) as Fur_3m -- Ftarget  天的90日留存率
from  t_user_ur_base_data t1 
left join t_user_ur_base_data t2 on t1.Ftarget=t2.Fcurrent
where t1.Ftarget between '2020-04-01' and '2020-04-22'
and t2.Fdiff=0
group by t1.Ftarget
order by t1.Ftarget desc