SPL 实践:客户画像

问题描述

数据结构与规模

客户 - 维度表 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 的布尔维序列机制和二值标签按位存储机制可以有效解决这一问题。