文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

深渊之刃-Greenplum数据库之拉链表的实现

2024-12-03 14:02

关注

历史拉链表是一种数据模型,主要是针对数据仓库设计中表存储数据的方式而定义的;顾名思义,所谓历史拉链表,就是记录一个事务从开始一直到当前状态的所有变化的信息,拉链表可以避免按每一天存储所有记录造成的海量存储问题,同事也是处理缓慢变化数据的一种常见方式。

一、概念

在拉链表中,每一条数据都有一个生效日期(sdate) 和 失效日期(edate)。假设在一个用户表中,在 2019年10月8日 新增了两个用户,则这两条记录的生效时间为当天,由于到 2019年10月8日 为止,这两条记录还没有被修改过,所以失效时间为无穷大,这里设置为数据库中的最大值(2999-12-31),如图所示:

第二天(2019-10-09),用户 1001 被删除,用户 1002 的电话号码被修改成 16500000006。为了保留历史状态,用户 1001 的失效时间被修改成 2019-10-09,用户 1002 则变成两条记录,如图所示:

第三天(2019-10-10),又新增了用户 1003,则用户表数据如图:

如果要查询最新的数据,那么只要查询失效时间为 2999-12-31 的数据即可,如果要查询 10月8号 的历史数据,则筛选生效时间 <= 2019-10-08 并且失效时间 > 2019-10-08 的数据即可;如果查询的是 10月9日的数据,那么筛选条件则是生效时间 <= 2019-10-09 并且失效时间 > 2019-10-09;以此类推。

二、表的创建

临时源表 T_FIN_ACCTION_SRC,接收其它数据库(如 oracle)表推送过来的数据 ,表结构和源数据库的表结构一致。

  1. --源表 
  2. create table T_FIN_ACCTION_SRC( 
  3.     eNo varchar(6), 
  4.     eName varchar(10), 
  5.     ePhone varchar(11), 
  6.     eData_date date 
  7. ); 

目标表 ( 即拉链表 ) T_FIN_ACCTION_TAR,这里注意的是:拉链表把源表的时间字段改成了生效时间和失效时间。

  1. --拉链表 
  2. create table T_FIN_ACCTION_TAR( 
  3.     eNo varchar(6), 
  4.     eName varchar(10), 
  5.     ePhone varchar(11), 
  6.     sdate date
  7.     edate date 
  8. ); 

三、存储过程的创建

在这里为了方便阅读以及代码的编写,先写出整体的存储过程架构,然后我们在一步一步添加代码:

