oracle实现拉链表

时间：2024-03-13 19:57:54

拉链表

概念

不是技术,而是解决方案
目的:节约存储空间

记录数据在某一时间区间内的状态
以及数据在某一时点上的变化的数据存储方式

也是应需求而产生的技术解决方案

历史数据的两种存储方式

账户ID 用户 状态 数据日期
001 张三 1 2008-06-27
001 张三 1 2008-06-28
001 张三 1 2008-06-29
001 张三 1 2008-06-30
001 张三 1 2008-07-01
001 张三 1 2008-07-02
001 张三 1 2008-07-03
001 张三 1 ...
001 张三 0 2010-02-23
001 张三 0 2010-02-24
001 张三 0 2010-02-25
001 张三 0 2010-02-26
001 张三 0 ...
账户ID 户名 状态 开始日期 结束日期
001 张三 1 2008-06-27 2010-02-23
001 张三 0 2010-02-23 2999-12-31

流程

  1. 建立临时表1
    用于存放转换,处理后的数据

  2. 建立临时表2
    用于存放比对出的增量数据

  3. 修改目标表
    进行关链更新操作

  4. 修改目标表
    进行开链插入操作

代码实现流程

建立源数据

create table test_src
(
ID varchar2(100),
NAME varchar2(100),
BAL number(20,2)
)

往源中插入数据

insert into test_src 
select \'1\',\'徐峥\',600 from dual
union all
select \'2\',\'黄渤\',700 from dual

建立目标表

create table test_tag
(
ID varchar2(100),
NAME varchar2(100),
BAL number(20,2),
START_DT date,
END_DT date
)

建立临时表temp1

用于存放原系统数据

--事务临时表
create global temporary table temp1
as select * from test_tag

建立第二个临时表temp2

用于存放对比后的增量或是状态有变化的数据

create global temporary table temp2 --事务临时表
as select * from test_tag

临时表temp1插入数据

此处方便测试,使用前天时间

insert into temp1
select a.*,trunc(sysdate-2,\'dd\'),to_date(\'29990101\',\'yyyy/mm/dd\')
from test_src a

临时表temp2插入数据

insert into temp2
SELECT  *
FROM  temp1 t1
WHERE  not exists(
SELECT  1
FROM  test_tag g 
WHERE  g.END_DT=to_date(\'29990101\',\'yyyy/mm/dd\')
and t1.id=g.id
and t1.bal=g.bal);

关链

此处方便测试,使用前天时间

update test_tag a set end_dt=trunc(sysdate-2,\'dd\')
where exists (select 1 from temp2 b where a.id=b.id)
and END_DT = to_date(\'29990101\',\'yyyy/mm/dd\');

开链

insert into test_tag
select * from temp2;
commit;

查看目标表

SQL> select * from test_tag;
ID      NAME     BAL START_DT    END_DT
----- ---------- --- ----------- -----------
2      黄渤     700.00  2020/3/23   2999/1/1
1      徐峥     600.00  2020/3/23   2999/1/1

修改数据再次测试

修改和插入数据

update test_src
set bal=2700
where id=2;
insert into test_src
values(3,\'黄晓明\',1000);
commit;

SQL> select * from test_src;
ID   NAME           BAL
---  --------  -----------
1    徐峥        600.00
2    黄渤       2700.00
3    黄晓明     1000.00

插入到临时表temp1

此时为测试方便使用昨天时间

insert into temp1
select a.*,trunc(sysdate-1,\'dd\'),to_date(\'29990101\',\'yyyy/mm/dd\')
from test_src a

插入到临时表temp2

insert into temp2
SELECT  *
FROM  temp1 t1
WHERE  not exists(
SELECT  1
FROM  test_tag g 
WHERE  g.END_DT=to_date(\'29990101\',\'yyyy/mm/dd\')
and t1.id=g.id
and t1.bal=g.bal);

关链

update test_tag a set end_dt=trunc(sysdate-1,\'dd\')
where exists (select 1 from temp2 b where a.id=b.id)
and END_DT = to_date(\'29990101\',\'yyyy/mm/dd\');

开链

insert into test_tag
select * from temp2;
commit;

查看目标表

SQL> select * from test_tag;
ID   NAME           BAL START_DT    END_DT
---- -------- ---------- --------- -------------
3    黄晓明     1000.00 2020/3/24   2999/1/1
2    黄渤       2700.00 2020/3/24   2999/1/1
2    黄渤        700.00 2020/3/23   2020/3/24
1    徐峥        600.00 2020/3/23   2999/1/1

应用

查看23号大家的余额情况

SQL> select * from test_tag
  2  where start_dt<=to_date(\'20200323\',\'yyyy/mm/dd\')
  3  and
  4  end_dt>to_date(\'20200323\',\'yyyy/mm/dd\')
  5  ;
ID  NAME       BAL START_DT    END_DT
--- ----- -------- ---------- -------------
2   黄渤    700.00 2020/3/23   2020/3/24
1   徐峥    600.00 2020/3/23   2999/1/1

开始时间<=20200323
结束时间>20200323

相关文章