1. 全栈程序员必看首页

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

全栈程序员-用户IM • • 未分类

etl算法详解_数据拉链处理什么意思所谓拉链，就是记录历史。记录一个事物从开始，一直到当前状态的所有变化的信息。   在历史表中对客户的一生的记录可能就这样几条记录，避免了按每一天记录客户状态造成的海量存储的问题：（NAME）人名（START-DATE）开始日期（END-DT）结束日期（STAT）状态    client             19000101                       

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

  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

 

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。

发布者：全栈程序员-用户IM，转载请注明出处：https://javaforall.cn/181234.html原文链接：https://javaforall.cn

【正版授权，激活自己账号】： Jetbrains全家桶Ide使用，1年售后保障，每天仅需1毛

【官方授权 正版激活】： 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

赞 (0)
全栈程序员-用户IM全栈程序员-用户IM
0 0


相关推荐

  • 什么学习软件需要身份证验证_什么是两层身份验证，为什么我需要它？

    什么学习软件需要身份证验证_什么是两层身份验证，为什么我需要它？

    什么学习软件需要身份证验证_什么是两层身份验证，为什么我需要它？什么学习软件需要身份证验证Moreandmorebanks,creditcardcompanies,andevensocialmedianetworksandgamingsitesarestartingtousetwo-factorauthentication.Ifyou’realittleunclearonwhatitisoronw…

    全栈程序员-用户IM 全栈程序员-用户IM
    2022年5月10日
  • Matlab中meshgrid的用法简介

    Matlab中meshgrid的用法简介

    Matlab中meshgrid的用法简介meshgrid：网格1、主要使用的函数为[X,Y]=meshgrid(xgv,ygv);meshgrid函数生成的X，Y是大小相等的矩阵，xgv，ygv是两个网格矢量，xgv，ygv都是行向量。X：通过将xgv复制length(ygv)行（严格意义上是length(ygv)-1行）得到Y：首先对ygv进行转置得到ygv’，将ygv’复制（length(xgv)-1）次得到。…

    全栈程序员-用户IM 全栈程序员-用户IM
    2022年6月12日
  • map集合遍历的三种方式(map遍历的两种方式)

    map集合遍历的三种方式(map遍历的两种方式)

    Map集合的遍历与List和Set集合不同。Map有两组值，因此遍历时可以只遍历值的集合，也可以只遍历键的集合，也可以同时遍历。Map以及实现Map的接口类(如HashMap、TreeMap、LinkedHashMap、Hashtable等)都可以用以下几种方式遍历。1)在for循环中使用entries实现Map的遍历(最常见和最常用的)。publicstati…

    全栈程序员-用户IM 全栈程序员-用户IM
    2022年4月17日
  • 矩形类定义

    矩形类定义

    矩形类定义

    全栈程序员-用户IM 全栈程序员-用户IM
    2022年1月11日
  • 语义分割的发展前景和概述[通俗易懂]

    语义分割的发展前景和概述[通俗易懂]

    语义分割的发展前景和概述[通俗易懂]感谢感谢！收藏用~原文出自：http://blog.geohey.com/ji-suan-ji-shi-jue-zhi-yu-yi-fen-ge/计算机视觉之语义分割2017年10月11日人工智能被认为是第四次工业革命，google，facebook等全球顶尖、最有影响力的技术公司都将目光转向AI，虽然免不了存在泡沫，被部分媒体夸大宣传，神经网络在图像识别，语音识别，自然语言处理，无人车等方面的贡…

    全栈程序员-用户IM 全栈程序员-用户IM
    2022年8月21日
  • 小米5 MIUI 10系统完全Root教程 （Root思想通用所有机型）

    小米5 MIUI 10系统完全Root教程 （Root思想通用所有机型）

    小米5 MIUI 10系统完全Root教程 （Root思想通用所有机型）WrittenbyQingXiaonan2018-8-251.摘要  虽然手机在完全root后存在一定风险，但是可以从事一些具有探索性的工作如修改手机游戏、修改APP权限要求、编写按键脚本等。此外，完全root非常适合那些拥有两个手机的人。这篇帖子以小米5最新MIUI10开发版系统完全root过程为例，介绍了一种通用的小米机型完全Root办法。2.重要概念…

    全栈程序员-用户IM 全栈程序员-用户IM
    2022年6月4日

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号