SPL 虚表的数据类型优化

物理上存储数据的表(简称物理表)往往会采用一些有利于提升性能、减小空间占用的存储机制。这些机制在提高性能的同时也会增加使用复杂度。

设有交易明细表、客户表、产品表,表结构和关联关系如下图:

..

三个表的数据大概是下图这样:

..

假如现在要完成这样的计算需求:先做数据过滤,条件是本交易所使用的设备类型是智能手机或者平板电脑,且客户所在州是 California、Texas 或者 Illinois,且产品名称不是 Stewing Beef。然后,再按照客户所在州、交易日期对应年月 yyyymm、设备类型、产品类型来分组求总金额和笔数。结果集中都要求以方便阅读的字符串方式展现。

我们来看三个物理表都采取了哪些方便存储的机制,并观察这些机制对于计算性能的影响。

首先,为了避免数据冗余减少磁盘占用,物理表 detail 中并不会存储客户或者产品的相关字段,要先将三个表做外键关联后再进行其他计算。detail 中也不会存储交易年月,只能在交易日期基础上用表达式计算得到。交易年月这样的字段是用其他字段计算出来的,称为计算列

其次,交易明细表要记录该交易使用的是 computer、smart phone、pad 其中的哪一种设备。因为整数占用空间小,计算速度也快,因此在 detail 表的枚举维度 device 中,实际存储的是整数 1、2、3,分别对应这三个设备类型。

第三,交易明细表还要存储很多布尔维度,例如是否海外购 isOversea、是否信用卡支付 isCreditCard 等等。如果我们将这样的维度都存储为独立字段,那么即使用二值维度(取值为 1 或 0)代替布尔维度,也要用很多个整型的二值字段来存储。在 detail 表中,实际只用了一个 16 位短整型字段 bools 就可以存储 16 个二值维度,每个二进制位存储一个。这样的字段我们称为位维度。采用位维度不仅可以减少存储空间占用,而且采用二进制与或计算,一次就可以完成最多 16 个二值计算,性能会提高很多。

如果我们直接使用这三个物理表完成上述需求,就要写脚本由 ddate 计算出年月、实现 device 枚举类型的转化、实现 detail 表 cid 字段与客户表的外键关联、实现 detail 表 pgid、pid 两个字段与产品表的外键关联。使用复杂度明显增加,也有一定的代码量。而且,需求中还可能会包括布尔维度,比如:交易明细必须是海外购,分组字段加上是否信用卡支付。这时还要自行实现 bools 中二进制位与布尔维度的转换,还会进一步增加复杂度。

SPL虚表是定义在物理数据表基础上的逻辑表,可以在真实字段上定义特殊字段,将计算列、枚举维度、位维度、外键预定义等等特殊的存储结构封装起来,实现物理表存储机制的透明化。

采用虚表,既可以方便存储,保证计算性能、减小磁盘占用;又能方便使用,降低使用复杂度。

下面我们来介绍虚表实现数据类型转换的工作原理。

计算列

将交易明细表按照年月 yyyymm 来分组求总金额和总笔数时,日期字段 ddate 不能直接使用,而是要先经过计算得出交易年月 yyyymm 再使用。类似这样的计算列还有很多,比如:年份、年月、星期几都可以由日期字段计算得到。

虽然在物理表中增加几个字段就可以保存这些数据,且在列存的情况下多几列也不太影响运算性能,但是这样会增加生成数据的时间,会占用更多的磁盘空间。而且,还有些数据是没有办法预先存储下来的,比如客户年龄,一般都要用出生日期和当前日期临时计算。

虚表中可以定义计算列,其值并非保存在物理表中,而是用真实字段计算得到。我们可以将年月、年龄等定义成虚表的计算列。一方面可以减少生成物理表数据的时间并节省磁盘空间,另一方面在使用虚表时可以像使用普通字段一样使用计算列,非常方便。

例如,我们可以在物理表 detail 基础上创建虚表 p_detail,并在其中定义一个特殊字段 dmonth,用于实现交易年月计算列,如下图:

..

detail 表中没有 dmonth 字段,需要通过表达式 exp 计算出来。这里的 exp 表达式是 month@y(ddate)。

SPL 使用包含多个属性的结构来定义虚表,每个属性都有固定的名称。本例中,虚表 p_detail 定义中要包含物理表名称、计算列名称,以及表达式 exp,大致是下图这样:

..

属性 file 是构成虚表的物理表的名称,本例中是组表 "detail.ctx";column 是特殊字段的集合,其中每一行对应一个特殊字段。name 是计算列名称 dmonth,exp 则是表达式 month@y(ddate)。