-- 将当前时间传入 (也可以传入昨天的时间哦,随机应变,如果传入的时间是今天则使用中要将时间减一,因为我们要处理的是昨天的数据)

  1. -- 将当前时间传入 (也可以传入昨天的时间哦,随机应变,如果传入的时间是今天则使用中要将时间减一,因为我们要处理的是昨天的数据) 
  2. create or replace function My_FIN_GL_SUBJECT_PRO(IN P_TODAY VARCHAR
  3.     returns void  
  4. as $$  
  5. declare 
  6.  
  7. begin    
  8.             --1.目标表中没有此主键的则确定为新增  -  新增 
  9.  
  10.             --2.源表中没有该ID则进行关链  -  删除 
  11.  
  12.             --3.修改 
  13.             --3.1 闭链:目标表中有此主键的记录,状态值不同,更新结束日期为当天 
  14.  
  15.             --3.2 开链:目标表中新增一条修改的数据,更新结束日期为无穷大 
  16.  
  17. end
  18. $$  
  19. language plpgsql; 

四、拉链的过程实现

目标表中没有此主键的则确定为新增 - 新增

  1. insert into gplcydb.public.T_FIN_ACCTION_TAR(eNo,eName,ePhone,sdate,edate)   
  2.                      select s.eNo,s.eName,s.ePhone,s.eData_date,to_date('2999-12-31','yyyy-mm-dd')    
  3.                                     from gplcydb.public.T_FIN_ACCTION_SRC s  
  4.                                          where s.eData_date=(to_date(P_TODAY,'yyyy-mm-dd') - 1)  
  5.                                          and not exists( 
  6.                                                 select 1 from gplcydb.public.T_FIN_ACCTION_TAR t  
  7.                                                         where  
  8.                                                             s.eNo=t.eNo  
  9.                                                         and s.eName=t.eName 
  10.                                                         and s.ePhone=t.ePhone 
  11.                                         ); 

源表中没有该ID则进行关链 - 删除

  1. update gplcydb.public.T_FIN_ACCTION_TAR a set edate=(to_date(P_TODAY,'yyyy-mm-dd')-1)  
  2.                      where not exists( 
  3.                          select 1 from gplcydb.public.T_FIN_ACCTION_SRC s  
  4.                                         where  
  5.                                             s.eNo=a.eNo  
  6.                                         and a.edate=to_date('2999-12-31''yyyy-mm-dd')  
  7.                                     ); 

修改

1 闭链:目标表中有此主键的记录,状态值不同,更新结束日期为当天

  1. update gplcydb.public.T_FIN_ACCTION_TAR b set edate=(to_date(P_TODAY,'yyyy-mm-dd')-1)     
  2.                      where b.edate=to_date('2999-12-31','yyyy-mm-dd')    
  3.                            and exists( 
  4.                                select 1 from gplcydb.public.T_FIN_ACCTION_SRC s  
  5.                                       where  
  6.                                             s.eNo = b.eNo and b.sdate < (to_date(P_TODAY,'yyyy-mm-dd')-1)  
  7.                                       and ( 
  8.                                             s.eName <> b.eName or s.ePhone <> b.ePhone  
  9.                                           )  
  10.                             ); 

2 开链:目标表中新增一条修改的数据,更新结束日期为无穷大

  1. insert into gplcydb.public.T_FIN_ACCTION_TAR(eNo,eName,ePhone,sdate,edate)   
  2.                      select s.eNo,s.eName,s.ePhone,(to_date(P_TODAY,'yyyy-mm-dd') - 1),to_date('2999-12-31','yyyy-mm-dd')    
  3.                             from gplcydb.public.T_FIN_ACCTION_SRC s  
  4.                                  where  
  5.                                      s.eData_date=(to_date(P_TODAY,'yyyy-mm-dd') - 1)  
  6.                                  and exists( --处理数据断链新增的情况 
  7.                                         select 1 from ( 
  8.                                                         select eNo,sdate,max(edate) end_date  
  9.                                                                 from gplcydb.public.T_FIN_ACCTION_TAR group by eNo,sdate) t  
  10.                                                                         where  
  11.                                                                             t.eNo=s.eNo  
  12.                                                                         and s.eData_date = t.sdate  
  13.                                                                         and t.end_date <= to_date(P_TODAY,'yyyy-mm-dd')  
  14.                                 ); 

五、测试

要测试拉链函数,首先我们必须要在原表中插入数据(模拟一天全量的数据):

  1. insert into T_FIN_ACCTION_SRC values('1001','feiniu','18500000001','2019-10-10'); 
  2. insert into T_FIN_ACCTION_SRC values('1002','beibei','18400000005','2019-10-10'); 
  3. insert into T_FIN_ACCTION_SRC values('1003','yuyu','13800000005','2019-10-10'); 

调用函数进行拉链测试:

  1. select My_FIN_GL_SUBJECT_PRO('2019-10-11');  --调用函数 
  2. select * from T_FIN_ACCTION_TAR;   --查询拉链表 

测试结果如下图:

插入第二天全量数据,这些数据中有新增的数据,有源数据被删除,还有源数据被修改,完整的模拟sql语句如下:

  1. delete from T_FIN_ACCTION_SRC where eno='1003'
  2. insert into T_FIN_ACCTION_SRC values('1004','kongkong','13800000666','2019-10-11'); 
  3. update T_FIN_ACCTION_SRC set ename='xiaofeifei' where eno='1001'
  4. select * from T_FIN_ACCTION_SRC; 

原表的效果图如下:

接下来执行拉链函数:

  1. --执行拉链函数 
  2. select My_FIN_GL_SUBJECT_PRO('2019-10-12'); 
  3. select * from T_FIN_ACCTION_TAR;  --查询目标表 

效果图如下:

本文转载自微信公众号「浪尖聊大数据」,可以通过以下二维码关注。转载本文请联系浪尖聊大数据公众号。

 

 

来源:浪尖聊大数据内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-后端开发
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