SPL 中的关联计算 - 内存篇

SPL 中的关联计算和 SQL 有很大不同,SQL 把连接定义为笛卡尔积后再过滤。SPL 也提供了这种运算,但在绝大多数场景下都有更好的替代方法,因而并不推荐使用。

应用 SPL 编程实现关联计算,要把连接细分成不同的类型,再选择对应的函数写出代码。

关联计算分类

..

图中的等值连接是指关联表的对应字段相等为过滤条件的连接。在实际应用中,非等值连接很少见,而且大多数可以转换成等值连接处理,我们在这里主要讨论等值连接。

等值连接包含三种类型:外键连接主键连接其他连接

外键连接是指 A 表的某个字段和 B 表的主键等值连接。A 表称为事实表,B 表称为维表。A 表中与 B 表主键关联的字段称为 A 指向 B 的外键,B 也称为 A 的外键表。

主键连接则是指 A 表的主键和 B 表的主键或部分主键等值连接。

两种连接都会有主键参与,这里的主键不一定是物理上的主键,也可以是逻辑上的主键。

除此之外的“其他连接”是指没有主键参与的等值连接,通常是业务逻辑和数据发生了错误才会出现。

主键连接再细分为同维连接主子连接。表 A 的主键与表 B 的主键关联称为同维连接,A 和 B 互称为同维表。表 A 的主键与表 B 的部分主键关联称为主子连接,A 称为主表,B 称为子表

图中绿色的三种连接方式“外键连接、同维连接、主子连接”涵盖了绝大多数等值连接的情况,可以说几乎全部有业务意义的等值连接都属于这三类,把等值连接限定在这三种情况之中,几乎不会减少其适应范围。

SPL 为外键连接和主键连接提供了不同的函数实现,应用连接运算时,先要明确区分连接的类型,并找到参与的主键。

如果需要连接的表数据量不是非常大,都可以装入内存,这种情况称为内存连接。这一篇先来看内存连接的编程方法。

外键连接

以订单表和员工表的连接为例:

..

订单表的员工字段 eid 和员工表的主键字段关联,订单表是事实表,员工表是维表。

订单表的 eid 是订单表指向员工表的外键,员工表是订单表的外键表,订单和员工是多对一关系。其他字段还有:订单日期 odate、客户号 cid、员工姓名 name、员工所在部门号 did。

将两个表做外键连接的目的,是在计算中既要用到事实表字段,也要用到对应的维表字段。比如,按照员工姓名和订单日期来过滤订单数据,就要用到员工表的姓名字段(name)和订单表的日期字段(odate)。

T.switch函数

在事实表和维表都可以装入内存的前提下,T.switch 函数将 T 表(事实表)的外键字段转化为维表对应记录的地址,计算时通过地址直接引用维表记录的字段。这种方法称为外键地址化

用这种方法可以将订单表中的员工(eid)字段转换为员工表的一条记录,计算时就能用 eid.name 来引用这条记录中的姓名字段。其中“点操作符”的含义是地址引用。这样,按照员工姓名和订单日期来过滤订单数据的代码就是下面这个样子:


A

B

1

=file("employee.btx").import@b().keys(eid)

=file("orders.btx").import@b()

2

>B1.switch(eid,A1:eid)


3

=B1.select(eid.name=="Tom" && odate=…)

A1 将员工表读入内存,并定义主键为 eid。B1 将订单表读入内存。

A2 将订单表的 eid 字段转换为员工表对应记录的地址,也就完成了外键地址化。因为员工表的主键是确定的 eid,所以 A2 可以简化成 orders.switch(eid,A1)。

A3 中可以很自然的通过 eid 来引用员工表中的字段 name。

被 T.switch 处理后,这两个表的记录感觉真地连到了一起,事实表 T 的字段取值是维表的记录,就能通过这个字段去引用维表的字段了。

根据 SPL 对外键关联的定义,维表的关联字段必须是主键。这样,事实表中每一条记录的外键字段关联的维表记录就是唯一的。也就是说,订单表中每一条记录的 eid 字段唯一关联一条员工表中的记录。这也就保证了,对于订单表中的每一条记录,eid.name 都有唯一的取值,可以被明确定义,因此 SPL 才能做到外键地址化。

T.switch实现多层外键连接

一个事实表通常有多个维表,并且维表还会有很多层。这时,可以更明显的体现出外键地址化在代码书写方面的优势。举例如下图:

..

对于这种多个或者多层的外键关联,可以逐个完成外键地址化。订单表的客户号字段 cid 是客户表记录的地址,客户表的 cityid 字段是城市表记录的地址,依次类推。这样,订单对应的客户所在国家名称,可以写作 cid.cityid.stateid.countryid.name,员工所在部门名称可以写作 eid.did.name。

