30-学习笔记尚硅谷数仓搭建-DWD层交易域购物车周期快照事实表及交易域交易流程累积快照事实表建表语句及分析

目录

1. 交易域购物车周期快照事实表（dwd_trade_cart_full）

业务理解

代码分析

业务价值

2. 交易域交易流程累积快照事实表（dwd_trade_trade_flow_acc）

业务理解

特殊处理逻辑

数据更新策略

业务价值

3. 两种事实表的对比

4. 数据处理特点

购物车表的特点

交易流程表的特点

5. 业务应用场景

购物车快照表的应用

交易流程表的应用

6. 数据质量考虑

购物车表

交易流程表

完整代码

总结

1.交易域购物车周期快照事实表（dwd_trade_cart_full）

业务理解

• 表类型：周期快照事实表（每日全量快照）

• 业务场景：记录用户购物车状态的每日快照，用于分析购物车中的商品情况

• 特点：

  • 每天记录所有活跃购物车（未下单）的商品

  • 保留购物车中商品的数量变化历史

  • 只包含未下单的购物车商品（is_ordered='0'）

代码分析

-- 只包含未下单的购物车商品 where dt='2022-06-08' and is_ordered='0' -- 关键过滤条件，只保留未下单的商品

业务价值

• 分析购物车商品的留存率

• 监控购物车到订单的转化漏斗

• 识别用户感兴趣但未下单的商品

2.交易域交易流程累积快照事实表（dwd_trade_trade_flow_acc）

业务理解

• 表类型：累积快照事实表（跟踪业务流程全周期）

• 业务场景：记录订单从创建到完成的整个生命周期

• 时间节点：包含三个关键业务事件时间

  1. 下单时间（order_time）

  2. 支付时间（payment_time）

  3. 完成时间（finish_time）

特殊处理逻辑

-- 特殊分区策略：使用9999-12-31表示进行中的订单 nvl(date_format(finish_time,'yyyy-MM-dd'),'9999-12-31')

业务含义：

• dt='9999-12-31'：表示订单尚未完成，仍在进行中

• dt=具体日期：表示订单在该日期已完成

数据更新策略

-- 每日装载逻辑的核心： -- 1. 保留所有未完成的订单（9999-12-31分区） -- 2. 合并当日新增订单 -- 3. 更新已完成订单的状态和时间 select ... from dwd_trade_trade_flow_acc where dt='9999-12-31' union all select ... from ods_order_info_inc where type='insert'

业务价值

• 流程时长分析：支付时长、配送时长等

• 转化率分析：下单到支付转化率、支付到完成转化率

• 订单状态监控：实时掌握订单在各环节的数量

3.两种事实表的对比

4.数据处理特点

购物车表的特点

-- 简单映射，无需复杂关联 -- 每日全量覆盖，数据量相对稳定 insert overwrite table ... partition(dt='2022-06-08')

交易流程表的特点

-- 复杂的多表关联（订单、支付、状态日志） -- 累积更新，保留历史状态 -- 使用动态分区（set hive.exec.dynamic.partition.mode=nonstrict）

5.业务应用场景

购物车快照表的应用

• 商品热度分析：哪些商品经常被加入购物车但未购买

• 用户行为分析：用户购物车商品数量分布

• 促销效果评估：促销活动对购物车商品的影响

交易流程表的应用

• 订单时效分析：平均支付时长、平均配送时长

• 漏斗转化分析：各环节转化率统计

• 异常订单监控：长时间未支付、未完成的订单

6.数据质量考虑

购物车表

• 需确保只包含未下单商品（is_ordered='0'）

• 每日全量，需关注数据量突变

交易流程表

• 需处理数据延迟到达（如支付信息晚于订单创建）

• 需处理状态异常（如支付失败后重新支付）

• 使用nvl()函数处理空值，确保数据完整性

完整代码

