poyee-data-warehouse/manual/backfill/ods_trd_card_group_order_info_init.sql

-- 作者：tianyu.chu
-- 日期：2026-05-07
-- 工单：(无)
-- 目的：订单表 ods 初始化一次性灌入：raw_his_o（存量历史）+ raw_inc_d（已跑全部增量）UNION ALL，
--       按 DATE_FORMAT(update_time, 'yyyyMMdd') 归位到 ods 各 dt 分区，(id, ods_dt) dedupe，动态分区写入
-- 状态：[待执行]
-- 备注：跑一次后由 jobs/ods/trd/ods_trd_card_group_order_info_inc_d.sql 接管日常增量；
--       his_o + inc_d schema 完全一致（91 字段 STRING），SELECT * UNION ALL 安全；
--       update_time 为空的行落入 __HIVE_DEFAULT_PARTITION__（非阻塞，下游自行处理）

INSERT OVERWRITE TABLE ods.ods_trd_card_group_order_info_inc_d PARTITION (dt)
SELECT
    CAST(id                        AS BIGINT)        AS id,
    CAST(group_info_id             AS BIGINT)        AS group_info_id,
    CAST(merchant_id               AS BIGINT)        AS merchant_id,
    CAST(user_id                   AS BIGINT)        AS user_id,
    CAST(shipping_address_id       AS BIGINT)        AS shipping_address_id,
    CAST(purchase_count            AS BIGINT)        AS purchase_count,
    order_no                                         AS order_no,
    CAST(accounts_payable          AS DECIMAL(20,4)) AS accounts_payable,
    CAST(actual_payment            AS DECIMAL(20,4)) AS actual_payment,
    payment_type                                     AS payment_type,
    CAST(payment_time              AS TIMESTAMP)     AS payment_time,
    CAST(coupon                    AS BIGINT)        AS coupon,
    CAST(discount                  AS DECIMAL(20,4)) AS discount,
    CAST(status                    AS BIGINT)        AS status,
    remark                                           AS remark,
    CAST(create_time               AS TIMESTAMP)     AS create_time,
    create_by                                        AS create_by,
    CAST(update_time               AS TIMESTAMP)     AS update_time,
    update_by                                        AS update_by,
    payment_status                                   AS payment_status,
    payment_status_desc                              AS payment_status_desc,
    CAST(payment_success_time      AS TIMESTAMP)     AS payment_success_time,
    CAST(del_flg                   AS BIGINT)        AS del_flg,
    curier_company                                   AS curier_company,
    CAST(refund_fee                AS DECIMAL(20,4)) AS refund_fee,
    CAST(refund_time               AS TIMESTAMP)     AS refund_time,
    CAST(anonymous                 AS BOOLEAN)       AS anonymous,
    pick_up_type                                     AS pick_up_type,
    CAST(ship_time                 AS TIMESTAMP)     AS ship_time,
    CAST(refund_success_time       AS TIMESTAMP)     AS refund_success_time,
    refund_recv_accout                               AS refund_recv_accout,
    refund_account                                   AS refund_account,
    refund_request_source                            AS refund_request_source,
    CAST(card_price                AS DECIMAL(20,4)) AS card_price,
    CAST(act_price                 AS DECIMAL(20,4)) AS act_price,
    goods_price_json                                 AS goods_price_json,
    payment_sub_type                                 AS payment_sub_type,
    team_first                                       AS team_first,
    CAST(refuse_status             AS BIGINT)        AS refuse_status,
    prop1                                            AS prop1,
    prop2                                            AS prop2,
    prop3                                            AS prop3,
    CAST(point                     AS BIGINT)        AS point,
    order_type                                       AS order_type,
    CAST(trade_amount              AS DECIMAL(20,4)) AS trade_amount,
    refund_type                                      AS refund_type,
    refund_reason                                    AS refund_reason,
    evaluation                                       AS evaluation,
    CAST(user_refund_time          AS TIMESTAMP)     AS user_refund_time,
    CAST(refund_status             AS BIGINT)        AS refund_status,
    merchant_refund_reason                           AS merchant_refund_reason,
    CAST(point_deduct              AS DECIMAL(20,4)) AS point_deduct,
    CAST(shipping_cost             AS DECIMAL(20,4)) AS shipping_cost,
    merchant_remark                                  AS merchant_remark,
    CAST(pay_record                AS BIGINT)        AS pay_record,
    order_sub_type                                   AS order_sub_type,
    give_user_code                                   AS give_user_code,
    CAST(give_order_id             AS BIGINT)        AS give_order_id,
    CAST(read_flag                 AS BIGINT)        AS read_flag,
    CAST(give_num                  AS BIGINT)        AS give_num,
    CAST(invoice_id                AS BIGINT)        AS invoice_id,
    combination_no                                   AS combination_no,
    CAST(open_self                 AS BIGINT)        AS open_self,
    refund_desc                                      AS refund_desc,
    CAST(goods_allocate            AS BIGINT)        AS goods_allocate,
    CAST(close_payment_status      AS BIGINT)        AS close_payment_status,
    CAST(close_payment_time        AS TIMESTAMP)     AS close_payment_time,
    CAST(finished_time             AS TIMESTAMP)     AS finished_time,
    CAST(expire_time               AS TIMESTAMP)     AS expire_time,
    CAST(settlement_amount         AS DECIMAL(20,4)) AS settlement_amount,
    CAST(platform_coupon           AS BIGINT)        AS platform_coupon,
    CAST(platform_discount         AS DECIMAL(20,4)) AS platform_discount,
    CAST(discount_amount           AS DECIMAL(20,4)) AS discount_amount,
    CAST(member_discount           AS DECIMAL(20,4)) AS member_discount,
    CAST(shipping_free_id          AS BIGINT)        AS shipping_free_id,
    CAST(shipping_free_amount      AS DECIMAL(20,4)) AS shipping_free_amount,
    CAST(discount_point            AS BIGINT)        AS discount_point,
    CAST(un_shipped_num            AS BIGINT)        AS un_shipped_num,
    CAST(pre_un_shipped_num        AS BIGINT)        AS pre_un_shipped_num,
    CAST(wait_shipped_num          AS BIGINT)        AS wait_shipped_num,
    CAST(pre_wait_shipped_num      AS BIGINT)        AS pre_wait_shipped_num,
    CAST(refuse_time               AS TIMESTAMP)     AS refuse_time,
    CAST(refuse_notice             AS BIGINT)        AS refuse_notice,
    CAST(pickup_time               AS TIMESTAMP)     AS pickup_time,
    waring_type                                      AS waring_type,
    CAST(waring_status             AS BIGINT)        AS waring_status,
    point_type                                       AS point_type,
    CAST(delivery_end_time         AS TIMESTAMP)     AS delivery_end_time,
    CAST(serve_status              AS BIGINT)        AS serve_status,
    CAST(self_pickup_time          AS TIMESTAMP)     AS self_pickup_time,
    CAST(act_discount              AS DECIMAL(20,4)) AS act_discount,
    CASE WHEN del_flg = '1' THEN TRUE ELSE FALSE END AS is_deleted,
    ods_dt                                           AS dt
