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

-- 作者：tianyu.chu
-- 日期：2026-07-24
-- 工单：(无)
-- 目的：raw → ods 全量快照 —— 读 raw dt=${dt} 全量（一行一 id = 当前态），CAST 落 ods ful_d dt=${dt}
-- 状态：[草案]
-- 备注：sched=T，${dt}=T-1；维度全量快照模型（无 seed / 无 merge / 无 dedup，PG id 唯一）见 kb/24 §1.2.2；
--       保留近 7 天：INSERT 前 DROP 掉 ${wdt}=T-8 一个分区，raw / ods 一并 drop（raw 的 drop 也收进本 ods
--       任务，DataX ini 不便 drop）；DS 传 wdt=$[yyyyMMdd-8]，手动跑 -p wdt=<T-8>；
--       静态分区写入 PARTITION(dt='${dt}')：源=raw、目标=ods，不同表无读写冲突。

ALTER TABLE raw.raw_shp_tzy_merchant_info_ful_d DROP IF EXISTS PARTITION (dt='${wdt}');
ALTER TABLE ods.ods_shp_tzy_merchant_info_ful_d DROP IF EXISTS PARTITION (dt='${wdt}');

INSERT OVERWRITE TABLE ods.ods_shp_tzy_merchant_info_ful_d PARTITION (dt='${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
FROM raw.raw_shp_tzy_merchant_info_ful_d
WHERE dt = '${dt}';