拉链表

/*拉链表: 也就是一个 记录历史 表,用于记录事物从 最开始的状态 到 当前状态 所有变化的信息 */

--历史表
drop table old_tb_his;
drop table new_tb;


--od 我们希望的拉链表记录数据的所有状态，初始化为昨日全量数据
create table old_tb_his(
id  number(10,0),
status  varchar2(20),
start_date varchar2(20),
end_date varchar2(20)
);
insert into old_tb_his values(1,'1', '200712' , '299901');
insert into old_tb_his values(2,'2', '200712' , '299901');
insert into old_tb_his values(3,'3', '200712' , '299901');
insert into old_tb_his values(4,'4', '200712' , '299901');
insert into old_tb_his values(5,'5', '200712' , '299901');
commit;
select * from old_tb_his;


--nd 今日全量数据
create table new_tb as select * from old_tb_his where 2 = 1 ;
insert into new_tb values(1,'2', '200801' , '299901');
insert into new_tb values(2,'2', '200801' , '299901');
insert into new_tb values(3,'4', '200801' , '299901');
insert into new_tb values(4,'4', '200801' , '299901');
insert into new_tb values(5,'6', '200801' , '299901');
commit;
select * from new_tb;


/*merge into old_tb_his
 using new_tb
 on (old_tb_his.id = new_tb.id and old_tb_his.status = new_tb.status )
when matched then  update set old_tb_his.end_date =  new_tb.start_date
when not matched then insert values(new_tb.id, new_tb.status, new_tb.start_date,new_tb.end_date);*/


/*
用不了  这个函数是匹配就更新   不匹配添加
   而拉链算法可以看作是 不匹配的更新 不匹配的也添加
*/
/*merge into old_tb_his
 using new_tb
 on (old_tb_his.id = new_tb.id and old_tb_his.status = new_tb.status )
when not matched then update set old_tb_his.end_date =  new_tb.start_date ;
when not matched then insert values(new_tb.id, new_tb.status, new_tb.start_date,new_tb.end_date);
*/
--如果函数不能完成拉链算法 只能通过存储过程来完成
select * from old_tb_his;
select * from new_tb;


drop table old_tb_his_temp;
drop table new_tb_temp;
--创建临时表old_tb_his_temp
create global temporary  table old_tb_his_temp
(
id  number(10,0),
status  varchar2(20),
start_date varchar2(20),
end_date varchar2(20)
)
on commit delete rows ;

--创建临时表new_tb_temp
create global temporary  table new_tb_temp
(
id  number(10,0),
status  varchar2(20),
start_date varchar2(20),
end_date varchar2(20)
)
on commit delete rows ;


-- w_i = nd - od  (  将w_i表的内容全部插入到历史表中，这些是新增记录 )
--今日全量数据减去拉链表数据 等于新增数据 用w_i表示
--UNION(无重并集)、UNION ALL(有重并集)、INTERSECT(交集)、MINUS (差集)：只显示在第一个集合中存在，在第二个集合中不存在的数据
insert into old_tb_his_temp
  select *
    from new_tb t
   where t.id not in (select id--今日全量数据，减去拉链表数据和今日全量数据的交集，等于今日新增数据
                        from (select t1.id, t1.status, t1.end_date
                                from old_tb_his t1
                              intersect
                              select t2.id, t2.status, t2.end_date
                                from new_tb t2));--查询拉链表数据和今日全量数据的交集

-- w_u = od - nd ( 对历史表进行w_u部份的更新操作，start_date保持不变，而end_date改为当天 )封链
insert into new_tb_temp
select *
  from old_tb_his t
 where t.id not in (select id--拉链表数据，减去拉链表数据和今日全量数据的交集，等于待更新数据
                      from (select t1.id, t1.status, t1.end_date
                              from old_tb_his t1
                            intersect
                            select t2.id, t2.status, t2.end_date
                              from new_tb t2));--查询拉链表数据和今日全量数据的交集
--查询两个临时表
select * from old_tb_his_temp;
select * from new_tb_temp;
commit;

--insert操作把w_i插入到拉链表
insert into old_tb_his
select * from old_tb_his_temp ;
--查询是否成功插入
--select * from old_tb_his ;


--多表更新语句一: update 操作按u更新his
/*merge into old_tb_his
using  old_tb_his_temp on (old_tb_his.id = old_tb_his_temp.id and old_tb_his.status <> old_tb_his_temp.status )
 when matched then update set old_tb_his.end_date = old_tb_his_temp.start_date ;*/

--多表更新语句二: or: update 操作按u更新his
update old_tb_his
set old_tb_his.end_date = (select old_tb_his_temp.start_date from old_tb_his_temp  where old_tb_his_temp.id = old_tb_his.id)
where exists(
select 1 from old_tb_his_temp where old_tb_his.id = old_tb_his_temp.id
                                and old_tb_his.status <> old_tb_his_temp.status
);

commit;


select * from old_tb_his

用户标志       状态 开始时间 结束时间
1               1   200712  200801 --change
2               2   200712  299901
3               3   200712  200801 --change
4               4   200712  299901
5               5   200712  200801 --change
1               2   200801  299901
3               4   200801  299901
5               6   200801  299901