拉链表实现及使用

大家好，又见面了，我是你们的朋友全栈君。如果您正在找激活码,请点击查看最新教程,关注关注公众号 “全栈程序员社区” 获取激活教程,可能之前旧版本教程已经失效.最新Idea2022.1教程亲测有效,一键激活。

Jetbrains全系列IDE稳定放心使用

一、概念

历史拉链表，就是记录一个事务从开始一直到当前状态的所有变化的信息，拉链表可以避免按每一天存储所有记录造成的海量存储问题，同时也是处理缓慢变化数据的一种常见方式。

 

假设企业拥有1000万的会员信息，每天有20万的会员资料变更，我们需要记录所有会议的历史变化记录，并至少保留两年，该怎么办？

储存两年就是 2 x 365 x 1000万 = 7300000000(70亿)，如果储存更长时间，则无法估算需要的存储。而用拉链表存储，每日只向表中新增和变化的数据量，每日不过20万条，

储存2年也只需要 2 x 365 * 200000 = 146000000 (1.46以)存储空间。

 

二、案例设计

–创建用户信息的原始表

drop table t_userinfo_src;

create table t_userinfo_src(

    user_id int,

    user_name character varying,

    user_no integer,

    phone_no character varying,

    create_date date,

    update_date date

) distribute by hash(user_id);

 

–创建目标拉链表

drop table t_userinfo_zipper;

create table t_userinfo_zipper(

    user_id int,

    user_name character varying,

    user_no integer,

    phone_no character varying,

    effective_date date,

    invalid_date date

) distribute by hash(user_id);

 

2019年11月12日 新增了两个用户，

则这两条记录的生效时间为当天，由于到 2019年11月12日 为止，这两条记录还没有被修改过，所以失效时间为无穷大，

这里设置为数据库中的最大值(3000-12-31)，数据如下：

insert into t_userinfo_src(user_id,user_name,user_no,phone_no,create_date,update_date) 

values(1001,’se7en.shi’,’110′,’13000000001′,’2019-11-12′,’2019-11-12′),

(1002,’eleven’,’120′,’13000000002′,’2019-11-12′,’2019-11-12′),

(1003,’rose’,’120′,’13000000003′,’2019-11-12′,’2019-11-12′);

 

postgres=> select * from t_userinfo_src;

 user_id | user_name | user_no |  phone_no   |     create_date     |     update_date

———+———–+———+————-+———————+———————

    1002 | eleven    |     120 | 13000000002 | 2019-11-12 00:00:00 | 2019-11-12 00:00:00

    1001 | se7en.shi |     110 | 13000000001 | 2019-11-12 00:00:00 | 2019-11-12 00:00:00

    1003 | rose      |     120 | 13000000003 | 2019-11-12 00:00:00 | 2019-11-12 00:00:00

(3 rows)

 

–执行函数，传入今天的时间，处理昨天的数据

select * from fn_userinfo_zipper(‘2019-11-13’);

 

–查看拉链表的数据

postgres=> select * from t_userinfo_zipper;

 user_id | user_name | user_no |  phone_no   |   effective_date    |    invalid_date

———+———–+———+————-+———————+———————

    1003 | rose      |     120 | 13000000003 | 2019-11-12 00:00:00 | 2999-12-31 00:00:00

    1001 | se7en.shi |     110 | 13000000001 | 2019-11-12 00:00:00 | 2999-12-31 00:00:00   

    1002 | eleven    |     120 | 13000000002 | 2019-11-12 00:00:00 | 2999-12-31 00:00:00

(3 rows)

 

第二天(2019-11-13)，

用户 1001 被删除，

用户 1002 的电话号码被修改成 13000000004 。

为了保留历史状态，用户 1001 的失效时间被修改成 2019-11-12，用户 1002 则变成两条记录，

新增1004用户数据。

 

–原始表的操作为

delete from t_userinfo_src where user_id=1001;

update t_userinfo_src set phone_no=’13000000004′,update_date=’2019-11-13′ where user_id=1002;

insert into t_userinfo_src(user_id,user_name,user_no,phone_no,create_date,update_date) 

