Greenplum数据库中怎么实现拉链表

这期内容当中小编将会给大家带来有关Greenplum数据库中怎么实现拉链表,文章内容丰富且以专业的角度为大家分析和叙述,阅读完这篇文章希望大家可以有所收获。

成都创新互联公司,为您提供重庆网站建设成都网站制作、网站营销推广、网站开发设计,对服务成都地磅秤等多个行业拥有丰富的网站建设及推广经验。成都创新互联公司网站建设公司成立于2013年,提供专业网站制作报价服务,我们深知市场的竞争激烈,认真对待每位客户,为客户提供赏心悦目的作品。 与客户共同发展进步,是我们永远的责任!

一、概念

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

Greenplum数据库中怎么实现拉链表

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

Greenplum数据库中怎么实现拉链表

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

Greenplum数据库中怎么实现拉链表

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

二、表的创建

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

--源表 create table T_FIN_ACCTION_SRC(     eNo varchar(6),     eName varchar(10),     ePhone varchar(11),     eData_date date );

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

--拉链表 create table T_FIN_ACCTION_TAR(     eNo varchar(6),     eName varchar(10),     ePhone varchar(11),     sdate date,     edate date );

三、存储过程的创建

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

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

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

四、拉链的过程实现

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

insert into gplcydb.public.T_FIN_ACCTION_TAR(eNo,eName,ePhone,sdate,edate)                        select s.eNo,s.eName,s.ePhone,s.eData_date,to_date('2999-12-31','yyyy-mm-dd')                                        from gplcydb.public.T_FIN_ACCTION_SRC s                                           where s.eData_date=(to_date(P_TODAY,'yyyy-mm-dd') - 1)                                           and not exists(                                                 select 1 from gplcydb.public.T_FIN_ACCTION_TAR t                                                          where                                                              s.eNo=t.eNo                                                          and s.eName=t.eName                                                         and s.ePhone=t.ePhone                                         );

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

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

3.修改

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

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

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

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

五、测试

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

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

调用函数进行拉链测试:

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

测试结果如下图:

Greenplum数据库中怎么实现拉链表

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

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

原表的效果图如下:

Greenplum数据库中怎么实现拉链表

接下来执行拉链函数:

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

效果图如下:

Greenplum数据库中怎么实现拉链表

上述就是小编为大家分享的Greenplum数据库中怎么实现拉链表了,如果刚好有类似的疑惑,不妨参照上述分析进行理解。如果想知道更多相关知识,欢迎关注创新互联行业资讯频道。


分享标题:Greenplum数据库中怎么实现拉链表
路径分享:http://myzitong.com/article/ghejsg.html