Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about how to realize the zipper list in the Greenplum database. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

I. concept

In the zipper list, each piece of data has an effective date (sdate) and an expiration date (edate). Suppose that in a user table, two new users are added on October 8, 2019, the effective time of these two records is the same day, and since the two records have not been modified as of October 8, 2019, the expiration time is infinite. Here, set to the maximum value in the database (2999-12-31), as shown in the figure:

The next day (2019-10-09), user 1001 was deleted and the phone number of user 1002 was modified to 16500000006. In order to retain the historical state, the expiration time of user 1001 is modified to 2019-10-09, and user 1002 becomes two records, as shown in the figure:

On the third day (2019-10-10), another 1003 users are added, and the user table data is as shown in the figure:

If you want to query the latest data, you only need to query the data with an expiration time of 2999-12-31; if you want to query the historical data on October 8, filter the data with an effective time of 2019-10-08; if you are querying the data on October 9, then the filter condition is the effective time of 2019-10-09, and so on.

Second, the creation of tables

Temporary source table T_FIN_ACCTION_SRC, which receives data pushed from other databases (such as oracle). The table structure is the same as that of the source database.

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

The target table (that is, the zipper table) T_FIN_ACCTION_TAR, note here: the zipper table changes the time field of the source table to the effective time and expiration time.

-Zipper list create table T_FIN_ACCTION_TAR (eNo varchar (6), eName varchar (10), ePhone varchar (11), sdate date, edate date)

Third, the creation of stored procedures

Here, in order to facilitate reading and code writing, we first write the overall stored procedure architecture, and then we add the code step by step:

-- pass in the current time (you can also pass in yesterday's time. If the time passed in is today, you have to reduce the time in use by one, because we are dealing with yesterday's data.

-- pass in the current time (can also be passed into yesterday's time, oh, adaptively, if the time passed in is today, you have to reduce the time in use by one, because we are dealing with yesterday's data) create or replace function My_FIN_GL_SUBJECT_PRO (IN P_TODAY VARCHAR) returns void as $$declare begin-- 1. If there is no primary key in the target table, it is determined to add-add-- 2. If there is no ID in the source table, then close-delete-- 3. Modification-3.1closed chain: there is a record of this primary key in the target table, and the status value is different. The update end date is the same day-3.2 open chain: a modified data is added to the target table, and the update end date is infinite end; $$language plpgsql.

Fourth, the process realization of zipper

1. If there is no primary key in the target table, it is determined to add-add.

Insert into gplcydb.public.T_FIN_ACCTION_TAR (eNo,eName,ePhone,sdate,edate) select s.eNotemery s.eNameparaments.ePhonenotes.eDatadatestampl toggle date ('2999-12-31' 'yyyy-mm-dd') from gplcydb.public.T_FIN_ACCTION_SRC s where s.eDatadatedate = (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)

two。 If the ID is not in the source table, it will be closed-deleted.

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. Modify

3.1 closed chain: there is a record of this primary key in the target table. The status value is different, and the update end date is the same day.

Update gplcydb.public.T_FIN_ACCTION_TAR b set edate= (to_date)-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 Open chain: add a modified data to the target table, and the update end date is infinite

Insert into gplcydb.public.T_FIN_ACCTION_TAR (eNo,eName,ePhone,sdate,edate) select s.eNameparentions.eNameparentions.ePhone, (to_date (Paddy TODAYJEYYYYYYYYYYYYPUMUMUDUD')-1), to_date ('2999-12-31') 'yyyy-mm-dd') from gplcydb.public.T_FIN_ACCTION_SRC s where s.eDatadatedate = (to_date (P_TODAY) 'yyyy-mm-dd')-1) and exists (--handles the addition of data breakage 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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.