分享人：digoal
正文：
本篇内容将从3个部分为读者介绍实时精准营销，希望可以让大家对PG+MySQL有更深入的了解，并可以将这些特性应用到项目中，达到降本提效的目的。
l 基于标签条件圈选目标客户
l 基于用户特征值扩选相似人群
l 群体用户画像分析
通常业务场景会涉及到如下一些技术：
l 基于标签的各种组合条件去圈选出符合标签条件的用户；
l 把用户的特征值转换成多维的基于浮点数的向量，在向量的每一个维度上代表特征值。基于这一系列的数据，我们就可以通过输入一个用户，基于某一个向量值去扩选出一批跟这个向量距离接近的用户，这叫做相似度的扩选，或者相似人群的扩选；
l 当我们圈选到目标用户后，可以基于这些用户本身的属性数据做一些透视，比如年龄段、收入、地理位置的透视等，在拿到了目标人群之后做一些事情。
一、基于标签条件圈选目标客户
基于标签的圈选，我们采用了两个典型的设计：
1）第一类设计
什么是第一类设计？比如有一张表，表中有一个组件userid。另外还有一个属性字段即这个userid身上贴了多少标签的标签字段。这样的一个数据结构是非常清晰的，直接看tagid就能知道用户身上贴了多少标签。基于单个用户的更新、删除、替换等操作都是非常简单的，所以可以把tagid理解为一个数组。
如果要基于标签去圈选用户，我们就可以采用这个标签，比如说包含相交。
包含是什么意思？举个例子，如果要查某人的年龄，在某某行业，是男还是女，这类的条件输进来实际上对应的是当tagid这个字段同时包含我们刚刚提到的那几个条件，这类用户就是我们要的用户，这就是一个数组的包含查询，它可以在pg里使用，我们可以使用倒排索引解决包含相交类的查询需求。
那么查出来的结果是什么？就是uesrid，比如我们的用户是一个b端用户，他要到你这个平台里面获取目标客户，那他就会告诉你条件，你基于这个条件圈选出这些userid之后，推给他即可。
那怎么做呢？下面举些例子。
2)例子
l 标签阶梯化字典表
如果我们要用数组来表示标签id，我们必须要将标签本身数字化。以收入为例，你就要把它转换成比如年收入超过10万，或者超过几万几万，需要把它阶梯化。
阶梯化之后的每一个阶梯是不能用数字化表达的即连续数据，我们要把它离散化。离散化之后我们要给每一个离散值一个描述。
这个两个是关键信息，一个是标签id，实际上就是对应values里面的数组即标签数组。然后一个人身上贴上哪些标签，我们就从这里面把标签取出来，再塞到数组里面就ok了。
l 生成10万标签（人群）
如果我们有10万个标签，那我们就要生成这张10万个标签的字联表（这里做测试用），所以就往这个标签表写入了10万条记录，比如标签id=1表示男，标签id=2表示女，标签id=3的表示大于24岁等。我们创建10万个这样的标签。
l 创建打标表
再往下我们要创建一个打标表或者叫userid的tag表。组件就是userid即用户id。其中字段tags就是这个人身上贴了哪些标签？我们用int数组来表示，int值对应的是就是字典表的tag组件，即它的组件组成的一个数组。
l 创建生成随机打标数组的函数
然后去创建一个可以随机打标的函数。函数中function的最核心的功能就是select array_agg(ceil(random()*$1)::int) from generate_series(1,$2);取值范围是1~10万，因为我们刚刚有10万个标签。从1~10万里面生成随机的63个随机数转换成一个数组，然后比如说我们调用一下db1=> select gen_rand_tags(100000, 63)，它就会返回出一些数组（图中红色方框），表示比如你如果把这个字段产生的值insert到userid标签表里，那么它其实对应到的就是这个数组。比如现在如果要查包含7430这个标签的用户有哪些，很显然这条记录（绿色方框）就会被拿出来； 或者同时包含597和7430有哪些？那这条记录也会拿出来（绿色方框+蓝色方框）。
l 给2000万用户打标
紧接着我们生成2000万用户的标签数据，怎么生成呢？就是往这个user tag表里面generate_series(1,10000000)，array_append(gen_rand_tags(100000, 63),1), now();我们给用户贴了64个标签，其中有一个标签是1即代表他是个男的，另外63个标签是随机生成；array_append(gen_rand_tags(100000, 63),2), now();再插入1000万条，2代表女的，另外63个标签也是随机的，所以总共这个表就有2000万条记录。
之后基于这个表的tag字段创建gin倒排索引create index idx_t_user_tags_1 on t_user_tags using gin (tags)，使用这个倒排索引的接口去创建。
创建完后我们看一下这份数据，对应的是哪个表呢？
即上图user tags这个表。我们可以来查一下，比如limit 10.
你会发现每个人身上都有一堆随机的标签。
l 查询标签人群
比如要查同时包含1和3这两个标签的用户有哪些？该怎么查呢？
select count(uid) from t_user_tags where tags @> array[1,3];
同时包含1和3的总共有6396条记录，它用了倒排索引gin。
如果要把这6000多个uid都拿出来select uid from t_user_tags where tags @> array[1,3]，就通过这个方法如uid=468、1325等等，同时包含1男的，并且包含了3号标签。
下面是比如你包含1或者是包含3或者包含10或者包含2、200,。这类人群对应到数组操作就是相交，比如tags && array[1,3,10,200]-tag里面包含数组里面的任意一个，1、3 、10、200任一个。我们来看一下这个查询。
你会发现这个查询用了全表扫描，我们可以把全表扫描关掉，因为是根据代价自动算出的。那么你就会发现这个时候花了3秒的时间，包含1或者3或者4或者200总共1000万条记录。
那么我们再来执行一下。它实际上现在用了位图扫描这样一个执行计划，同时用了并行计算，开了四个并行。那我们可以把并行计算也关掉设成0，那么就不会用并行计算，我们来看一下会花多少时间？差不多 11秒，接近于四倍。
为什么或的查询和与的查询性能差别这么大呢？同时包含1并且包含3的返回记录就只有六千多条，查询只花了43个毫秒，而这个或者因为这个1有4个男的记录就有1000万条。如果把1去掉，比如后面30、200返回记录数不多，那么它这个速度也是非常快的，只花了20几个毫秒。
也就是说性能取决于你最后返回给你的有多少条。如果总共2000万条记录，删选满足条件的就有一半，很显然这个扫描时间本身就会很久。
所以索引本身适合于返回结果少的，比如你一次圈选，搞一次活动可能会圈几百万，这已经算比较多了，通常来说这种操作都是付费的即精准用户精准付费的操作。
另外通常也不会大面积的去圈选，所以一般来说这种查询性能都应该是在100mm以内就可以满足了，比如我们这里再加一个或4，这个返回结果又多了一点，那么再加一个5又会多一点点。
随着慢慢的增加，比如再多几个，100，201，你会发现输入了这么多或的条件之后已经返回了五万多条结果。
当然我们还可以再结合，比如包含一个条件，或者又包含另外一个@的条件，一定包含某一个的这种条件也可以组合起来。
以上就是第一类设计，我们首先把不同的人群标签化，比如男人是一个人群，女人是一个人群，学生是一个人群等；接着标签字典化；字典化之后对用户本身贴标；贴完标之后用数组的包含相交去查询。
3）第二类设计
第二类设计就是我们反过来，把人群作为一个组件，比如男人，标签=1的男人作为一个组件， value是一个userid组成的bitmap。比如男人有一半，那么bitmap就等于有1000万个bit。而比如有一些标签，如年收入超过100万的，那相对来说就会少一点，这个时候bitmap里面存的东西就会更少。所以用这样的数据结构的好处就是现在标签上面我们不用建倒排索引，因为标签就是一个组件；bitmap也不需要建索引，因为最后我们如果要计算它是个男人，同时又是个白的，或者是个男人，同时是一个大约24岁的这样的一个人群，我们实际上就是拿这两个标签对应的bitmap做bitmap位的与操作，操作完之后就是表示它同时包含这两个标签。
那么像刚刚另外一个条件select uid from t_user_tags where tags && array[1,3,10,200]这个查询，用设计2的时候其实就是这四个bitmap做逻辑或的操作，即任意一个。所以设计2是基于组件的查询，同时基于bitmap的聚合.
l Roaringbitmap使用方法
设计2我们要用到一个插件叫Roaringbitmap，rdspg12已经支持了，
create extension roaringbitmap这个插件就创建好了。完了之后可以去设置它的输出格式，默认是二进制，可能看不太懂它输出的情况，那我们可以把它设置为array也就是数组的格式，这个时候一个bitmap里面包含哪些id我们就一目了然了。
另外要注意是，因为我们把id映射到了bitmap位上面，value即userid最多能映射40亿的范围，超出40亿就超出了它的寻址空间，你可以理解为超出了bitmap的空间。如果userid超过40亿的大小，我们可以用offset去转换，比如40亿~80亿，我们的offset是1，就是得在userid上面加一个基础值40亿，然后80亿~120亿就在基础值上offset 2，或者offset 80亿，在基础上再加个80亿。所以bitmap的范围问题我们可以通过offset来解决。
那么怎么构造一个bitmap？还是比较简单的，在int4这样一个整型数组，用rb的build就可以去构建出一个bitmap的value，通过rb_to_array(rb)可以把一个bitmap转换成一个数组，或者是 rb_iterate(rb)可以把它单个值转换成多条记录。比如我要返回id时，返回limit10或者其他就可以转换一下返回，当然也可以直接rb里面出来。
那么bitmap是否包含某一个userid?比如一个app，某一个标签里面有没有包含userid=100的用户？那就用– rb_cardinality(rb)这个函数来表示。
l bit操作
我们来看一下bitmap支持的一些逻辑操作。
包括聚合操作，逻辑判断等，它的逻辑运算就是bitmap的运算：与、或，异或或者差这样的一些逻辑操作。
聚合也是bit位的聚合，与、或，异或这些聚合操作，直接统计一个rbmap中bitmap的value里面有多少个对象即聚合之后有多少个对象？比如有多少个用户贴了某几个标签，那我们就可以直接用rb_and_cardinality_agg把它聚合起来。
逻辑就是说比如一个两个rbmap的value它们相互之间是一个包含的关系还是一个相交的关系，或者是一个相等不相等的关系。
4）例子
接下来我们就来看一个例子。
l 创建roaringbitmap插件
首先要创建一个extension去执行create extension roaringbitmap，这个因为这个插件我们已经创建了，所以再创建它就会报错。
l 创建标签，用户bitmap表
接下来创建一个叫t_tag_users这样一张表，用第二类设计方法，它的组件是一个标签id，或者叫人群id， value最主要的字段是userid的offset，如果你确认userid不会超过40亿，也可以直接把这块的offset去掉，直接用userbits roaringbitmap这样的单个字段即可。这里我们是模拟userid一定是超过40亿，所以我们加了一个uid_offset int的这样的一个子段，然后生成这个数据。
l 生成标签
因为之前用设计一的方法有一张t_user_ tag 2000万的表
我们直接把这2000万的表翻转一下，翻转成刚刚说的结构，然后把它存到这张表表里面去。如何翻转？首先把这张t_user_ tag表的tag标签字段unnest解析解出来；解出来之后用uid做一下转换，把它转成40亿的空间范围内的value，等于除以2的31次方；然后取除2的31次方的余数作为一个新的就是它的这基础uid，(uid / (2^31)::int8) as uid_offset, mod(uid, (2^31)::int8) as uid ,然后set，那么把t_user_ tag这张表根据这样的一个解析出来之后，我们针对这个子查询基于tagid和uid的offset这两个字段去做group by。
聚合是什么操作呢？聚合就是把uid聚合到bitmap里面，即函数rb_build_agg，那么rbitmap的这些函数可通过下图网址打开文档文章查看，里面会告诉你bitmap函数，怎么去build bitmap。
这个方法实际上就是传入的userid是个int4的类型我们通过这聚合操作把它转换成rbmap，再把它塞到tap_user这张表里面。完成之后这个表
t_tag_users会有多少条记录呢？
10万条，因为我们总共就只有10万个标签，转换之后就变成只有10万条记录。
l 查询包含1,3标签的人群
查询包含1和3这两个标签的人群怎么查呢？做聚合即可。
首先where tagid in (1,3)，聚合是and操作，因为是同时包含。那么有多少人？ Sum即可,6396人，只花了1.5个毫秒。
设计1的查询同时包含1和3花了43个毫秒。
如果要直接把人群id取出来我们可以通过如下的语句：
select uid_offset,rb_and_agg(userbits) as ub
from t_tag_users
where tagid in (1,3)
group by uid_offset;
这样就把id给取出了，这些id同时包含1和3的标签。
l 查询包含1或3或10或200标签的人群
这样一个操作就是4个bitmap的聚合运算，同样我们通过它来查一下：
也只花了花了1.6个毫秒就把1000万条记录直接聚合出来了，而刚刚那个操作用方法1花了11秒，方法2我们只花了1.6个毫秒就出结果。这1000万的id要把它取出来可以用如下语句：
select uid_offset,rb_or_agg(userbits) as ub ，
from t_tag_users
where tagid in (1,3,10,200)
group by uid_offset;
当行这条SQL时，因为1000万的字段本身会比较大，所以速度取决于你的网络，因为聚合出来的bitmap有1000万个userid，然后要返回给你，那结果可想而知。
5）方案1VS方案2
我们对比一下方案：
方案1：
l 在与操作弹性速度上，我们输入了两个与的操作测试数据是2000万条记录，每一条记录64个，最后把它展开成tag是12.8亿个user_tag。方案1的与操作就返回6000多条记录花了42个毫秒，或操作开并行是3秒，不开并行是11秒。
l 空间的占比方案1占了多少块钱，3126MB。同时它有倒排索引占了3139MB，加起来就是6 g。
l build的索引花了20分钟。
方案2：
l 无论是与操作还是或操作，不管反馈多少条，基本上1.5个毫秒左右，对比方案1的十几秒就是1000倍以上的性能的提升。
l 空间占用因为bitmap本身是压缩的，所以只占用了1.4 gb。
l 索引这块因为它没有倒排索引，只是10万条记录的组件的索引就只有2M。
l build的索引速度因为它不需要倒排索引，所以没有build的索引的时间，为零秒。
所以从整体上看，方案2占有绝对的优势，所以大家去做方案的时候就可以采用方案2，用阿里rdspg里rbmap这的一个插件。
6）更新标签-放大问题优化
因为每一次查询都会产生一个新的人群，我们可以在产品上面这么去设计，比如你的一个b端用户一年可以产生或者新增1000个人群或者1万个人群，每产生一次新的人群，在方案2里面是只有一条记录，即这个人群的id以及这个人群对应的userid的bitmap，只会产生一条新的；而在方案1里面，每一次产生一个新的人群，都要去更新这个人群上面的每一个user id的tag字段即数组字段，你要把新产生的标签的id update，每一次产生新的人群都会产生大量的更新。
所以方案1在产生新人群之后的更新操作也是比方案2要弱的，方案2只是一条inset，方案1要update大量的记录。所以如果你采用了方案1，那么就一定要用批量更新，不要每一条记录都去更新，或者每产生一个新的标签、新的人群去更新一次，你可以产生很多个人群之后再去批量更新。
以上就是基于标签动态的实时圈选。
二、基于用户特征值扩选相似人群
接下来我们来讲一下第二类场景，就是说我们在用户身上已经把它的特征值给数字化或者向量化了，然后基于这个向量去扩选我们的人群，那应该怎么做呢？
1）相似人群扩选
首先相似的人群要有特征向量。 
这个特征向量的结构是userid，对应的特征向量是什么呢？比如点数的数组，特征向量有100个维度，那么这个数组就有100个value。然后每一个value代表它在这个维度上的特征值。
如何计算两个特征值之间的距离或者相似性？这个其实算的就是两个向量的距离，计算有若干种方法，其中一个叫欧式向量距离，还有取累积的距离等等。
2）例子
那么我们来看一个例子。
首先我们要创建一个人群特征表，接着build向量索引，然后做相似的扩选。
l ivfflat -> 中心点收敛
我们先了解两个概念。
在相似的扩选里，我们用到了rdspg中一个功能模块 pase，针对这种向量的数据类型去做build的向量索引。它支持两类索引，一类叫ivfflat，也叫中间点收敛，你可以理解为在这个索引里面，它把每一条记录都投射到了一个坐标系，在这个坐标系里生成了若干个中心点，也就是说通过kmeant算法做聚类，然后去生成了若干个中心点。每一个中心点周围有一圈聚集点，把这些聚集点存储在对应的行号，每一个聚集点对应若干条记录，那么这些记录都是属于这一这一个聚集点的。
搜索的时候它会去索引里面找到离我们的目标点，或者b端客户他提交了一个向量值，然后要搜离他给的这个向量值最近的前十条记录，这个时候我们实际上就是在这个索引里面，会拿离向量最近的若干个中心点去展开搜索，先搜到了离它近的中心，点完了之后，在这个中心点里面基于这个中心点本身去找离我们的目标点最近的若干个点，然后把这若干个点全部拿出来之后，比如我搜了10个中心点，在10个中心点里面每一个中心点再取10条离你的目标点最近的点，那么就会拿到100条记录，然后这100条记录里面再筛选出10条离目标点最近的，这就叫做中心点收敛.
l hnsw -> 图层收敛
第二个索引的方法就是hnsw这个叫图层收敛，看我们右边这张图，你就可以把它理解为每一个高层都是它下面这一层的一个缩略图，比如我们的最底层layer0是全量数据，它的上一层就是一个概略数据。举个例子，一张4K图片到上层可能就变成了2k，再往上可能就变成了1k，分辨率越来越低，里面只提取了一些关键点。我们搜索的时候就是从缩略图开始，从最高层开始，那么我们在最高层会先拿到一个随机的点，比如我们要搜离图中绿色点最近的点，那我们在最高层的时候首先随机拿到一个点（红色点），然后在这个点所有的相邻点里找到一个离这个绿色点最近的点，比如在l2这一层找到了这个点（红色方框），然后这个点因为是下面这一层的缩略图，所以在l1里面一定也会包含这个点，那么在l1里面也使用同样的方法，基于这个点的相邻点展开搜索，又搜到了一个离这个绿色的点最近的点，这个点再往下就到了l0层，那么它会使用贪婪算法把离这个绿色的点最近的点把它找出来，可能是这个点（绿色圆框）离它最近，所以hnsw实际是用了图示的关系算法去分层，从缩略图开始逐渐逐往下找到l0层，找到离我们的目标点最近的点，那么这个是基于图的算法来做的。
l 例子
这两种算法无非就是创建索引的时候有点不一样，其他查询的方法都是类似的。
首先要创建pase这样的一个extension，接着创建一个user向量表，即每一个user身上用一个向量来表示它的特征值，然后创建一个生成随机向量的函数，比如100万条或者1000万条向量数据。第一个字段第一个函数的输入是一个向量的每一个维度上面的取值空间，第二个值是生成这个向量的维度或者长度是多少？比如要生成一个80维的，那么第二个参数就是80，它的取值范围比如是1万即1~10000，那就输入1万，（10000，80）。我们实际生成的是16维的的特征向量，取值范围是1万，插入了100万条，创建了ivfflat这个聚集索引，我们这里使用的最主要的是这个参数clustering_params 。维度以一定要跟数据一样，比如16位，那就要指定16位。最后这个参数clustering_params = "10,1001")比较有意思，我们在这个表上面采样千分之十，也就是1%的数据。第一个参数是千分位，这是代表采样这么多条记录，那么生成多少个中心点呢？生成1001个中心点，也就意味着我们创建的索引有多有1001个聚集，这是ivflat索引。
下面这个索引是hnsw，这两个索引二选一即可，不用两个都创建，就看哪个算法比较适合。它也有一些参数，最重要的就是要指定这个唯独dim = 16， ef_build = 40, ef_search = 200是一些生成这个图的算法时的列表的长度，还有摄取长度，这都是生成图时的一些参数。
l 查询
这两个索引任选一个创建好之后我们去做查询。
举个例子，我们先查一下这个表里面有多少特征向量。pase插件在rdspg11的版本里面，我们马上会把它给扩展到pg12，所以我们这里是在pg11上面做了一个测试。那么首先我们查到了一个这样的向量，或者是limit10。
这个实际上就是当前我们创建测试数据里的一些特征向量值。之后我们用ivfflat算法去搜索10条离这个向量最近value，花了3.5毫秒左右的一时间，用了ivfflat索引。
操作符是<#>，这个代表的就是用的是ivfflat这个索引去做的这样一个向量相近的操作。order by就是离我们给的这个向量越近，它会越优先输出。
如果我们用hnsw索引， order by就得用来代替。
operate这里也可以看到，比如也是一个算距离的方法。
三、群体用户画像分析
最后基于我们圈出来的群体，上了1000或者1万10万条等等数据，最后要拿到这些userid了，实际上每一个user本身还有user的字典表，比如性别，年龄，所在的城市等等，你有可能有一个表表示的是id身上一些通用属性，然后基于这些属性去做透视，我想知道这群人里面白领占了多少，年龄段是怎样分布的，在地域里面怎么分布的呀，职业是怎么分布的等等。
l 例子
这个怎么做呢？基于id，我们去做group by，做透视等等这样一些操作。比如有一张基础表，就是我们刚刚说的user表，然后where uid = any(array(,
select offset*(2^31)::int8+unnest(rb_to_array(uidbitmaps是我们不管是通过相似圈选出来的相似扩选出来的，还是通过我们的rbmap圈选出来的，我们其实就是这么一条简单的查询，然后group by就可以了。
那么这里就会用到并行计算，因为如果你圈选出来的人群特别大，比如上千万或者上亿，那我们要针对这样的一些数据去做透视就得用并行计算；如果只是百万千万级别，做一下透视花一两秒的时间其实也能接受。
