离线数仓（八）【DWD 层开发】（2）-阿里云开发者社区

离线数仓（八）【DWD 层开发】（1）https://developer.aliyun.com/article/1532413

1.3、交易域取消订单事务事实表

注意是取消订单，不是退单，还没完成支付呢。

1.3.1、设计表结构

依然是那4个步骤：

选择业务过程：取消订单
声明粒度：谁+在什么时候+取消了哪个商品
确认维度：时间、用户、商品、地区、活动、优惠券
确认事实：取消的商品件数、取消的金额、下单的原始金额、最终下单金额、活动优惠金额、优惠券金额

可以看到，取消订单表的大部分维度和下单表是一致的，只是语义不一样而已。

1.3.2、建表语句

DROP TABLE IF EXISTS dwd_trade_cancel_detail_inc;
CREATE EXTERNAL TABLE dwd_trade_cancel_detail_inc
(
    `id`                    STRING COMMENT '编号',
    `order_id`              STRING COMMENT '订单id',
    `user_id`               STRING COMMENT '用户id',
    `sku_id`                STRING COMMENT '商品id',
    `province_id`           STRING COMMENT '省份id',
    `activity_id`           STRING COMMENT '参与活动规则id',
    `activity_rule_id`      STRING COMMENT '参与活动规则id',
    `coupon_id`             STRING COMMENT '使用优惠券id',
    `date_id`               STRING COMMENT '取消订单日期id',
    `cancel_time`           STRING COMMENT '取消订单时间',
    `source_id`             STRING COMMENT '来源编号',
    `source_type_code`      STRING COMMENT '来源类型编码',
    `source_type_name`      STRING COMMENT '来源类型名称',
    `sku_num`               BIGINT COMMENT '商品数量',
    `split_original_amount` DECIMAL(16, 2) COMMENT '原始价格',
    `split_activity_amount` DECIMAL(16, 2) COMMENT '活动优惠分摊',
    `split_coupon_amount`   DECIMAL(16, 2) COMMENT '优惠券优惠分摊',
    `split_total_amount`    DECIMAL(16, 2) COMMENT '最终价格分摊'
) COMMENT '交易域取消订单明细事务事实表'
    PARTITIONED BY (`dt` STRING)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
    STORED AS ORC
    LOCATION '/warehouse/gmall/dwd/dwd_trade_cancel_detail_inc/'
    TBLPROPERTIES ('orc.compress' = 'snappy');

1.3.3、数据流向

我们需要知道取消订单会对哪些表产生影响，事实上，只会对 order_info 产生影响（改变 order_info 的 order_status 字段），同样，我们的首日数据（存在 ods_order_info_inc 当中）当中也存在一些取消订单的数据需要我们进行分区处理。

不同 order_status 对应的状态：

这里需要注意的是：对于已取消、已完成、退款完成这些操作表示的都是最终的操作，这些订单的状态不会再发生变化；但是对于未支付、已支付、退款中这些订单状态都还可能会发送变化。所以当我们在找支付成功的订单时，不仅要考虑订单状态是已支付状态，还有退款完成、已支付这些状态也经历过已支付状态。

1.3.4、首日数据装载

set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dwd_trade_cancel_detail_inc partition (dt)
select
    od.id,
    order_id,
    user_id,
    sku_id,
    province_id,
    activity_id,
    activity_rule_id,
    coupon_id,
    date_format(canel_time,'yyyy-MM-dd') date_id,
    canel_time,
    source_id,
    source_type,
    dic_name,
    sku_num,
    split_original_amount,
    split_activity_amount,
    split_coupon_amount,
    split_total_amount,
    date_format(canel_time,'yyyy-MM-dd')
from
(
    select
        data.id,
        data.order_id,
        data.sku_id,
        data.source_id,
        data.source_type,
        data.sku_num,
        data.sku_num * data.order_price split_original_amount,
        data.split_total_amount,
        data.split_activity_amount,
        data.split_coupon_amount
    from ods_order_detail_inc
    where dt = '2020-06-14'
    and type = 'bootstrap-insert'
) od
join
(
    select
        data.id,
        data.user_id,
        data.province_id,
        data.operate_time canel_time
    from ods_order_info_inc
    where dt = '2020-06-14'
    and type = 'bootstrap-insert'
    and data.order_status='1003'
) oi
on od.order_id = oi.id
left join
(
    select
        data.order_detail_id,
        data.activity_id,
        data.activity_rule_id
    from ods_order_detail_activity_inc
    where dt = '2020-06-14'
    and type = 'bootstrap-insert'
) act
on od.id = act.order_detail_id
left join
(
    select
        data.order_detail_id,
        data.coupon_id
    from ods_order_detail_coupon_inc
    where dt = '2020-06-14'
    and type = 'bootstrap-insert'
) cou
on od.id = cou.order_detail_id
left join
(
    select
        dic_code,
        dic_name
    from ods_base_dic_full
    where dt='2020-06-14'
    and parent_code='24'
)dic
on od.source_type=dic.dic_code;

