-- 作者:tianyu.chu -- 日期:2026-05-10 -- 工单:(无) -- 目的:dwd_trd_order_pay_apd_d 日常增量(kb/27 §1.4 + §2): -- 扫 ods.dt=${dt} + 过滤业务时间 DATE_FORMAT(payment_success_time)=${dt} + status / order_type 支付成功 + -- ROW_NUMBER 取每 order_id 最新版本 + -- LEFT JOIN dim_trd_card_group_ful_d.dt=${dt} 维度退化 + -- 11 字段金额 mer_act% 派生(kb/27 §2.5)+ 写入 dwd dt=${dt} 单分区 -- 状态:[草案] -- 备注:sched=T,${dt}=T-1; -- _apd_d 单分区不回算(ODS 漂移已在 ODS 层归位,kb/27 §1.4); -- 过滤 DATE(payment_success_time)=${dt} 取业务时间是 ${dt} 的事件(排除"今天 update 的旧订单"误归); -- 前置 DS DEPENDENT:ods_trd_card_group_order_info_inc_d.dt=${dt} + dim_trd_card_group_ful_d.dt=${dt} INSERT OVERWRITE TABLE dwd.dwd_trd_order_pay_apd_d PARTITION (dt='${dt}') SELECT o.id AS order_id, o.order_no AS order_no, o.combination_no AS combination_no, o.give_order_id AS give_order_id, o.user_id AS user_id, o.merchant_id AS merchant_id, o.group_info_id AS group_info_id, cg.name AS group_name, cg.list_id AS list_id, cg.panini_list_id AS panini_list_id, cg.category AS category, cg.manufacturer AS manufacturer, cg.sets AS sets, cg.year AS year, o.shipping_address_id AS shipping_address_id, o.purchase_count AS purchase_cnt, o.give_num AS give_cnt, CASE WHEN o.point_type LIKE 'mer_act%' THEN ROUND(o.accounts_payable / 100.00, 2) ELSE o.accounts_payable END AS payable_amt_cny, CASE WHEN o.point_type LIKE 'mer_act%' THEN ROUND(o.point / 100.00, 2) ELSE o.actual_payment END AS pay_amt_cny, CASE WHEN o.point_type LIKE 'mer_act%' THEN ROUND(o.trade_amount / 100.00, 2) ELSE o.trade_amount END AS trade_amt_cny, CASE WHEN o.point_type LIKE 'mer_act%' THEN ROUND(o.settlement_amount / 100.00, 2) ELSE o.settlement_amount END AS settle_amt_cny, CASE WHEN o.point_type LIKE 'mer_act%' THEN ROUND(o.card_price / 100.00, 2) ELSE o.card_price END AS card_price_cny, CASE WHEN o.point_type LIKE 'mer_act%' THEN ROUND(o.act_price / 100.00, 2) ELSE o.act_price END AS act_price_cny, CASE WHEN o.point_type LIKE 'mer_act%' THEN ROUND(o.discount / 100.00, 2) ELSE o.discount END AS merchant_discount_amt_cny, CASE WHEN o.point_type LIKE 'mer_act%' THEN ROUND(o.platform_discount / 100.00, 2) ELSE o.platform_discount END AS platform_discount_amt_cny, CASE WHEN o.point_type LIKE 'mer_act%' THEN ROUND(o.member_discount / 100.00, 2) ELSE o.member_discount END AS member_discount_amt_cny, CASE WHEN o.point_type LIKE 'mer_act%' THEN ROUND(o.act_discount / 100.00, 2) ELSE o.act_discount END AS act_discount_amt_cny, CASE WHEN o.point_type LIKE 'mer_act%' THEN ROUND(o.point_deduct / 100.00, 2) ELSE o.point_deduct END AS point_deduct_amt_cny, o.shipping_cost AS shipping_amt_cny, o.shipping_free_amount AS shipping_free_amt_cny, CASE WHEN o.point_type LIKE 'mer_act%' THEN ROUND(o.discount_amount / 100.00, 2) ELSE o.discount_amount END AS discount_amount_amt_cny, o.point AS point, o.discount_point AS discount_point, o.coupon AS coupon, o.platform_coupon AS platform_coupon, o.shipping_free_id AS shipping_free_id, o.payment_type AS payment_type, o.payment_sub_type AS payment_sub_type, o.payment_status AS payment_status, o.payment_status_desc AS payment_status_desc, o.payment_time AS payment_time, o.payment_success_time AS payment_success_time, o.pay_record AS pay_record, o.order_type AS order_type, o.order_sub_type AS order_sub_type, o.give_user_code AS give_user_code, o.anonymous AS anonymous, o.pick_up_type AS pick_up_type, o.point_type AS point_type, o.open_self AS open_self, o.create_time AS order_create_time, o.expire_time AS expire_time, o.is_deleted AS is_deleted, CURRENT_TIMESTAMP() AS etl_time FROM ( SELECT * FROM ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY id ORDER BY COALESCE(update_time, create_time) DESC ) AS rn FROM ods.ods_trd_card_group_order_info_inc_d WHERE dt = '${dt}' AND order_type = 'group' AND status IN (101, 103, 104, 105, 106, 301, 302) AND DATE_FORMAT(payment_success_time, 'yyyyMMdd') = '${dt}' ) t WHERE t.rn = 1 ) o LEFT JOIN dim.dim_trd_card_group_ful_d cg ON o.group_info_id = cg.group_info_id AND cg.dt = '${dt}';