poyee-data-warehouse/manual/backfill/20260509_dim_usr_user_ful_d_init.sql

-- 作者:tianyu.chu
-- 日期:2026-05-09
-- 工单:(无)
-- 目的:dim_usr_user_ful_d 初始化(kb/28 §1.3):扫 ods 历史分区 (dt < ${dt}) + ROW_NUMBER 取每 pk 最新版本,
--      base_user LEFT JOIN cert_info 落 dim dt=${pdt} 单分区
-- 状态:[待执行]
-- 备注:首次上线 init 与 sche 同一天跑(sched=T),${dt}=T-1,${pdt}=T-2;
--      init 灌 dim.dt=${pdt} 作为 sche 首次依赖的"昨日 dim";扫 ods 范围 dt < ${dt} (即 dt <= ${pdt});
--      cert_info update_time 几乎全空,ORDER BY 用 COALESCE(update_time, create_time) 兜底;
--      跑一次后由 jobs/dim/usr/dim_usr_user_ful_d.sql 接管日常增量

INSERT OVERWRITE TABLE dim.dim_usr_user_ful_d PARTITION (dt='${pdt}')
SELECT
    bu.id                              AS user_id,
    bu.appid                           AS appid,
    bu.username                        AS username,
    bu.code                            AS code,
    ci.cert_sex                        AS sex_cert,
    ci.cert_birthday                   AS birthday_cert,
    ci.cert_province                   AS cert_province,
    ci.cert_city                       AS cert_city,
    CASE WHEN ci.user_id IS NOT NULL THEN TRUE ELSE FALSE END AS is_cert,
    bu.id_card                         AS id_card,
    bu.face_verify                     AS face_verify,
    bu.cancel_verify_num               AS cancel_verify_num,
    bu.register_channel                AS register_channel,
    bu.register_addr                   AS register_addr,
    bu.register_ip_addr                AS register_ip_addr,
    bu.create_time                     AS reg_create_time,
    bu.login_addr                      AS login_addr,
    bu.login_ip_addr                   AS login_ip_addr,
    bu.level                           AS level,
    bu.member_level                    AS member_level,
    bu.member_name                     AS member_name,
    bu.growth_num                      AS growth_num,
    bu.current_month_growth            AS current_month_growth,
    bu.member_keep_growth              AS member_keep_growth,
    bu.member_init_flag                AS member_init_flag,
    bu.point                           AS point,
    bu.consume_amount                  AS consume_amount_cny,
    bu.order_total_num                 AS order_total_num,
    bu.status                          AS status,
    bu.blacklist                       AS blacklist,
    bu.refuse_pick_up                  AS refuse_pick_up,
    bu.notify_flag                     AS notify_flag,
    bu.open_invoice                    AS open_invoice,
    bu.open_psd                        AS open_psd,
    bu.daily_limit                     AS daily_limit,
    bu.weekly_limit                    AS weekly_limit,
    bu.monthly_limit                   AS monthly_limit,
    bu.update_time                     AS last_update_time,
    bu.is_deleted                      AS is_deleted,
    CURRENT_TIMESTAMP()                AS etl_time
FROM (
    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
) bu
LEFT JOIN (
    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
) ci ON bu.id = ci.user_id;