1.3.5、每日数据装载

这里需要注意：我们取消的订单可能是前一天下的，所以我们从当天的 ods_order_detail_inc 中是查不到的，所以我们查询的时候还需要从当天的前一天订单明细中也查一份，而且为了和 order_detail 进行 join 关联（防止关联不上），我们的其它表也需要查询前一天的数据。

insert overwrite table dwd_trade_cancel_detail_inc partition (dt='2020-06-15')
select
    od.id,
    order_id,
    user_id,
    sku_id,
    province_id,
    activity_id,
    activity_rule_id,
    coupon_id,
    date_format(canel_time,'yyyy-MM-dd') date_id,
    canel_time,
    source_id,
    source_type,
    dic_name,
    sku_num,
    split_original_amount,
    split_activity_amount,
    split_coupon_amount,
    split_total_amount
from
(
    select
        data.id,
        data.order_id,
        data.sku_id,
        data.source_id,
        data.source_type,
        data.sku_num,
        data.sku_num * data.order_price split_original_amount,
        data.split_total_amount,
        data.split_activity_amount,
        data.split_coupon_amount
    from ods_order_detail_inc -- 取消订单不会影响订单明细表
    where (dt='2020-06-15' or dt=date_add('2020-06-15',-1))
    and (type = 'insert' or type= 'bootstrap-insert') -- 过了第2天就可以不写bootstrap-insert
) od
join
(
    select
        data.id,
        data.user_id,
        data.province_id,
        data.operate_time canel_time
    from ods_order_info_inc
    where dt = '2020-06-15'
    and type = 'update'
    and data.order_status='1003'
    and array_contains(map_keys(old),'order_status')
) oi
on order_id = oi.id
left join
(
    select
        data.order_detail_id,
        data.activity_id,
        data.activity_rule_id
    from ods_order_detail_activity_inc
    where (dt='2020-06-15' or dt=date_add('2020-06-15',-1))
    and (type = 'insert' or type= 'bootstrap-insert')
) act
on od.id = act.order_detail_id
left join
(
    select
        data.order_detail_id,
        data.coupon_id
    from ods_order_detail_coupon_inc
    where (dt='2020-06-15' or dt=date_add('2020-06-15',-1))
    and (type = 'insert' or type= 'bootstrap-insert')
) cou
on od.id = cou.order_detail_id
left join
(
    select
        dic_code,
        dic_name
    from ods_base_dic_full
    where dt='2020-06-15'
    and parent_code='24'
)dic
on od.source_type=dic.dic_code;

1.4、交易域支付成功事务事实表

我们要求事实表对应的业务过程必须是原子操作的，也就是不可再切分的，所以这里说的是支付成功这个业务过程，而不是支付这个行为，因为支付可能成功、可能失败。

1.4.1、设计表结构

选择业务过程：支付成功
声明粒度：谁+什么时候+成功支付了哪个商品
确认维度：时间、用户、商品、地区、活动、优惠券、支付方式（维度退化）
确认事实：支付件数、支付金额、最终支付金额、活动优惠金额、优惠券金额

1.4.2、建表语句

同样是维度外键+度量值+退化字段：

