到此，我们的拉链实现就完成咯，当初还用了很多个临时表，琢磨了好久终于可以实现了，希望能帮助到你！如果对你有用可以给我赞哦~ 原创不易，谢谢！
其他
系列 | 数仓实践第三篇NO.3『拉链表』
点击上方蓝色字体，置顶/星标哦
目前10000+人已关注加入我们
拉链表是一种数据模型，主要是针对数据仓库设计中表存储数据的方式而定义的；顾名思义，所谓拉链表，就是记录历史，记录一个事务从开始一直到当前状态的所有变化的信息。
拉链表可以避免按每一天存储所有记录造成的海量存储问题，同时也是处理缓慢变化数据（SCD2）的一种常见方式。
一、概念原理
在拉链表中，每一条数据都有一个生效日期(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)表推送过来的数据 ，表结构和源数据库的表结构一致
目标表 ( 即拉链表 ) T_FIN_ACCTION_TAR，这里注意的是：拉链表把源表的时间字段改成了 生效时间 和 失效时间 哦
在这里为了方便阅读以及代码的编写，先写出整体的存储过程架构，然后我们在一步一步添加代码：
-- 将当前时间传入 (也可以传入昨天的时间哦，随机应变，
-- 如果传入的时间是今天则使用中要将时间减1，因为我们要处理的是昨天的数据)
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;
三、拉链实现过程
目标表中没有此主键的则确定为新增 - 新增
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
);
3.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 )
)
;
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; -- 查询拉链表
测试结果如下图：
插入第二天全量数据，这些数据中有新增的数据，有源数据被删除，还有源数据被修改，完整的模拟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;
原表的效果图如下：
接下来执行拉链函数：
-- 执行拉链函数
select My_FIN_GL_SUBJECT_PRO('2019-10-12');
select * from T_FIN_ACCTION_TAR; --查询目标表
效果图如下：
作者：小飞牛_666
来源：https://www.jianshu.com/p/aca4983e9bde
著作权归作者所有，转载请联系作者获得授权。
番外语
1.本文操作环境：GreenPlum数据库。故采用了过程函数的方式insert/update实现拉链
2.若Hive上，则需要采用full outer join实现拉链表。后面会有专篇文章介绍。
3.建议添加chain_status字段，分别记录拉链状态（active、expired、history）。
觉得内容还不错的话，请分享给更多的朋友哦
对文章感兴趣的小伙伴，欢迎加入技术群与作者交流（公众号后台回复：加群）。
近6篇精彩文章回顾
1.用户画像
3.双剑合璧：Apache Kylin 和 Superset
01. 后台回复「经典」,即可领取大数据数仓经典书籍。
02. 后台回复「中台」,即可领取大厂中台架构高清ppt。
03. 后台回复「加群」,或 添加小助微信ID：iom1128 拉您入群或领取资料。
技术大佬们在等你，各种资源定期分享~
Q: 关于数据仓库，你还想了解什么？
欢迎留言区与大家分享
觉得不错，请把这篇文章分享给你的朋友哦
投稿请联系小助手：iom1128『紫霞仙子』
更多精彩，请戳"阅读原文"到"数仓之路"查看
！关注不迷路~ 各种福利、资源定期分享！