CN100576203C - 多表连接方法 - Google Patents

Abstract

公开了一种将具有多条记录的第一表与具有多条记录的第二表连接以形成第三表的多表连接方法，所述第一表和第二表的每一条记录都具有各自的有效开始日期、有效结束日期、第一数量和/或第二数量。本发明的方法充分利用了两表根据时间段进行合并的这个前提条件，只需对两表遍历一次就可以得到结果。因此，只需要对M+N条记录进行处理，就能够将两个表连接成一个表，并且克服了SQL方法造成的边界值丢失问题。

Description

多表连接方法

技术领域

本发明涉及数据仓库中的多表连接技术领域，特别是涉及一种按照时间压缩的多表连接方法，以提高多表连接的效率。

背景技术

数据仓库的一个重要特点是需要对大量历史数据进行存储。一种常见的情况是多日的数据相同，简单地对这些记录进行存储将造成存储空间极大的浪费。一种常用的改进方法是对这些记录按照时间段进行压缩存储，通过在事实表与维表中加上有效开始日期(Effective_From)和有效结束日期(Effective_To)两个字段表示记录的时间有效性。这样，一段时间内相同记录可以压缩为一条记录，这种方法可以充分利用存储空间。数据仓库之父W.H.Inmon和数据仓库专家Ralph Kimball都在各自的专著中对这种方法进行了介绍。但是，这种常用的压缩方法同时也给多表连接操作带来了复杂性，因为传统的索引和表连接方式都不能完全发挥作用。因此，如何快速而高效地实现这种按时间段压缩的多表的连接操作成为了一个常见的问题。

为了更清晰地阐述这类问题，下面针对图6(A)～6(C)所示的三张表来进行描述。在图6(A)所示的表，例如T1是常见的事实表，其中SK字段表示代理键，A字段表示一个数量(可能是交易数量或其他数量)，有效开始日期，有效结束日期，其中“1900-01-01”表示一个最小的日期，“9999-12-31”表示一个最大的日期。类似地，图6(B)所示的表，例如T2，是常见的维表，其中SK字段表示代理键，B字段表示某个属性信息(可能是信用卡编号或其他信息)，有效开始日期和有效结束日期两个字段的含义与如上所述相同。将表T1和表T2按照有效期时间段关系进行连接后生成的正确结果表T3，如图6(C)所示。

对于这种类型的多表连接操作，一种最原始的方法是首先“解压”记录，即将按照时间段存储的一条记录变为按日的记录，其中的数量全部相同，例如对于一条时间段为5月1日至5月31日的时间压缩记录，按日进行解压后将生成31条记录，这些记录除了日期字段其余所有字段都相同，然后对事实表与维表按照SK与日期进行合并，最后将结果表重新按照时间段进行压缩存储。这种最简单的方法在效率上十分低下，并且在三个步骤的合并过程中将耗费许多存储空间供临时表使用。

其他一些方法是根据时间段合并的不同条件采用SQL语句实现，在这方面，IBM，NCR等公司都给出了他们建议的SQL语句方式，效率比上述的原始方法有较大提高，在数据仓库项目中一般都采用这种SQL语句的方式解决此类连接问题。但是这种目前普遍采用的方法仍然存在着两个主要的缺点。

1.SQL语句方式的执行效率不高

对于SQL语句的效率从算法理论角度分析如下：

用SQL语句进行两表的连接操作，其基础建立在对两表做笛卡尔积。如果两表分别有M，N条记录，则需要对M×N条记录进行处理，算法复杂性为O(M×N)。

根据更一般的情况可以得到如下分析过程：

假设T1表中不同SK的取值有M种，每一种SK的取值分别有N_i条记录(即第i个SK有N_i条记录，i＝1，2，3……M)。

假设T2表中不同SK的取值有P种，每一种SK的取值分别有Q_i条记录(即第i个SK有Q_i条记录，i＝1，2，3……P)。

则对于SQL实现的笛卡尔积有如下分析：

连接条件为T1.SK＝T2.SK，则连接后形成的结果表共有：

条记录，其中s为N与Q中相同SK的数目。

但是，如果考虑索引的因素，可以进一步得到如下的分析过程：

