poyee-data-warehouse/jobs/ods/usr/ods_usr_app_base_user_inc_d.sql

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

INSERT OVERWRITE TABLE ods.ods_usr_app_base_user_inc_d PARTITION (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,
    ods_dt                                          AS dt
FROM (
    SELECT *,
        DATE_FORMAT(update_time, 'yyyyMMdd') AS ods_dt,
        ROW_NUMBER() OVER (
            PARTITION BY id, DATE_FORMAT(update_time, 'yyyyMMdd')
            ORDER BY update_time DESC
        ) AS rn
    FROM raw.raw_usr_app_base_user_inc_d
    WHERE dt IN ('${dt}', '${pdt}')
      AND DATE_FORMAT(update_time, 'yyyyMMdd') = '${dt}'
) t
WHERE t.rn = 1;