深淵之刃-Greenplum數據庫之拉鏈表的實現

作者：zyflin 2021-01-08 05:27:49

歷史拉鏈表是一種數據模型，主要是針對數據倉庫設計中表存儲數據的方式而定義的;顧名思義，所謂歷史拉鏈表，就是記錄一個事務從開始一直到當前狀態的所有變化的信息，拉鏈表可以避免按每一天存儲所有記錄造成的海量存儲問題，同事也是處理緩慢變化數據的一種常見方式。

[[374518]]

一、概念

在拉鏈表中，每一條數據都有一個生效日期(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)表推送過來的數據，表結構和源數據庫的表結構一致。

--源表 
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;   --查詢拉鏈表

測試結果如下圖：

插入第二天全量數據，這些數據中有新增的數據，有源數據被刪除，還有源數據被修改，完整的模擬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;  --查詢目標表

效果圖如下：

本文轉載自微信公眾號「浪尖聊大數據」，可以通過以下二維碼關注。轉載本文請聯系浪尖聊大數據公眾號。

責任編輯：武曉燕來源：浪尖聊大數據

數據庫拉鏈表存儲