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

-- 作者:tianyu.chu
-- 日期:2026-05-10（2026-06-03 日调度+补数合一为统一参数化）
-- 工单:(无)
-- 目的:dwd_trd_order_pay_apd_d 统一计算 SQL（kb/93 ADR-09/ADR-11 宽扫窄落）:
--      日调度 + 手动补数共用本文件,传参区分职能（两个独立 DS workflow）:
--        日调度(滚动 N=30): -p start_date=$[yyyyMMdd-30] -p stop_date=${cdt}（挂 schedule 自动跑）
--        手动补数:           -p start_date=<起始> -p stop_date=${cdt}（不挂 schedule,运维兜底）
--      ods.dt 宽扫 [${start_date}-1, 不限]（捞回 update_time 漂移到后续 ods 分区的版本）;
--      payment 业务时间窄落 [${start_date}, ${stop_date}) 左闭右开;
--      status 在 ROW_NUMBER 外层判最新版本（避免捞回已退款单的退款前版本,见 ADR-09/12）;
--      LEFT JOIN dim_trd.dt=${stop_date}-1（最新全量快照退化 category 等）
-- 状态:[草案]
-- 备注:${stop_date}=${cdt}(today,左闭右开覆盖到 today-1,当天不落交次日);
--      回算窗 N=30 依据退款窗实测(P95=15/MAX=20,ADR-09);跨多 dt 动态分区 SET 上限 2000;
--      前置:ods 补到 today-1 + dim_trd 补到 today-1(否则 category 全 NULL);
--      dwd 历史可变可接受(ods 真源,dwd 可从 ods 回放重建,ADR-09);
--      上界不限 = 扫到 ods 最新分区,payment ∈ [${start_date}, ${stop_date}) 的订单 ods 版本落点必 ≤ 最新,全部捞到

set hive.exec.max.dynamic.partitions=2000;
set hive.exec.max.dynamic.partitions.pernode=200;

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 >= DATE_FORMAT(DATE_SUB(FROM_UNIXTIME(UNIX_TIMESTAMP('${start_date}', 'yyyyMMdd')), 1), 'yyyyMMdd')
          AND payment_success_time IS NOT NULL
          AND DATE_FORMAT(payment_success_time, 'yyyyMMdd') >= '${start_date}'
          AND DATE_FORMAT(payment_success_time, 'yyyyMMdd') <  '${stop_date}'
    ) t
    WHERE t.rn = 1
      AND t.status IN (101, 103, 104, 105, 106, 301, 302)
) o
LEFT JOIN dim.dim_trd_card_group_ful_d cg
    ON o.group_info_id = cg.group_info_id
   AND cg.dt = DATE_FORMAT(DATE_SUB(FROM_UNIXTIME(UNIX_TIMESTAMP('${stop_date}', 'yyyyMMdd')), 1), 'yyyyMMdd');