-- 作者：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;