问题描述

数据结构与规模

客户 - 维度表 cust_dim

字段名称	字段类型	字段注释	示例数据
id	Number	客户号	18765
d1	Number	维度 1	12
…	…	…	…
d10	Number	维度 10	3
…	…	…	…

每个客户有唯一 id，共一亿个客户。

每个客户有 10 个维度，比如 d2 是客户年龄段维度，对应的枚举值有 8 个，则 d2 取值范围就是 1 到 8。

d4 是学历维度，对应的枚举值有 12 个，则 d4 的取值范围就是 1 到 12。

客户 - 客群表 cust_group

字段名称	字段类型	字段注释	示例数据
id	Number	客户号	18765
cg	Number	客群号	304
…

一个客户属于多个客群，客群共有 1000 个。平均来看，每个客户大约属于 10 个客群，cust_group 共有大约 10 亿条记录。

这两个表每个月生成全量数据快照，实际的计算都针对一个月，不会跨月计算。这里以一个月的数据为例进行实践。

环境与期望

客户画像计算包含客群交叉和维度过滤。

1、 客群交叉。求同时属于两个或者三个客群的客户，比如既属于 18 号客群、又属于 25 号客群的客户。

2、 维度过滤。求维度值在指定范围内的客户，比如 d2 的值是 2 或者 4，且 d4 的值是 8、10 或者 11 的客户。

3、 最终的结果是同时满足 1、2 条件的去重客户数。

写成 SQL 大致是下面这样：

select count(*) from (
    select count(g.cg) from customer_group g
                              left join customer_dim d on g.id=d.id
             where g.cg in ('18','25')
             and d.d2 in ('2','4')
             and d.d4 in ('3','6','10')
             group by g.id
             having count(g.cg)=2
)

期望用尽可能少的硬件配置，实现在 10 秒内并发完成 10-20 个这样的计算。

采用 HADOOP 上的某著名商用数仓动用了 100 个 CPU（核）的虚拟机集群，完成两客群交叉的任务平均要 2 分钟左右，三个或者更多客群就算不出来了。

也无法采用该商用数仓推荐的预计算方式。因为，上千个客群任意两个交集，再加上 10 个维度的任意组合，结果数量就非常庞大了。而三个或更多客群交集想事先保存下来，就更是不可能完成的任务。

问题分析

IN 计算

维度过滤条件是一个集合 IN 运算。IN 计算的性能较差，主要由于其中有太多的比较运算。要判断字段 d 是否包含在值集合中，如果采用顺序查找，需用 d 与值集合中的成员做 1 到 n 次的比较计算。即使在值集合有序的情况下用二分法查找，也要比较数次。数据量较大时比较次数会非常多，判断 IN 的速度就会很慢，而且值集合越大速度越慢。

SPL 的布尔维序列可以消除 IN 中的比较运算。首先，确定 IN 字段（即写成 IN 条件前面的字段）可能取值的列表。可能值通常不会太多，这个列表也不会太长。然后转换原数据，把 IN 字段值替换为列表中对应记录的序号（位置），另存成一份新数据。

对替换后的新数据做 IN 判断时，先要生成一个与列表等长的布尔值集合，其第 i 个值由列表的第 i 个成员是否在 IN 字段的值集合中决定，在其中就是 true，不在就是 false。遍历时，用 IN 字段值（列表的序号）去取布尔值集合中的成员，是 true 就符合过滤条件，否则就不符合。

布尔维序列本质上是将“集合值比较”转换为“序号引用”，省去了比较计算，性能会大幅提升。而且计算耗时和值集合大小无关，不会随着 IN 条件中枚举值的增多而增加。

关系数据库一般不支持通过序号（位置）直接取集合成员的方法，要用关联表过渡，会导致更复杂的 JOIN 运算，不能直接实现这种优化方法。

大表关联

cust_dim 和 cust_group 这两个大表的关联是拖慢整体性能的主要原因之一。但如果把两个大表 JOIN 生成一个宽表，那么相同的维度值会出现十倍以上的冗余，查询速度还会更慢。

如果客群数量不太多，可以考虑将每个客群存成一个字段，用 0 和 1 代表客户是否属于这个客群。但是现在客群有 1000 个，已经超过了数据库表的最大字段数（通常是 256 或 512 个）。