-- 四、交易域购物车周期快照事实表 -- 建表语句 DROP TABLE IF EXISTS dwd_trade_cart_full; CREATE EXTERNAL TABLE dwd_trade_cart_full ( `id` STRING COMMENT '编号', `user_id` STRING COMMENT '用户ID', `sku_id` STRING COMMENT 'SKU_ID', `sku_name` STRING COMMENT '商品名称', `sku_num` BIGINT COMMENT '现存商品件数' ) COMMENT '交易域购物车周期快照事实表' PARTITIONED BY (`dt` STRING) STORED AS ORC LOCATION '/warehouse/gmall/dwd/dwd_trade_cart_full/' TBLPROPERTIES ('orc.compress' = 'snappy'); -- 交易域购物车周期快照事实表数据装载 insert overwrite table dwd_trade_cart_full partition(dt='2022-06-08') select id, user_id, sku_id, sku_name, sku_num from ods_cart_info_full where dt='2022-06-08' and is_ordered='0'; -- 五、交易域交易流程累积快照事实表 -- 建表语句 DROP TABLE IF EXISTS dwd_trade_trade_flow_acc; CREATE EXTERNAL TABLE dwd_trade_trade_flow_acc ( `order_id` STRING COMMENT '订单ID', `user_id` STRING COMMENT '用户ID', `province_id` STRING COMMENT '省份ID', `order_date_id` STRING COMMENT '下单日期ID', `order_time` STRING COMMENT '下单时间', `payment_date_id` STRING COMMENT '支付日期ID', `payment_time` STRING COMMENT '支付时间', `finish_date_id` STRING COMMENT '确认收货日期ID', `finish_time` STRING COMMENT '确认收货时间', `order_original_amount` DECIMAL(16, 2) COMMENT '下单原始价格', `order_activity_amount` DECIMAL(16, 2) COMMENT '下单活动优惠分摊', `order_coupon_amount` DECIMAL(16, 2) COMMENT '下单优惠券优惠分摊', `order_total_amount` DECIMAL(16, 2) COMMENT '下单最终价格分摊', `payment_amount` DECIMAL(16, 2) COMMENT '支付金额' ) COMMENT '交易域交易流程累积快照事实表' PARTITIONED BY (`dt` STRING) STORED AS ORC LOCATION '/warehouse/gmall/dwd/dwd_trade_trade_flow_acc/' TBLPROPERTIES ('orc.compress' = 'snappy'); -- 交易域交易流程累积快照事实表首日数据装载 set hive.exec.dynamic.partition.mode=nonstrict; insert overwrite table dwd_trade_trade_flow_acc partition(dt) select oi.id, user_id, province_id, date_format(create_time,'yyyy-MM-dd'), create_time, date_format(callback_time,'yyyy-MM-dd'), callback_time, date_format(finish_time,'yyyy-MM-dd'), finish_time, original_total_amount, activity_reduce_amount, coupon_reduce_amount, total_amount, nvl(payment_amount,0.0), nvl(date_format(finish_time,'yyyy-MM-dd'),'9999-12-31') from ( select data.id, data.user_id, data.province_id, data.create_time, data.original_total_amount, data.activity_reduce_amount, data.coupon_reduce_amount, data.total_amount from ods_order_info_inc where dt='2022-06-08' and type='bootstrap-insert' )oi left join ( select data.order_id, data.callback_time, data.total_amount payment_amount from ods_payment_info_inc where dt='2022-06-08' and type='bootstrap-insert' and data.payment_status='1602' )pi on oi.id=pi.order_id left join ( select data.order_id, data.create_time finish_time from ods_order_status_log_inc where dt='2022-06-08' and type='bootstrap-insert' and data.order_status='1004' )log on oi.id=log.order_id; -- 交易域交易流程累积快照事实表每日数据装载 set hive.exec.dynamic.partition.mode=nonstrict; insert overwrite table dwd_trade_trade_flow_acc partition(dt) select oi.order_id, user_id, province_id, order_date_id, order_time, nvl(oi.payment_date_id,pi.payment_date_id), nvl(oi.payment_time,pi.payment_time), nvl(oi.finish_date_id,log.finish_date_id), nvl(oi.finish_time,log.finish_time), order_original_amount, order_activity_amount, order_coupon_amount, order_total_amount, nvl(oi.payment_amount,pi.payment_amount), nvl(nvl(oi.finish_time,log.finish_time),'9999-12-31') from ( select order_id, user_id, province_id, order_date_id, order_time, payment_date_id, payment_time, finish_date_id, finish_time, order_original_amount, order_activity_amount, order_coupon_amount, order_total_amount, payment_amount from dwd_trade_trade_flow_acc where dt='9999-12-31' union all select data.id, data.user_id, data.province_id, date_format(data.create_time,'yyyy-MM-dd') order_date_id, data.create_time, null payment_date_id, null payment_time, null finish_date_id, null finish_time, data.original_total_amount, data.activity_reduce_amount, data.coupon_reduce_amount, data.total_amount, null payment_amount from ods_order_info_inc where dt='2022-06-09' and type='insert' )oi left join ( select data.order_id, date_format(data.callback_time,'yyyy-MM-dd') payment_date_id, data.callback_time payment_time, data.total_amount payment_amount 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 oi.order_id=pi.order_id left join ( select data.order_id, date_format(data.create_time,'yyyy-MM-dd') finish_date_id, data.create_time finish_time from ods_order_status_log_inc where dt='2022-06-09' and type='insert' and data.order_status='1004' )log on oi.order_id=log.order_id;

总结

这两个表体现了数据仓库中两种典型的事实表设计：

1. 周期快照表：适用于状态相对稳定、需要历史快照的业务场景

2. 累积快照表：适用于有明确业务流程、需要跟踪状态变化的时间序列场景

通过这两种表的结合，可以全面分析用户从浏览商品（购物车）到完成购买（订单流程）的完整消费旅程，为业务决策提供全面的数据支持。