FROM (
    SELECT *,
        DATE_FORMAT(update_time, 'yyyyMMdd') AS ods_dt,
        ROW_NUMBER() OVER (
            PARTITION BY id, DATE_FORMAT(update_time, 'yyyyMMdd')
            ORDER BY update_time DESC
        ) AS rn
    FROM (
        SELECT
            id, group_info_id, merchant_id, user_id, shipping_address_id, purchase_count,
            order_no, accounts_payable, actual_payment, payment_type, payment_time, coupon,
            discount, status, remark, create_time, create_by, update_time, update_by,
            payment_status, payment_status_desc, payment_success_time, del_flg, curier_company,
            refund_fee, refund_time, anonymous, pick_up_type, ship_time, refund_success_time,
            refund_recv_accout, refund_account, refund_request_source, card_price, act_price,
            goods_price_json, payment_sub_type, team_first, refuse_status, prop1, prop2, prop3,
            point, order_type, trade_amount, refund_type, refund_reason, evaluation,
            user_refund_time, refund_status, merchant_refund_reason, point_deduct, shipping_cost,
            merchant_remark, pay_record, order_sub_type, give_user_code, give_order_id, read_flag,
            give_num, invoice_id, combination_no, open_self, refund_desc, goods_allocate,
            close_payment_status, close_payment_time, finished_time, expire_time, settlement_amount,
            platform_coupon, platform_discount, discount_amount, member_discount, shipping_free_id,
            shipping_free_amount, discount_point, un_shipped_num, pre_un_shipped_num, wait_shipped_num,
            pre_wait_shipped_num, refuse_time, refuse_notice, pickup_time, waring_type, waring_status,
            point_type, delivery_end_time, serve_status, self_pickup_time, act_discount
        FROM raw.raw_trd_card_group_order_info_his_o
        UNION ALL
        SELECT
            id, group_info_id, merchant_id, user_id, shipping_address_id, purchase_count,
            order_no, accounts_payable, actual_payment, payment_type, payment_time, coupon,
            discount, status, remark, create_time, create_by, update_time, update_by,
            payment_status, payment_status_desc, payment_success_time, del_flg, curier_company,
            refund_fee, refund_time, anonymous, pick_up_type, ship_time, refund_success_time,
            refund_recv_accout, refund_account, refund_request_source, card_price, act_price,
            goods_price_json, payment_sub_type, team_first, refuse_status, prop1, prop2, prop3,
            point, order_type, trade_amount, refund_type, refund_reason, evaluation,
            user_refund_time, refund_status, merchant_refund_reason, point_deduct, shipping_cost,
            merchant_remark, pay_record, order_sub_type, give_user_code, give_order_id, read_flag,
            give_num, invoice_id, combination_no, open_self, refund_desc, goods_allocate,
            close_payment_status, close_payment_time, finished_time, expire_time, settlement_amount,
            platform_coupon, platform_discount, discount_amount, member_discount, shipping_free_id,
            shipping_free_amount, discount_point, un_shipped_num, pre_un_shipped_num, wait_shipped_num,
            pre_wait_shipped_num, refuse_time, refuse_notice, pickup_time, waring_type, waring_status,
            point_type, delivery_end_time, serve_status, self_pickup_time, act_discount
        FROM raw.raw_trd_card_group_order_info_inc_d
    ) u
) t
WHERE t.rn = 1;