poyee-data-warehouse/jobs/dim/trd/dim_trd_card_group_ful_d.sql

-- 作者:tianyu.chu
-- 日期:2026-05-10
-- 工单:(无)
-- 目的:dim_trd_card_group_ful_d 日常增量(kb/28 §1.3 + ADR-08,业界主流模式 B):
--      (a) today_changed = 今日 ods cgi 增量 group_info_id 集合;
--      (b) today_rebuilt = 今日 ods cgi 行(sport→category 清洗);
--      (c) 昨日 dim 中今日没变的 group_info_id (NOT IN today_changed) 直接保留;
--      (d) UNION ALL 写入 dim dt=${dt} 单分区
-- 状态:[草案]
-- 备注:sched=T,${dt}=T-1,${pdt}=T-2;
--      单源(ods.cgi 仅一张表)写法比 dim_usr_user 简单:今日变更行直接用今日 cgi 字段,无需 LEFT JOIN 昨日 dim 兜底;
--      sport→category 清洗规则 init 与 sche 同步实现(kb/28 §3.2);
--      性能:dim 大表只 scan + broadcast NOT IN 剪枝,不参与 shuffle;
--      前置 DS DEPENDENT:ods.cgi.dt=${dt} + dim.dt=${pdt}

INSERT OVERWRITE TABLE dim.dim_trd_card_group_ful_d PARTITION (dt='${dt}')
WITH today_cgi AS (
    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_info_inc_d
        WHERE dt = '${dt}'
    ) t
    WHERE t.rn = 1
),
today_changed AS (
    SELECT id AS group_info_id FROM today_cgi
),
today_rebuilt AS (
    SELECT
        cg.id                              AS group_info_id,
        cg.code                            AS code,
        cg.appid                           AS appid,
        cg.name                            AS name,
        cg.display_name                    AS display_name,
        cg.group_show_name                 AS group_show_name,
        cg.merchant_id                     AS merchant_id,
        cg.mer_name                        AS mer_name,
        cg.list_id                         AS list_id,
        cg.list_code                       AS list_code,
        cg.panini_list_id                  AS panini_list_id,
        CASE
            WHEN cg.sport = 'mlb'    THEN 'MLB'
            WHEN cg.sport = 'Boxing' THEN 'UFC'
            WHEN cg.sport = 'other'  THEN NULL
            ELSE cg.sport
        END                                AS category,
        cg.year                            AS year,
        cg.manufacturer                    AS manufacturer,
        cg.sets                            AS sets,
        cg.type                            AS type,
        cg.sub_type                        AS sub_type,
        cg.random_type                     AS random_type,
        cg.specs                           AS specs,
        cg.total_price                     AS total_price_cny,
        cg.unit_price                      AS unit_price_cny,
        cg.act_price                       AS act_price_cny,
        cg.copies                          AS copies,
        cg.total_num                       AS total_num,
        cg.weight                          AS weight,
        cg.order_quota_min                 AS order_quota_min,
        cg.order_quota_max                 AS order_quota_max,
        cg.user_quota_max                  AS user_quota_max,
        cg.min_card_num                    AS min_card_num,
        cg.mix_copies                      AS mix_copies,
        cg.change_type                     AS change_type,
        cg.status                          AS status,
        cg.sold_copies                     AS sold_copies,
        cg.real_sold_num                   AS real_sold_num,
        cg.sold_end_time                   AS sold_end_time,
        cg.finished_time                   AS finished_time,
        cg.release_time                    AS release_time,
        cg.start_time                      AS start_time,
        cg.cycle                           AS cycle,
        cg.group_full_time                 AS group_full_time,
        cg.review_hold_time                AS review_hold_time,
        cg.review_approval_time            AS review_approval_time,
        cg.review_num                      AS review_num,
        cg.confirm_send_time               AS confirm_send_time,
        cg.close_payment_time              AS close_payment_time,
        cg.close_payment_status            AS close_payment_status,
        cg.group_sets_no                   AS group_sets_no,
        cg.live_create_time                AS live_create_time,
        cg.live_start_time                 AS live_start_time,
        cg.live_end_time                   AS live_end_time,
        cg.report_start_time               AS report_start_time,
        cg.report_end_time                 AS report_end_time,
        cg.report_review_num               AS report_review_num,
        cg.report_review_first_time        AS report_review_first_time,
        cg.report_review_end_time          AS report_review_end_time,
        cg.act_id                          AS act_id,
        cg.act_type                        AS act_type,
        cg.act_point_type                  AS act_point_type,
        cg.free_flag                       AS free_flag,
        cg.exclusive                       AS exclusive,
        cg.has_bg                          AS has_bg,
        cg.team_first                      AS team_first,
        cg.use_member_discount             AS use_member_discount,
        cg.use_coupon                      AS use_coupon,
        cg.user_level                      AS user_level,
        cg.merchant_open                   AS merchant_open,
        cg.merchant_sort                   AS merchant_sort,
        cg.custom                          AS custom,
        cg.gift_card_id                    AS gift_card_id,
        cg.open_card                       AS open_card,
        cg.goods_type                      AS goods_type,
        cg.point_rate                      AS point_rate,
        cg.point_max                       AS point_max,
        cg.point_min                       AS point_min,
        cg.point_type                      AS point_type,
        cg.payment_method                  AS payment_method,
        cg.payment_total_price             AS payment_total_price_cny,
        cg.payment_commission              AS payment_commission_cny,
        cg.payment_finished_price          AS payment_finished_price_cny,
        cg.payment_remain_price            AS payment_remain_price_cny,
        cg.payment_online_price            AS payment_online_price_cny,
        cg.commission_rate                 AS commission_rate,
        cg.hot_type                        AS hot_type,
        cg.report_flag                     AS report_flag,
        cg.banner_end_time                 AS banner_end_time,
        cg.waring_type                     AS waring_type,
        cg.compensation_status             AS compensation_status,
        cg.reviewmsg                       AS reviewmsg,
        cg.review_account                  AS review_account,
        cg.`lock`                          AS `lock`,
        cg.marketing_info                  AS marketing_info,
        cg.msg                             AS msg,
        cg.title                           AS title,
        cg.remark                          AS remark,
        cg.create_time                     AS create_time,
        cg.update_time                     AS last_update_time,
        cg.del_time                        AS del_time,
        cg.is_deleted                      AS is_deleted,
        CURRENT_TIMESTAMP()                AS etl_time
    FROM today_cgi cg
)
SELECT
    group_info_id,
    code,
    appid,
    name,
    display_name,
    group_show_name,
    merchant_id,
    mer_name,
    list_id,
    list_code,
    panini_list_id,
    category,
    year,
    manufacturer,
    sets,
    type,
    sub_type,
    random_type,
    specs,
    total_price_cny,
    unit_price_cny,
    act_price_cny,
    copies,
    total_num,
    weight,
    order_quota_min,
    order_quota_max,
    user_quota_max,
    min_card_num,
    mix_copies,
    change_type,
    status,
    sold_copies,
    real_sold_num,
    sold_end_time,
    finished_time,
    release_time,
    start_time,
    cycle,
    group_full_time,
    review_hold_time,
    review_approval_time,
    review_num,
    confirm_send_time,
    close_payment_time,
    close_payment_status,
    group_sets_no,
    live_create_time,
    live_start_time,
    live_end_time,
    report_start_time,
    report_end_time,
    report_review_num,
    report_review_first_time,
    report_review_end_time,
    act_id,
    act_type,
    act_point_type,
    free_flag,
    exclusive,
    has_bg,
    team_first,
    use_member_discount,
    use_coupon,
    user_level,
    merchant_open,
    merchant_sort,
    custom,
    gift_card_id,
    open_card,
    goods_type,
    point_rate,
    point_max,
    point_min,
    point_type,
    payment_method,
    payment_total_price_cny,
    payment_commission_cny,
    payment_finished_price_cny,
    payment_remain_price_cny,
    payment_online_price_cny,
    commission_rate,
    hot_type,
    report_flag,
    banner_end_time,
    waring_type,
    compensation_status,
    reviewmsg,
    review_account,
    `lock`,
    marketing_info,
    msg,
    title,
    remark,
    create_time,
    last_update_time,
    del_time,
    is_deleted,
    etl_time