对T1表和T2表作为连接条件的字段SK，EF，ET分别加上索引，这样SQL语句在实际执行过程中每次读取T1表中一条记录，然后在T2表中进行查找，由于采用了索引，因此不必顺序查找整个表，而是在一定的排序算法基础上根据查找算法进行查找，根据算法理论证明可知，查找算法效率最高是log₂ ^N，其中N是记录条数，经过我们对oracle环境中SQL语句的查询计划分析，oracle环境中对索引表的查找效率即为log₂ ^N。

为了分析的简洁起见，在这里假设T1表中只有一个SK，取值有M条记录，T2中有同样对应的一个SK，取值有N条记录，可以看到，将M看作T1表中不同SK对应记录数的平均值，将N看作T2表中不同SK对应记录数的平均值，根据上述假设，SQL方式访问表的次数为：M×log₂ ^N+M，当M，N很大时，这样的操作仍然需要耗费比较长的时间。

此外，现代关系数据库管理系统(RDBMS：Relation DataBaseManagement System)对于表连接SQL语句主要采用的是在笛卡尔积基础上发展而来的嵌套循环连接(Nested Loop Join)、排序合并连接(Sort Merge Join)以及哈希连接(Hash Join)以及基于这些方法的改进算法。

但是，由于此类问题存在的按照时间段进行连接的特殊性，使得这些普遍采用的算法在此类问题上仍然不能完全发挥作用。在实际数据库管理系统所采用的三种表连接算法中，嵌套循环连接方式基本与笛卡尔积操作一样，不同的是实际采用时对于数据的I/O操作进行了一定的优化，但是表连接的基本操作：记录比较的数目是不变的。

排序合并连接虽然对于一般的表连接可以达到O(M+N)的效率，但是其前提条件是作为连接条件的字段值是表内唯一的并且连接条件为等值连接时才能达到这个最优的效率。如果连接条件字段值不唯一或者连接条件为不等值连接，那么效率将大大下降，这被国外研究者称为排序合并连接算法的本质缺陷(Intrinsic Skew)。针对这些由于数据问题引起的排序连接算法效率下降问题，很多研究者都进行了研究，但是提出的改进算法主要着重于在比较指针需要回溯的时候如何减少数据库的读写次数，目前还没有一种算法能大幅减少记录比较次数，使得效率回到O(M+N)这个最佳值。

哈希连接依赖于所选择的哈希函数，目前还没有一种能使得各种表连接情况都能达到最优效率的哈希函数。另外，哈希连接的实现比较复杂，在很大程度上依赖于具体的数据库管理系统。对于一些比较轻型的数据库管理系统可能没有哈希连接，那么多表连接就很难得到优化。

2.SQL语句对于边界值情况处理时会造成记录丢失

SQL方法对于事实表和维表每个SK第一条记录的处理会丢失一段时间，例如对于图6(A)和图6(B)的表中所示的数据，对于SK＝1的记录，使用SQL语句得到的结果表中第一条记录如图7的表所示。从图7中可以看出，丢失了从1999-01-01到1999-04-30这段时间的信息，也就是说对于图6(A)中的第一条记录的信息造成了丢失，而丢失记录在数据仓库中绝对不允许的。SQL语句方式为了解决这种边界值的丢失，必须加入额外的语句进行判别和处理。

发明内容

本发明克服了现有技术中的不足，提供一种形式简洁，与具体平台和编程语言无关，对数据仓库领域中按时间压缩存储的多表连接具有普遍适用性，执行效率提高明显的时间压缩的多表连接方法。

