【ETL】拉链表算法，

【ETL】拉链表算法，

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

    在历史表中对客户的一生的记录可能就这样几条记录，避免了按每一天记录客户状态造成的海量存储的问题：
（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

1. OD(在第一天就等于HIS)  
2. 用户标志      状态 开始时间   结束时间  
3. 1             1    200712   299901  
4. 2             2    200712   299901  
5. 3             3    200712   299901  
6. 4             4    200712   299901  
7. 5             5    200712   299901  
8.   
9. ND  
10. 用户标志     状态  开始时间     结束时间  
11. 1             2    200801      299901  
12. 2             2    200801      299901  
13. 3             4    200801      299901  
14. 4             4    200801      299901  
15. 5             6    200801      299901  
16.   
17. W_I=ND-OD  (  将W_I表的内容全部插入到历史表中，这些是新增记录 )  
18. 用户标志    状态   开始时间     结束时间  
19. 1             2   200801        299901  
20. 3             4   200801        299901  
21. 5             6   200801        299901  
22.   
23. W_U=OD-ND ( 对历史表进行W_U部份的更新操作，start_date保持不变，而end_date改为当天 )  
24. 用户标志    状态  开始时间       结束时间  
25. 1            1   200712          299901  
26. 3            3   200712          299901  
27. 5            5   200712          299901  
28.   
29. INSERT操作把I插入到HIS  
30. 用户标志   状态   开始时间        结束时间  
31. 1            1   200712           299901  
32. 2            2   200712           299901  
33. 3            3   200712           299901  
34. 4            4   200712           299901  
35. 5            5   200712           299901  
36. 1            2   200801           299901  --new  
37. 3            4   200801           299901  --new  
38. 5            6   200801           299901  --new  
39. </span>  
40. update操作按U更新HIS  
41. 用户标志       状态 开始时间 结束时间  
42. 1               1   200712  200801 --change  
43. 2               2   200712  299901    
44. 3               3   200712  200801 --change  
45. 4               4   200712  299901  
46. 5               5   200712  200801 --change  
47. 1               2   200801  299901  
48. 3               4   200801  299901  
49. 5               6   200801  299901  

 

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

另一个操作SQL的例子 

[sql] view plain copy

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

 

自己编写的例子：

[sql] view plain copy

