数据仓库之拉链算法(转)
链:古代软兵器的中介之物,故名思意.有着连接、衔接的意思.拉链算法是目前数据仓库领域比较XX的算法之一..通用非常广.记录数据量很大且为全量实体记录历史的操作。
例如,某某移动通信公司客户资料,以河北为例,河北有客户2800W,客户资料每个一条就是2800W条记录算上历史客户,全量大概有5000W条左右。作为数据仓库来存储这些信息几千万条记录不算什么。可是要是记录历史全量所用到的存储就非常的庞大。问题实例为:一般正常情况下,从河北移动的BOSS系统上每天采集全量的日数据大概为2500W条,历史存储每天存储一个2500W条的日表,存储三个月,就需要3*30*2500W条的数据存储空间,数据量为20E。这只是存储三个月的历史如果存储更长时间则无法估计需要的存储。而用拉链算法存储。每日只是向历史表(HIS)中添加新增和变化的数据量。每日不过数十W条。存储一年也就是需要5000W条记录的存储空间即两个日全量的空间。下面详细介绍下拉链算法:
1.采集当日全量存储到 ND(NewDay)表中。(比正常的全量表多两个字段(START_DATE&END_DATE))
2.可从历史表中取出昨日全量数据存储到 OD(OldDay)表中。(比正常的全量表多两个字段(START_DATE&END_DATE))
3.用ND-OD为当日新增和变化的数据(即每日增量)。
4.用OD-ND为状态到此结束需要封链的数据。
5.历史表(HIS)比ND表和OD表多两个字段(START_DATE&END_DATE)
6.针对第三部来讲,ND和OD表的(START_DATE&END_DATE)分别记录当前日期和最大日期,取意为开始日期为当前天的数据和结束日期为最大日期。注意OD和ND的START_DATE
ND——OD两个表进行全字段比较但是(START_DATE&END_DATE)除外。将结果记录到W_I表中
OD——ND两个表进行全字段比较同样(START_DATE&END_DATE)除外。将结果记录到W_U表中
7.将W_I表的内容全部插入到HIS表中。
8. 对历史表(HIS)和OD表比较对历史表最更新操作即在历史表(HIS)中数据进行更新操作以W_U表为准,即对历史表与W_U比对(START_DATE&END_DATE除外),在历史表(HIS)中也在W_U表中的数据将其END_DATE改成当前天,说明该记录对当前天失效。
9。取数据时候对日期进行条件选择即可如:取20080101日的数据条件部分为
(where start-date<='20080101' and end_date>20070801 )即可
全部SQL为:
(select * from table(his) where start-date<='20080101' and end_date>20070801 )
下面为具体例子:
OD(在第一天就等于HIS)
用户标志 状态 开始时间 结束时间
1 1 200712 299901
2 2 200712 299901
3 3 200712 299901
4 4 200712 299901
5 5 200712 299901
ND
用户标志 状态 开始时间 结束时间
1 2 200801 299901
2 2 200801 299901
3 4 200801 299901
4 4 200801 299901
5 6 200801 299901
W_I=ND-OD
用户标志 状态 开始时间 结束时间
1 2 200801 299901
3 4 200801 299901
5 6 200801 299901
W_U=OD-ND
用户标志 状态 开始时间 结束时间
1 1 200712 299901
3 3 200712 299901
5 5 200712 299901
INSERT操作 把I插入到HIS
用户标志 状态 开始时间 结束时间
1 1 200712 299901
2 2 200712 299901
3 3 200712 299901
4 4 200712 299901
5 5 200712 299901
1 2 200801 299901
3 4 200801 299901
5 6 200801 299901
update操作 按U更新HIS
用户标志 状态 开始时间 结束时间
1 1 200712 200801
2 2 200712 299901
3 3 200712 200801
4 4 200712 299901
5 5 200712 200801
1 2 200801 299901
3 4 200801 299901
5 6 200801 299901
表结构设计之拉链表
拉链表是针对 数据仓库设计中表存储数据的方式而定义的,顾名思义,所谓拉链,就是记录历史。记录一个事物从开始,一直到当前状态的所有变化的信息。
在历史表中对客户的一生的记录可能就这样几条记录,避免了按每一天记录客户状态造成的海量存储的问题:
(NAME)人名
client
client
client
client
client
client
client
上面的每一条记录都是不算末尾的,比如到19070901,client已经在A,而不是H了。所以除最后一条记录因为状态到目前都未改变的,其余的记录实际上在END-DT那天,都不在是该条记录END-DT那天的状态。这种现象可以理解为算头不算尾。
(二)算法
1采集当日全量数据到ND(NewDay)表;
2可从历史表中取出昨日全量数据存储到OD(OldDay)表;
3(ND-OD)就是当日新增和变化的数据,也就是当天的增量,用W_I表示;
4(OD-ND)为状态到此结束需要封链的数据,用W_U表示;
5将W_I表的内容全部插入到历史表中,这些是新增记录,start_date为当天,而end_date为max值;
6对历史表进行W_U部份的更新操作,start_date保持不变,而end_date改为当天,也就是关链操作;