在本发明的一个方面，提出了一种将具有多条记录的第一表与具有多条记录的第二表连接以形成第三表的多表连接方法，所述第一表和第二表的每一条记录都具有各自的有效开始日期、有效结束日期、第一数量和/或第二数量，所述的方法包括步骤：第一步骤，初始化分别用来暂存第一数量和第二数量的第一数量变量和第二数量变量，以及分别用来暂存第一表的记录和第二表的记录的第一记录变量和第二记录变量；第二步骤，将第一表的一条记录和第二表的一条记录分别读入第一记录变量和第二记录变量；第三步骤，将第一记录变量的有效开始日期和第二记录变量的有效开始日期中较小的有效开始日期作为第三表中一条记录的有效开始日期，并将较小有效开始日期所在的记录中的第一数量和第二数量的一个作为第三表的该条记录的第一数量和第二数量中的一个，将第一数量变量和第二数量变量之一中的数量作为第三表中该条记录的第二数量；第四步骤，如果第一表和第二表中有效开始日期小的那个表中有未处理的记录，则将该表的第一数量或第二数量保存到第一数量变量和第二数量变量，以及将第一表的下一条记录或者第二表的下一条记录存储到第一记录变量或者第二记录变量，并保持第一记录变量和第二记录变量中有效开始时间大的那个记录变量不变；以及第五步骤，如果第一表和第二表中有效开始时间小的那个表中没有未处理的记录，则将第一表和第二表的另一表中的所有记录顺序输入到第三表。

根据本发明的一个实施例，在所述第三步骤，如果第一表中该条记录的有效开始日期等于第二表中该条记录的有效开始日期，则将两个表中相应记录的任意一个有效开始日期作为第三表中该条记录的有效开始日期，并且第三表的该条记录的第一数量和第二数量取当前的第一记录变量的第一数量和第二记录变量的第二数量。

根据本发明的一个实施例，在所述第三步骤，如果输出到第三表的记录不是第一条，则将当前输出的有效开始日期的前一天作为第三表中的前一条记录的有效结束时间。

根据本发明的一个实施例，在所述第四步骤，如果第一记录变量的有效开始时间等于第二记录变量的有效开始时间，则保存当前的第一数量和第二数量到第一数量变量和第二数量变量，以及从第一表和第二表中读取下一条记录到第一记录变量和第二记录变量，执行第三步骤。

根据本发明的一个实施例，所述第一表是事实表，所述第二表是维表。

根据本发明的一个实施例，所述第一表和第二表中的记录是按照主键排序的。

根据本发明的一个实施例，所述第一表和第二表中的记录是按照代理键、有效开始日期、有效结束日期升序排序的。

根据本发明的一个实施例，所述第一数量是交易数量，所述第二数量是信用卡编号。

利用本发明的方案，表连接的执行效率比SQL语句方式有着很大的提高，并且不会造成边界值的丢失。

附图说明

图1是根据本发明实施例的多表连接方法的流程图；

图2是在根据本发明实施例的多表连接方法的执行过程中使用的数据表的示意图；

图3示出了用来说明本发明的方法的原理所采用的抽象表结构的示意图；

图4示出了根据本发明的多表连接方法的执行效率与现有技术之间的比较；

图5示出了根据本发明实施例的多表连接方法对边界值的修正结果；

图6(A)、6(B)和6(C)分别示出了用来说明现有技术的事实表T1、维表T2和结果表T3；

图7示出了用来说明SQL方法造成的边界值丢失的问题的表。

具体实施方式

下面对照附图详细说明本发明的具体实施例。

【原理】

图3示出了用来说明本发明的方法的原理所采用的抽象表结构的示意图。如图3所示的表结构是上述问题的一个抽象，基于这个表结构讨论可以很容易地推广到此类问题的具体表结构中。

在图3中，表T1和T2表示连接操作的两个源表，T3表示连接后的结果表，表T1中SK表示代理健，A表示某个数值，EF表示有效开始日期，ET表示有效结束日期，表T2中B表示某个数值，其他三个字段含义与表T1中的一致。这里假设表T1和T2都已经按照SK(代理键)，EF(有效开始日期)，ET(有效结束日期)升序排序完毕，这个前提在实际中是很容易满足的。

根据本发明实施例的多表连接方法利用此类问题根据时间段作为连接条件的特点，只需要对两表各扫描一次就可以得到结果，也就是说当两表记录数分别是M，N时，总共只需要处理M+N条记录。

根据时间段连接的前提是两记录键值相同。但是，由于两表已经按照主键有序，因此如果两记录键值不相同，那么直接输出即可。为了清晰描述本发明的方法，下面假设两表待比较的记录SK相同，根据时间段关系进行比较。鉴于时间压缩的数据仓库加载方式，该前提条件是一定在此类应用中自然满足的，因此这个假设不影响本方法的可推广性以及后续对于方法效率的分析。

