poyee-data-warehouse/jobs/ods/trd/ods_trd_card_group_info_inc_d.sql

-- 作者：tianyu.chu
-- 日期：2026-05-07
-- 工单：(无)
-- 目的：raw → ods 增量同步（ADR-03）；双源 union (raw dt=${dt} + raw dt=${pdt}) + DATE_FORMAT(update_time)=${dt} 过滤 + (id, ods_dt) dedupe + 动态分区写入
-- 状态：[草案]
-- 备注：sched=T，${dt}=业务日 T-1（yyyyMMdd），${pdt}=T-2；ods 跨 dt 不去重，同 pk 多 dt 并存（拉链表底层）

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,
    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 raw.raw_trd_card_group_info_inc_d
    WHERE dt IN ('${dt}', '${pdt}')
      AND DATE_FORMAT(update_time, 'yyyyMMdd') = '${dt}'
) t
WHERE t.rn = 1;