拉链表的实现过程_希嘉统一集成管道之拉链算法

随着学校规模不断的扩大以及数据分析需求的越来越精准 ，对于传统的快照存储或者全量备份存储等方式已经不满足学校数据分析的需求，而是更期待采用拉链表方式进行存储，拉链历史数据的入库对保证数据的连续、完整及资源共享有一定的意义.

传统ELT工具通过自定义脚本、存储过程、控件封装方式实现拉链表，实现过程比较繁琐复杂，具体的实现过程就不在这里讲解，下面重点讲解希嘉在拉链表实现是怎样处理？

讲解拉链表实现，首先先对比一下当前表和拉链历史表的结构，发现历史拉链表多出三个字段(生效日期、失效日期、数据状态)，如下图所示：

历史拉链表结构解析：

生效日期：是指数据的初始日期

失效日期：是指这条数据原系统已经删除了或者数据更新导致数据生命周期结束，默认日期(9999/12/31)

数据状态：I代表数据的初始状态，U代表更新，D代表删除

接口开发：通过希嘉统一集成管道用接口方式实现拉链，进行实操，如下图所示：

1.选取拉链功能及保存历史数据对应的数据源和拉链历史表。

2.定义主键。

数据准备：通过模拟原表数据中的数据，如下图所示：

数据同步：第一次数据同步，拉链历史表中会存在同样的数据，其中生效日期更新跑批日期，失效日期更新为默认日期(9999/12/31),状态更新为数据的初始状态(I)，如下图所示：

数据准备：第二次数据模拟同步，这次原表数据新增一位老师(工号：00000-张三)，更新一位老师的身份证号状态(工号：00190-张XX)，删除一位老师(工号：0019-周XX)，原表数据如下图所示：

结果展示：拉链历史表数据更新如下图所示，后续数据同步，历史表数据状态依次类推，如下图所示：

就以上表为例，分别给出3种数据使用需求的SQL语句：

查询当前数据

SELECT *

FROM T_RS_JZGJBXX_HIS

WHERE TO_CHAR(D_END_DATE, 'YYYY/MM/DD') = '9999/12/31'

查询一个特定用户的所有过往历史数据

SELECT * FROM T_RS_JZGJBXX_HIS WHERE ZGH = '00190'

查询该表在过去的某一天的数据状态

SELECT *

FROM T_RS_JZGJBXX_HIS

WHERE D_STATUS = 'I'

and TO_CHAR(D_BEG_DATE, 'YYYY/MM/DD') <= '2020/03/13'

UNION ALL

SELECT T.ZGH,

T.SZDWDM,

T.XM,

T.XBM,

T.CSRQ,

T.CSDM,

T.JG,

T.MZM,

T.SFZJLXM,

T.SFZJH,

T.NL,

T.DQZTM,

T.TSTAMP,

T.D_BEG_DATE,

T.D_END_DATE,

T.D_STATUS

FROM (SELECT ZGH,

SZDWDM,

XM,

XBM,

CSRQ,

CSDM,

JG,

MZM,

SFZJLXM,

SFZJH,

NL,

DQZTM,

TSTAMP,

D_BEG_DATE,

D_END_DATE,

D_STATUS,

row_number() over(partition by ZGH order by D_BEG_DATE desc) RN

FROM T_RS_JZGJBXX_HIS

WHERE TO_CHAR(D_BEG_DATE, 'YYYY/MM/DD') = '2020/03/13'

and D_STATUS = 'U')T

WHERE RN = 1

查询所有某天发生了变化的数据

---截止某一天未发生变化的数据记录

SELECT *

FROM T_RS_JZGJBXX_HIS

WHERE D_STATUS = 'I'

and TO_CHAR(D_BEG_DATE, 'YYYY/MM/DD') <= '2020/03/13'

UNION ALL

---某一天删除数据的记录

SELECT *

FROM T_RS_JZGJBXX_HIS

WHERE D_STATUS = 'D'

and TO_CHAR(D_END_DATE, 'YYYY/MM/DD') = '2020/03/13'

UNION ALL

---某一天更新数据的记录

SELECT ZGH,

SZDWDM,

XM,

XBM,

CSRQ,

CSDM,

JG,

MZM,

SFZJLXM,

SFZJH,

NL,

DQZTM,

TSTAMP,

D_BEG_DATE,

D_END_DATE,

D_STATUS

FROM T_RS_JZGJBXX_HIS

WHERE TO_CHAR(D_BEG_DATE, 'YYYY/MM/DD') <= '2020/03/13'

and D_STATUS = 'U'

UNION ALL

---某一天删除数据的记录

SELECT *

FROM T_RS_JZGJBXX_HIS

WHERE D_STATUS = 'D'

and TO_CHAR(D_END_DATE, 'YYYY/MM/DD') = '2019/12/31'

UNION ALL

---某一天更新数据的记录

SELECT ZGH,

SZDWDM,

XM,

XBM,

CSRQ,

CSDM,

JG,

MZM,

SFZJLXM,

SFZJH,

NL,

DQZTM,

TSTAMP,

D_BEG_DATE,

D_BEG_DATE,

D_STATUS

FROM T_RS_JZGJBXX_HIS

WHERE TO_CHAR(D_BEG_DATE, 'YYYY/MM/DD') <= '2019/12/31'

and D_STATUS = 'U'