缓慢渐变维与拉链表

一、缓慢渐变维

Slowly Changing Dimensions简称SCD

缓慢变化维的提出是因为在现实世界中，维度的属性并不是静态的，它会随着时间的流逝发生缓慢的变化。这种随时间发生变化的维度我们一般称之为缓慢变化维

比如学历，住址

学历维度

本科，硕士，博士

select 学历，count(*) from tb_user group by 学历

区域维度

在数仓中如何处理变化的维度数据?

对于发生变化的数据在数仓中有三种方式处理

• 方式1

  • 直接覆盖原有数据

• 方式2

  • 增加一行新数据

• 方式2

  • 增加一列新数据

在实际的数仓开发中更多采用方式2

二、拉链表

拉链表是维护历史状态，以及最新状态数据的一种表。记录每条信息的生命周期，一旦一条记录的生命周期结束，就重新开始一条新的记录，并把当前日期放入生效开始日期。

在表中增加两个字段

start_time

end_time

可使用使用拉链表实现对变更数据的记录

拉链表的实现

在表中增加两个时间字段

start_time 生效开始时间 使用数据导入的时间

end_time 生效结束时间 初始的结束时间都设置为 9999-99-99

通过时间判断该条数据是最新数据还是历史数据

create table tb_mysql(
    id int,
    name varchar(20) comment '姓名',
    address varchar(20) comment '地址',
    create_time datetime not null default current_timestamp comment '创建时间',
    update_time datetime not null default current_timestamp on update current_timestamp comment '更新时间'
);

insert into tb_mysql values (1,'李四','北京',timestampadd(day,-12,current_timestamp),update_time),
                            (2,'王五','上海',timestampadd(day,-8,current_timestamp),update_time),
                            (3,'赵六','广州',timestampadd(day,-1,current_timestamp),update_time),
                            (4,'孙悟空','深圳',timestampadd(day,-3,current_timestamp),update_time),
                            (5,'猪八戒','天津',timestampadd(day,-5,current_timestamp),update_time),
                            (6,'沙和尚','重庆',timestampadd(day,-6,current_timestamp),update_time);

create table tb_dw(
    id int,
    name varchar(20) comment '姓名',
    address varchar(20) comment '地址',
    create_time datetime comment '创建时间',
    update_time varchar(20)  comment '更新时间',
    start_time varchar(20) comment '起始时间',
    end_time varchar(20) comment '结束时间'
);

-- 第一全量导入
insert into tb_dw select *,current_date,'9999-99-99' from tb_mysql;

-- mysql原始表中新增数据和更新数据
insert into tb_mysql values (7,'唐僧','西安',current_timestamp,update_time);
update tb_mysql set address='南京' where name='李四';

-- 第二天对昨天的数据进行数数据导入
-- 查询新增的数据
-- 修改原来的表数据
-- 增加新的数据
with tb_add_new as(
    -- 查询新增的数据
    select *,'2024-5-12' as start_time,'9999-99-99' as end_time from tb_mysql where date(create_time)=current_date or date(update_time) =current_date
    ),
    tb_left_join as(
        -- 筛序哪个更新
        select
                tb_dw.id,
                tb_dw.name,
               tb_dw.address,
               tb_dw.create_time,
               tb_dw.update_time,
               tb_dw.start_time,
               if(tb_add_new.id is null,tb_dw.end_time,'2024-5-12')  as end_time
            from tb_dw left join tb_add_new on tb_dw.id = tb_add_new.id
    )
    -- 增加新的数据
select * from tb_left_join union all select * from tb_add_new;