使用拉链表记录用户表信息的变化

野性酷女 2023-01-03 14:27 185阅读 0赞

# 使用拉链表记录用户表信息的变化 #

## 一、拉链表 ##

### 1.1 什么是拉链表 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjc5NjQwMw_size_16_color_FFFFFF_t_70]

### 1.2 为什么要做拉链表 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjc5NjQwMw_size_16_color_FFFFFF_t_70 1]

### 1.3 拉链表制作过程 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjc5NjQwMw_size_16_color_FFFFFF_t_70 2]

### 1.4 拉链表形成过程 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjc5NjQwMw_size_16_color_FFFFFF_t_70 3]

## 二、代码案例 ##

INSERT overwrite table dwd_dim_user_info_his 
    --数据分两批导入到dwd_dim_user_his
    --第一批： old + 更新需要变化的old的数据
    SELECT 
    	--左表和右边关联后，右表中为NULL的是不需要更新的数据，否则且左表的end_date为9999-99-99，需要将次字段更新
    	old.id,
    	old.name ,
    	old.birthday ,
    	old.gender ,
    	old.email ,
    	old.user_level ,
    	old.create_time ,
    	old.operate_time ,
    	old.start_date ,
    	if(new.id is null,old.end_date , if(old.end_date ='9999-99-99',date_sub(dt,1),old.end_date ) )
    from dwd_dim_user_info_his old
    left join
    (
    SELECT 
    	*
    from ods_user_info 
    where dt='$do_date'
    ) new
    on old.id = new.id
    UNION  all
    --第二批： ods层同步的新的数据
    SELECT 
    	*,'9999-99-99' end_date
    from ods_user_info 
    where dt='$do_date';

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjc5NjQwMw_size_16_color_FFFFFF_t_70]: /images/20221119/921173dfc67b48778932037ce597d972.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjc5NjQwMw_size_16_color_FFFFFF_t_70 1]: /images/20221119/2a239148fc1341c1be865775da61592f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjc5NjQwMw_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20210108202557589.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjc5NjQwMw==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjc5NjQwMw_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20210108202646193.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjc5NjQwMw==,size_16,color_FFFFFF,t_70