SPL 时间键

时间键介绍

维表的数据相对比较固定,但仍然也会有变动。比如某客户的所在城市发生了变化:2020 年 5 月 15 日,city 从 New York 变更为 Chicago。

在订单表关联客户表时,这个日期前的订单应该关联老的客户记录,也就是 city 仍然是 New York;而这个日期及以后的订单,则应该关联新的客户记录,即 city 应该是 Chicago。

也就是说每个订单都要找到正确的客户记录:订单发生时(订单日期)最新的客户记录。

关系数据库中常常使用快照表或拉链表来实现维表数据变动的需求。但这样做存在计算复杂或增加存储空间占用等问题。

SPL 在普通主键之外增加一种特殊主键:时间键。时间键均为时间日期类型,在按主键查询时做特殊处理,能够很好的解决维表数据变动的问题。

相应的,我们把普通主键称为基本键

例如:增加了时间键(记录生效时间 edate 字段)的客户表大致是下图这样:

..

客户表主键是客户号和记录生效时间两个字段,记录生效时间是时间键。客户 B20101 在 2020-05-15 时所在城市发生了变动,所以有两条记录,生效时间 edate 和 city 不相同。

现在我们要查询指定日期的最新客户记录,采用时间键的代码大致是这样:


A

B

1

=T("customer.btx")

>A1.keys@t(cid,edate)

2

=A1.find('B20101',date(2020-05-01))

3

=A1.pfind('B20101')

A1:读入客户表,B1 定义联合主键 cid 和 edate,@t 选项就表示主键的最后一个字段 edate 是时间键,cid 则是基本键。

如果业务需要,也可以用精度更高(比如秒或毫秒)的日期时间类型字段作为时间键。

A2:查找指定客户和日期的记录。按照 cid 找到的并不是唯一记录,SPL 会在其中找到时间键不大于且最接近 2020-05-01 的记录,这里就是 city 是 New York 的记录。

可以看到按时间键查找时并不是等值查找,而是找不大于且最接近指定时间的记录

A3:查找指定客户和时间记录的序号位置。此处省略了时间参数,SPL 自动以当前时间作为时间键参数,找到截止到当前时间的最新记录,这里将返回 city 是 Chicago 的记录的序号。

外键关联中的时间键

客户表(维表)增加了时间键后,再用订单表(事实表)的客户号 o_cid 来关联客户表,每个订单的 o_cid 在客户表中都可能找到多条记录,需要从中找到生效日期 edate 不大于且最接近订单日期 odate 的记录。

也就是说日期字段不是相等关系,不能直接使用订单的客户号、订单日期与客户的客户号、生效日期做等值关联。

当维表比较小可以全内存时,用带时间键的序表存储维表。时间键建立在序表索引上,switch/join 支持对有时间键的维表做外键关联计算。我们也可以在 fjoin 中使用 find 表达式来实现这种计算,代码大致是这样:


A

B

1

=file("customer.btx").import@b(cid,edate,city)

>A1.keys@t(cid,edate)

2

=file("orders.ctx").open().cursor(o_cid,odate,amt)

3

=A2.fjoin(A1.find(o_cid,odate),city)

4

=B2.groups(city;sum(amt),count(~))

A1:读入客户表,B1 定义联合主键 id 和 edate,最后一个主键 edate 是时间键。

A2:建立订单表的游标。

A3:将游标和 A1 中的客户表关联起来,订单表的关联字段是 o_cid 和 odate,客户表则是主键 cid、edate。

这时,find 函数除了找和 o_cid 相等的 cid,还要比较交易时间 odate 和 edate,找到 edate 不大于且最接近 odate 的值,其所在记录才是对应的关联记录,也就是截止到 odate 的最新客户记录。

A4:用关联的结果游标按照客户所在城市分组汇总交易金额和交易笔数,这时候就不必再关心时间键了。

假设事实表和维表关联不上的记录需要过滤掉,可以使用 fjoin@i。这时,如果事实表是组表游标,建议使用组表游标关联过滤机制来实现外键关联、过滤,性能会更好,代码大致是这样的:


A

B

1

=file("customer.btx").import@b(cid,edate,city)

>A1.keys@t(cid,edate)

2

=file("orders.ctx").open().cursor(o_cid,odate,amt; o_cid=A1.find(o_cid,odate))

3

=B2.groups(o_cid.city;sum(amt),count(~))

A2:将原来 fjoin 中的表达式写到游标前过滤条件中,find 函数会将查找到的维表记录赋值给 o_cid,找不到则过滤掉订单。

A3:这时 o_cid 就可以用点操作符“.”来引用维表的字段了。

A2 中过滤表达式的执行是在生成订单记录之前,不符合条件的订单记录将不被生成。这是组表游标关联过滤机制性能好于 fjoin@i 的原因,详细介绍参见:SPL 新关联计算

主键关联中的时间键

switch/join/fjoin 可以实现事实表和维表(包含时间键)的关联,但要求维表必须全内存。如果维表比较大需要外存,就不能用这些函数来关联了。SPL 目前还没有提供外存维表上的时间键机制。

我们知道,主子表关联时,可以把主表看成是子表的维表。而这个主表也可能存在时间键的问题。

比如我们用客户号、订单号做订单表主键,客户表和订单表就是主子关系。主表中存在数据变动的情况,比如客户城市变更。这时候,每个子表记录(订单)都要找到订单发生时(订单日期)的最新客户数据。

对于这种情况,SPL 允许在作为维表的主表上建立时间键,并使用 pjoin 完成有时间键的外存主子关联。

注意:join/joinx 不支持这种主键关联。

将主表(客户)、子表(订单)存储成组表,并在主表上定义时间键“记录生效时间 edate”,再使用 pjoin 实现子表与主表的关联。

生成客户组表、订单组表的代码大致是这样的:


A

1

2

3

=file("customer.ctx").create@t(#cid,#edate,city,...)

4

=file("orders.ctx").create(#o_cid,#odate,oid,amount,...)

5

=A3.append(A1)

6

=A4.append(A2)

A1、A2 准备从数据来源中读取客户、订单数据,两者都要按照客户号、日期排好序,相当于经过 sortx 计算。

A3:create@t 表示维(#cid、#edate)构成键,且最后一个键 edate 是时间键。

A4:订单表要用客户号、订单日期构成维,表示按照这两个字段有序。

准备好数据后,用下面这样的代码完成关联:


A

1

=file("customer.ctx").open().cursor(cid,edate,city)

2

=file("orders.ctx").open().cursor(o_cid,odate,amount)

3

=A2.pjoin@t(o_cid:odate,amount;A1,cid:edate,city)

4

=A4.groups(city;sum(amount))

A1:SPL 自动发现客户组表有时间键 edate。

A3:pjoin 计算时使用 @t 选项,会自动处理 A1 上的时间键 edate。

也就是子表订单表中的每条记录的 o_cid,在主表客户表中可能会找到多条记录。这时候 SPL 会自动从中找到 edate 不大于且最接近 odate 的记录,就是订单对应的最新客户记录。

由于订单和客户是多对一关系,所以这条客户记录会按照对应订单记录数被复制成多条,和这些订单记录完成关联。

pjoin 仅支持在子表关联主表时,主表上有时间键的情况。实际上这时候主表就相当于维表,pjoin 仍然是在解决维表数据变动的问题。