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 全量历史分区 + ROW_NUMBER 取每 pk 最新版本，
--       base_user LEFT JOIN cert_info 落 dim dt=${dt} 单分区
-- 状态：[待执行]
-- 备注：${dt} 由 DS 填最新可用 ods dt（首日分区）；cert_info update_time 几乎全空，
--       ORDER BY 用 COALESCE(update_time, create_time) 兜底；
--       跑一次后由 jobs/dim/usr/dim_usr_user_ful_d.sql 接管日常增量

-- 动态分区不需要（init 单分区写入），但首次会扫 ods 多 dt：
-- ods.base_user 30 万行 × N dt + ods.cert_info 15 万行 × N dt，ROW_NUMBER 取最新即可

INSERT OVERWRITE TABLE dim.dim_usr_user_ful_d PARTITION (dt='${dt}')
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;