SPL 列存文件的列数容量要大很多，可以多到上千列。

而且，我们还可以利用下面要介绍的按位存储机制，用二进制位来表示客群。这样可以将两个表合并，消除大表关联计算，同时，也能避免出现列数过多的情况。

二值标签

用 0 和 1 代表客户是否属于某个客群实际上就是二值标签。SPL 支持按位存储二值标签，即用一个二进制位来存储一个标签，把多个标签组合成一个 16 或 32 位以及 64 位整数，这样可以大幅减少表中列的数量以及存储容量（和常规存储方式相比能减少 16 倍 -64 倍）。

SPL 对 16 位小整数有专门的性能优化机制，推荐用小整数实现二值标签的按位存储。

客群对应的二值标签很多，有上千个，按位存储能有效地减少数据读取量和计算量。一个 16 位整数中可以存储 16 个需要计算条件的二值标签，只需要读取和计算一次，就可以完成 16 个客群的交叉计算。

现在也有些数据库能支持位操作，但 SQL 语法写起来还比较繁琐。SPL 提供了虚表对象，可以将组合二值标签的运算透明化，程序员可以继续操作单个的标签字段，实际上会被 SPL 转换成 16 位整数的某些位。

实践过程

准备数据

	A	B
1	=file("cust_dim.txt")
2	=file("cust_group.txt")
3	>movefile@y(A1),movefile@y(A2)
4	for 1000	=to(100000).new((#A4-1)*100000+~:id,rand(20)+1:d1,rand(8)+1:d2,rand(30)+1:d3,rand(12)+1:d4,rand(25)+1:d5,rand(18)+1:d6,rand(50)+1:d7,rand(20)+1:d8,rand(35)+1:d9,rand(40)+1:d10)
5		=A1.export@at(B4)
6		=B4.new(id,to(8+rand(4)).new(rand(1000)+1:cg):cgroup)
7		=B6.news(cgroup;id,cg)
8		=A2.export@at(B7)

这段代码生成文本文件，模拟从业务系统中导出的原始数据，包括：一个月的客户 - 维度表和客户 - 客群表。前者数据量是 1 亿条，后者约 10 亿条。

数据预处理

	A	B
1	=file("cust_dim.txt").cursor@t().sortx(id)
2	=file("cust_group.txt").cursor@t().sortx(id,cg)
3	=A2.group(id;~.(cg):cgs,1008.(false):t)
4	=A3.run(cgs.(t(~)=true))
5	=1008.("t("/~/"):cg"/~).concat@c()	=A4.derive(${A5})
6	=B5.joinx@m(id,A1:id,d1,d2,d3,d4,d5,d6,d7,d8,d9,d10)
7	=(1008\16).("bits"/~).concat@c()
8	=file("cust_dim_group.ctx").create@y(#id,d1,d2,d3,d4,d5,d6,d7,d8,d9,d10,${A7})
9	=1008.("cg"/~).group((#-1)\16).("bits@b("/~.concat@c()/"):bits"/#)
10	=A6.new(id,d1,d2,d3,d4,d5,d6,d7,d8,d9,d10,${A9.concat@c()})
11	=A8.append(A10)	>A8.close()

这段代码完成文本文件的预处理，实现两表合并消除大表关联，且实现二值标签按位存储。

A1、A2：客户 - 维度表按照 id 排序、客户 - 客群表按照 id 和 cg 排序。原始数据往往是按照时间顺序生成、存放的，一般都不是对 id 有序，所以这里要专门排序。

A3：客户 - 客群表按照 id 有序分组，每组 cg 合并成一个集合字段 cgs，且生成一个新集合字段 t，包含 1008 个 false。

客群数量是 1000，并不能被 16 整除。这里增加到 1008 个，则可以用 63 个 16 位整数存储了。多出来的部分不会影响计算。

A4：根据 cgs 中客群号 cg 的值，在 t 中将对应位置的成员赋值为 true。

A5 动态生成产生新字段的代码，B5 中动态生成新字段。实际上 B5 中的代码大致是这样的：=A4.derive(t(1):cg1,t(2):cg2,t(3):cg3,…,t(1008):cg1008)

作用是将 t 中的 1008 个布尔值，定义成新字段 cg1 到 cg1008。

A6：对 B5 和 A1 按照 id 有序归并。由于 B5 也是按照 id 有序且唯一的，所以此时两个表是同维表关系。

A7 动态生成 63 个字段的名称，A8 定义组表。实际上 A8 的代码大致是这样的：=file("cust_dim_group.ctx").create@y(#id,d1,d2, …,d10, bits1,bits2,bits3,…,bits63)

A9 动态生成转换代码。作用是将 1008 个布尔型字段，转换成 63 个 16 进制整数字段按位存储。

A10 完成转换的实际代码大致是这样的：

=A6.new(id,d1,d2,…,d10,
bits@b(cg1,cg2,...,cg16):bits1,
bits@b(cg17,cg18,...,cg32):bits2,
...
bits@b(cg993,cg994,...,cg1008):bits63
)

假设 id 为 1 的客户属于 cg18 和 cg25，那么第二个 16 进制字段 bits2 就被赋值为二进制数：0100000010000000，从左起第 2 位、第 9 位分别对应 cg18 和 cg25。

这个二进制数转换为 10 进制是 16512。

A11 向组表 A8 插入转换好的数据，B11 关闭组表。

客户画像计算

	A	B
1	=arg_cg="18,25"
2	=arg_d2="2,4"	=arg_d4="3,6,10"
3	=arg_cg.split@c().(int(~)).group@n(~\16+1)
4	=A3.(~.sum(shift(1,~%16-16)))
5	=A4.pselect@a(~>0)
6	=A5.("and(bits"/~/","/A4(~)/")=="/A4(~))
7	=d2s=8.(false)	=arg_d2.split@c().(A7(int(~))=true)
8	=d4s=12.(false)	=arg_d4.split@c().(A8(int(~))=true)
9	=file("cust_dim_group.ctx").open().cursor(id;d2s(d2) && d4s(d4) && ${A6.concat("&&")})
10	=A9.skip()

A1、A2、B2 分别是传入的参数：客群号集合、d2 的枚举值集合、d4 的枚举值集合。这里可以看到客群号集合、枚举值集合中成员的个数都是可扩展的，比如三个或更多客群交集，只要增加字符串中逗号分隔的成员就可以了。

实际应用中的参数往往是动态的，可以用 json 字符串传入，再用 SPL 代码解析。

A3 对客群号参数按照 16 的整数倍分组，18 和 25 被分到第 2 组，后续会对应查询 bits2。

A4 用移位、求和的方法计算出 cg 对应的二进制数，18 和 25 按位存储的整数是 16512。

A5 求出大于零的成员位置，这里的结果是 2。

A6 动态生成按位与计算的代码，这里是 and(bits2,16512)==16512。如果 bits2 与 16512（二进制数 0100000010000000）按位与的结果还是 16512，那么 bits2 就符合包含 cg18、cg25 的条件。

A7、A8 生成 ds2 和 ds4 的布尔维序列。

A9 用按位与和布尔维过滤组表游标。实际执行的代码是：

=file("cust_dim_group.ctx").open().cursor(id;d2s(d2) && d4s(d4) 
&& and(bits2,16512)==16512)

A10 由于组表的 id 是唯一不重复的，所以这里直接对游标计数即可得到去重的 id 个数。

利用虚表

我们可以利用 SPL 企业版的虚表对象来封装按位存储机制，简化代码。

数据预处理的代码要改成下面这样：

	A	B
1	=file("cust_dim.txt").cursor@t().sortx(id)
2	=file("cust_group.txt").cursor@t().sortx(id,cg)
3	=A2.group(id;~.(cg):cgs,1008.(false):t)
4	=A3.run(cgs.(t(cgs.~)=true))
5	=1008.("t("/~/"):cg"/~).concat@c()	=A4.derive(${A5})
6	=B5.joinx@m(id,A1:id,d1,d2,d3,d4,d5,d6,d7,d8,d9,d10)
7	=(1008\16).("bits"/~).concat@c()
8	=file("cust_dim_group.ctx").create@y(#id,d1,d2,d3,d4,d5,d6,d7,d8,d9,d10,${A7})
9	=(1008\16).(16.("\"cg"/((get(1)-1)*16+~)/"\"").concat@c())
10	=(1008\16).("{name:\"bits"/~/"\",bits:["/A9(#)/"]}").concat@c()
11	=[{file:"cust_dim_group.ctx", column:[ {name:"d2",enum:"ages",list:["0-10","11-20","21-30","31-40","41-50","51-60","61-70","71-"]}, {name:"d4",enum:"edu",list:["edu1","edu2","edu3","edu4","edu5","edu6","edu7","edu8","edu9","edu10","edu11","edu12"]}, ${A10} ] }]
12	=p_cust_dim_group=pseudo(A11)
13	=p_cust_dim_group.append(A6)	>p_cust_dim_group.close()

A1 到 A8 代码没有变。

A9 到 A11 分三步准备了定义虚表的 json 字符串，大致是这样的：

[{file:"cust_dim_group.ctx",  
column:[
{name:"d2",enum:"ages",list:["0-10","11-20","21-30","31-40","41-50","51-60","61-70","71-"]},     
{name:"d4",enum:"edu",list:["edu1","edu2","edu3","edu4","edu5","edu6","edu7","edu8","edu9","edu10","edu11","edu12"]},  
{name:"bits1",bits:["cg1",...,"cg16"]},
{name:"bits2",bits:["cg17",...,"cg32"]},
...
{name:"bits63",bits:["cg993",...,"cg1008"]}  
] 
}]

定义中约定了虚表对应的组表是 cust_dim_group.ctx。

前两个伪字段 ages 和 edu，对应的是真字段 ds2 和 ds4，用 list 中的字符串对应组表中的整数值。比如 ages 中的 "11-20" 对应 ds2 的 2，edu 中的 "edu3" 对应 ds4 的 3。

后面的伪字段对应真字段 bits1 到 bits63 的二进制值。比如 cg18 的 true 和 false 对应 bits2 中的左起第二位二进制值 1、0。

A12 用 A11 中的虚表定义，生成虚表 p_cust_dim_group 对象。

A13 对虚表添加数据，SPL 会自动根据虚表定义，将源数据中的布尔值，转换为按位存储。

生成数据后，客户画像的代码也要修改一下：

	A
1	=(1008\16).(16.("\"cg"/((get(1)-1)*16+~)/"\"").concat@c())
2	=(1008\16).("{name:\"bits"/~/"\",bits:["/A1(#)/"]}").concat@c()
3	=[{file:"cust_dim_group.ctx", column:[ {name:"d2",enum:"ages",list:["0-10","11-20","21-30","31-40","41-50","51-60","61-70","71-"]}, {name:"d4",enum:"edu",list:["edu1","edu2","edu3","edu4","edu5","edu6","edu7","edu8","edu9","edu10","edu11","edu12"]}, ${A2} ] }]
4	=p_cust_dim_group=pseudo(A3)
5	=p_cust_dim_group.select(cg18 && cg25 && (ages=="11-20"||ages=="31-40") &&["edu3","edu6","edu10"].contain(edu))
6	=A5.cursor(id).skip()
7	=A5.cursor(id,ages,edu,cg18,cg25,d2,d4,bits2).fetch(100)

A1 到 A4 是定义、生成虚表对象。虚表对象可以放到全局变量中，避免每次都要重新定义和生成。

A5 是对虚表做过滤，这时候可以用伪字段，比如布尔字段 cg18 和 cg25，还有枚举字段 ages 和 edu。SPL 会自动完成伪字段和真字段的转换。

A6 求客户去重计数。

A7 将相关的字段取出，观察到下面的结果：

增量数据

客户 - 维度表和客户 - 客群表是每个月生成全量数据的快照，所以每月定时将快照转换成组表存储即可。

客户画像计算时，组表名称加上年月，用传入参数中的年月动态生成组表或者虚表名称。

实践效果

CPU12 核，内存 64G。执行 12 次客户画像计算仅需 4 秒。

后记

客户画像分析场景中，往往要对枚举维度和二值标签的组合条件做过滤计算。

总数据量很巨大时，运算性能的瓶颈常常集中在这些组合条件的过滤上。这些条件非常随意，无法预先计算或指望索引，必须要有高效的硬遍历能力。

SPL 的布尔维序列机制和二值标签按位存储机制可以有效解决这一问题。