观察一些大用户的BI系统，经常会发现数据仓库中有很多快照表。例如，在某交易业务的BI系统中，交易明细表非常庞大，按月存储到多个段表中。还有一些比较小的表，在计算的时候需要关联交易明细表，比如客户表，员工表，商品表等等。在每个月末，这些表的完整数据将被存储为快照表，用于匹配当月的交易明细段表。为什么会有这么多看似冗余的快照表？这张交易明细表通常被称为事实表，是用来存储事件的数据表，数据量会随着时间的推移不断增长。除了交易明细表，订单表、保单表、银行账户访问记录表等也是事实表。事实表中会有一些代码字段与其他表相关联。比如上面提到的交易明细表，通过客户号、员工号、产品号关联到客户表、员工表、产品表。又如订单表通过商品号字段关联到商品表，银行账户出入记录表通过账号字段关联到账户表，等等。这些与事实表关联的表称为维度表。下图中的交易明细表和客户表构成了事实表和维表的关联关系：事实表关联维表的目的是利用维表的字段参与计算。例如，交易明细表与客户表关联后，可以根据客户所在城市对交易金额和交易笔数进行分组汇总。维度表的数据相对固定，但还是会被修改。维表数据发生变化后，不会影响事实表中新生成的数据。事实表以前的历史数据可能与维度表的新数据不匹配。这时候如果用事实表的旧数据关联维表的新数据，就会出错。例如，客户James，号码为B20101，原居住在纽约，产生了一些交易记录。2020年5月15日，詹姆斯搬到了芝加哥，一些新的交易细节产生了。如果我们直接把customer表中James所在的城市改成Chicago，那么当交易金额按customercity分组时，James之前在纽约的交易也会算到Chicago，这显然是错误的。错误的原因是，詹姆斯的交易记录中统计的是哪个城市与时间有关，算成纽约还是芝加哥是错误的。如果不特别处理这个问题，BI系统中的统计值（针对历史数据）和ERP系统中的统计值（针对当时的数据）会不匹配，出现这种错误很难排除故障。快照表就是为了解决这个问题而产生的。定期（比如每个月末）生成相关数据表的快照，保存事实表中某一时间段（比如一个月）的数据和维度表当时的完整数据，以供后续使用统计分析计算，保证事实表始终与同期维表关联，统计结果不会出错。但是这样会造成大量冗余的维表数据，增加数据库的存储容量；通常有多个维表，每个维表又有多个快照表，这会使表之间的关系变得极其复杂。大大增加了系统的复杂度。这样一来，计算代码也会变得复杂。比如有一张交易明细表，每个月对应一批维表快照。如果要统计一年中客户所在城市分组的交易金额和交易笔数，需要编译十二个月的交易明细表，关联各自的维度表快照后，进行十一次UNION.这只是一个简单的分组和汇总，更复杂的统计分析计算会产生非常长和复杂的SQL语句。维护难，更不用说性能调优了。为此，许多使用快照方案的BI系统禁止在较长时间内进行统计分析和计算。严重的只能选择一个时间段（一个月）的数据进行计算。而且快照方案并没有完全解决维表变化带来的查询不准确的问题。BI系统不可能在维度发生变化时立即生成快照（快照过多会造成存储容量巨大），一般只会周期性地生成快照表。这样，如果两次生成快照的时间点之间维表数据发生变化，仍然会出现计算错误。假设在每个月的最后一天生成交易明细表和客户表的快照。詹姆斯5月15日搬家，5月31日生成快照时，詹姆斯所在城市将保存为芝加哥。而在6月1日之后，5月的查询统计将基于此快照。那么詹姆斯在5月1日到15日这段时间的交易，本来应该算纽约的，现在都算芝加哥了，还是会出现错误，只是错误量比较小。另一种解决方法是生成包含事实表和维度表的宽表。将交易明细表与客户表数据链接起来，生成宽交易表。这张宽表中的客户姓名、所在城市等不会因为客户维度表中的数据变化而受到影响，系统的数据结构可以保持相对简单。但是宽表只能周期性生成（实时生成宽表记录会降低事务系统的性能），仍然会存在两个生成点之间维表的上述变化导致的错误。而且，由于事实表和维度表是多对一的关系，事务宽表中的客户数据会存在大量冗余，导致事实表膨胀，空间占用会远超过快照解决方案。再者，宽表结构的维护非常不灵活，尤其是需要增加字段和考虑处理大量历史数据的时候。这就需要在创建宽表的时候尽可能多的添加字段，而一个大而完整的宽表会占用更多的空间。事实上，维表数据虽然发生了变化，但是变化会比较小，变化量通常比总数据量小一个到几个数量级。利用这个特点，我们可以设计一个成本更低的方法来解决这个问题。开源数据计算引擎SPL提供的timekey机制就是利用了这个特性，可以方便准确的解决维表数据变化的问题。具体方法是在维度表中增加时间字段，与原主键形成联合主键。该字段称为时间密钥。事实表与维表关联时，使用原来的外键字段加上合适的时间字段与新维表的联合主键关联。时间键的关联方式与原来的外键不同。不是通过“相等”的关系来判断，而是通过查找“指定时间之前的最新记录”来关联。仍然以上述交易明细表和客户表为例，后者需要添加一个生效时间字段edate，如下图：edate存储这条记录生效的时间，即维表生效的时间变化。例如客户James搬家后，客户表会变成如下图所示：图中，客户James在搬家之前只有一个维度表记录i。搬家当天新增一条记录ii，生效日期为搬家时间2020-05-15。此时SPL将交易明细表与客户表关联起来。除了比较cid和id是否相等外，还会比较交易时间ddate和客户记录生效时间edate，找到不大于ddate的edate最大值，对应的记录就是对应的记录。相关记录（即该时间点之前的最新记录）。这样，James搬家前的交易记录日期早于2020-05-15，会关联到客户表中生效日期为2017-02-01的记录i，所以这些交易明细会被算作纽约。但是，如果James搬家后的交易记录日期等于或晚于2020-05-15，则会关联到客户表中生效日期为2020-05-15的记录ii，这些交易明细将是算作芝加哥。可以看出采用时间密钥机制后的关联结果是符合实际情况的。这是因为我们添加维表记录，存储在维表发生数据变化的有效时间，这样可以保证后续计算的正确性。这样就可以避免周期性生成快照或宽表时存在的问题，两个生成时间点之间不会出现计算错误。而且，由于维表的变化量很小，添加了变化信息的维表的大小和原来的维表基本一样，存储容量不会有太大的增加。理论上也可以在关系数据库的维度表中加入类似时间的字段，但是没有办法表示这种关系。时间键的关联显然不是常规的等价JOIN。使用非等价JOIN需要复杂的子查询来选择最新的维度表记录，然后将它们关联起来。语句非常复杂，难以保证执行性能。因此，在关系型数据库中，只能采用快照或宽表等解决方案。SPL实现timekey机制的代码也很简单，大致如下：AB1=T("customer.btx")>A1.keys@t(id,edate)2=file("detail.ctx").open().cursor()=A2.switch(cid:ddate,A1)3=B2.groups(cid.city;sum(amt),count(~))A1读取customer表，B1定义jointprimarykeyid和edate，@s选项表示主键的最后一个字段是时间键。如果业务需要，也可以使用精度更高的日期时间类型的字段作为时间键。A2为交易明细表创建游标。B2将游标关联到A1中的customer表，detail表的关联字段为cid和ddate，customer表为主键。它的使用方式与没有时间键的普通维度表相同。A3使用关联的结果游标，按照客户所在城市对交易金额和交易笔数进行分组汇总。这个时候就不用关心timekey了。SPL内置时间键处理机制，计算性能与没有时间键的维度表有很大区别。与普通维表类似，关联时可以随机选择时间间隔进行统计，不存在快照表难以跨越时间段的问题。SPL提供的timekey可以轻松解决维表数据变化的问题。事实表保持不变，只是在维度表中增加了时间字段，并记录了变化。在保证统计结果准确和计算性能的前提下，避免保留大量快照表，降低系统复杂度；它还可以避免宽表的大量数据冗余，并保持灵活的系统结构。SPL下载地址：http://c.raqsoft.com.cn/article/1595816810031SPL开源地址：https://github.com/SPLWare/esProc
