累计型快照事实表——订单优惠表使用

累计快照适用于较短周期，有着明确的开始和结束状态的过程，如一个订单执行的过程，并记录过程中每个步骤的执行时间，使分析人员对执行的过程有整体的把握，周期快照事实表记录上每一个步骤的执行时间是逐步建立的，随着执行的过程逐步更新的事实表中。

优惠券的生命周期：领取优惠券→用优惠券下单→优惠券参与支付

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

1.建表

drop table if exists dwd_fact_coupon_use;  COMMENT '删除已存在的表'
create external table dwd_fact_coupon_use(
    `id` string COMMENT '编号',
    `coupon_id` string  COMMENT '优惠券ID',
    `user_id` string  COMMENT 'userid',
    `order_id` string  COMMENT '订单id',
    `coupon_status` string  COMMENT '优惠券状态',
    `get_time` string  COMMENT '领取时间',
    `using_time` string  COMMENT '使用时间(下单)',
    `used_time` string  COMMENT '使用时间(支付)'
) COMMENT '优惠券领用事实表'
PARTITIONED BY (`dt` string)
row format delimited fields terminated by '\t'
location '/warehouse/gmall/dwd/dwd_fact_coupon_use/';

2.数据装载

创建动态分区，根据条件会进行覆盖。

FULL OUTER JOIN关键字只要左表(new)和右表(old)其中一个表中存在匹配，则返回行

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-10-30'
    )
)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-10-30'
)new
on old.id=new.id;

3.查询加载结果

select * from dwd_fact_coupon_use where dt='2020-10-30';

问题总结

new表和old表？

new表是每天的新增与变化的数据；新增的数据，只需要插入到当天的分区就可以了，修改的数据要和old的数据做同步，九十八这些数据覆盖到old表上。

old表是需要同步的分区，每个分区里面或多或少都有数据需要被同步，每个分区里面，有的数据需要被修改，有的不需要被修改。

两个表怎么进行join操作？（new表： 新增与变化 和 old表：这个事实表）

• new表有变化，old表也有变化，需要进行更新
• new表有变化，old表没有变化，需要把有变化的数据插入到当天的分区
• new表没有变化，old表有变化，保留数据

更新数据的分区怎么分？

分为动态分区和静态分区。

具体是哪个分区不确定采用动态分区，根据dt不同的值到各自不同的分区里取，静态分区是已经确定了是哪个分区。

上面sql是按照dt进行分区，那每个dt里面应该放什么数据？

dt是按照优惠券领用时间get_time作为分区，因为using_time，used_time是当天，但是get_time是之前的时间，我们需要把using_time，used_time这两个时间更新到有这条数据的上，这样我们需要把相同的get_time的数据放在一起。

具体来说，我们设计了一个分区表，这个分区表存放的当天的领取记录，每天我们拉取的数据有三个时间的数据，有get_time，using_time，used_time，我们直接把get_time放到当天分区，然后用包含using_time，used_time去各自get_time所在的分区去跟新各自的数据。

如何更新数据？

用new表的数据更新old表的数据，new可以从ods_coupon_use的当天的分区表里面获取。

old的数据有数据被修改的分区，dt in （select  gettime from ods_coupon_use where dt = ‘2020-10-30’），需要能够更新的数据用new的对应数据去替换old的数据

old没有但是new有的数据，插入到新的分区里面。采用full outer join，上面这些逻辑需要做判断，需要用if...