虚表对应的物理表也可以是其他外存表(集文件、复组表、集群组表等),或者内存表(序表、内表、集群内表等)。

定义虚表 p_detail 的代码是这样的:


A

1

=[{file:"detail.ctx",

column:[

{name:"dmonth",exp:"month@y(ddate)"}

]

}]

2

>p_detail=pseudo(A1)

A1 完成一个虚表定义。A2 生成虚表 p_detail。

生成虚表之后,可以用游标(p_detail.cursor().fetch(6))取出虚表部分数据,大致是下图这样:

..

虚表在物理表 detail 真实字段的基础上增加了计算列 dmonth。程序员在计算时,可以将 p_detail 看作一个简单的普通表,直接使用 dmonth 字段来分组汇总:


3

=p_detail.groups(dmonth;sum(amt):sum,count(~):count)

A3 中 SPL 根据上述虚表定义,自动将伪字段 dmonth 转换为真实字段 ddate 的表达式,计算后返回的部分结果如下图:

..

枚举维度

物理表 detail 中的枚举维度字段 device 是采用数值类型 1、2、3 存储的。数值计算速度快,但是却无法像字符串那样容易被人读懂和使用。如果将字符串和数值一起存储下来可以兼顾性能和易用性。但是,也会面临前面说过的问题:增加生成数据的时间,而且占用更多的磁盘空间。有些情况下,还有可能出现字符串和数值不一致的错误。

类似的,我们也可以使用虚表的伪字段,来解决这个问题。在物理表中只保存数值,在虚表中用伪字段定义枚举维度数值和字符串的对应关系。这样,用虚表计算时,就可以直接使用字符串,计算结果中也可以得到字符串。物理表还可以继续存储数值以达到高性能计算目标。

下图中,虚表 p_detail 就新定义了一个伪字段 deviceString 用于枚举维度的透明化:

..

物理表 detail 没有 deviceString 字段,需要依据枚举集合 enum 转换得来。本例中 enum 为 ["computer","smart phone","pad"],其中的三个枚举值成员依次对应自然数 1、2、3。

这时候,虚表定义要修改为下图的样子:

..

column 中增加了一个伪字段 deviceString 的定义,包含上面说的 enum。

SPL 定义虚表 p_detail 的代码会变成这样:


A

1

=[{file:"detail.ctx",

column:[

{name:"dmonth",exp:"month@y(ddate)"},

{name:"device",pseudo:"deviceString",enum:["computer","smart phone","pad"]}

]

}]

2

>p_detail=pseudo(A1)

A1 为虚表定义增加了一个枚举类型的伪字段 deviceString。

这时候取出虚表的部分数据就是这样:

..

计算时仍将 p_detail 看作是一个简单的普通表,直接使用 deviceString 字段来做条件过滤和分组,并在结果集中显示字符串:


3

=p_detail.select(deviceString=="pad" || deviceString=="smart phone")

4

=A3.groups(dmonth,deviceString;sum(amt),count(~))

A3 中过滤条件中的字符串 smart phone 会被自动转换为 2;pad 会被转换为 3,然后才会用来过滤物理表 detail。

A4 计算结果集中的 device 字段值 2、3 则分别会被转换为 "smart phone","pad",如下图:

..

位维度

交易明细表 detail 的 bools 是位维度,存储了两个二值维度 isOversea(是否海外购)、isCreditCard(是否信用卡支付),每个维度占用 bools 字段的一个二进制位。这样,一个 16 位短整型字段 bools 就可以存储 16 个布尔维度。前面说过,采用位维度不仅可以减少占用的存储空间,而且在计算的时候用二进制与或计算,一次就可以完成最多 16 个二值计算。但是,位维度没有单独的布尔维度或者二值维度那么容易阅读和使用。

这时,我们也可以在虚表中定义伪字段,将 bools 字段和布尔维度之间的转换透明化,如下图:

..

这时候,虚表定义要修改成下图的样子:

..

column 中增加了一个特殊字段的定义,name 是物理表真实字段的名称 bools,bits 属性中是伪字段名称集合,最多可以有 16 个二值字段名称。本例 bits 中是两个字段名,分别对应 bools 字段中的第 1、2 个二进制位。

相应的,定义虚表 p_detail 的代码也要修改:


A

B

1

=[{file:"detail.ctx",

column:[

{name:"dmonth",exp:"month@y(ddate)"},

{name:"device",pseudo:"deviceString",enum:["computer","smart phone","pad"]},

{name:"bools",bits:["isOversea","isCreditCard"]}

]

}]

2

