维度表更新方法- 增量更新总结:
/******批量导入,从source表到stage target表,赋相应标识位:目的是找出新增加的和经过修改的记录******/
begin
merge into emp_t t
using (select * from emp_s) s
on ( t.empid=s.empid
and t.modify_time=s.modify_time)
when matched then
update set t.action_flag='0'
when not matched then
insert (empid,empnm,deptnm,create_time,modify_time,action_flag)
values (s.empid, s.empnm, s.deptnm, s.create_time, s.modify_time,'4' )
;
commit;
end;
/******找出距离最近一次ETL时间以来,由源系统新增加的记录,赋标志位为2,维度表直接Insert*********/
begin
update emp_t set action_flag='2' where create_time>(select nvl(max(create_time),to_date('01/01/2000','DD/MM/YYYY')) as createmaxtime from emp_t where action_flag='0' );
commit;
end;
/****************找出数据仓库已经Load到的数据,但是在源系统经过修改的记录,改标志位为1,维度表需要更新或者生成新版本;
改原有记录的标志位为0,出错改标志位为5,0和5的记录不经过ETL到数据仓库*********/
begin
merge into emp_t t
using ( select empid,action_flag,empnm,deptnm,create_time,modify_time,rnk from(
select empid,action_flag,empnm,deptnm,create_time,modify_time,rank() over(partition by empid order by modify_time desc) as rnk
from emp_t where empid in ( select empid from emp_t where action_flag='4' )) s_tmp where rnk='1') s
on (t.empid=s.empid
and t.modify_time=s.modify_time)
when matched then
update set t.action_flag='1'
when not matched then
insert (empid,empnm,deptnm,create_time,modify_time,action_flag)
values (s.empid, s.empnm, s.deptnm, s.create_time, s.modify_time,'5' );
commit;
merge into emp_t t
using ( select empid,action_flag,empnm,deptnm,create_time,modify_time,rnk from(
select empid,action_flag,empnm,deptnm,create_time,modify_time,rank() over(partition by empid order by modify_time desc) as rnk
from emp_t where empid in ( select empid from emp_t where action_flag='4' )) s_tmp where rnk='2') s
on (t.empid=s.empid
and t.modify_time=s.modify_time)
when matched then
update set t.action_flag='0'
when not matched then
insert (empid,empnm,deptnm,create_time,modify_time,action_flag)
values (s.empid, s.empnm, s.deptnm, s.create_time, s.modify_time,'5' );
commit;
end;
/******批量导入,从source表到stage target表,赋相应标识位:目的是找出新增加的和经过修改的记录******/
begin
merge into emp_t t
using (select * from emp_s) s
on ( t.empid=s.empid
and t.modify_time=s.modify_time)
when matched then
update set t.action_flag='0'
when not matched then
insert (empid,empnm,deptnm,create_time,modify_time,action_flag)
values (s.empid, s.empnm, s.deptnm, s.create_time, s.modify_time,'4' )
;
commit;
end;
/******找出距离最近一次ETL时间以来,由源系统新增加的记录,赋标志位为2,维度表直接Insert*********/
begin
update emp_t set action_flag='2' where create_time>(select nvl(max(create_time),to_date('01/01/2000','DD/MM/YYYY')) as createmaxtime from emp_t where action_flag='0' );
commit;
end;
/****************找出数据仓库已经Load到的数据,但是在源系统经过修改的记录,改标志位为1,维度表需要更新或者生成新版本;
改原有记录的标志位为0,出错改标志位为5,0和5的记录不经过ETL到数据仓库*********/
begin
merge into emp_t t
using ( select empid,action_flag,empnm,deptnm,create_time,modify_time,rnk from(
select empid,action_flag,empnm,deptnm,create_time,modify_time,rank() over(partition by empid order by modify_time desc) as rnk
from emp_t where empid in ( select empid from emp_t where action_flag='4' )) s_tmp where rnk='1') s
on (t.empid=s.empid
and t.modify_time=s.modify_time)
when matched then
update set t.action_flag='1'
when not matched then
insert (empid,empnm,deptnm,create_time,modify_time,action_flag)
values (s.empid, s.empnm, s.deptnm, s.create_time, s.modify_time,'5' );
commit;
merge into emp_t t
using ( select empid,action_flag,empnm,deptnm,create_time,modify_time,rnk from(
select empid,action_flag,empnm,deptnm,create_time,modify_time,rank() over(partition by empid order by modify_time desc) as rnk
from emp_t where empid in ( select empid from emp_t where action_flag='4' )) s_tmp where rnk='2') s
on (t.empid=s.empid
and t.modify_time=s.modify_time)
when matched then
update set t.action_flag='0'
when not matched then
insert (empid,empnm,deptnm,create_time,modify_time,action_flag)
values (s.empid, s.empnm, s.deptnm, s.create_time, s.modify_time,'5' );
commit;
end;