poyee-data-warehouse/jobs/dwd/trd/dwd_trd_order_pay_apd_d.sql

-- 作者:tianyu.chu
-- 日期:2026-05-10
-- 工单:(无)
-- 目的:dwd_trd_order_pay_apd_d 日常增量(kb/27 §1.4 + §2):
--      回算近 2 日(kb/20 §7.3 通用兜底,业界主流 N=2):扫 ods.dt IN (${dt}, ${pdt}) +
--      过滤业务时间 DATE(payment_success_time) IN (${dt}, ${pdt}) + 状态码筛选支付成功 +
--      ROW_NUMBER 取每 order_id 最新版本 +
--      LEFT JOIN dim_trd_card_group_ful_d.dt=${dt} 维度退化 +
--      11 字段金额 mer_act% 派生(kb/27 §2.5)+ 动态分区写入 dwd dt IN (${dt}, ${pdt})
-- 状态:[草案]
-- 备注:sched=T,${dt}=T-1,${pdt}=T-2;
--      回算 N=2 兜底跨零点漂移:业务时间 T-1 但 update_time 漂到 T 的事件,在 T+1 跑批时通过扫 ods.dt=T 兜回;
--      INSERT OVERWRITE 动态分区(kb/26 §8 项目默认 DYNAMIC mode):只覆盖 SELECT 出现的 dt 分区,不动其他历史分区;
--      dwd_pay 是数仓支付明细,不限定 order_type(所有订单类型都进);
--      前置 DS DEPENDENT:ods.${dt} + dim.${dt}(${pdt} 历史分区已就绪)

INSERT OVERWRITE TABLE dwd.dwd_trd_order_pay_apd_d PARTITION (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,
    DATE_FORMAT(o.payment_success_time, 'yyyyMMdd')                                     AS dt
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 IN ('${dt}', '${pdt}')
          AND status IN (101, 103, 104, 105, 106, 301, 302)
          AND payment_success_time IS NOT NULL
          AND DATE_FORMAT(payment_success_time, 'yyyyMMdd') IN ('${dt}', '${pdt}')
    ) 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}';