带删除的历史拉链算法

drop table if exists test;
create temp table test
(
 id varchar(100)
 ,name varchar(100)
 ,state varchar(100)
 ,start_date varchar(100)
 ,end_date varchar(100)
)
distributed by (id);
comment on table test is '员工状态表';
comment on column test.id is '员工的id';
comment on column test.name is '员工的name';
comment on column test.state is '员工的状态';
comment on column test.start_date is '开始时间';
comment on column test.end_date is '结束时间';


insert into test values('1','A','1','20150101','99991231');
insert into test values('2','B','1','20150101','99991231');
insert into test values('3','C','1','20150101','99991231');
insert into test values('4','D','1','20150101','99991231');


drop table if exists org_test;
create temp table org_test
(
 id varchar(100)
 ,name varchar(100)
 ,state varchar(100)
)
distributed by (id);
comment on table org_test is '员工状态表';
comment on column org_test.id is '员工的id';
comment on column org_test.name is '员工的name';
comment on column org_test.state is '员工的状态';
insert into org_test values('1','A','1');
insert into org_test values('2','B','0');
insert into org_test values('4','D','0');
insert into org_test values('5','E','1');
select * from org_test;
--创建三张临时表
drop table if exists test1;
drop table if exists test2;
drop table if exists test3;
create  temp table test1--存放源数据
(
  id varchar(100)
 ,name varchar(100)
 ,state varchar(100)
 ,start_date varchar(100)
 ,end_date varchar(100)
)
distributed by (id);
create  temp table test2--存放更新和新增的数据
(
  id varchar(100)
 ,name varchar(100)
 ,state varchar(100)
 ,start_date varchar(100)
 ,end_date varchar(100)
)
distributed by (id);
create  temp table test3--存放没有变更的数据
(
  id varchar(100)
 ,name varchar(100)
 ,state varchar(100)
 ,start_date varchar(100)
 ,end_date varchar(100)
)
distributed by (id);




--第二部：将源数据加载到临时表1中
insert into test1
select id,name,state,'20150501','99991231'
from org_test;
--第三部
--删除目标表中的大于等于今天日期的所有数据
delete from test where start_date >='20150501';
--将修改的数据全部欢颜
update test set end_date ='99991231' where end_date ='20150501';


--第四步：将新增和修改的数据添加到临时表2中
insert into test2
select t1.id,t1.name,t1.state,t1.start_date,t1.end_date--,t2.*
from test1     t1
left join test t2
on t1.id=t2.id
and t1.name=t2.name
and t2.state=t1.state
where t2.id is null
and t2.end_date is null;
select * from test order by 1;
select * from test3 order by 1;
--第四步：将没有改变的数据添加到
insert into test3
select t.id,t.name,t.state,t.start_date--,t2.* 
,case when t1.id is not null and t1.end_date is not null and t.end_date ='99991231'
then '20150501'
when t2.id is  null and t2.end_date is  null and t.end_date ='99991231'
then '20150501'
else t.end_date end end_date
 from test t
 left join test2 t1
 on t1.id=t.id
 left join test1 t2
 on t.id=t2.id
 and t.name=t2.name
 and t.state=t2.state;
 --第五步：清空目标表
 truncate test;
 --第六步：将更新的数据添加到目标表中
 insert into test
 select * from test2;
 --第七部：将没有变更的数据添加到目标表中
  insert into test
 select * from test3;
 select * from test order by 1;
 --第八步:提交事务
 commit;
--第十步：分析处理目标表
VACUUM ANALYZE org_whh;
--|ˈvækjuəm|真空; 空白; 空虚; 清洁;