本发明的多表连接方法的原理如下所述。

第一步骤，初始化变量PreA，PreB保存前一次输出的A，B值。初始化变量Tmp1，Tmp2保存T1，T2当前处理的记录。

第二步骤，读入T1，T2两条记录到Tmp1，Tmp2。

第三步骤，比较Tmp1的EF和Tmp2的EF，输出较小的EF值作为T3.EF，T3的A或B取较小的EF值所对应记录的变量值(A或B)，另一个变量取保存变量PreA或PreB。如果T1.EF＝T2.EF，则输出任意EF值作为T3.EF，T3中的A，B取当前Tmp1，Tmp2的A，B。如果输出的不是T3的第一条记录，则取当前输出的EF减1作为T3中前一条记录的ET。

第四步骤，如果较小EF的来源表有未处理记录，则保存当前A或B到PreA或PreB，读入下一条记录到Tmp1或Tmp2，较大的EF所对应的Tmp1或Tmp2保持不变。如果Tmp1的EF等于Tmp2的EF，则保存当前A，B到PreA，PreB，两表都读入下一条记录至Tmp1和Tmp2，回到第三步骤。

第五步骤，如果较小EF的来源表中没有未处理记录，则顺序输出另一张表中所有记录到T3，T3中的另一个变量取PreA或PreB，然后结束。

【例子】

下面将对照附图，对本发明的技术方案进行详细说明。图1是根据本发明实施例的多表连接方法的流程图。图2是在根据本发明实施例的多表连接方法的执行过程中使用的数据表的示意图。

步骤101：方法开始。

步骤102：初始化变量PreA，PreB，Tmp1，Tmp2，其中变量PreA用于保存上一次处理的A，PreB用于保存上一次处理的B，Tmp1用于保存T1当前处理的记录，Tmp2用于保存T2当前处理的记录。

步骤103：读入T1的第一条记录到Tmp1，T2的第一条记录到Tmp2。

步骤104：判断Tmp1.EF是否大于Tmp2.EF。

步骤105：当步骤104中判断结果为“是”，即Tmp1.EF＞Temp2.EF时，输出Tmp2.EF作为T3.EF，Tmp2.B作为T3.B，PreA作为T3.A。如果不是T3第一条记录，则输出当前的Tmp2.EF-1作为T3上一条记录的ET，保存Tmp2.3到PreB。

步骤106：判断T2表是否还有未处理过的记录。

步骤107：当步骤106中判断结果为“是”，即T2中还有未处理过的记录，则读入T2的下一条记录到Tmp2，同时保持Tmp1不变，并转至步骤104。

步骤108：当步骤106中判断结果为“否”，即T2中没有未处理过的记录，则输出T1中剩余的记录到T3，将T1.EF作为T3.EF，T1.A作为T3.A，PreB作为T3.B，转至步骤121，方法结束。

步骤109：当步骤104中判断结果为“否”，即Tmp1.EF＜＝Temp2.EF时，判断Tmp1.EF是否小于Tmp2.EF。

步骤110：当步骤109中判断结果为“是”，即Tmp1.EF＜Tmp2.EF时，输出Tmp1.EF作为T3.EF，Tmp1.A作为T3.A，PreB作为T3.B，如果不是T3第一条记录，则输出当前的Tmp1.EF-1作为T3上一条记录的ET，保存Tmp1.A到PreA。

步骤111：判断T1表是否还有未处理过的记录。

步骤112：当步骤111中判断结果为“是”，即T1中还有未处理过的记录，则读入T1的下一条记录到Tmp1，同时保持Tmp2不变，并转至步骤104。

步骤113：当步骤111中判断结果为“否”，即T1中没有未处理过的记录，则输出T2中剩余的记录到T3，将T2.EF作为T3.EF，T2.B作为T3.B，PreA作为T3.A，并转至步骤121，方法结束。

步骤114：当步骤109中判断结果为“否”，即Tmp1.EF＝Tmp2.EF时，输出Tmp1.EF作为T3.EF，Tmp1.A作为T3.A，Tmp2.B作为T3.B，如果不是T3第一条记录，则输出当前的Tmp1.EF-1作为T3上一条记录的ET，保存Tmp1.A到PreA，保存Tmp2.B到PreB。

