etl算法详解_数据拉链处理什么意思

大家好，又见面了，我是你们的朋友全栈君。如果您正在找激活码,请点击查看最新教程,关注关注公众号 “全栈程序员社区” 获取激活教程,可能之前旧版本教程已经失效.最新Idea2022.1教程亲测有效,一键激活。

Jetbrains全系列IDE稳定放心使用

所谓拉链，就是记录历史。记录一个事物从开始，一直到当前状态的所有变化的信息。

    在历史表中对客户的一生的记录可能就这样几条记录，避免了按每一天记录客户状态造成的海量存储的问题：
（NAME）人名（START-DATE）开始日期（END-DT）结束日期（STAT）状态
     client              19000101                        19070901                H在家
     client              19070901                         19130901                A小学
     client              19130901                         19160901                B初中
     client              19160901                         19190901                C高中
     client              19190901                         19230901                D大学
     client              19230901                         19601231                E公司
     client              19601231                         29991231             H退休在家

上面的每一条记录都是不算末尾的，比如到19070901，client已经在A，而不是H了。所以除最后一条记录因为状态到目前都未改变的，其余的记录实际上在END-DT那天，都不在是该条记录END-DT那天的状态。这种现象可以理解为算头不算尾。

算法：（拉链表算法其实就是以前遇到过的缓慢变化维的其中一种情况，用存储过程实现的话稍微麻烦点。）

1采集当日全量数据到ND(NewDay)表；

2可从历史表中取出昨日全量数据存储到OD(OldDay)表;

3(ND-OD)就是当日新增和变化的数据，也就是当天的增量，用W_I表示；

4(OD-ND)为状态到此结束需要封链的数据，用W_U表示；

5将W_I表的内容全部插入到历史表中，这些是新增记录，start_date为当天，而end_date为max值；

6对历史表进行W_U部份的更新操作，start_date保持不变，而end_date改为当天，也就是关链操作；

下面为具体例子：

[sql]
view plain
copy

OD(在第一天就等于HIS)
用户标志状态开始时间结束时间
1 1 200712 299901
2 2 200712 299901
3 3 200712 299901
4 4 200712 299901
5 5 200712 299901
ND
用户标志状态开始时间结束时间
1 2 200801 299901
2 2 200801 299901
3 4 200801 299901
4 4 200801 299901
5 6 200801 299901
W_I=ND-OD ( 将W_I表的内容全部插入到历史表中，这些是新增记录 )
用户标志状态开始时间结束时间
1 2 200801 299901
3 4 200801 299901
5 6 200801 299901
W_U=OD-ND ( 对历史表进行W_U部份的更新操作，start_date保持不变，而end_date改为当天 )
用户标志状态开始时间结束时间
1 1 200712 299901
3 3 200712 299901
5 5 200712 299901
INSERT操作把I插入到HIS
用户标志状态开始时间结束时间
1 1 200712 299901
2 2 200712 299901
3 3 200712 299901
4 4 200712 299901
5 5 200712 299901
1 2 200801 299901 –new
3 4 200801 299901 –new
5 6 200801 299901 –new
</span>
update操作按U更新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

转载 :http://blog.csdn.NET/paopaomm/article/details/7491400

另一个操作SQL的例子

[sql]
view plain
copy

一个实际例子(teradata)
1、定义两个临时表，一个为当日全量数据，另一个为需要新增或更新的数据；
CREATE VOLATILE TABLE VT_xxxx_NEW AS xxxx WITH NO DATA ON COMMIT PRESERVE ROWS;
CREATE VOLATILE SET TABLE VT_xxxx_CHG,NO LOG AS xxxx WITH NO DATA ON COMMIT PRESERVE ROWS;
2、获取当日全量数据
INSERT INTO VT_xxxx_NEW(xx) SELECT (xx,cur_date, max_date) FROM xxxx_sorce; ND
3、抽取新增或有变化的数据，从xxxx_NEW临时表到xxxx_CHG临时表；
INSERT INTO VT_xxxx_CHG(xx)
SELECT xx FROM VT_xxxx_NEW
WHERE (xx) NOT IN (select xx from xxxx_HIS where end_date=‘max_date’);
4、更新历史表的失效记录的end_date为max值
UPDATE A1 FROM xxxx_HIS A1, VT_xxxx_CHG A2
SET End_Date=‘current_date’
WHERE A1.xx=A2.xx AND A1.End_Date=‘max_date’;
5、将新增或者有变化的数据插入目标表*/
INSERT INTO xxxx_HIS SELECT * FROM VT_xxxx_CHG;

自己编写的例子：

[sql]
view plain
copy

/**拉链表: 也就是一个记录历史表,用于记录事物从最开始的状态到当前状态所有变化的信息 */
select * from emp ;
–历史表
DROP TABLE old_tb_his;
drop table new_tb;
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’);
–ROLLBACK;
COMMIT;
select * from old_tb_his;
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;
–创建临时表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表的内容全部插入到历史表中，这些是新增记录 )
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操作把I插入到HIS
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
select * from emp;
–单表更新
update emp set empno = 7777 where ename = upper(‘smith’) ;
–多表更新
merge into t2
using t1 on (t2.id = t1.id and t2.status <> t1.status )
when matched then update set t2.end_date = t1.start_date ;
–or
update t2
set t2.end_date = (select t1.start_date from t1 where t1.id = t2.id)
where exists(
select 1 from t1 where t2.id = t1.id
and t2.status <> t1.status
)