poyee-data-warehouse/jobs/dim/usr/dim_usr_user_ful_d.sql

-- 作者:tianyu.chu
-- 日期:2026-05-09
-- 工单:(无)
-- 目的:dim_usr_user_ful_d 日常增量(kb/28 §1.3):
--      new = 今日 ods (base / cert) 增量;old = dim.dt=${pdt} 昨日全量;
--      old FULL OUTER JOIN today_base FULL OUTER JOIN today_cert,字段 COALESCE(today, old) 优先取今日
-- 状态:[草案]
-- 备注:sched=T,${dt}=T-1,${pdt}=T-2;
--      ods 增量同步只含当天有 update 的 user,所以今日 ods 即"今日变更行";
--      base 字段从 today_base 取(今日没变就 NULL→fallback old);cert 字段从 today_cert 取同理;
--      cert_info update_time 几乎全空,ORDER BY 用 COALESCE(update_time, create_time);
--      前置 DS DEPENDENT:ods.base_user.dt=${dt} + ods.cert_info.dt=${dt} + dim.dt=${pdt}

INSERT OVERWRITE TABLE dim.dim_usr_user_ful_d PARTITION (dt='${dt}')
WITH today_base AS (
    SELECT *
    FROM (
        SELECT *,
            ROW_NUMBER() OVER (
                PARTITION BY id
                ORDER BY COALESCE(update_time, create_time) DESC
            ) AS rn
        FROM ods.ods_usr_app_base_user_inc_d
        WHERE dt = '${dt}'
    ) t
    WHERE t.rn = 1
),
today_cert AS (
    SELECT *
    FROM (
        SELECT *,
            ROW_NUMBER() OVER (
                PARTITION BY user_id
                ORDER BY COALESCE(update_time, create_time) DESC
            ) AS rn
        FROM ods.ods_usr_app_user_cert_info_inc_d
        WHERE dt = '${dt}'
    ) t
    WHERE t.rn = 1
),
old_dim AS (
    SELECT * FROM dim.dim_usr_user_ful_d WHERE dt = '${pdt}'
)
SELECT
    COALESCE(bu.id, ci.user_id, o.user_id)                    AS user_id,
    COALESCE(bu.appid, o.appid)                               AS appid,
    COALESCE(bu.username, o.username)                         AS username,
    COALESCE(bu.code, o.code)                                 AS code,
    COALESCE(ci.cert_sex, o.sex_cert)                         AS sex_cert,
    COALESCE(ci.cert_birthday, o.birthday_cert)               AS birthday_cert,
    COALESCE(ci.cert_province, o.cert_province)               AS cert_province,
    COALESCE(ci.cert_city, o.cert_city)                       AS cert_city,
    CASE WHEN ci.user_id IS NOT NULL OR o.is_cert = TRUE THEN TRUE ELSE FALSE END AS is_cert,
    COALESCE(bu.id_card, o.id_card)                           AS id_card,
    COALESCE(bu.face_verify, o.face_verify)                   AS face_verify,
    COALESCE(bu.cancel_verify_num, o.cancel_verify_num)       AS cancel_verify_num,
    COALESCE(bu.register_channel, o.register_channel)         AS register_channel,
    COALESCE(bu.register_addr, o.register_addr)               AS register_addr,
    COALESCE(bu.register_ip_addr, o.register_ip_addr)         AS register_ip_addr,
    COALESCE(bu.create_time, o.reg_create_time)               AS reg_create_time,
    COALESCE(bu.login_addr, o.login_addr)                     AS login_addr,
    COALESCE(bu.login_ip_addr, o.login_ip_addr)               AS login_ip_addr,
    COALESCE(bu.level, o.level)                               AS level,
    COALESCE(bu.member_level, o.member_level)                 AS member_level,
    COALESCE(bu.member_name, o.member_name)                   AS member_name,
    COALESCE(bu.growth_num, o.growth_num)                     AS growth_num,
    COALESCE(bu.current_month_growth, o.current_month_growth) AS current_month_growth,
    COALESCE(bu.member_keep_growth, o.member_keep_growth)     AS member_keep_growth,
    COALESCE(bu.member_init_flag, o.member_init_flag)         AS member_init_flag,
    COALESCE(bu.point, o.point)                               AS point,
    COALESCE(bu.consume_amount, o.consume_amount_cny)         AS consume_amount_cny,
    COALESCE(bu.order_total_num, o.order_total_num)           AS order_total_num,
    COALESCE(bu.status, o.status)                             AS status,
    COALESCE(bu.blacklist, o.blacklist)                       AS blacklist,
    COALESCE(bu.refuse_pick_up, o.refuse_pick_up)             AS refuse_pick_up,
    COALESCE(bu.notify_flag, o.notify_flag)                   AS notify_flag,
    COALESCE(bu.open_invoice, o.open_invoice)                 AS open_invoice,
    COALESCE(bu.open_psd, o.open_psd)                         AS open_psd,
    COALESCE(bu.daily_limit, o.daily_limit)                   AS daily_limit,
    COALESCE(bu.weekly_limit, o.weekly_limit)                 AS weekly_limit,
    COALESCE(bu.monthly_limit, o.monthly_limit)               AS monthly_limit,
    COALESCE(bu.update_time, o.last_update_time)              AS last_update_time,
    COALESCE(bu.is_deleted, o.is_deleted)                     AS is_deleted,
    CURRENT_TIMESTAMP()                                       AS etl_time
FROM old_dim o
FULL OUTER JOIN today_base bu ON o.user_id = bu.id
FULL OUTER JOIN today_cert ci ON COALESCE(o.user_id, bu.id) = ci.user_id;