这样的写法会大大简化代码,我们通过例子来感受一下。假设要对客户所在国家为 "US",员工所在部门为 "Sales" 的订单,按照客户所在州和城市分组统计订单数量。那么,SPL 代码是这个样子的:


A

B

1

>state.switch(countryid,country)

>city.switch(stateid,state)

2

>customer.switch(cityid,city)


3

>employee.switch(departmentid,department)

4

>orders.switch(cid,customer;eid,employee)

5

=orders.select(cid.cityid.stateid.countryid.name=="US" && eid.did.name=="Sales")

6

=A5.groups(cid.cityid.stateid,cid.cityid;count(1))

这里省略了读入各表、设置主键,并赋值给对应变量的代码,例如:

>country=file("country.btx").import@b().keys(cid)

A1 使用 T.switch 函数,将 state 表中的 countryid 字段,转换成 country 表中记录的地址。这样就可以通过 country 引用 country 表中的字段了,也就完成了 country 表的外键地址化。

B1 再将 city 表中的 stateid 字段,转换为 state 表中的一条记录,从而实现了 city、state 和 country 三个表多层外键的地址化。

A2 到 A3,依次完成了 customer 等四个表和 employee 等两个表多层外键的地址化。

A4 中,orders 表有两个外键,T.switch 函数可以同时转换为两个表记录的地址。

从 A1 到 A4,可以看作外键地址化的准备阶段。完成准备后,A5、A6 就可以很自然的写出过滤条件和分组表达式了。

更重要的是,表的主键和外键通常是固定的,完成外键地址化的各个表可以重复使用,计算时就只要写类似 A5 和 A6 的代码就可以了,非常便捷。我们可以在系统启动时把数据表读入内存后做好外键地址化,这个动作称为预关联

通常,外键字段的取值一定会在维表的主键取值范围内,但有时候也会出现超出的情况。比如新员工在尚未确定所属部门前,did 字段都被填成了 0,部门表中没有对应的记录。这时可以用 T.switch 函数的不同选项得到不同的期望结果:


A

B

1

=file("department.btx").import@b().keys(eid)

=file("emplopyee.btx").import@b()

2

=B1.switch(did,A1)

=A2.count(did.name==null)

2

=B1.switch@i(did,A1)

=A2.count(did.name==null)

2

=B1.switch@d(did,A1)

=A2.count(did.name==null)

注意连续三个行号“2”,含义是这三个代码要分别执行,不能顺序执行。因为执行后 B1 将会被改变,再执行另一种 A2 就得不到希望的结果了。

T.switch不带任何选项,找不到外键对应的维表记录时会将外键填成 null,之后再引用其字段时并不会报错,只是会返回 null。因此 A2 中 did 本来为 0 的员工记录,did 字段会被转换为 null。相应的 B2 将是 did 原为 0 的条数。

T.switch@i会删除找不到外键对应记录的事实表记录,保证事实表外键都正确转换成维表记录。所以 A2 中 did 本来为 0 的员工会被删去。相应的 B2 将是 0。

T.switch@d则只保留找不到外键对应记录的事实表记录,并且无法再做外键转换。也就是说 A2 中 did 本来不是 0 的员工会被删除,保留下来的记录 did 不做转换,仍然是 0。相应的 B5 会报出错误,因为数值 0 不是地址,不能用“点操作符”。

T.join函数

有些情况下,事实表外键字段的某些值可能会在维表中找不到对应记录。这时候 T.switch 函数会将外键转换为空,也就找不到原来的外键值了。

比如有些员工记录中的部门号写错了,找不到对应的部门。使用 T.switch 计算员工表 empolyee 和部门表 department 连接时,这些员工记录中的部门号字段 did 就变成空了。如果必须保留原外键值,可以用 T.join 函数,将维表的某些字段临时拼接到事实表 T 中。例如,将部门名称 name、经理 manager 字段拼接到员工表的写法是:


A

1

=employee.join(did,department,name:dname,manager:dmanager)

A1 中新建了一个表,其中包含员工表全部字段,以及部门表中的 name 和 manager 字段。

T.join 的参数依次为:did 外键、department 维表、维表 name 字段重命名为 dname、维表 manager 字段重命名为 dmanager。

我们还可以利用 T.join 函数,将维表的记录地址作为一个新字段和事实表拼接起来,这样既能实现外键地址化,也能避免上面说的外键字段值丢失问题。对于员工表和部门表,用这样的办法查询所在部门为 "Sales" 的员工的写法是这样的:


A

1

=employee.join(did,department,~:did_fk)

2

=A1.select(did_fk.name=="Sales")

A1 中新建一个表,除了 employee 的字段之外,还增加了一个 did_fk,储存 department 表对应记录的地址。如果找不到对应记录,就为空。相当于将外键字段 did 复制了一份,并完成了外键地址化。

A2 中用 did_fk 来引用部门表的字段 name。

和 T.switch 函数不同,T.join 函数不直接转换原表的字段,而是产生一个新的表。所以,在做多层外键地址化时要注意次序,那些既是事实表又是维表的数据表,要先关联自己的维表后得到新的数据表,然后再用来被事实表关联。

T.join 实现多字段关联

T.switch 函数不支持多个字段做维表主键和事实表外键的情况,也需要用 T.join 函数实现。假设专业号、班级号两个字段是学生表指向班级表的外键,如下图:

..

用 T.join 函数,将 teacher 字段拼接到学生表中的代码写法是:


A

1

>class.keys(mid,cid)

2

=student.join(mid:cid,class,teacher)

A1 给 class 表指定联合主键。A2 中新建表,在 student 表基础上,增加 class 表的 teacher 字段,填入班主任。T.join 函数的第一个参数 mid:cid 表示两个关联字段,要用冒号分开。

和 T.switch 函数一样,T.join 针对外键值在维表找不到的情况,也提供了 @i 和 @d 选项。仍以新员工 did 为 0,且在部门表中找不到记录为例:


A

B

1

=file("department.btx").import@b().keys(eid)

=file("emplopyee.btx").import@b()

2

=B1.join(did,A1,name:dname)

=A2.count(dname==null)

3

=B1.join@i(did,A1,name:dname)

=A3.count(dname==null)

4

=B1.join@d(did,A1)

=A4.count(dname==null)

注意 A2、A3、A4 是可以顺序执行的,这是因为T.join 是生成新表,执行后 B1 不会被改变,还可以继续执行。

T.join不带任何选项,A2 中 did 本来为 0 的员工记录,新增的 dname 字段值是 null。相应的 B2 将是 did 原为 0 的记录条数。

T.join@i的结果 A3 中 did 本来为 0 的员工记录会被删去。相应的 B3 将是 0。

T.join@d的结果 A4 中 did 本来不是 0 的员工记录会被删除。@d 选项要求不能有name:dname 参数,否则会失效,计算结果和没有 @d 选项一样。相应的 B4 会报出错误,因为结果中没有 dname 字段。

主键连接

同维连接

员工表和经理表之间就是同维连接:

..

有部分员工是经理,而经理比普通员工多一些属性,比如岗位津贴(allowance),所以要另用一个经理表来保存。员工表的主键 eid 和经理表的主键 mid 关联,这两个字段的值都是员工编号。

join 函数

join 函数和前面的 T.join 是不同的两个函数,我们先看 join 的用法,再介绍两者的不同。

假设要查询每个员工(包括经理)的编号、姓名和总收入(工资加津贴),代码是这样的:


A

B

1

=file("employee.btx").import@b().keys(eid)

=file("manager.btx").import@b().keys(mid)

2

=join@1(A1:e,eid;B1:m,mid)


3

=A2.new(e.eid:eid,e.name:name,e.salary+m.allowance:income)

A2 中的 join 函数返回一个新表,新表的两个字段分别为 e 和 m。它会将 A1 中的员工记录和 B1 中的经理记录连接起来,也就是把 eid 和 mid 相等的记录地址分别填入 e 和 m 字段,作为新表的一条记录。join 函数的 @1 选项稍后介绍。

字段 e 和 m 的值是记录地址,有点像外键被 T.switch 函数转换后的结果。

因为两个表已经设置了主键,A2 可以简化为:=join@1(A1:e;A2:m)。

A3 利用 A2 的结果,生成包含 eid、name 和 income 三个字段的新表。由于 e 和 m 都是记录,所以可以用 e 来引用员工的编号、姓名字段填入新表的 eid 和 name 字段,再用员工的工资加上 m 引用经理的津贴字段得到新表的 income 字段。

这里用新表 A2 的两个字段 e 和 m 来引用表 A1 和 B1 各自的字段,和外键地址化一样,很自然、容易理解。

例子中,员工表包含了经理,但并不是每个员工都是经理。也就是说,很多员工在经理表中是找不到对应记录的。这时候新表的 m 字段应该填上空,这样,员工的总收入就等于工资。

