-- 作者:tianyu.chu -- 日期:2026-05-09 -- 工单:(无) -- 目的:dim_usr_user_ful_d 日常增量(kb/28 §1.3,业界主流模式 B): -- (a) 找今日变更 user_id (base 或 cert 任一变); -- (b) 这些 user 重 join 形成 today_rebuilt:base 字段 CASE WHEN bu IS NOT NULL THEN bu.x ELSE o.x,cert 字段同理; -- (c) 昨日 dim 中今日没变的 user (NOT IN today_changed) 直接保留; -- (d) UNION ALL 写入 dim dt=${dt} 单分区 -- 状态:[草案] -- 备注:sched=T,${dt}=T-1,${pdt}=T-2; -- base / cert 整组判断"是否今日变更",避免字段级 COALESCE 在"业务库主动置 NULL"场景下错误兜底昨日值; -- cert_info update_time 几乎全空,ORDER BY 用 COALESCE(update_time, create_time); -- 性能:dim 大表只 scan + broadcast NOT IN 剪枝,不参与 shuffle;today_changed user 数量小 broadcast 有效; -- 前置 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 ), today_changed AS ( SELECT id AS user_id FROM today_base UNION SELECT user_id FROM today_cert ), today_rebuilt AS ( SELECT COALESCE(bu.id, ci.user_id, o.user_id) AS user_id, CASE WHEN bu.id IS NOT NULL THEN bu.appid ELSE o.appid END AS appid, CASE WHEN bu.id IS NOT NULL THEN bu.username ELSE o.username END AS username, CASE WHEN bu.id IS NOT NULL THEN bu.code ELSE o.code END AS code, CASE WHEN ci.user_id IS NOT NULL THEN ci.cert_sex ELSE o.sex_cert END AS sex_cert, CASE WHEN ci.user_id IS NOT NULL THEN ci.cert_birthday ELSE o.birthday_cert END AS birthday_cert, CASE WHEN ci.user_id IS NOT NULL THEN ci.cert_province ELSE o.cert_province END AS cert_province, CASE WHEN ci.user_id IS NOT NULL THEN ci.cert_city ELSE o.cert_city END AS cert_city, CASE WHEN ci.user_id IS NOT NULL OR o.is_cert = TRUE THEN TRUE ELSE FALSE END AS is_cert, CASE WHEN bu.id IS NOT NULL THEN bu.id_card ELSE o.id_card END AS id_card, CASE WHEN bu.id IS NOT NULL THEN bu.face_verify ELSE o.face_verify END AS face_verify, CASE WHEN bu.id IS NOT NULL THEN bu.cancel_verify_num ELSE o.cancel_verify_num END AS cancel_verify_num, CASE WHEN bu.id IS NOT NULL THEN bu.register_channel ELSE o.register_channel END AS register_channel, CASE WHEN bu.id IS NOT NULL THEN bu.register_addr ELSE o.register_addr END AS register_addr, CASE WHEN bu.id IS NOT NULL THEN bu.register_ip_addr ELSE o.register_ip_addr END AS register_ip_addr, CASE WHEN bu.id IS NOT NULL THEN bu.create_time ELSE o.reg_create_time END AS reg_create_time, CASE WHEN bu.id IS NOT NULL THEN bu.login_addr ELSE o.login_addr END AS login_addr, CASE WHEN bu.id IS NOT NULL THEN bu.login_ip_addr ELSE o.login_ip_addr END AS login_ip_addr, CASE WHEN bu.id IS NOT NULL THEN bu.level ELSE o.level END AS level, CASE WHEN bu.id IS NOT NULL THEN bu.member_level ELSE o.member_level END AS member_level, CASE WHEN bu.id IS NOT NULL THEN bu.member_name ELSE o.member_name END AS member_name, CASE WHEN bu.id IS NOT NULL THEN bu.growth_num ELSE o.growth_num END AS growth_num, CASE WHEN bu.id IS NOT NULL THEN bu.current_month_growth ELSE o.current_month_growth END AS current_month_growth, CASE WHEN bu.id IS NOT NULL THEN bu.member_keep_growth ELSE o.member_keep_growth END AS member_keep_growth, CASE WHEN bu.id IS NOT NULL THEN bu.member_init_flag ELSE o.member_init_flag END AS member_init_flag, CASE WHEN bu.id IS NOT NULL THEN bu.point ELSE o.point END AS point, CASE WHEN bu.id IS NOT NULL THEN bu.consume_amount ELSE o.consume_amount_cny END AS consume_amount_cny, CASE WHEN bu.id IS NOT NULL THEN bu.order_total_num ELSE o.order_total_num END AS order_total_num, CASE WHEN bu.id IS NOT NULL THEN bu.status ELSE o.status END AS status, CASE WHEN bu.id IS NOT NULL THEN bu.blacklist ELSE o.blacklist END AS blacklist, CASE WHEN bu.id IS NOT NULL THEN bu.refuse_pick_up ELSE o.refuse_pick_up END AS refuse_pick_up, CASE WHEN bu.id IS NOT NULL THEN bu.notify_flag ELSE o.notify_flag END AS notify_flag, CASE WHEN bu.id IS NOT NULL THEN bu.open_invoice ELSE o.open_invoice END AS open_invoice, CASE WHEN bu.id IS NOT NULL THEN bu.open_psd ELSE o.open_psd END AS open_psd, CASE WHEN bu.id IS NOT NULL THEN bu.daily_limit ELSE o.daily_limit END AS daily_limit, CASE WHEN bu.id IS NOT NULL THEN bu.weekly_limit ELSE o.weekly_limit END AS weekly_limit, CASE WHEN bu.id IS NOT NULL THEN bu.monthly_limit ELSE o.monthly_limit END AS monthly_limit, CASE WHEN bu.id IS NOT NULL THEN bu.update_time ELSE o.last_update_time END AS last_update_time, CASE WHEN bu.id IS NOT NULL THEN bu.is_deleted ELSE o.is_deleted END AS is_deleted, CURRENT_TIMESTAMP() AS etl_time FROM today_base bu FULL OUTER JOIN today_cert ci ON bu.id = ci.user_id LEFT JOIN dim.dim_usr_user_ful_d o ON COALESCE(bu.id, ci.user_id) = o.user_id AND o.dt = '${pdt}' ) SELECT user_id, appid, username, code, sex_cert, birthday_cert, cert_province, cert_city, is_cert, id_card, face_verify, cancel_verify_num, register_channel, register_addr, register_ip_addr, reg_create_time, login_addr, login_ip_addr, level, member_level, member_name, growth_num, current_month_growth, member_keep_growth, member_init_flag, point, consume_amount_cny, order_total_num, status, blacklist, refuse_pick_up, notify_flag, open_invoice, open_psd, daily_limit, weekly_limit, monthly_limit, last_update_time, is_deleted, etl_time FROM today_rebuilt UNION ALL SELECT user_id, appid, username, code, sex_cert, birthday_cert, cert_province, cert_city, is_cert, id_card, face_verify, cancel_verify_num, register_channel, register_addr, register_ip_addr, reg_create_time, login_addr, login_ip_addr, level, member_level, member_name, growth_num, current_month_growth, member_keep_growth, member_init_flag, point, consume_amount_cny, order_total_num, status, blacklist, refuse_pick_up, notify_flag, open_invoice, open_psd, daily_limit, weekly_limit, monthly_limit, last_update_time, is_deleted, etl_time FROM dim.dim_usr_user_ful_d WHERE dt = '${pdt}' AND user_id NOT IN (SELECT user_id FROM today_changed);