实现历史数据的插入、更新和版本管理-拉链算法

前言

  如果您觉得有用的话，记得给博主点个赞，评论，收藏一键三连啊，写作不易啊^ _ ^。
  而且听说点赞的人每天的运气都不会太差，实在白嫖的话，那欢迎常来啊!!!

实现历史数据的插入、更新和版本管理-拉链算法

特别适用于处理数据中的慢变化维度（如客户信息变化）。具体来说，代码通过“开链”和“闭链”操作来处理和管理历史数据。

下面是 实现了一个简化版的 拉链算法逻辑
主要是围绕 拉链算法（SCD） 来管理历史记录数据。你通过临时表 new 和 inc，以及对 tag 表的更新来实现历史数据的插入、更新和版本管理。

1. 初始数据表 cust

创建了一个包含客户信息的表 cust，并插入了多条数据，表示不同日期（day_dt）对同一客户信息的多次更新
。

create table cust(id int,name varchar(10),num varchar(11),day_dt date);
insert into cust values(100,'张三','13333333333','20170102');
insert into cust values(200,'李四','12222222222','20170102');
insert into cust values(300,'王五','17777773333','20170102');
insert into cust values(100,'张三','13333333333','20170103');
insert into cust values(200,'李四','12222222222','20170103');
insert into cust values(400,'孙六','19999999999','20170103');
insert into cust values(100,'张三','13333333333','20170104');
insert into cust values(200,'李四','12222222222','20170104');
insert into cust values(400,'孙六','13385588555','20170104');
insert into cust values(500,'赵七','77777777777','20170104');

2. 创建 tag 表(结果集)

创建了一个名为 tag 的表，用来存储历史数据，其中包含了 start_dt（开始日期）和 end_dt（结束日期）字段，用来标记记录的有效期。

create table tag(id int,name varchar(10),num varchar(11),start_dt date,end_dt date);

3. 流程解释

3.1. 创建 new 表

创建一个 new 表，并将 cust 表中 day_dt 为 ‘20170102’ 的数据插入 new 表，并将这些数据的有效结束日期设为 29991231（即未来的一个遥远时间，表示数据是“当前有效”的）。

create table new as(select * from tag where 1=0);
insert into new
select id, name, num, '20170102', '29991231'
from cust
where day_dt = '20170102';

3.2. 创建 inc 表

创建 inc 表，用来存放需要更新的记录（即变化的记录）。首先，inc 表插入的是 new 表中没有在 tag 表中已经存在的记录。

create table inc as(select * from tag where 1=0);

这一步会找出 new 表中在 tag 表中已经存在并且仍标记为有效（end_dt = ‘29991231’）的记录，从而确定需要插入 inc 表的“新增记录”。
接着，inc 表还插入了 tag 表中那些已经是“有效记录”但不在 new 表中的记录，即记录变化的部分：

insert into inc
select n.id, n.name, n.num, n.start_dt, n.end_dt
from new n
left join tag t on n.id = t.id and n.name = t.name and n.num = t.num and t.end_dt = '29991231'
where t.id is null;

-- 将已经有效的记录（end_dt = '29991231'）更新为结束日期
insert into inc
select id, name, num, start_dt, '11111111'
from tag
where end_dt = '29991231'
and (id, name, num) not in (select id, name, num from new);

3.3. 闭链操作

闭链操作是更新 tag 表中的数据，标记这些记录的有效期结束，将 end_dt 更新为 20170102（表示该记录结束）。

update tag
set end_dt = '20170102'
where end_dt = '29991231'
and (id, name, num) in (select id, name, num from inc);

这一步通过更新 tag 表中历史记录的 end_dt，标记这些记录已经不再有效。

3.4. 开链操作

开链操作是将 inc 表中的新增记录插入到 tag 表中，表示这些记录的新版本。只有 end_dt 不为 11111111 的记录才会被插入。

insert into tag
select * from inc
where end_dt <> '11111111';

这一步是将新记录插入 tag 表，代表这些记录在 20170102 后开始生效。

3.5. 清理临时表

最后，删除临时表 new 和 inc，清理工作。

drop table new;
drop table inc;

4. 总结

这段代码实现了一个简单的 拉链算法（慢变化维度，SCD）。主要通过以下步骤完成拉链操作：

1. 开链：创建新的历史记录（new 表和 inc 表）。
2. 闭链：更新现有记录的结束日期，表示该记录不再有效。
3. 插入新数据：将新的记录插入到主表 tag 中，表示新记录开始生效。

这个过程的核心思想是将每一条数据的变化记录成一个版本，并通过有效日期（start_dt 和 end_dt）来管理不同版本的数据。这种方式能够在数据中“保留”历史数据，并根据时间查询某个版本的数据，正是慢变化维度（SCD）的一种常见实现。

5. 示例demo

5.1. 准备数据

create table cust(id int,name varchar(10),num varchar(11),day_dt date);

insert into  cust values(100,'张三','13333333333','20170102');
insert into  cust values(200,'李四','12222222222','20170102');
insert into  cust values(300,'王五','17777773333','20170102');
insert into  cust values(100,'张三','13333333333','20170103');
insert into  cust values(200,'李四','12222222222','20170103');
insert into  cust values(400,'孙六','19999999999','20170103');
insert into  cust values(100,'张三','13333333333','20170104');
insert into  cust values(200,'李四','12222222222','20170104');
insert into  cust values(400,'孙六','13385588555','20170104');
insert into  cust values(500,'赵七','77777777777','20170104');