1. /**拉链表: 也就是一个 记录历史 表,用于记录事物从 最开始的状态 到 当前状态 所有变化的信息 */  
2. select * from emp ;   
3.   
4. --历史表   
5. DROP TABLE old_tb_his;  
6. drop table new_tb;   
7.   
8. create table old_tb_his(  
9. id  number(10,0),  
10. status  varchar2(20),  
11. start_date varchar2(20),  
12. end_date varchar2(20)  
13. );  
14.   
15. insert into old_tb_his values(1,'1', '200712' , '299901');  
16. insert into old_tb_his values(2,'2', '200712' , '299901');  
17. insert into old_tb_his values(3,'3', '200712' , '299901');  
18. insert into old_tb_his values(4,'4', '200712' , '299901');  
19. insert into old_tb_his values(5,'5', '200712' , '299901');  
20.  --ROLLBACK;  
21. COMMIT;  
22. select * from old_tb_his;      
23.   
24.   
25. CREATE TABLE NEW_TB AS SELECT * FROM old_tb_his WHERE 2 =1 ;  
26. insert into NEW_TB values(1,'2', '200801' , '299901');  
27. insert into NEW_TB values(2,'2', '200801' , '299901');  
28. insert into NEW_TB values(3,'4', '200801' , '299901');  
29. insert into NEW_TB values(4,'4', '200801' , '299901');  
30. insert into NEW_TB values(5,'6', '200801' , '299901');  
31. COMMIT;   
32.   
33. SELECT * FROM NEW_TB;    
34.   
35. /*  
36. merge into old_tb_his   
37.  using NEW_TB      
38.  on (old_tb_his.id = NEW_TB.id and old_tb_his.status = new_tb.status )    
39. when matched then  update set old_tb_his.end_date =  NEW_TB.start_date   
40. when not matched then insert values(NEW_TB.id, NEW_TB.status, NEW_TB.start_date,NEW_TB.end_date);    
41.  */  
42.   
43. /**用不了  这个函数是匹配就更新   不匹配添加   
44.    而拉链算法可以看作是 不匹配的更新 不匹配的也添加  
45.   
46. merge into old_tb_his   
47.  using NEW_TB      
48.  on (old_tb_his.id = NEW_TB.id and old_tb_his.status = new_tb.status )    
49. when not matched then update set old_tb_his.end_date =  NEW_TB.start_date ;  
50. --when not matched then insert values(NEW_TB.id, NEW_TB.status, NEW_TB.start_date,NEW_TB.end_date);    
51.  */  
52. --如果函数不能完成拉链算法 只能通过存储过程来完成  
53.    
54.    
55. select * from old_tb_his;   
56. SELECT * FROM NEW_TB;    
57.   
58.   
59.   
60. --创建临时表old_tb_his_temp  
61. CREATE GLOBAL TEMPORARY  TABLE old_tb_his_temp     
62. (  
63. id  number(10,0),  
64. status  varchar2(20),  
65. start_date varchar2(20),  
66. end_date varchar2(20)  
67. )  
68. ON COMMIT DELETE ROWS ;  
69.   
70. --创建临时表new_tb_temp  
71. CREATE GLOBAL TEMPORARY  TABLE new_tb_temp     
72. (  
73. id  number(10,0),  
74. status  varchar2(20),  
75. start_date varchar2(20),  
76. end_date varchar2(20)  
77. )  
78. ON COMMIT DELETE ROWS ;  
79.   
80.   
81. -- W_I = ND - OD  (  将W_I表的内容全部插入到历史表中，这些是新增记录 )    
82. insert into  old_tb_his_temp   
83. select *  
84.   from new_tb t  
85.  where t.id not in (select id  
86.                       from (select t1.id, t1.status, t1.end_date  
87.                               from old_tb_his t1  
88.                             intersect  
89.                             select t2.id, t2.status, t2.end_date  
90.                               from new_tb t2));  
91.                                 
92.                      
93.    
94. -- W_U = OD - ND ( 对历史表进行W_U部份的更新操作，start_date保持不变，而end_date改为当天 )    
95. insert into new_tb_temp  
96. select *  
97.   from old_tb_his t  
98.  where t.id not in (select id  
99.                       from (select t1.id, t1.status, t1.end_date  
100.                               from old_tb_his t1  
101.                             intersect  
102.                             select t2.id, t2.status, t2.end_date  
103.                               from new_tb t2));  
104.   
105.                                 
106. select * from old_tb_his_temp;  
107.   
108. select * from new_tb_temp;  
109.   
110.   
111.   
112. commit;   
113.   
114.   
115.   
116. --INSERT操作把I插入到HIS    
117. INSERT INTO old_tb_his  
118. SELECT * FROM old_tb_his_temp ;  
119.   
120.   
121. select * from old_tb_his ;  
122.   
123.   
124. --多表更新语句一: update 操作按U更新HIS    
125. merge into old_tb_his  
126. 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 )  
127.  when matched then update set old_tb_his.end_date = old_tb_his_temp.start_date ;  
128.   
129. --多表更新语句二: or: update 操作按U更新HIS    
130. update old_tb_his   
131. 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)  
132. where exists(  
133. select 1 from old_tb_his_temp where old_tb_his.id = old_tb_his_temp.id   
134.                                 and old_tb_his.status <> old_tb_his_temp.status  
135. )  
136.   
137. commit;   
138.   
139.   
140. select * from old_tb_his  
141.   
142.    
143. select * from emp;   
144.    
145.   
146. --单表更新  
147. update emp set empno = 7777 where ename = upper('smith') ;  
148.    
149. --多表更新  
150. merge into t2  
151. using   t1 on (t2.id = t1.id and t2.status <> t1.status )  
152.  when matched then update set t2.end_date = t1.start_date ;  
153. --or  
154. update t2   
155. set t2.end_date = (select t1.start_date from t1  where t1.id = t2.id)  
156. where exists(  
157. select 1 from t1 where t2.id = t1.id   
158.                    and t2.status <> t1.status  
159. )  

 

 

转自： http://blog.csdn.net/badyflf/article/details/51097552