2025年【博学谷学习记录】超强总结，用心分享 - DWB层构建-订单明细宽表

科技前沿 • 2025-02-11 08:54 • 阅读 42

大家好，我是讯享网，很高兴认识大家。

#博学谷IT技术支持#

DWB层：基于业务进行降维，将DWD业务上关联密切的表进行提前join形成宽表，为了以后避免大量的join操作，直接基于宽表进行分析。

订单明细宽表

寻找业务关联表

订单事实表: fact_shop_order
订单组表: fact_shop_order_group
订单组支付信息表: fact_order_pay
商品快照表: fact_shop_order_goods_details
订单副表(订单详情表): fact_shop_order_address_detail
退款订单表: fact_refund_order
结算信息表: fact_order_settle
订单评价表: fact_goods_evaluation
订单配送表: fact_order_delievery_item

讯享网

订单事实表id:订单组表order_id

订单事实表id:商品快照表order_id

订单事实表id:订单副表id

订单事实表id:退款订单表order_id

订单事实表id:结算信息表order_id

订单事实表id:订单评价表order_id

订单事实表id:订单配送表order_id

订单组表group_id:订单组支付信息表group_id

代码实现-以订单事实表、订单副表、订单组表为例
1. 子查询1：查询订单事实表中字段，拉链表查询最新数据
2. 子查询2：查询订单副表中字段，拉链表查询最新数据
3. 子查询3：查询订单组表中字段，拉链表查询最新数据
4. 使用left join关联三个子查询，关联字段为id和order_id，生成宽表

with so as ( select -- 订单事实表 id , order_num, buyer_id, store_id, order_from, order_state, create_date, finnshed_time, is_settlement, is_delete, evaluation_state, way, is_stock_up from yp_dwd.fact_shop_order where end_date = '9999-99-99' and is_valid = 1 ), soad as ( select -- 订单副表 id, order_amount, discount_amount, goods_amount, is_delivery, buyer_notes, pay_time, receive_time, delivery_begin_time, arrive_store_time, arrive_time, create_user, create_time, update_user, update_time, is_valid from yp_dwd.fact_shop_order_address_detail where end_date = '9999-99-99' and is_valid = 1 ), sog as ( select order_id, group_id, is_pay from yp_dwd.fact_shop_order_group where end_date = '9999-99-99' and is_valid = 1 ) select -- 订单事实表 so.id as order_id, so.order_num, so.buyer_id, so.store_id, so.order_from, so.order_state, so.create_date, so.finnshed_time, so.is_settlement, so.is_delete, so.evaluation_state, so.way, so.is_stock_up, -- 订单副表 soad.order_amount, soad.discount_amount, soad.goods_amount, soad.is_delivery, soad.buyer_notes, soad.pay_time, soad.receive_time, soad.delivery_begin_time, soad.arrive_store_time, soad.arrive_time, soad.create_user, soad.create_time, soad.update_user, soad.update_time, soad.is_valid, -- 订单组表 sog.group_id, sog.is_pay from so left join soad on so.id = soad.id left join sog on so.id = sog.order_id;

讯享网

2025年【博学谷学习记录】超强总结，用心分享 - DWB层构建-订单明细宽表

订单明细宽表

相关推荐