5.2. 拉链算法

-- 创建结果集
create table tag (
    id int,
    name varchar(10),
    num varchar(11),
    start_dt date,
    end_dt date
);


-- 创建临时 new 表
create table new as(select * from tag where 1=0);
insert into new
select id, name, num, '20170102', '29991231'
from cust
where day_dt = '20170102';


-- 创建临时 inc 表
create table inc as(select * from tag where 1=0);


-- 使用 LEFT JOIN 替代 NOT IN，避免效率瓶颈
insert into inc
select n.id, n.name, n.num, n.start_dt, n.end_dt
from new n
left join tag t on n.id = t.id and n.name = t.name and n.num = t.num and t.end_dt = '29991231'
where t.id is null;


-- 将已经有效的记录（end_dt = '29991231'）更新为结束日期
insert into inc
select id, name, num, start_dt, '11111111'
from tag
where end_dt = '29991231'
and (id, name, num) not in (select id, name, num from new);


-- 闭链操作，更新现有记录的 end_dt
update tag
set end_dt = '20170102'
where end_dt = '29991231'
and (id, name, num) in (select id, name, num from inc);


-- 开链操作，插入新记录
insert into tag
select * from inc
where end_dt <> '11111111';


-- 删除临时表 new 和 inc
drop table new;
drop table inc;


-- 第二天的数据处理：20170103
create table new as(select * from tag where 1=0);
insert into new
select id, name, num, '20170103', '29991231'
from cust
where day_dt = '20170103';


-- 创建临时 inc 表
create table inc as(select * from tag where 1=0);


-- 使用 LEFT JOIN 替代 NOT IN，避免效率瓶颈
insert into inc
select n.id, n.name, n.num, n.start_dt, n.end_dt
from new n
left join tag t on n.id = t.id and n.name = t.name and n.num = t.num and t.end_dt = '29991231'
where t.id is null;


-- 将已经有效的记录（end_dt = '29991231'）更新为结束日期
insert into inc
select id, name, num, start_dt, '11111111'
from tag
where end_dt = '29991231'
and (id, name, num) not in (select id, name, num from new);


-- 闭链操作，更新现有记录的 end_dt
update tag
set end_dt = '20170103'
where end_dt = '29991231'
and (id, name, num) in (select id, name, num from inc);


-- 开链操作，插入新记录
insert into tag
select * from inc
where end_dt <> '11111111';


-- 删除临时表 new 和 inc
drop table new;
drop table inc;


-- 第三天的数据处理：20170104
create table new as(select * from tag where 1=0);
insert into new
select id, name, num, '20170104', '29991231'
from cust
where day_dt = '20170104';


-- 创建临时 inc 表
create table inc as(select * from tag where 1=0);


-- 使用 LEFT JOIN 替代 NOT IN，避免效率瓶颈
insert into inc
select n.id, n.name, n.num, n.start_dt, n.end_dt
from new n
left join tag t on n.id = t.id and n.name = t.name and n.num = t.num and t.end_dt = '29991231'
where t.id is null;


-- 将已经有效的记录（end_dt = '29991231'）更新为结束日期
insert into inc
select id, name, num, start_dt, '11111111'
from tag
where end_dt = '29991231'
and (id, name, num) not in (select id, name, num from new);


-- 闭链操作，更新现有记录的 end_dt
update tag
set end_dt = '20170104'
where end_dt = '29991231'
and (id, name, num) in (select id, name, num from inc);


-- 开链操作，插入新记录
insert into tag
select * from inc
where end_dt <> '11111111';


-- 删除临时表 new 和 inc
drop table new;
drop table inc;

5.3. 执行效果

查看原始数据：

查看结果集:

解释:
这段代码通过拉链算法的操作，达到了 数据历史版本管理 的效果，即在主表 tag 中存储了客户信息的多个版本，记录了每个客户信息在不同时间点的有效期。通过有效期字段（start_dt 和 end_dt），你能够追溯每条记录在不同时段的状态。
每条记录都有一个明确的有效期：start_dt 表示该记录开始生效的时间，end_dt 表示该记录结束生效的时间，默认为 ‘29991231’，表示该记录目前仍有效
举例:
张三 在 20170102 时的记录会被标记为有效，end_dt = ‘29991231’（表示未来有效）。
如果 张三 在 20170103 时发生了变化，那么 20170102 这条记录会被标记为无效，end_dt = ‘20170103’，而新的记录会插入 tag 表，start_dt = ‘20170103’,
比如孙六就符合这个逻辑。

5.4. 数据一致性与历史查询

你可以基于 start_dt 和 end_dt 查询任意时间点的数据。例如，查询某个客户在 20170103 时的信息，可以通过 start_dt <= ‘20170103’ 且 end_dt > ‘20170103’ 来获取有效记录。
这种方式保证了数据的一致性，并且你可以根据时间查询历史版本数据（例如查询客户在某一日期的“历史版本”）。