values(1004,’jack’,’110′,’13000000005′,’2019-11-13′,’2019-11-13′);

 

–查看原始表数据

postgres=> select * from t_userinfo_src;

 user_id | user_name | user_no |  phone_no   |     create_date     |     update_date

———+———–+———+————-+———————+———————

    1003 | rose      |     120 | 13000000003 | 2019-11-12 00:00:00 | 2019-11-12 00:00:00

    1004 | jack      |     110 | 13000000005 | 2019-11-13 00:00:00 | 2019-11-13 00:00:00

    1002 | eleven    |     120 | 13000000004 | 2019-11-12 00:00:00 | 2019-11-13 00:00:00

 

postgres=> select * from t_userinfo_zipper;

 user_id | user_name | user_no |  phone_no   |   effective_date    |    invalid_date

———+———–+———+————-+———————+———————

    1003 | rose      |     120 | 13000000003 | 2019-11-12 00:00:00 | 2999-12-31 00:00:00   –拉链表中，14号执行后，应该被新增 

    1001 | se7en.shi |     110 | 13000000001 | 2019-11-12 00:00:00 | 2999-12-31 00:00:00   –拉链表中，14号执行后，应该被删除

    1002 | eleven    |     120 | 13000000002 | 2019-11-12 00:00:00 | 2999-12-31 00:00:00   –拉链表中，14号执行后，应该被标记为无效，无效时间是2019-11-13 00:00:00

 

–执行函数

select * from fn_userinfo_zipper(‘2019-11-14’);

 

–执行函数后查看数据

postgres=> select * from t_userinfo_src;

 user_id | user_name | user_no |  phone_no   |     create_date     |     update_date

———+———–+———+————-+———————+———————

    1003 | rose      |     120 | 13000000003 | 2019-11-12 00:00:00 | 2019-11-12 00:00:00

    1004 | jack      |     110 | 13000000005 | 2019-11-13 00:00:00 | 2019-11-13 00:00:00

    1002 | eleven    |     120 | 13000000004 | 2019-11-12 00:00:00 | 2019-11-13 00:00:00

(3 rows)

 

postgres=> select * from t_userinfo_zipper;

 user_id | user_name | user_no |  phone_no   |   effective_date    |    invalid_date

———+———–+———+————-+———————+———————

    1003 | rose      |     120 | 13000000003 | 2019-11-12 00:00:00 | 2999-12-31 00:00:00   

    1001 | se7en.shi |     110 | 13000000001 | 2019-11-12 00:00:00 | 2019-11-13 00:00:00   –被标记为删除，invalid_date为2019-11-13 00:00:00

    1004 | jack      |     110 | 13000000005 | 2019-11-13 00:00:00 | 2999-12-31 00:00:00   –新增数据

    1002 | eleven    |     120 | 13000000002 | 2019-11-12 00:00:00 | 2019-11-13 00:00:00   –被标记为无效 

    1002 | eleven    |     120 | 13000000004 | 2019-11-13 00:00:00 | 2999-12-31 00:00:00   –更新后的数据

(5 rows)

 

–拉链表的使用

1,如果要查询最新的数据，那么只要查询失效时间为 2999-12-31 的数据即可

postgres=> select * from t_userinfo_zipper where invalid_date=’2999-12-31′;

 user_id | user_name | user_no |  phone_no   |   effective_date    |    invalid_date

———+———–+———+————-+———————+———————

    1004 | jack      |     110 | 13000000005 | 2019-11-13 00:00:00 | 2999-12-31 00:00:00

    1003 | rose      |     120 | 13000000003 | 2019-11-12 00:00:00 | 2999-12-31 00:00:00

    1002 | eleven    |     120 | 13000000004 | 2019-11-13 00:00:00 | 2999-12-31 00:00:00

 

1,如果要查询 2019年11月12号 的历史数据，则筛选生效时间 <= 2019-11-13 并且失效时间 > 2019-11-13 的数据即可；

postgres=> select * from t_userinfo_zipper where invalid_date<=’2019-11-13′ and invalid_date >=’2019-11-13′;

 user_id | user_name | user_no |  phone_no   |   effective_date    |    invalid_date

