拉链表
拉链表是维护历史状态、以及最新状态的一种方式。
拉链表对快照表进行了优化,根据拉链粒度(一般为时间)的不同,去除了在粒度范围内不变的数据。
拉链表可以维护两个时间(start_time, end_time),来标识当前记录是否还有效,以及更好的定位历史数据
实现前提:
首先要有某一时刻的全量数据,作为起始表
其次要有流水表或者快照表两者其一,作为变化的依据
在数据仓库中,经常会用历史数据和时间维度做数据分析。而保存历史数据最常见的方案是使用拉链表进行存储。
创建测试表(以MySQL为例子)
create table deal_order
(
order_id varchar(20) comment '订单ID',
order_updatetime date comment '订单更新时间',
order_status_cn varchar(50) comment '订单更新状态'
);
插入数据
insert into deal_order values('100001',date_format('2020-05-07 12:23:18','%Y-%m-%d %H:%i:%S'),'订单创建');
insert into deal_order values('100002',date_format('2020-05-07 16:48:05','%Y-%m-%d %H:%i:%S'),'订单创建');
insert into deal_order values('100003',date_format('2020-05-07 20:47:25','%Y-%m-%d %H:%i:%S'),'支付完成');
所以2020年5月7日的订单情况如下
第二天订单发生改变
update deal_order
set order_updatetime = date_format('2020-05-08 09:12:46', '%Y-%m-%d %H:%i:%S'),order_status_cn = '等待支付'
where order_id = '100001';
update deal_order
set order_updatetime = date_format('2020-05-08 13:25:15', '%Y-%m-%d %H:%i:%S'),order_status_cn = '已发货'
where order_id = '100003';
insert into deal_order values('100004',date_format('2020-05-08 16:11:48','%Y-%m-%d %H:%i:%S'),'等待发货');
insert into deal_order values('100005',date_format('2020-05-08 18:23:31','%Y-%m-%d %H:%i:%S'),'等待发货');
2020年5月8日订单详情
到了2020年5月9日,部分订单再次发生了改变,并且有了新的订单:
update deal_order
set order_updatetime = date_format('2020-05-09 11:28:35', '%Y-%m-%d %H:%i:%S'),order_status_cn = '订单关闭'
where order_id = '100002';
update deal_order
set order_updatetime = date_format('2020-05-09 14:48:48', '%Y-%m-%d %H:%i:%S'),order_status_cn = '交易完成'
where order_id = '100003';
update deal_order
set order_updatetime = date_format('2020-05-09 17:09:34', '%Y-%m-%d %H:%i:%S'),order_status_cn = '支付成功'
where order_id = '100005';
insert into deal_order values('100006',date_format('2020-05-09 19:49:55','%Y-%m-%d %H:%i:%S'),'等待发货');
5月9日状态:
对于上述表来说,由于没有保存历史数据,因此查询“100001”订单的状态历史就查询不到。如果使用历史拉链表来存储以上信息,则存储内容如下:
create table deal_order_zipper_tb
(
order_id varchar(20) comment '订单ID',
order_updatetime date comment '订单更新时间',
order_status_cn varchar(50) comment '订单当前状态',
order_start_time date comment '该记录生命周期开始时间(包括该时间点)',
order_end_time date comment '该记录生命周期结束时间(不包括该时间点)'
);
历史拉链表中,增加了记录的生命周期开始时间和结束时间,一条记录的生命周期为 [生命周期开始时间,生命周期结束时间)。
将原来的数据导入到历史拉链表,最终结果如下
insert into deal_order_zipper_tb values('100001',date_format('2020-05-07 12:23:18','%Y-%m-%d %H:%i:%S'),'订单创建',date_format('2020-05-07 12:23:18','%Y-%m-%d %H:%i:%S'),date_format('2020-05-08 09:12:46','%Y-%m-%d %H:%i:%S'));
insert into deal_order_zipper_tb values('100001',date_format('2020-05-07 12:23:18','%Y-%m-%d %H:%i:%S'),'等待支付',date_format('2020-05-08 09:12:46','%Y-%m-%d %H:%i:%S'),date_format('9999-09-09 23:59:59','%Y-%m-%d %H:%i:%S'));
insert into deal_order_zipper_tb values('100002',date_format('2020-05-07 16:48:05','%Y-%m-%d %H:%i:%S'),'订单创建',date_format('2020-05-07 16:48:05','%Y-%m-%d %H:%i:%S'),date_format('2020-05-09 11:28:35','%Y-%m-%d %H:%i:%S'));
insert into deal_order_zipper_tb values('100002',date_format('2020-05-07 16:48:05','%Y-%m-%d %H:%i:%S'),'订单关闭',date_format('2020-05-09 11:28:35','%Y-%m-%d %H:%i:%S'),date_format('9999-09-09 23:59:59','%Y-%m-%d %H:%i:%S'));
insert into deal_order_zipper_tb values('100003',date_format('2020-05-07 20:47:25','%Y-%m-%d %H:%i:%S'),'支付完成',date_format('2020-05-07 20:47:25','%Y-%m-%d %H:%i:%S'),date_format('2020-05-08 13:25:15', '%Y-%m-%d %H:%i:%S'));
insert into deal_order_zipper_tb values('100003',date_format('2020-05-07 20:47:25','%Y-%m-%d %H:%i:%S'),'已发货',date_format('2020-05-08 13:25:15', '%Y-%m-%d %H:%i:%S'),date_format('2020-05-09 14:48:48', '%Y-%m-%d %H:%i:%S'));
insert into deal_order_zipper_tb values('100003',date_format('2020-05-07 20:47:25','%Y-%m-%d %H:%i:%S'),'交易完成',date_format('2020-05-09 14:48:48', '%Y-%m-%d %H:%i:%S'),date_format('9999-09-09 23:59:59','%Y-%m-%d %H:%i:%S'));
insert into deal_order_zipper_tb values('100004',date_format('2020-05-08 16:11:48','%Y-%m-%d %H:%i:%S'),'等待发货',date_format('2020-05-08 16:11:48','%Y-%m-%d %H:%i:%S'),date_format('9999-09-09 23:59:59','%Y-%m-%d %H:%i:%S'));
insert into deal_order_zipper_tb values('100005',date_format('2020-05-08 18:23:31','%Y-%m-%d %H:%i:%S'),'等待发货',date_format('2020-05-08 18:23:31','%Y-%m-%d %H:%i:%S'),date_format('2020-05-09 17:09:34', '%Y-%m-%d %H:%i:%S'));
insert into deal_order_zipper_tb values('100005',date_format('2020-05-08 18:23:31','%Y-%m-%d %H:%i:%S'),'支付成功',date_format('2020-05-09 17:09:34', '%Y-%m-%d %H:%i:%S'),date_format('9999-09-09 23:59:59','%Y-%m-%d %H:%i:%S'));
insert into deal_order_zipper_tb values('100006',date_format('2020-05-09 19:49:55','%Y-%m-%d %H:%i:%S'),'等待发货',date_format('2020-05-09 19:49:55','%Y-%m-%d %H:%i:%S'),date_format('9999-09-09 23:59:59','%Y-%m-%d %H:%i:%S'));
此时的订单状态
如果需要查询2020年5月8日的历史快照,可以直接写出SQL:
select * from deal_order_zipper_tb d
where d.order_start_time < date_format('2020-05-09', '%Y-%m-%d')
and d.order_end_time >= date_format('2020-05-09', '%Y-%m-%d');
该SQL的查询结果与之前2020-05-08的数据一致:
SQL文件:https://download.csdn.net/download/zhangvalue/12897367