poyee-data-warehouse/manual/backfill/20260510_dim_trd_card_group_ful_d_init.sql

-- 作者:tianyu.chu
-- 日期:2026-05-10
-- 工单:(无)
-- 目的:dim_trd_card_group_ful_d 初始化(kb/28 §1.3 + ADR-08):
--      扫 ods 历史分区 dt < ${dt} + ROW_NUMBER 取每 pk 最新版本,
--      sport→category 数据脏点清洗(mlb→MLB / Boxing→UFC / other→NULL,kb/28 §3.2),
--      落 dim dt=${pdt} 单分区
-- 状态:[待执行]
-- 备注:首次上线 init/sche 同日跑(sched=T),${dt}=T-1,${pdt}=T-2;
--      init 灌 dim.dt=${pdt} 作 sche 首次依赖的"昨日 dim";扫 ods 范围 dt < ${dt}(即 dt <= ${pdt});
--      跑一次后由 jobs/dim/trd/dim_trd_card_group_ful_d.sql 接管日常增量

INSERT OVERWRITE TABLE dim.dim_trd_card_group_ful_d PARTITION (dt='${pdt}')
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 (
    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
) cg;