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):
--      扫 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}';