步骤115：判断T1表是否还有没有处理过的记录。

步骤116：当步骤115中判断结果为“否”，即T1中没有未处理过的记录，则输出T2中剩余的记录到T3，将T2.EF作为T3.EF，T2.B作为T3.B，PreA作为T3.A，转至步骤121，方法结束。

步骤117：当步骤115中判断结果为“是”，即T1中还有没有处理过的记录，则读入T1的下一条记录到Tmp1，同时保持Tmp2不变。

步骤118：判断T2表是否还有没有处理过的记录。

步骤119：当步骤118中判断结果为“否”，即T2中没有未处理过的记录，则输出T1中剩余的记录到T3，将T1.EF作为T3.EF，T1.A作为T3.A，PreB作为T3.B，转至步骤121，方法结束。

步骤120：当步骤118中判断结果为“是”，即T2中还有没有处理过的记录，则读入T2的下一条记录到Tmp2，同时保持Tmp1不变。转至步骤104。

步骤121：方法结束。

为了对上述方法描述有一个更直观的认识，图2是针对表T1和T2数据所给出的方法执行过程例子，由于SK＝1与SK＝2记录执行过程一致，因此图2中只给出了SK＝1的记录的执行情况。图中标号1至6显示了每一次对记录操作的过程。图2的左面显示了4个变量的取值变化情况，箭头右边代表了每次的输出结果。因为表1，表2中SK＝1的记录各有三条，因此总共需要执行6步就能得到最终的结果。

【比较】

通过上面的描述，可以看出本发明的多表连接方法的执行效率比SQL语句方式有着很大的提高(因为第一种原始的解压缩处理方式的效率显然没有SQL语句高，因此不将TCJ方法与该方法进行比较)。

首先，本发明的方法充分利用了两表根据时间段进行合并的这个前提条件，只需对两表遍历一次就可以得到结果。因此只需要对M+N条记录进行处理，本发明方法的复杂性为O(M+N)，与SQL方式相比具有明显的优势。

与背景技术部分中对于SQL方法效率的分析相对应，对于本发明的方法根据更一般的情况可以得到如下分析过程。

假设事实表中不同SK的取值有M种，每一种SK的取值分别有N_i条记录(即第i个SK有N_i条记录，i＝1，2，3……M)。

假设维表中不同SK的取值有P种，每一种SK的取值分别有Q_i条记录(即第i个SK有Q_i条记录，i＝1，2，3……P)。

这样，本发明的方法连接后形成的结果表共有：

条记录，其中s为N与Q中相同SK的数目。

则TCJ方法与SQL方法的效率之比为

为了简化上式，取N’，Q’分别表示N_i，Q_i的平均值，则上式可简化为 $\frac{S(N^{\′}\×Q^{\′})}{S(N^{\′}+Q^{\′})}=\frac{N^{\′}\×Q^{\′}}{N^{\′}+Q^{\′}},$ 而一般情况下事实表N’值远大于维表Q’值，则上式可进一步简化为 $\frac{N^{\′}\×Q^{\′}}{N^{\′}}=Q^{\′}.$

而如果考虑索引的因素，从背景技术部分中的分析可以看到SQL方法访问表的次数约为M×log₂ ^N+M，而本发明方法访问表的次数为：M+N，两者效率之比为

当N＝1时，log₂ ^N取到最小值为0，则效率之比为

一般情况下M＞＞1，则两者效率值比约等于1，说明当维表中每个SK下只有一条记录时，两种方法效率相等。如果有M＞＞N，则上式可简化为 $\frac{M\×{\log }_2^N+M}{M}=\frac{M({\log }_2^N+1)}{M}={\log }_2^N+1,$ 说明当采用了索引进行优化后，SQL方式的效率有所提高，但是本发明的方法仍然可以比SQL方式提高log₂ ^N+1倍。

实验采用了SAS V8.2实现了本发明的方法，在Intel P42.8GHZ，512MB RAM，Windows 2000Professional环境中运行，与SQL语句方式进行了比较，SQL语句采用的是对所有SQL实现方式经过分析后得到的最简洁高效的一种：