———+———–+———+————-+———————+———————

    1002 | eleven    |     120 | 13000000002 | 2019-11-12 00:00:00 | 2019-11-13 00:00:00

    1001 | se7en.shi |     110 | 13000000001 | 2019-11-12 00:00:00 | 2019-11-13 00:00:00

(2 rows)

 

–实现函数如下

create or replace function fn_userinfo_zipper(IN cur_date text)

    returns void

as $$

/*

本功能是将原数据表中 新增数据、修改、删除记录到拉链表中

invalid_date 设定为 2999-12-31

 

本函数传入值为时间，具体为今天执行昨天的数据，参数为 (to_date(cur_date,’yyyy-mm-dd’) – 1)

总体逻辑如下

–1.目标表中没有此主键的，确定为新增  –  新增

–2，捕获原表被删除的数据，并更新拉链表被删除数据的失效时间

–3 捕获被修改的内容，将其置为无效

–3.1 闭链：目标表中有此主键的记录,状态值不同，更新结束日期为当天

–3.2 开链：目标表中新增一条修改的数据，更新结束日期为无穷大

 

@author: se7en.shi

@date: 2019-11-13

*/

declare

begin

–1.目标表中没有此主键的，确定为新增  –  新增

insert into t_userinfo_zipper(user_id,user_name,user_no,phone_no,effective_date,invalid_date)

select a.user_id,a.user_name,a.user_no,a.phone_no,a.create_date,to_date(‘2999-12-31′,’yyyy-mm-dd’) as invalid_date

from t_userinfo_src a

where a.create_date=(to_date(cur_date,’yyyy-mm-dd’) – 1)

and not exists(

select 1 from t_userinfo_zipper b

where a.user_id=b.user_id);

raise notice ‘finish new increasing …’;

 

–2，捕获原表被删除的数据，并更新拉链表被删除数据的失效时间

update t_userinfo_zipper a set invalid_date=(to_date(cur_date,’yyyy-mm-dd’)-1)

where not EXISTS(

    select 1 from t_userinfo_src b 

    where a.user_id=b.user_id

);

raise notice ‘finish delete data capture …’;

 

–3 捕获被修改的内容，将其置为无效

–3.1 闭链：目标表中有此主键的记录,状态值不同，更新结束日期为当天

update t_userinfo_zipper a set invalid_date=(to_date(cur_date,’yyyy-mm-dd’)-1)

where a.invalid_date=to_date(‘2999-12-31′,’yyyy-mm-dd’)

and exists(

    select 1 from t_userinfo_src b 

    where a.user_id=b.user_id and b.create_date < (to_date(cur_date,’yyyy-mm-dd’)-1)

    and (b.user_name<>a.user_name or b.user_no<>a.user_no or b.phone_no<>a.phone_no)

);

raise notice ‘finish modifyed data capture lable invalid…’;

 

–3.2 开链：目标表中新增一条修改的数据，更新结束日期为无穷大

insert into t_userinfo_zipper(user_id,user_name,user_no,phone_no,effective_date,invalid_date)

select a.user_id,a.user_name,a.user_no,a.phone_no,(to_date(cur_date,’yyyy-mm-dd’) – 1) as effective_date,to_date(‘2999-12-31′,’yyyy-mm-dd’) as invalid_date

from t_userinfo_src a

where a.create_date<=(to_date(cur_date,’yyyy-mm-dd’) – 1)

and exists(

    select 1 from (

        select user_id,effective_date,max(invalid_date) as invalid_date

        from t_userinfo_zipper 

        group by user_id,effective_date ) b

        where a.user_id=b.user_id

        and a.create_date=b.effective_date

        and b.invalid_date <=  (to_date(cur_date,’yyyy-mm-dd’) – 1)

);

raise notice ‘finish modifyed data capture new insert’;

end;

$$ language plpgsql;

 

各种数据库迁移到PostgreSQL及PG维保、紧急救援及商业服务，请扫码联系。

———————————————————-

          个人微信                                  

创作不易，如果对你的工作有帮助，打赏小费，以资鼓励吧