DROP TABLE IF EXISTS dwd_trade_pay_detail_suc_inc;
CREATE EXTERNAL TABLE dwd_trade_pay_detail_suc_inc
(
    `id`                    STRING COMMENT '编号',
    `order_id`              STRING COMMENT '订单id',
    `user_id`               STRING COMMENT '用户id',
    `sku_id`                STRING COMMENT '商品id',
    `province_id`           STRING COMMENT '省份id',
    `activity_id`           STRING COMMENT '参与活动规则id',
    `activity_rule_id`      STRING COMMENT '参与活动规则id',
    `coupon_id`             STRING COMMENT '使用优惠券id',
    `payment_type_code`     STRING COMMENT '支付类型编码',
    `payment_type_name`     STRING COMMENT '支付类型名称',
    `date_id`               STRING COMMENT '支付日期id',
    `callback_time`         STRING COMMENT '支付成功时间',
    `source_id`             STRING COMMENT '来源编号',
    `source_type_code`      STRING COMMENT '来源类型编码',
    `source_type_name`      STRING COMMENT '来源类型名称',
    `sku_num`               BIGINT COMMENT '商品数量',
    `split_original_amount` DECIMAL(16, 2) COMMENT '应支付原始金额',
    `split_activity_amount` DECIMAL(16, 2) COMMENT '支付活动优惠分摊',
    `split_coupon_amount`   DECIMAL(16, 2) COMMENT '支付优惠券优惠分摊',
    `split_payment_amount`  DECIMAL(16, 2) COMMENT '支付金额'
) COMMENT '交易域成功支付事务事实表'
    PARTITIONED BY (`dt` STRING)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
    STORED AS ORC
    LOCATION '/warehouse/gmall/dwd/dwd_trade_pay_detail_suc_inc/'
    TBLPROPERTIES ('orc.compress' = 'snappy');

1.4.3、数据流向

支付成功会影响哪些表？其实只会影响一张表：当支付成功后，order_info 的 order_status 字段就会变成 1002，payment_info 的 order_status 字段会变成 1602 。

事实表要求我们的粒度是最小的，所以我们需要找到支付成功的订单明细，而不是订单信息。所以我们需要先从 order_info 中找到支付成功的订单，然后去和 order_detail 关联得到该订单的所有商品 ...

尽管支付成功后 order_info 的 order_status 字段会变成 1002，但是我们并不能把它当做过滤条件，因为它并不是一个最终状态，我们应该去 payment_info 去找更合适。

当我们点击支付按钮的时候，payment_info 中就会插入一条数据，此时的 payment_status 值为 1601 （待支付状态），create_time 也会插入当前时间；但是 callback_time 字段为空，callback_content 字段同样为空。当支付成功的时候， payment_status 值变为 1602 （支付成功状态）， callback_time 字段为支付成功的时间，callback_content 字段为回调内容。

所以当我们每日装载数据的时候，就需要从 ods_payment_info_inc 的当日分区中过滤 type 为 update 的数据（首日装载另做判断，因为首日装载都是 bootstrap-insert ，我们需要判断支 payment_status ）。

1.4.4、首日装载语句

insert overwrite table dwd_trade_pay_detail_suc_inc partition (dt)
select
    od.id,
    od.order_id,
    user_id,
    sku_id,
    province_id,
    activity_id,
    activity_rule_id,
    coupon_id,
    payment_type,
    pay_dic.dic_name,
    date_format(callback_time,'yyyy-MM-dd') date_id,
    callback_time,
    source_id,
    source_type,
    src_dic.dic_name,
    sku_num,
    split_original_amount,
    split_activity_amount,
    split_coupon_amount,
    split_total_amount,
    date_format(callback_time,'yyyy-MM-dd')
from
(
    select
        data.id,
        data.order_id,
        data.sku_id,
        data.source_id,
        data.source_type,
        data.sku_num,
        data.sku_num * data.order_price split_original_amount,
        data.split_total_amount,
        data.split_activity_amount,
        data.split_coupon_amount
    from ods_order_detail_inc
    where dt = '2020-06-14'
    and type = 'bootstrap-insert'
) od
join
(
    select
        data.user_id,
        data.order_id,
        data.payment_type,
        data.callback_time
    from ods_payment_info_inc
    where dt='2020-06-14'
    and type='bootstrap-insert'
    and data.payment_status='1602'
) pi
on od.order_id=pi.order_id
left join
(
    select
        data.id,
        data.province_id
    from ods_order_info_inc
    where dt = '2020-06-14'
    and type = 'bootstrap-insert'
) oi
on od.order_id = oi.id
left join
(
    select
        data.order_detail_id,
        data.activity_id,
        data.activity_rule_id
    from ods_order_detail_activity_inc
    where dt = '2020-06-14'
    and type = 'bootstrap-insert'
) act
on od.id = act.order_detail_id
left join
(
    select
        data.order_detail_id,
        data.coupon_id
    from ods_order_detail_coupon_inc
    where dt = '2020-06-14'
    and type = 'bootstrap-insert'
) cou
on od.id = cou.order_detail_id
left join
(
    select
        dic_code,
        dic_name
    from ods_base_dic_full
    where dt='2020-06-14'
    and parent_code='11'
) pay_dic
on pi.payment_type=pay_dic.dic_code
left join
(
    select
        dic_code,
        dic_name
    from ods_base_dic_full
    where dt='2020-06-14'
    and parent_code='24'
)src_dic
on od.source_type=src_dic.dic_code;

