29-学习笔记尚硅谷数仓搭建-DWD层交易域下单事务事实表和交易域支付成功事务事实表

目录

一、交易域下单事务事实表 (dwd_trade_order_detail_inc)

业务理解：

代码关键点：

1. 首日装载（全量历史数据）

2. 每日装载（增量数据）

核心字段解释：

二、交易域支付成功事务事实表 (dwd_trade_pay_detail_suc_inc)

业务理解：

代码关键点：

1. 首日装载

2. 每日装载（复杂！重点理解）

为什么支付事实表需要关联两天数据？

支付状态流转逻辑：

三、两张表的区别与联系

四、数据流示意图

五、业务意义

六、设计亮点

七、完整代码

一、交易域下单事务事实表 (dwd_trade_order_detail_inc)

业务理解：

代码关键点：

1. 首日装载（全量历史数据）

sql

— 特点：使用'bootstrap-insert'类型，加载历史全量数据
where dt = '2022-06-08' and type = 'bootstrap-insert'
— 使用动态分区
partition (dt)
— 从4个ODS表关联获取完整下单信息：
— ods_order_detail_inc: 订单明细基础信息
— ods_order_info_inc: 订单主表信息（用户、省份）
— ods_order_detail_activity_inc: 活动信息
— ods_order_detail_coupon_inc: 优惠券信息

2. 每日装载（增量数据）

sql

— 特点：只处理新增数据
where dt = '2022-06-09' and type = 'insert'
— 使用静态分区
partition (dt='2022-06-09')

核心字段解释：

• split_original_amount: 商品原价金额（单价×数量）

• split_activity_amount: 活动优惠分摊金额

• split_coupon_amount: 优惠券优惠分摊金额

• split_total_amount: 最终支付金额

• date_id: 下单日期（用于时间维度关联）

二、交易域支付成功事务事实表 (dwd_trade_pay_detail_suc_inc)

业务理解：

代码关键点：

1. 首日装载

sql

— 关键条件：只取支付成功的记录
where data.payment_status='1602' — 1602表示支付成功
— 关联5个表，比下单表多了支付信息表和字典表

2. 每日装载（复杂！重点理解）

sql

— 1. 订单明细需要取两天数据（今日+昨日）
where (dt = '2022-06-09' or dt = date_add('2022-06-09',-1))
— 原因：今天支付的订单可能是昨天创建的

— 2. 支付信息表只取今天的更新记录
where dt='2022-06-09' and type='update'
— 并且支付状态变为'1602'（支付成功）
and array_contains(map_keys(old),'payment_status')
and data.payment_status='1602'

— 3. 关联支付方式字典表
left join ods_base_dic_full — 获取支付方式名称
where parent_code='11' — 11是支付类型字典

为什么支付事实表需要关联两天数据？

业务场景举例：

• 用户A在6月8日下单（记录在ods_order_detail_inc的6月8日分区）

• 用户A在6月9日支付成功（记录在ods_payment_info_inc的6月9日分区）

• 查询6月9日支付成功的数据时，需要关联6月8日的订单明细数据

支付状态流转逻辑：

text

下单 → 待支付 → 支付成功(1602)
↑
支付表记录状态变化：从其他状态 → 1602

三、两张表的区别与联系

四、数据流示意图

text

ods_order_detail_inc (订单明细)
↓
ods_order_info_inc (订单信息) ←─ 用户、省份信息
↓
ods_order_detail_activity_inc (活动信息) ←─ 活动、规则
↓
ods_order_detail_coupon_inc (优惠券信息) ←─ 优惠券
↓
dwd_trade_order_detail_inc (下单事实表)
│
│ (支付成功后)
↓
ods_payment_info_inc (支付信息) ←─ 支付成功状态
↓
ods_base_dic_full (字典表) ←─ 支付方式名称
↓
dwd_trade_pay_detail_suc_inc (支付成功事实表)

五、业务意义

六、设计亮点

七、完整代码

— 交易域下单事务事实表首日数据装载
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dwd_trade_order_detail_inc partition (dt)
select
od.id,
order_id,
user_id,
sku_id,
province_id,
activity_id,
activity_rule_id,
coupon_id,
date_format(create_time, 'yyyy-MM-dd') date_id,
create_time,
sku_num,
split_original_amount,
nvl(split_activity_amount,0.0),
nvl(split_coupon_amount,0.0),
split_total_amount,
date_format(create_time,'yyyy-MM-dd')
from
(
select
data.id,
data.order_id,
data.sku_id,
data.create_time,
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 = '2022-06-08'
and type = 'bootstrap-insert'
) od
left join
(
select
data.id,
data.user_id,
data.province_id
from ods_order_info_inc
where dt = '2022-06-08'
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 = '2022-06-08'
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 = '2022-06-08'
and type = 'bootstrap-insert'
) cou
on od.id = cou.order_detail_id;

— 交易域下单事务事实表每日数据装载
insert overwrite table dwd_trade_order_detail_inc partition (dt='2022-06-09')
select
od.id,
order_id,
user_id,
sku_id,
province_id,
activity_id,
activity_rule_id,
coupon_id,
date_id,
create_time,
sku_num,
split_original_amount,
nvl(split_activity_amount,0.0),
nvl(split_coupon_amount,0.0),
split_total_amount
from
(
select
data.id,
data.order_id,
data.sku_id,
date_format(data.create_time, 'yyyy-MM-dd') date_id,
data.create_time,
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 = '2022-06-09'
and type = 'insert'
) od
left join
(
select
data.id,
data.user_id,
data.province_id
from ods_order_info_inc
where dt = '2022-06-09'
and type = '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 = '2022-06-09'
and type = '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 = '2022-06-09'
and type = 'insert'
) cou
on od.id = cou.order_detail_id;

— 三、交易域支付成功事务事实表
— 建表语句
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 'SKU_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 '支付成功时间',
`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)
STORED AS ORC
LOCATION '/warehouse/gmall/dwd/dwd_trade_pay_detail_suc_inc/'
TBLPROPERTIES ('orc.compress' = 'snappy');

— 交易域支付成功事务事实表首日数据装载
set hive.exec.dynamic.partition.mode=nonstrict;
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,
sku_num,
split_original_amount,
nvl(split_activity_amount,0.0),
nvl(split_coupon_amount,0.0),
split_total_amount,
date_format(callback_time,'yyyy-MM-dd')
from
(
select
data.id,
data.order_id,
data.sku_id,
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 = '2022-06-08'
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='2022-06-08'
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 = '2022-06-08'
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 = '2022-06-08'
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 = '2022-06-08'
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='2022-06-08'
and parent_code='11'
) pay_dic
on pi.payment_type=pay_dic.dic_code;
— 交易域支付成功事务事实表每日数据装载
insert overwrite table dwd_trade_pay_detail_suc_inc partition (dt='2022-06-09')
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,
sku_num,
split_original_amount,
nvl(split_activity_amount,0.0),
nvl(split_coupon_amount,0.0),
split_total_amount
from
(
select
data.id,
data.order_id,
data.sku_id,
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 = '2022-06-09' or dt = date_add('2022-06-09',-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='2022-06-09'
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 = '2022-06-09' or dt = date_add('2022-06-09',-1))
and (type = 'insert' or 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 = '2022-06-09' or dt = date_add('2022-06-09',-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 = '2022-06-09' or dt = date_add('2022-06-09',-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='2022-06-09'
and parent_code='11'
) pay_dic
on pi.payment_type=pay_dic.dic_code;

这种设计支持了电商交易核心业务的分析需求，为后续的DWS层和ADS层提供了高质量的明细数据。