dwd_fact_coupon_use

优惠券领用表

属于累计型快照事实表

优惠卷的生命周期：领取优惠卷-》用优惠卷下单-》优惠卷参与支付

 

累积型快照事实表使用：统计优惠卷领取次数、优惠卷下单次数、优惠卷参与支付次数

 

特殊：用领取时间作为分区

 

图解：

左边为9号前的数据，     右边为10号的数据（最新数据）

 利用 full outer join 拼在一起

代码：

set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dwd_fact_coupon_use 
partition(dt) 
select 
    if(new.id is null,old.id,new.id), 
    if(new.coupon_id is null,old.coupon_id,new.coupon_id), 
    if(new.user_id is null,old.user_id,new.user_id), 
    if(new.order_id is null,old.order_id,new.order_id), 
    if(new.coupon_status is null,old.coupon_status,new.coupon_status), 
    if(new.get_time is null,old.get_time,new.get_time), 
    if(new.using_time is null,old.using_time,new.using_time), 
    if(new.used_time is null,old.used_time,new.used_time), 
    date_format(if(new.get_time is null,old.get_time,new.get_time),'yyyy-MM-dd') 
from 
( 
select 
    id, 
    coupon_id, 
    user_id, 
    order_id, 
    coupon_status, 
    get_time, 
    using_time, 
    used_time 
from dwd_fact_coupon_use 
where dt in ( 
            select 
                date_format(get_time,'yyyy-MM-dd') 
            from ods_coupon_use 
            where dt='2020-03-11' 
            ) 
)old 

full outer join 
( 
select 
    id, 
    coupon_id, 
    user_id, 
    order_id, 
    coupon_status,
    get_time, 
    using_time, 
    used_time 
from ods_coupon_use 
where dt='2020-03-11' 
)new 
on old.id=new.id;