1.4.5、每日装载语句

直接从 payment_info 中过滤出 type=update 的记录，然后在关联 oder_detail 的时候需要考虑不只是获取当日分区的订单明细，因为可能今天的支付成功订单是昨天创建的，所以需要从两个分区（当日和前一天）获取订单明细数据。在关联 order_info 的时候不需要获取前一天的分区数据，因为只要 payment_info 发生变化，order_info 的 order_status 也会发生变化，所以只需要过滤出 ods_order_info_inc 中的 type=update 的数据。

insert overwrite table dwd_trade_pay_detail_suc_inc partition (dt='2020-06-15')
select
    od.id,
    od.order_id,
    user_id,
    sku_id,
    province_id,
    activity_id,
    activity_rule_id,
    coupon_id,
    payment_type,
    pay_dic.dic_name,
    date_format(callback_time,'yyyy-MM-dd') date_id,
    callback_time,
    source_id,
    source_type,
    src_dic.dic_name,
    sku_num,
    split_original_amount,
    split_activity_amount,
    split_coupon_amount,
    split_total_amount
from
(
    select
        data.id,
        data.order_id,
        data.sku_id,
        data.source_id,
        data.source_type,
        data.sku_num,
        data.sku_num * data.order_price split_original_amount,
        data.split_total_amount,
        data.split_activity_amount,
        data.split_coupon_amount
    from ods_order_detail_inc
    where (dt = '2020-06-15' or dt = date_add('2020-06-15',-1))
    and (type = 'insert' or type = 'bootstrap-insert')
) od
join
(
    select
        data.user_id,
        data.order_id,
        data.payment_type,
        data.callback_time
    from ods_payment_info_inc
    where dt='2020-06-15'
    and type='update'
    and array_contains(map_keys(old),'payment_status')
    and data.payment_status='1602'
) pi
on od.order_id=pi.order_id
left join
(
    select
        data.id,
        data.province_id
    from ods_order_info_inc
    where (dt = '2020-06-15')
    and (type = 'update')
    and array_contains(map_keys(old),'order_status')
    and order_status='1002'
) oi
on od.order_id = oi.id
left join
(
    select
        data.order_detail_id,
        data.activity_id,
        data.activity_rule_id
    from ods_order_detail_activity_inc
    where (dt = '2020-06-15' or dt = date_add('2020-06-15',-1))
    and (type = 'insert' or type = 'bootstrap-insert')
) act
on od.id = act.order_detail_id
left join
(
    select
        data.order_detail_id,
        data.coupon_id
    from ods_order_detail_coupon_inc
    where (dt = '2020-06-15' or dt = date_add('2020-06-15',-1))
    and (type = 'insert' or type = 'bootstrap-insert')
) cou
on od.id = cou.order_detail_id
left join
(
    select
        dic_code,
        dic_name
    from ods_base_dic_full
    where dt='2020-06-15'
    and parent_code='11'
) pay_dic
on pi.payment_type=pay_dic.dic_code
left join
(
    select
        dic_code,
        dic_name
    from ods_base_dic_full
    where dt='2020-06-15'
    and parent_code='24'
)src_dic
on od.source_type=src_dic.dic_code;

离线数仓（八）【DWD 层开发】（3）https://developer.aliyun.com/article/1532418

离线数仓（八）【DWD 层开发】（2）

1.3、交易域取消订单事务事实表

1.3.1、设计表结构

1.3.2、建表语句

1.3.3、数据流向

1.3.4、首日数据装载

1.3.5、每日数据装载

1.4、交易域支付成功事务事实表

1.4.1、设计表结构

1.4.2、建表语句

1.4.3、数据流向

1.4.4、首日装载语句

1.4.5、每日装载语句

热门文章

最新文章

相关课程

相关电子书

相关实验场景

探索云世界

热门

云计算

大数据

云原生

人工智能

数据库

开发与运维

活动广场

任务中心

训练营

直播

乘风者计划

下载

镜像站

技术资料

离线数仓（八）【DWD 层开发】（2）

1.3、交易域取消订单事务事实表

1.3.1、设计表结构

1.3.2、建表语句

1.3.3、 数据流向

1.3.4、首日数据装载

1.3.5、每日数据装载

1.4、交易域支付成功事务事实表

1.4.1、设计表结构

1.4.2、建表语句

1.4.3、数据流向

1.4.4、首日装载语句

1.4.5、每日装载语句

热门文章

最新文章

相关课程

相关电子书

相关实验场景

1.3.3、数据流向