拉链表是针对数据仓库设计中表存储数据的方式而定义的,就是记录历史数据的每个状态,记录一个事物从开始,一直到当前状态的所有变化的信息;拉链表通常是对账户信息的历史变动进行处理保留的结果
转载地址:http://lxw1234.com/archives/2015/04/20.htm
使用场景:
- 数据量比较大;
- 表中的部分字段会被update,如用户的地址,产品的描述信息,订单的状态等等;
- 需要查看某一个时间点或者时间段的历史快照信息,比如,查看某一个订单在历史某一个时间点的状态,
比如,查看某一个用户在过去某一段时间内,更新过几次等等; - 变化的比例和频率不是很大,比如,总共有1000万的会员,每天新增和发生变化的有10万左右;
- 如果对这边表每天都保留一份全量,那么每次全量中会保存很多不变的信息,对存储是极大的浪费;
这些场景下使用拉链历史表,既可以反映数据的历史状态,又可以最大程度的节省存储。
例子:
有一张订单表,6月20号有3条记录:
订单创建日期 | 订单编号 | 订单状态 |
---|---|---|
2012-06-20 | 001 | 创建订单 |
2012-06-20 | 002 | 创建订单 |
2012-06-20 | 003 | 支付完成 |
到6月21号,表中有5条记录:
订单创建日期 | 订单编号 | 订单状态 |
---|---|---|
2012-06-20 | 001 | 支付完成(从创建到支付) |
2012-06-20 | 002 | 创建订单 |
2012-06-20 | 003 | 支付完成 |
2012-06-21 | 004 | 创建订单 |
2012-06-21 | 005 | 创建订单 |
到6月22号,表中有6条记录:
订单创建日期 | 订单编号 | 订单状态 |
---|---|---|
2012-06-20 | 001 | 支付完成(从创建到支付) |
2012-06-20 | 002 | 创建订单 |
2012-06-20 | 003 | 已发货(从支付到发货) |
2012-06-21 | 004 | 创建订单 |
2012-06-21 | 005 | 支付完成(从创建到支付) |
2012-06-22 | 006 | 创建订单 |
没有使用拉链表的情况下数据仓库中对该表的保留方法:
1、只保留一份全量表,则数据和6月22号的记录一样,如果需要查看6月21号订单001的状态,则无法满足;
2、每天都保留一份全量,即将每天的记录都保留下来,则数据仓库中的该表共有14条记录,但好多记录都是重复保存的,没有任务变化,如订单002,004,数据量大的话会造成很大的存储浪费;
如果在数据仓库中设计成历史拉链表保存该表,则表如下:
订单创建日期 | 订单编号 | 订单状态 | dw_begin_date | dw_end_date |
---|---|---|---|---|
2012-06-20 | 001 | 创建订单 | 2012-06-20 | 2012-06-20 |
2012-06-20 | 001 | 支付完成 | 2012-06-21 | 9999-12-31 |
2012-06-20 | 002 | 创建订单 | 2012-06-20 | 9999-12-31 |
2012-06-20 | 003 | 支付完成 | 2012-06-20 | 2012-06-21 |
2012-06-20 | 003 | 已发货 | 2012-06-22 | 9999-12-31 |
2012-06-21 | 004 | 创建订单 | 2012-06-21 | 9999-12-31 |
2012-06-21 | 005 | 创建订单 | 2012-06-21 | 2012-06-21 |
2012-06-21 | 005 | 支付完成 | 2012-06-22 | 9999-12-31 |
2012-06-22 | 006 | 创建订单 | 2012-06-22 | 9999-12-31 |
说明:
- dw_begin_date表示该条记录的生命周期开始时间
- dw_end_date表示该条记录的生命周期结束时间
- dw_end_date=‘9999-12-31’ 表示该条记录目前处于有效状态
- 如果查询当前所有有效的记录,则select * from order_history where dw_end_date=‘9999-12-31’
- 如果查询2012-06-21的历史快照,则select * from order_history where dw_begin_date<=‘2012-06-21’ and dw_end_date>=‘2012-06-21’,这条语句会查询到以下记录:
订单创建日期 | 订单编号 | 订单状态 | dw_begin_date | dw_end_date |
---|---|---|---|---|
2012-06-20 | 001 | 支付完成 | 2012-06-21 | 9999-12-31 |
2012-06-20 | 002 | 创建订单 | 2012-06-20 | 9999-12-31 |
2012-06-20 | 003 | 支付完成 | 2012-06-20 | 2012-06-21 |
2012-06-21 | 004 | 创建订单 | 2012-06-21 | 9999-12-31 |
2012-06-21 | 005 | 创建订单 | 2012-06-21 | 2012-06-21 |
和源表在6月21号的记录完全一致:
订单创建日期 | 订单编号 | 订单状态 |
---|---|---|
2012-06-20 | 001 | 支付完成(从创建到支付) |
2012-06-20 | 002 | 创建订单 |
2012-06-20 | 003 | 支付完成 |
2012-06-21 | 004 | 创建订单 |
2012-06-21 | 005 | 创建订单 |
由此,拉链表即能满足对历史数据的需求,又能很大程度的节省存储资源。