??歷史拉鏈表是一種數(shù)據(jù)模型聚凹，主要是針對數(shù)據(jù)倉庫設(shè)計中表存儲數(shù)據(jù)的方式而定義的芜壁；顧名思義，所謂歷史拉鏈表庐冯，就是記錄一個事務(wù)從開始一直到當(dāng)前狀態(tài)的所有變化的信息孽亲，拉鏈表可以避免按每一天存儲所有記錄造成的海量存儲問題，同事也是處理緩慢變化數(shù)據(jù)的一種常見方式展父。
一返劲、概念
??在拉鏈表中玲昧，每一條數(shù)據(jù)都有一個生效日期(sdate) 和 失效日期(edate)。假設(shè)在一個用戶表中篮绿，在 2019年10月8日 新增了兩個用戶孵延，則這兩條記錄的生效時間為當(dāng)天，由于到 2019年10月8日 為止亲配，這兩條記錄還沒有被修改過尘应，所以失效時間為無窮大，這里設(shè)置為數(shù)據(jù)庫中的最大值(2999-12-31)吼虎，如圖所示：
??第二天(2019-10-09)犬钢，用戶 1001 被刪除，用戶 1002 的電話號碼被修改成 16500000006思灰。為了保留歷史狀態(tài)玷犹，用戶 1001 的失效時間被修改成 2019-10-09，用戶 1002 則變成兩條記錄官辈，如圖所示：
??第三天(2019-10-10)箱舞，又新增了用戶 1003，則用戶表數(shù)據(jù)如圖：
??如果要查詢最新的數(shù)據(jù)拳亿，那么只要查詢失效時間為 2999-12-31 的數(shù)據(jù)即可，如果要查詢 10月8號 的歷史數(shù)據(jù)愿伴，則篩選生效時間 <= 2019-10-08 并且失效時間 > 2019-10-08 的數(shù)據(jù)即可肺魁；如果查詢的是 10月9日的數(shù)據(jù)，那么篩選條件則是生效時間 <= 2019-10-09 并且失效時間 > 2019-10-09隔节；以此類推鹅经。
二、表的創(chuàng)建
??臨時源表 T_FIN_ACCTION_SRC怎诫，接收其它數(shù)據(jù)庫(如 oracle)表推送過來的數(shù)據(jù) 瘾晃，表結(jié)構(gòu)和源數(shù)據(jù)庫的表結(jié)構(gòu)一致
--源表
create table T_FIN_ACCTION_SRC(
eNo varchar(6),
eName varchar(10),
ePhone varchar(11),
eData_date date
);
??目標(biāo)表 ( 即拉鏈表 ) T_FIN_ACCTION_TAR，這里注意的是：拉鏈表把源表的時間字段改成了 生效時間 和 失效時間 哦
--拉鏈表
create table T_FIN_ACCTION_TAR(
eNo varchar(6),
eName varchar(10),
ePhone varchar(11),
sdate date,
edate date
);
三幻妓、存儲過程的創(chuàng)建
??在這里為了方便閱讀以及代碼的編寫蹦误，先寫出整體的存儲過程架構(gòu)，然后我們在一步一步添加代碼：
-- 將當(dāng)前時間傳入 (也可以傳入昨天的時間哦肉津，隨機應(yīng)變强胰，如果傳入的時間是今天則使用中要將時間減一，因為我們要處理的是昨天的數(shù)據(jù))
create or replace function My_FIN_GL_SUBJECT_PRO(IN P_TODAY VARCHAR)
returns void
as $$
declare
begin
--1.目標(biāo)表中沒有此主鍵的則確定為新增 - 新增
--2.源表中沒有該ID則進行關(guān)鏈 - 刪除
--3.修改
--3.1 閉鏈：目標(biāo)表中有此主鍵的記錄,狀態(tài)值不同妹沙，更新結(jié)束日期為當(dāng)天
--3.2 開鏈：目標(biāo)表中新增一條修改的數(shù)據(jù)偶洋，更新結(jié)束日期為無窮大
end;
$$
language plpgsql;
四、拉鏈的過程實現(xiàn)
1.目標(biāo)表中沒有此主鍵的則確定為新增 - 新增
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則進行關(guān)鏈 - 刪除
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 閉鏈：目標(biāo)表中有此主鍵的記錄,狀態(tài)值不同距糖，更新結(jié)束日期為當(dāng)天
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 開鏈：目標(biāo)表中新增一條修改的數(shù)據(jù)玄窝，更新結(jié)束日期為無窮大
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( --處理數(shù)據(jù)斷鏈新增的情況
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')
);
五牵寺、測試
??要測試拉鏈函數(shù)，首先我們必須要在原表中插入數(shù)據(jù)（模擬一天全量的數(shù)據(jù)）：
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');
調(diào)用函數(shù)進行拉鏈測試：
select My_FIN_GL_SUBJECT_PRO('2019-10-11'); --調(diào)用函數(shù)
select * from T_FIN_ACCTION_TAR; --查詢拉鏈表
??插入第二天全量數(shù)據(jù)恩脂，這些數(shù)據(jù)中有新增的數(shù)據(jù)缸剪，有源數(shù)據(jù)被刪除，還有源數(shù)據(jù)被修改东亦，完整的模擬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;
接下來執(zhí)行拉鏈函數(shù)：
--執(zhí)行拉鏈函數(shù)
select My_FIN_GL_SUBJECT_PRO('2019-10-12');
select * from T_FIN_ACCTION_TAR; --查詢目標(biāo)表
??到此杏节，我們的拉鏈實現(xiàn)就完成咯，哈哈哈典阵，容易把奋渔，當(dāng)初還用了很多個臨時表，網(wǎng)上的資料也基本沒有壮啊，琢磨了好久終于可以實現(xiàn)了嫉鲸，希望能幫助到你，如果對你有用可以給我贊哦歹啼；轉(zhuǎn)載請標(biāo)明出處玄渗，原創(chuàng)不易，謝謝狸眼。
