poyee-data-warehouse/manual/backfill/20260723_ods_trd_card_group_info_reinit_rebucket.sql

-- 作者：tianyu.chu
-- 日期：2026-07-23
-- 工单：(无)
-- 目的：拼团全量 re-init 重灌——读 tmp(dt=20260723 全表当前态)，每单一行落其活动日 = GREATEST(create,update)，
--       重灌 ods_inc 全史分区（至 0721）。业务会改 create、无"创建日"概念，取较晚者为最后活动日。
-- 状态：[待执行]
-- 备注：口径与 jobs/ods/trd/ods_trd_card_group_info_inc_d.sql 一致（GREATEST 活动日、单条）；
--       上界 ≤ 0721：0722 交调度自愈（任务日 0723 处理 0722），无下界（全史）；
--       拼团仅 ~50 万行，无需资源调优；lock 是保留字，SELECT 带反引号；
--       dump 一单一行一活动日，(id) 天然不重，无需去重；
--       跑前先 DROP + 重建空表 ods.ods_trd_card_group_info_inc_d（外部表 DROP 不清 HDFS，另 hdfs rm）；
--       跑完按 id 对 dump（活动日≤0721 的单数）验平后，tmp 中转表可 DROP。
--       跑法：python3 bin/spark-sql-starter.py -f manual/backfill/20260723_ods_trd_card_group_info_reinit_rebucket.sql

SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
SET hive.exec.max.dynamic.partitions=5000;
SET hive.exec.max.dynamic.partitions.pernode=5000;