select

      a.sk，

      a.A，

      b.B，

      max(a.ef，b.ef，&MIN_DATE)as ef，

      min(a.et，b.et，&MAX_DATE)as et

from tl as a

      left join t2 as b

      on(a.sk＝b.sk)and

      (a.ef＜＝b.et and a.et＞＝b.ef)；

实验结果如图4所示的数据表。从图4的表中可以看到，本发明的方法的执行速度比SQL语句提高了3倍至45倍，提高的倍数与维表中每个SK对应的记录数成正比关系。

此外，使用SQL语句方式得到的结果在每个SK的第一条记录会造成丢失，而本发明的方法能得到正确结果。例如对于图6(A)和图6(B)所示的表T1和T2中的数据，对于SK＝1的记录，使用SQL语句得到的结果表中第一条记录如图7所示。相反，使用本发明的方法得到的正确结果如图5所示。因此，本发明的方法完全可以消除SQL方法造成的边界值丢失问题。

以上所述，仅为本发明中的具体实施方式，但本发明的保护范围并不局限于此，任何熟悉该技术的人在本发明所披露的技术范围内，可轻易想到的变换或替换，都应涵盖在本发明的包含范围之内。因此，本发明的保护范围应该以权利要求书的保护范围为准。

Claims (8)

1、一种将具有多条记录的第一表与具有多条记录的第二表连接以形成第三表的多表连接方法，所述第一表和第二表的每一条记录都具有各自的有效开始日期、有效结束日期、第一数量和第二数量，所述的方法包括步骤：

第一步骤，初始化分别用来暂存第一数量和第二数量的第一数量变量和第二数量变量，以及分别用来暂存第一表的记录和第二表的记录的第一记录变量和第二记录变量；

第二步骤，将第一表的一条记录和第二表的一条记录分别读入第一记录变量和第二记录变量；

第三步骤，将第一记录变量的有效开始日期和第二记录变量的有效开始日期中较小的有效开始日期作为第三表中一条记录的有效开始日期，并将较小有效开始日期所在的记录中的第一数量和第二数量分别作为第三表的该条记录的第一数量和第二数量，将第二数量变量的数量作为第三表中该条记录的第二数量；

第四步骤，如果第一表和第二表中有效开始日期小的那个表中有未处理的记录，则将该表的第一数量和第二数量分别保存到第一数量变量和第二数量变量，以及将第一表的下一条记录或者第二表的下一条记录存储到第一记录变量或者第二记录变量，并保持第一记录变量和第二记录变量中有效开始时间大的那个记录变量不变；以及

第五步骤，如果第一表和第二表中有效开始时间小的那个表中没有未处理的记录，则将第一表和第二表的另一表中的所有记录顺序输入到第三表。

2、如权利要求1所述的多表连接方法，其特征在于，在所述第三步骤，如果第一表中该条记录的有效开始日期等于第二表中该条记录的有效开始日期，则将两个表中相应记录的任意一个有效开始日期作为第三表中该条记录的有效开始日期，并且第三表的该条记录的第一数量和第二数量取当前的第一记录变量的第一数量和第二记录变量的第二数量。

3、如权利要求1所述的多表连接方法，其特征在于，在所述第三步骤，如果输出到第三表的记录不是第一条，则将当前输出的有效开始日期的前一天作为第三表中的前一条记录的有效结束时间。

4、如权利要求1所述的多表连接方法，其特征在于，在所述第四步骤，如果第一记录变量的有效开始时间等于第二记录变量的有效开始时间，则保存当前的第一数量和第二数量到第一数量变量和第二数量变量，以及从第一表和第二表中读取下一条记录到第一记录变量和第二记录变量，执行第三步骤。

5、如权利要求2～4之一所述的多表连接方法，其特征在于，所述第一表是事实表，所述第二表是维表。

6、如权利要求5所述的多表连接方法，其特征在于，所述第一表和第二表中的记录是按照主键排序的。

7、如权利要求5所述的多表连接方法，其特征在于，所述第一表和第二表中的记录是按照代理键、有效开始日期、有效结束日期升序排序的。

8、如权利要求5所述的多表连接方法，其特征在于，所述第一数量是交易数量，所述第二数量是信用卡编号。

Images