数据仓库中的拉链表

在数据仓库的数据模型设计过程中，经常会遇到这样的需求：
 1.1 数据量比较大;
 1.2 表中的部分字段会被更新,比如订单余额;
 1.3 需要查看某一个时间点或者时间段的历史快照信息,比如查看某一个订单在某个时间点的订单余额
 1.4 变化的比例和频率不是很大,比如,总共有1000万的会员，每天新增和发生变化的有10万左右;
如果表每天都保留一份全量,那么每次全量中会保存很多不变的信息,对存储是极大的浪费.如果保存所有数据的一份最新数据,又不能解决历史数据的查询问题.

综上所述: 引入"拉链表",既能满足反应数据的历史状态,又可以最大程度的节省存储;

创建订单历史信息表，每天保存全量的数据,如下所示:

--订单历史信息表 
drop table t_order_history;
create table t_order_history(
day_id varchar2(10) ,
order_id varchar2(10),
balance int,
create_time varchar2(10),
modify_time varchar2(10)
);
comment on table t_order_history is '订单历史信息表';
comment on column t_order_history.day_id is '日期ID';
comment on column t_order_history.order_id is '订单ID';
comment on column t_order_history.balance is '订单余额';
comment on column t_order_history.create_time is '创建时间';
comment on column t_order_history.modify_time is '修改时间';

--插入数据
insert into t_order_history values('20180101','001',10000,'20180101','20180101');
insert into t_order_history values('20180101','002',40000,'20180101','20180101');
insert into t_order_history values('20180101','003',30000,'20180101','20180101');

insert into t_order_history values('20180102','001',10000,'20180101','20180101');
insert into t_order_history values('20180102','002',50000,'20180101','20180102');
insert into t_order_history values('20180102','003',26000,'20180101','20180102');
insert into t_order_history values('20180102','004',8000,'20180102','20180102');

insert into t_order_history values('20180103','001',12000,'20180101','20180103');
insert into t_order_history values('20180103','002',50000,'20180101','20180102');
insert into t_order_history values('20180103','003',26000,'20180101','20180102');
insert into t_order_history values('20180103','004',10000,'20180102','20180103');
insert into t_order_history values('20180103','005',20000,'20180103','20180103');
commit;

查询测试表,结果如下所示:

  可以看到数据是有冗余的,比如订单“002”和“003”在20180102和20180103日的数据是一样的。

创建订单拉链表,如下所示:

---订单拉链表
create table t_order_zipper(
day_id varchar2(10) ,
order_id varchar2(10) ,
balance int,
start_date varchar2(10),
end_date varchar2(10)
);
comment on table t_order_zipper is '订单拉链表';
comment on column t_order_zipper.day_id is '日期ID';
comment on column t_order_zipper.order_id is '订单ID';
comment on column t_order_zipper.balance is '订单余额';
comment on column t_order_zipper.start_date is '记录开始时间';
comment on column t_order_zipper.end_date is '记录结束时间';

依据订单历史信息表，往订单拉链表中插入数据，逻辑如下所示:

---1 拉链表从历史表中获取全量数据
insert into t_order_zipper(day_id,order_id,balance,start_date,end_date)
select day_id,order_id,balance,create_time as start_date,'88888888' from t_order_history where day_id='20180101';

--2 拉链表中获取历史表的增量数据 --20180102
2.1 更新拉链表中已有的记录的结束时间
update t_order_zipper t set t.end_date=to_char(to_date('20180102','yyyymmdd')-1,'yyyymmdd')  where exists (select 1 from t_order_history d where t.order_id=d.order_id and d.day_id='20180102' and d.modify_time='20180102')  and t.end_date='88888888';

2.2 拉链表从历史表中获取增量数据
insert into t_order_zipper(day_id,order_id,balance,start_date,end_date)
select day_id,order_id,balance,modify_time as start_date,'88888888' from t_order_history where day_id='20180102' and modify_time='20180102';

--3 拉链表中获取历史表的增量数据 --20180103
3.1 更新拉链表中已有的记录的结束时间
update t_order_zipper t set t.end_date=to_char(to_date('20180103','yyyymmdd')-1,'yyyymmdd')  where exists (select 1 from t_order_history d where t.order_id=d.order_id and d.day_id='20180103' and d.modify_time='20180103')  and t.end_date='88888888';

3.2 拉链表从历史表中获取增量数据
insert into t_order_zipper(day_id,order_id,balance,start_date,end_date)
select day_id,order_id,balance,modify_time as start_date,'88888888' from t_order_history where day_id='20180103' and modify_time='20180103';

订单拉链表的数据，如下所示：

可以看到，数据量已经由原来的12条，精简到了9条,没有数据冗余了

验证一 ： 核对某天的数据是否一致

订单历史信息表中的20180102日的数据如下所示:

拉链表中的20180102日的数据如下所示:

说明: 可以看到订单历史表和订单拉链表的20180102日期的数据是一致，数据验证完毕

验证二 ： 核对最新的数据是否一致

订单历史信息表中的最新数据(时间为20180103)

查询拉链表中的最新数据

说明: 订单历史信息表和拉链表中的最新数据是一致的,数据验证完毕