poyee-data-warehouse/jobs/ods/shp/ods_shp_tzy_merchant_info_inc_d.sql

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

INSERT OVERWRITE TABLE ods.ods_shp_tzy_merchant_info_inc_d PARTITION (dt)
SELECT
    CAST(id                  AS BIGINT)    AS id,
    appid                                  AS appid,
    CAST(user_id             AS BIGINT)    AS user_id,
    username                               AS username,
    name                                   AS name,
    CAST(status              AS BIGINT)    AS status,
    remark                                 AS remark,
    create_by                              AS create_by,
    CAST(create_time         AS TIMESTAMP) AS create_time,
    update_by                              AS update_by,
    CAST(update_time         AS TIMESTAMP) AS update_time,
    code                                   AS code,
    CAST(fans                AS BIGINT)    AS fans,
    CAST(sale_num            AS BIGINT)    AS sale_num,
    CAST(applet_auth         AS BOOLEAN)   AS applet_auth,
    CAST(applet_lives_auth   AS BOOLEAN)   AS applet_lives_auth,
    applet_lives_role                      AS applet_lives_role,
    commission_rate                        AS commission_rate,
    prop_json                              AS prop_json,
    CAST(sort_rate           AS BIGINT)    AS sort_rate,
    CAST(check_status        AS BIGINT)    AS check_status,
    live_type                              AS live_type,
    living_auth_config                     AS living_auth_config,
    CAST(goods_sold_num      AS BIGINT)    AS goods_sold_num,
    hot_config                             AS hot_config,
    tag_config                             AS tag_config,
    CAST(mall_role           AS BIGINT)    AS mall_role,
    living_time                            AS living_time,
    express_level                          AS express_level,
    CAST(del_flg             AS BIGINT)    AS del_flg,
    group_show_name                        AS group_show_name,
    main_business                          AS main_business,
    CAST(min_card_num        AS BIGINT)    AS min_card_num,
    dy_name                                AS dy_name,
    CAST(current_month_score AS DOUBLE)    AS current_month_score,
    CAST(member_level        AS BIGINT)    AS member_level,
    member_name                            AS member_name,
    member_medal                           AS member_medal,
    CAST(prefer_valid_time   AS TIMESTAMP) AS prefer_valid_time,
    tag_id                                 AS tag_id,
    CAST(show_status         AS BIGINT)    AS show_status,
    point_type                             AS point_type,
    CAST(refund_limit_day    AS BIGINT)    AS refund_limit_day,
    CAST(open_act_discount   AS BIGINT)    AS open_act_discount,
    CAST(reputation_score    AS BIGINT)    AS reputation_score,
    CAST(hide_stock          AS BIGINT)    AS hide_stock,
    CAST(version             AS BIGINT)    AS version,
    CAST(total_sold_num      AS BIGINT)    AS total_sold_num,
    shipping_cost_config                   AS shipping_cost_config,
    CAST(merchant_group_id   AS BIGINT)    AS merchant_group_id,
    CASE WHEN del_flg = '1' THEN TRUE ELSE FALSE END AS is_deleted,
    ods_dt                                 AS dt
FROM (
    SELECT *,
        DATE_FORMAT(COALESCE(NULLIF(update_time, ''), create_time), 'yyyyMMdd') AS ods_dt,
        ROW_NUMBER() OVER (
            PARTITION BY id, DATE_FORMAT(COALESCE(NULLIF(update_time, ''), create_time), 'yyyyMMdd')
            ORDER BY COALESCE(NULLIF(update_time, ''), create_time) DESC
        ) AS rn
    FROM raw.raw_shp_tzy_merchant_info_inc_d
    WHERE dt IN ('${dt}', '${pdt}')
      AND DATE_FORMAT(COALESCE(NULLIF(update_time, ''), create_time), 'yyyyMMdd') = '${dt}'
) t
WHERE t.rn = 1;