INSERT OVERWRITE TABLE ods.ods_trd_card_group_info_inc_d PARTITION (dt)
SELECT
    CAST(id                           AS BIGINT)        AS id,
    CAST(merchant_id                  AS BIGINT)        AS merchant_id,
    appid                                               AS appid,
    name                                                AS name,
    code                                                AS code,
    CAST(status                       AS BIGINT)        AS status,
    specs                                               AS specs,
    type                                                AS type,
    random_type                                         AS random_type,
    CAST(total_price                  AS DECIMAL(20,4)) AS total_price,
    CAST(copies                       AS BIGINT)        AS copies,
    CAST(unit_price                   AS DECIMAL(20,4)) AS unit_price,
    CAST(sold_copies                  AS BIGINT)        AS sold_copies,
    release_time                                        AS release_time,
    cycle                                               AS cycle,
    show_applet                                         AS show_applet,
    title                                               AS title,
    msg                                                 AS msg,
    remark                                              AS remark,
    CAST(create_time                  AS TIMESTAMP)     AS create_time,
    update_by                                           AS update_by,
    CAST(update_time                  AS TIMESTAMP)     AS update_time,
    CAST(order_quota_min              AS BIGINT)        AS order_quota_min,
    CAST(order_quota_max              AS BIGINT)        AS order_quota_max,
    CAST(user_quota_max               AS BIGINT)        AS user_quota_max,
    CAST(start_time                   AS TIMESTAMP)     AS start_time,
    marketing_info                                      AS marketing_info,
    reviewmsg                                           AS reviewmsg,
    CAST(`lock`                       AS BOOLEAN)       AS `lock`,
    commission_rate                                     AS commission_rate,
    year                                                AS year,
    sport                                               AS sport,
    manufacturer                                        AS manufacturer,
    sets                                                AS sets,
    act                                                 AS act,
    config                                              AS config,
    info_config                                         AS info_config,
    CAST(total_num                    AS BIGINT)        AS total_num,
    CAST(banner_end_time              AS TIMESTAMP)     AS banner_end_time,
    add_banner                                          AS add_banner,
    CAST(finished_time                AS TIMESTAMP)     AS finished_time,
    display_name                                        AS display_name,
    CAST(group_sets_no                AS BIGINT)        AS group_sets_no,
    CAST(close_payment_time           AS TIMESTAMP)     AS close_payment_time,
    CAST(confirm_send_time            AS TIMESTAMP)     AS confirm_send_time,
    CAST(close_payment_status         AS BIGINT)        AS close_payment_status,
    CAST(open_card                    AS BIGINT)        AS open_card,
    close_payment_record                                AS close_payment_record,
    CAST(group_full_time              AS TIMESTAMP)     AS group_full_time,
    CAST(live_create_time             AS TIMESTAMP)     AS live_create_time,
    CAST(live_start_time              AS TIMESTAMP)     AS live_start_time,
    CAST(live_end_time                AS TIMESTAMP)     AS live_end_time,
    CAST(report_start_time            AS TIMESTAMP)     AS report_start_time,
    CAST(report_end_time              AS TIMESTAMP)     AS report_end_time,
    CAST(report_review_num            AS BIGINT)        AS report_review_num,
    CAST(report_review_first_time     AS TIMESTAMP)     AS report_review_first_time,
    CAST(report_review_end_time       AS TIMESTAMP)     AS report_review_end_time,
    CAST(review_hold_time             AS TIMESTAMP)     AS review_hold_time,
    CAST(review_approval_time         AS TIMESTAMP)     AS review_approval_time,
    CAST(review_num                   AS BIGINT)        AS review_num,
    config_json                                         AS config_json,
    CAST(free_flag                    AS BIGINT)        AS free_flag,
    mer_name                                            AS mer_name,
    change_type                                         AS change_type,
    CAST(act_price                    AS DECIMAL(20,4)) AS act_price,
    act_config_json                                     AS act_config_json,
    CAST(real_sold_num                AS BIGINT)        AS real_sold_num,
    CAST(weight                       AS BIGINT)        AS weight,
    hot_type                                            AS hot_type,
    CAST(team_first                   AS BIGINT)        AS team_first,
    prop1                                               AS prop1,
    prop2                                               AS prop2,
    prop3                                               AS prop3,
    CAST(point_rate                   AS BIGINT)        AS point_rate,
    CAST(point_max                    AS BIGINT)        AS point_max,
    CAST(point_min                    AS BIGINT)        AS point_min,
    CAST(list_id                      AS BIGINT)        AS list_id,
    list_code                                           AS list_code,
    CAST(mix_copies                   AS BIGINT)        AS mix_copies,
    sub_type                                            AS sub_type,
    act_point_type                                      AS act_point_type,
    CAST(payment_method               AS BIGINT)        AS payment_method,
    CAST(payment_total_price          AS DECIMAL(20,4)) AS payment_total_price,
    CAST(payment_commission           AS DECIMAL(20,4)) AS payment_commission,
    CAST(payment_finished_price       AS DECIMAL(20,4)) AS payment_finished_price,
    CAST(payment_remain_price         AS DECIMAL(20,4)) AS payment_remain_price,
    CAST(payment_online_price         AS DECIMAL(20,4)) AS payment_online_price,
    CAST(exclusive                    AS BIGINT)        AS exclusive,
    CAST(has_bg                       AS BIGINT)        AS has_bg,
    CAST(merchant_sort                AS BIGINT)        AS merchant_sort,
    CAST(del_flg                      AS BIGINT)        AS del_flg,
    CAST(del_time                     AS TIMESTAMP)     AS del_time,
    review_account                                      AS review_account,
    CAST(act_id                       AS BIGINT)        AS act_id,
    CAST(sold_end_time                AS TIMESTAMP)     AS sold_end_time,
    CAST(panini_list_id               AS BIGINT)        AS panini_list_id,
    hot_type_config                                     AS hot_type_config,
    CAST(goods_type                   AS BIGINT)        AS goods_type,
    CAST(report_flag                  AS BIGINT)        AS report_flag,
    CAST(use_coupon                   AS BIGINT)        AS use_coupon,
    CAST(user_level                   AS BIGINT)        AS user_level,
    CAST(custom                       AS BIGINT)        AS custom,
    CAST(gift_card_id                 AS BIGINT)        AS gift_card_id,
    group_show_name                                     AS group_show_name,
    CAST(min_card_num                 AS BIGINT)        AS min_card_num,
    act_type                                            AS act_type,
    waring_type                                         AS waring_type,
    CAST(compensation_status          AS BIGINT)        AS compensation_status,
    point_type                                          AS point_type,
    first_act_config                                    AS first_act_config,
    gift_config                                         AS gift_config,
    CAST(version                      AS BIGINT)        AS version,
    extra_prop                                          AS extra_prop,
    CAST(use_member_discount          AS BIGINT)        AS use_member_discount,
    CAST(merchant_open                AS BIGINT)        AS merchant_open,
    CASE WHEN del_flg = '1' THEN TRUE ELSE FALSE END    AS is_deleted,
    adt                                                 AS dt
FROM (
    SELECT *,
        DATE_FORMAT(GREATEST(create_time, NULLIF(update_time, '')), 'yyyyMMdd') AS adt   -- 活动日 = GREATEST(create,update)
    FROM raw.tmp_trd_card_group_info
    WHERE dt = '20260723'
) t
WHERE adt <= '20260721'
DISTRIBUTE BY dt;