poyee-data-warehouse/jobs/ods/usr/ods_usr_app_base_user_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_usr_app_base_user_ful_d DROP IF EXISTS PARTITION (dt='${wdt}');
ALTER TABLE ods.ods_usr_app_base_user_ful_d DROP IF EXISTS PARTITION (dt='${wdt}');

INSERT OVERWRITE TABLE ods.ods_usr_app_base_user_ful_d PARTITION (dt='${dt}')
SELECT
    CAST(id                       AS BIGINT)        AS id,
    appid                                           AS appid,
    CAST(point                    AS BIGINT)        AS point,
    CAST(level                    AS BIGINT)        AS level,
    register_channel                                AS register_channel,
    CAST(status                   AS BIGINT)        AS status,
    CAST(del_flg                  AS BIGINT)        AS del_flg,
    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,
    username                                        AS username,
    CAST(growth_num               AS BIGINT)        AS growth_num,
    code                                            AS code,
    CAST(notify_flag              AS BIGINT)        AS notify_flag,
    CAST(user_id                  AS BIGINT)        AS user_id,
    notify_type                                     AS notify_type,
    CAST(face_verify              AS BIGINT)        AS face_verify,
    CAST(open_psd                 AS BIGINT)        AS open_psd,
    CAST(refuse_pick_up           AS BIGINT)        AS refuse_pick_up,
    prop1                                           AS prop1,
    prop2                                           AS prop2,
    prop3                                           AS prop3,
    prop4                                           AS prop4,
    CAST(window_open              AS BIGINT)        AS window_open,
    CAST(open_invoice             AS BIGINT)        AS open_invoice,
    CAST(blacklist                AS BIGINT)        AS blacklist,
    id_card                                         AS id_card,
    CAST(member_level             AS BIGINT)        AS member_level,
    member_name                                     AS member_name,
    CAST(current_month_growth     AS BIGINT)        AS current_month_growth,
    CAST(member_init_flag         AS BIGINT)        AS member_init_flag,
    CAST(member_keep_growth       AS BIGINT)        AS member_keep_growth,
    register_ip_addr                                AS register_ip_addr,
    register_addr                                   AS register_addr,
    login_ip_addr                                   AS login_ip_addr,
    login_addr                                      AS login_addr,
    CAST(notify_top_show          AS BIGINT)        AS notify_top_show,
    CAST(voice_reminder           AS BIGINT)        AS voice_reminder,
    CAST(vibrate_reminder         AS BIGINT)        AS vibrate_reminder,
    CAST(consume_amount           AS DECIMAL(20,4)) AS consume_amount,
    CAST(order_total_num          AS BIGINT)        AS order_total_num,
    CAST(open_card_show           AS BIGINT)        AS open_card_show,
    effects_type                                    AS effects_type,
    live_config_json                                AS live_config_json,
    CAST(cancel_verify_num        AS BIGINT)        AS cancel_verify_num,
    CAST(version                  AS BIGINT)        AS version,
    CAST(daily_limit              AS BIGINT)        AS daily_limit,
    CAST(weekly_limit             AS BIGINT)        AS weekly_limit,
    CAST(monthly_limit            AS BIGINT)        AS monthly_limit,
    CAST(live_anonymous           AS BIGINT)        AS live_anonymous,
    CASE WHEN del_flg = '1' THEN TRUE ELSE FALSE END AS is_deleted
FROM raw.raw_usr_app_base_user_ful_d
WHERE dt = '${dt}';