这时join@1将会以左边的关联表(join 函数的第一个参数)为基准,其他关联表中如果有与左边第一个表关联字段相同的记录,则关联上,如果没有,则会填成空。这种以左边第一个表为准的连接方式称为左连接。@1 是数字 1 不是字母 l,它表示以第 1 个为准。由于同维表是通过主键关联的,结果序表的关联字段值和左边关联表一致,其长度也和左边关联表相同。三个或者更多个表左连接时,同样以左边第一个表为准。

SPL 没有提供右连接,因为只要把函数参数换个位置就可以了。

同维连接也适用于临时产生的表,只要满足关联字段是主键的条件就可以。比如下图中的合同表、回款表和发票表:

..

现在想统计每一天的合同额、回款额以及发票额。我们可以把三个表分别按照日期分组汇总金额,并将分组结果看作三个表。其中的分组字段日期具有唯一性,天然能构成这些表的主键。这三个表主键相同,符合同维表的定义。三个表按照主键(日期)关联,就是同维连接。因此,这个问题同样可以用 join 函数来解决,代码是这个样子:


A

B

1

=file("contract.btx").import@b()

=A1.groups(cdate;sum(amount):amount)

2

=file("payment.btx").import@b()

=A2.groups(pdate;sum(amount):amount)

3

=file("invice.btx").import@b()

=A3.groups(idate;sum(amount):amount)

4

=join@f(B1:c;B2:p;B3:i)


5

=A4.new(ifn(c.cdate,p.pdate,i.idate):adate,c.amount:camount,p.amount:pamount,i.amount:iamount)

A1 到 B3,依次将三个表按照日期汇总金额,汇总结果 B1、B2、B3 是以日期为主键的表。

A4 中,将 B1、B2、B3 做连接,生成一个新表,其三个字段 c、p、i,分别存储 B1、B2、B3 的记录。

A5:利用 A4 引用三个表的字段,再生成一个新表,得到想要的结果。

从需求上看,合同额、回款额和发票额都为空的日期不会出现在结果中了,但只要有一个不为空就应该放到结果中。这里采用的join@f会兼顾所有连接的表,关联字段值在任何一个表中存在,就会在结果表中生成一条记录,并将存在关联字段值的表的记录填入相应的字段实现关联,不存在的相应字段值填为空。最后结果表中还有的关联字段值集合,将是多个参与关联的序表的关联字段集合的并集。这种多方兼顾的连接方式称为全连接

因为全连接的结果中,c、p、i 都有可能为空,所以 A5 中要用 ifn 函数返回三个记录中不为空的第一个的日期字段,作为最终结果的日期字段。

主子表

主子表的典型示例是订单和订单明细,如下图:

..

明细表的主键是订单号 oid 和产品号 pid。订单表的主键 oid 与明细表的部分主键 oid 关联。订单表是主表,明细表是子表,两者是一对多关系。

主子连接时 SPL 也使用 join 函数来处理。比如我们想计算每个客户的订单总金额,代码是这样的:


A

B

1

=file("orders.btx").import@b()

=file("order_detail.btx").import@b()

2

=join(A1:o,oid;B1:od,oid)


3

=A2.groups(o.cid:cid;sum(od.price*od.quantity))

A2 中的 join 将 A1 中的订单记录和 B1 中的明细记录连接起来,也就是把 oid 相等的记录分别填入 o 和 od 字段,作为新表的一条记录。

与同维表关联不同的是,oid 只是明细表的部分主键,明细中可能会有 oid 相同的多条记录。这时候 join 函数的结果中订单记录会重复出现,每一条明细记录都会关联一条订单记录,而每一条订单记录则会关联多条明细记录,最后的结果的记录数和明细表相同。

这里用不带任何选项的 join 函数,会找出所有表的关联字段都存在且相等的记录来关联,如果有一个表的关联字段值在其他任一表中不存在,则这个表的相应记录也会被舍弃。这种连接方式称为内连接

join 函数和 T.join 函数

join 和 T.join 的定义、计算方法、书写方式和适用场景都不相同。

在 SPL 中,两个数据表通过某个字段(或表达式)相等的关系生成一个新数据表(两字段),新表的两个字段分别是这两个数据表对应记录的地址,这样就完成了两表的关联。这种关联运算称为连接,用于判定关联的字段(表达式)称为关联键,关联键值相等记录互称为关联记录

SPL 用 join 函数来实现连接

外键连接也是一种连接运算,以事实表的外键和维表的主键作为关联键。

实际上,T.join 函数也是用类似 join 函数的方式连接维表和事实表的记录,且用事实表的字段加上维表对应记录引用出来的字段生成一个新表。

SPL 之所以提供 T.join 函数,是因为连接加引用字段的组合计算对于外键连接来说更常见,使用起来更方便。