FROM today_rebuilt

UNION ALL

SELECT
    group_info_id,
    code,
    appid,
    name,
    display_name,
    group_show_name,
    merchant_id,
    mer_name,
    list_id,
    list_code,
    panini_list_id,
    category,
    year,
    manufacturer,
    sets,
    type,
    sub_type,
    random_type,
    specs,
    total_price_cny,
    unit_price_cny,
    act_price_cny,
    copies,
    total_num,
    weight,
    order_quota_min,
    order_quota_max,
    user_quota_max,
    min_card_num,
    mix_copies,
    change_type,
    status,
    sold_copies,
    real_sold_num,
    sold_end_time,
    finished_time,
    release_time,
    start_time,
    cycle,
    group_full_time,
    review_hold_time,
    review_approval_time,
    review_num,
    confirm_send_time,
    close_payment_time,
    close_payment_status,
    group_sets_no,
    live_create_time,
    live_start_time,
    live_end_time,
    report_start_time,
    report_end_time,
    report_review_num,
    report_review_first_time,
    report_review_end_time,
    act_id,
    act_type,
    act_point_type,
    free_flag,
    exclusive,
    has_bg,
    team_first,
    use_member_discount,
    use_coupon,
    user_level,
    merchant_open,
    merchant_sort,
    custom,
    gift_card_id,
    open_card,
    goods_type,
    point_rate,
    point_max,
    point_min,
    point_type,
    payment_method,
    payment_total_price_cny,
    payment_commission_cny,
    payment_finished_price_cny,
    payment_remain_price_cny,
    payment_online_price_cny,
    commission_rate,
    hot_type,
    report_flag,
    banner_end_time,
    waring_type,
    compensation_status,
    reviewmsg,
    review_account,
    `lock`,
    marketing_info,
    msg,
    title,
    remark,
    create_time,
    last_update_time,
    del_time,
    is_deleted,
    etl_time
FROM dim.dim_trd_card_group_ful_d
WHERE dt = '${pdt}'
  AND group_info_id NOT IN (SELECT group_info_id FROM today_changed);