>p_detail=pseudo(A1)

A1 增加了特殊字段 bools,属性 bits 中定义了两个伪字段名称。

此时,取出虚表 p_detail 的部分数据会变成下面这样:

..

计算时直接使用 isOversea、isCreditCard 字段来做条件过滤和分组,并在结果集中显示布尔值:


3

=p_detail.select(isOversea && (deviceString=="pad" || deviceString=="smart phone"))

4

=A3.groups(dmonth,deviceString,isCreditCard;sum(amt),count(~))

A3 中过滤条件中的 isOversea 会被自动转换为 bools 字段的第 1 位,用来过滤物理表 detail。

A4 计算结果集中的 bools 字段第 2 位的值,会被转换为布尔值 isCreditCard:

..

外键预定义

物理表往往还要通过一些代码字段和其他表关联。比如前面讲到的,交易明细表 detail 通过客户号 cid 和客户表 customer 的主键 id 关联;通过 pgid、pid 两个字段和产品表关联。关联的目的,是需要用三个表的字段共同参与计算。例如前面计算中的过滤条件要增加客户所在州 state 是 California、Texas 或者 Illinois,且产品不是 Stewing Beef,分组字段也增加客户所在州 state 和产品分类 type。

这时,我们可以在虚表中定义特殊字段,将交易明细表、客户表、产品表的关联关系透明化,如下图:

..图中,cid 定义成了一个特殊字段,其 dim 属性要定义物理表 detail 和 customer 的外键关联关系,本例中 dim 就是 customer 表,主键是 id。

由于 detail 和 product 是通过两个字段关联的,所以上图中新增了一个特殊字段 product,用来指引这个关联,这个字段是物理表中没有的,称为指引字段。

这时候,虚表定义要调整成下图的样子:

..

column 中增加了两个特殊字段的定义,包括 cid 和 product。因为物理表中并没有指引字段 product,所以还需要一个 fkey 属性,用以定义物理表的外键字段 pgid 和 pid。

定义虚表 p_detail 的代码也要相应的改变:


A

B

1

=T("customer.btx").keys(id)

=T("product.btx").keys(gid,id)

2

=[{file:"detail.ctx",

column:[

{name:"dmonth",exp:"month@y(ddate)"},

{name:"device",pseudo:"deviceString",enum:["computer","smart phone","pad"]},

{name:"bools",bits:["isOversea","isCreditCard"]},

{name:"cid",dim:A1},

{name:"product",dim:B1,fkey:["pgid","pid"]}

]

}]

3

>p_detail=pseudo(A2)

A2 增加了特殊字段 cid,并指明了对应 dim 是 A1。增加了指引字段 product,对应 dim 是 B1,物理表 detail 中的外键是 pgid 和 pid 两个字段。

此时,虚表 p_detail 的数据会变成下面这样:

..

虚表的 cid 字段已经和客户表关联好了,用 cid 可以直接点取客户表的字段,比如 cid.name、cid.state。用 product 也可以直接点取产品表的字段:


4

=p_detail.select(product.name!="Stewing Beef" && ["California","Illinois","Texas"].pos(cid.state) &&(deviceString=="pad" || deviceString=="smart phone"))

5

=A4.groups(cid.state,dmonth,deviceString,product.type;sum(amt),count(~))

A4 过滤条件包含特殊字段 cid 和 product,SPL 会自动使用预先关联好的物理表进行计算。

A5 计算结果集值,部分数据如下图:

..

回顾与总结

SPL 虚表相当于元数据,可以将物理表中比较复杂的存储机制透明化。这样,我们就可以像一个最简单的表一样使用虚表,降低了使用复杂度。同时,虚表又可以将逻辑结构上的简单计算,自动映射成高性能存储机制上的高效计算,可以保证计算性能。

在虚表中定义好计算列、枚举维度、位维度和外键预定义后,使用者就不必再考虑这些存储和计算机制,将虚表当成简单的普通表即可。除此之外,虚表还可以用于用户分析计算,实现两个维度字段同时有序,详情参见SPL 虚表的双维度有序机制


以下是广告时间

对润乾产品感兴趣的小伙伴,一定要知道软件还能这样卖哟性价比还不过瘾? 欢迎加入好多乾计划。
这里可以低价购买软件产品,让已经亲民的价格更加便宜!
这里可以销售产品获取佣金,赚满钱包成为土豪不再是梦!
这里还可以推荐分享抢红包,每次都是好几块钱的巨款哟!
来吧,现在就加入,拿起手机扫码,开始乾包之旅



嗯,还不太了解好多乾?
猛戳这里
玩转好多乾