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

本文介绍了电商交易域核心事实表的设计实现，包括下单明细表(dwd_trade_order_detail_inc)和支付成功明细表(dwd_trade_pay_detail_suc_inc)。下单表记录SKU级别的订单明细，通过关联4个ODS表获取完整信息；支付表则需关联5个表，并只记录状态为1602的支付成功数据。设计亮点包括：维度退化减少关联、金额分摊便于分析、增量更新提高效率、状态过滤保证质量

2401_87177990

337人浏览 · 2026-02-03 21:55:36

2401_87177990 · 2026-02-03 21:55:36 发布

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

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

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

业务理解：

表用途：记录用户下单的明细事实，每个订单项（SKU级别）一条记录
业务过程：用户创建订单（下单）
粒度：订单商品明细级别（一个订单包含多个商品，每个商品一条记录）

代码关键点：

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

-- 特点：使用'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. 每日装载（增量数据）

-- 特点：只处理新增数据
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. 首日装载

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

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

-- 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日的订单明细数据

支付状态流转逻辑：

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

三、两张表的区别与联系

维度	下单事实表	支付成功事实表
业务过程	下单	支付成功
触发条件	创建订单	支付状态=1602
数据时间	下单时间(create_time)	支付回调时间(callback_time)
数据范围	所有下单记录	只包含支付成功的记录
关联复杂度	4表关联	5表关联（+支付表+字典表）
增量逻辑	简单：只取当日新增	复杂：需关联前一日订单数据

四、数据流示意图

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 (支付成功事实表)

五、业务意义

下单事实表：
- 用于分析：用户下单行为、商品销量、活动效果、优惠券使用情况
- 计算指标：下单用户数、下单商品数、下单金额、优惠金额
支付成功事实表：
- 用于分析：实际成交情况、支付方式偏好、支付成功率
- 计算指标：支付用户数、支付金额、客单价、支付转化率
- 特点：这是真正的交易事实，代表资金流转完成

六、设计亮点

维度退化：将省份、用户等维度信息直接冗余到事实表中，减少关联查询
分摊金额：记录了活动优惠、优惠券优惠的分摊金额，便于分析营销效果
增量更新：使用type字段区分全量和增量数据，提高处理效率
状态过滤：支付表只处理成功状态，保证数据质量
字典关联：通过字典表获取支付方式等编码的名称，增强可读性

七、完整代码

-- 交易域下单事务事实表首日数据装载
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层提供了高质量的明细数据。

2048 AI社区

有“AI”的1024 = 2048，欢迎大家加入2048 AI社区

更多推荐

44、AI系统监控与可观测性实践：从三大支柱到智能运维的全链路解决方案

在数字化转型的浪潮中，AI系统已成为企业核心竞争力的重要组成部分。然而，与传统的软件系统相比，AI系统带来了独特的监控挑战：模型性能漂移、数据质量变化、计算资源消耗巨大等。根据Gartner的报告，超过60%的AI项目在生产环境中遭遇了可观测性问题，其中数据漂移和模型衰减是最主要的挑战。可观测性（Observability）不再仅仅是传统意义上的系统监控，而是一个多维度的洞察系统，它需要覆盖从基础