CREATE TABLE dim.dim_sku_full
(
    `id`                   VARCHAR(255) COMMENT 'SKU ID，商品唯一標(biāo)識(shí)',
    `k1`                   DATE NOT NULL COMMENT '分區(qū)字段，數(shù)據(jù)日期',
    `price`                DECIMAL(16, 2) COMMENT '商品價(jià)格，單位元',
    `sku_name`             STRING COMMENT '商品名稱，展示用',
    -- 其他商品屬性字段
    `category3_id`         STRING COMMENT '三級(jí)分類ID，最細(xì)粒度的商品分類',
    `category3_name`       STRING COMMENT '三級(jí)分類名稱，如"休閑男鞋"',
    `category2_id`         STRING COMMENT '二級(jí)分類ID，中間層級(jí)商品分類',
    `category2_name`       STRING COMMENT '二級(jí)分類名稱，如"男鞋"',
    `category1_id`         STRING COMMENT '一級(jí)分類ID，頂層商品分類',
    `category1_name`       STRING COMMENT '一級(jí)分類名稱，如"鞋靴"',
    `tm_id`                STRING COMMENT '品牌ID，品牌唯一標(biāo)識(shí)',
    `tm_name`              STRING COMMENT '品牌名稱，如"Nike"、"Adidas"',
    -- 擴(kuò)展屬性字段
    `attr_ids`             ARRAY<int(11)> COMMENT '平臺(tái)屬性ID集合',
    `sale_attr_ids`        ARRAY<int(11)> COMMENT '銷售屬性ID集合',
    `create_time`          STRING COMMENT '創(chuàng)建時(shí)間，商品首次錄入時(shí)間'
)
    ENGINE=OLAP
UNIQUE KEY(`id`,`k1`) -- 使用商品ID和日期作為聯(lián)合主鍵
PARTITION BY RANGE(`k1`) () -- 按日期范圍分區(qū)
DISTRIBUTED BY HASH(`id`) -- 按商品ID哈希分布

CREATE TABLE dim.dim_user_zip
(
    `id`           VARCHAR(64) COMMENT '用戶ID，用戶唯一標(biāo)識(shí)',
    `k1`           DATE NOT NULL COMMENT '分區(qū)字段，數(shù)據(jù)日期',
    `login_name`   STRING COMMENT '用戶登錄名，賬號(hào)名稱',
    `nick_name`    STRING COMMENT '用戶昵稱，用戶自定義展示名',
    `name`         STRING COMMENT '用戶真實(shí)姓名，已加密',
    -- 其他用戶屬性字段
    `start_date`   STRING COMMENT '開始日期，當(dāng)前版本生效開始日期',
    `end_date`     STRING COMMENT '結(jié)束日期，當(dāng)前版本失效日期，9999-12-31表示當(dāng)前有效版本'
)

-- 用戶維度拉鏈表初始化插入（設(shè)置歷史起點(diǎn)）
insert into dim.dim_user_zip(id, k1, login_name, ... , start_date, end_date)
select
    id,                       -- 用戶ID
    k1,                       -- 分區(qū)日期
    login_name,               -- 用戶登錄名
    -- 其他字段
    md5(name),                -- 用戶真實(shí)姓名（MD5加密處理）
    md5(phone_num),           -- 手機(jī)號(hào)碼（MD5加密處理）
    md5(email),               -- 電子郵箱（MD5加密處理）
    -- 其他字段
    '2020-06-14' start_date,  -- 拉鏈起始日期（業(yè)務(wù)起始日期）
    '9999-12-31' end_date     -- 拉鏈結(jié)束日期（當(dāng)前有效版本標(biāo)記）
from ods.ods_user_info_full;

insert into dim.dim_user_zip(id, k1, login_name, ... , start_date, end_date)
with
    tmp as
    (
        select
            -- 當(dāng)前拉鏈表最新有效數(shù)據(jù)
            old.id old_id,                    -- 原用戶ID
            -- 其他old字段
            old.end_date old_end_date,        -- 原版本結(jié)束日期


            -- 新增數(shù)據(jù)(當(dāng)日變化)
            new.id new_id,                    -- 新用戶ID
            -- 其他new字段
            new.end_date new_end_date         -- 新版本結(jié)束日期
        from
            -- 查詢當(dāng)前拉鏈表中最新有效記錄
            (
                select * from dim.dim_user_zip
                where end_date = '9999-12-31'  -- 篩選當(dāng)前有效版本
            ) old
        -- 使用FULL OUTER JOIN合并新舊數(shù)據(jù)
        full outer join
            -- 當(dāng)日增量數(shù)據(jù)處理
            (
                select
                    cast(t1.id as VARCHAR(64)) as id,  -- 轉(zhuǎn)換用戶ID類型
                    -- 其他字段處理
                    md5(t1.name) as name,            -- 敏感信息加密
                    -- 其他字段
                    '2024-06-15' as start_date,       -- 新版本開始日期
                    '9999-12-31' as end_date          -- 新版本結(jié)束日期
                from
                    (-- 取每個(gè)用戶最新記錄
                        select id, k1, ... , row_number() over (partition by id order by create_time desc) rn
                        from ods.ods_user_info_full
                    ) t1
                where rn=1  -- 只取每個(gè)用戶的最新記錄
            ) new
        on old.id=new.id  -- 按用戶ID關(guān)聯(lián)
    )


-- 查詢1：處理有變更的用戶數(shù)據(jù)，生成新版本記錄
select
    if(new_id is not null, new_id, old_id),         -- 用戶ID
    -- 其他字段選擇邏輯
    if(new_id is not null, new_start_date, old_start_date), -- 版本開始日期
    if(new_id is not null, new_end_date, old_end_date)      -- 版本結(jié)束日期
from tmp
where k2 is not NULL  -- 只處理有新分區(qū)數(shù)據(jù)的記錄


union all


-- 查詢2：處理原記錄的歷史版本化，更新結(jié)束日期
select
    old_id,                       -- 用戶ID
    -- 其他原字段
    old_start_date,               -- 版本開始日期
    cast(date_add(date('${pdate}'), -1) as string) old_end_date  -- 更新版本結(jié)束日期為當(dāng)前日期前一天
from tmp
where k1 is not NULL    -- 有原分區(qū)數(shù)據(jù)
  and old_id is not null  -- 有原用戶ID
  and new_id is not null; -- 同時(shí)有新用戶ID，說明是變更記錄