云数据库 RDS：实时精准营销（人群圈选）

方案一

方案概览

• 表结构：

  KEY: 用户ID
  标签1: 
  标签2: 
  ...
  标签N:

• 表索引：每个标签字段一个索引。

• 搜索方法：

  and , or , not 组合
  where 标签a and 标签b and ...

• 方案缺陷：

  • 用户标签多、字段多，占用存储空间也多。

  • 字段多，可能会超过数据库的字段数限制，一般数据库最多支持1000多个字段。也可以设置为多对多的结构，每个标签一条记录，解决字段数限制的问题。

  • 圈选条件组合多样化，没有固定索引可以优化，如果每个字段一个索引，存储空间又会暴增。

  • 新增一个用户群体标签时，需要更新大量数据。

  • 查询性能差。

操作示例

1. 创建人群表，每条记录代表一个人群。示例如下：

   create table t_tag_dict (
   tag int primary key,   -- 标签(人群)id
   info text,  -- 人群描述
   crt_time timestamp  -- 时间
   ); 

2. 生成10万个人群标签。示例如下：

   insert into t_tag_dict values (1, '男', now());
   insert into t_tag_dict values (2, '女', now());
   insert into t_tag_dict values (3, '大于24岁', now());
   -- ...
   
   insert into t_tag_dict 
   select generate_series(4,100000), md5(random()::text), clock_timestamp();

3. 创建用户画像表，每个用户N条记录，每条记录代表这个用户贴的某个标签。示例如下：

   create table t_user_tag (
   uid int8,   -- 用户id
   tag int,            -- 用户对应标签（人群）
   mod_time timestamp,     -- 时间
   primary key (tag,uid)
   ); 

4. 给2000万个用户打标，每个用户64个随机标签，其中男、女各一半，共12.8亿条记录。示例如下：

   create or replace function gen_rand_tag(int,int) returns setof int as
   $$
     select case when random() > 0.5 then 1::int else 2::int end as tag
     union all
     select ceil(random()*$1)::int as tag from generate_series(1,$2);
   $$ language sql strict volatile;
   
   insert into t_user_tag
   select uid, gen_rand_tag(100000,63) as tag, clock_timestamp() 
   from generate_series(1,20000000) as uid on conflict (uid,tag) do nothing;
   
   -- 或使用如下方法加速导入
   create sequence seq;
   
   vi test.sql
   insert into t_user_tag
   select uid, gen_rand_tag(100000,63) as tag, clock_timestamp() 
   from nextval('seq'::regclass) as uid 
   on conflict(tag,uid) do nothing;
   
   pgbench -M prepared -n -r -P 1 -f ./test.sql -c 50 -j 50 -t 400000

5. 查询包含标签1和3的人群。示例如下：

   -- 人群数量
   select count(*) from 
   (
   select uid from t_user_tag where tag=1 
   intersect
   select uid from t_user_tag where tag=3
   ) t;
   -- Time: 1494.789 ms (00:01.495)
   
   -- 提取人群ID
   select uid from t_user_tag where tag=1 
   intersect
   select uid from t_user_tag where tag=3;
   -- Time: 3246.184 ms (00:03.246)

6. 查询包含标签1、3、10或200的人群。示例如下：

   -- 人群数量
   select count(*) from 
   (
   select uid from t_user_tag where tag=1 
   union
   select uid from t_user_tag where tag=3
   union
   select uid from t_user_tag where tag=10
   union
   select uid from t_user_tag where tag=200
   ) t;
   -- Time: 3577.714 ms (00:03.578)
   
   -- 提取人群ID
   select uid from t_user_tag where tag=1 
   union
   select uid from t_user_tag where tag=3
   union
   select uid from t_user_tag where tag=10
   union
   select uid from t_user_tag where tag=200;
   -- Time: 5682.458 ms (00:05.682)

方案二

方案概览

• 表结构：

  KEY：用户ID
  VALUES：标签数组

• 表索引：

  标签数组字段: GIN倒排索引

• 搜索方法

  与、或、非
  where VALUES @> array[标签s] -- 与
  where VALUES && array[标签s] -- 或
  where not VALUES @> array[标签s] -- 非

• 方案缺陷

  • 使用数组替代多字段存储标签，需要数据库支持倒排索引，但并不是所有数据库都支持倒排索引。

  • 使用数组替代多字段存储标签，加上倒排索引，存储空间会暴增。

  • 新增一个用户群体标签时，需要更新大量数据。

操作示例

1. 创建人群表，每条记录代表一个人群。示例如下：

   create table t_tag_dict (
   tag int primary key,   -- 标签(人群)id
   info text,  -- 人群描述
   crt_time timestamp  -- 时间
   ); 

2. 生成10万个人群标签。示例如下：

   insert into t_tag_dict values (1, '男', now());
   insert into t_tag_dict values (2, '女', now());
   insert into t_tag_dict values (3, '大于24岁', now());
   -- ...
   
   insert into t_tag_dict 
   select generate_series(4,100000), md5(random()::text), clock_timestamp();

3. 创建用户画像表，每个用户一条记录，用数组表示这个用户归属哪些标签。示例如下：

   create table t_user_tags (
   uid int8 primary key,   -- 用户id
   tags int[],            -- 用户标签（人群）数组
   mod_time timestamp     -- 时间
   ); 

4. 创建随机生成打标数组的函数。示例如下：

   create or replace function gen_rand_tags(int,int) returns int[] as $$
     select array_agg(ceil(random()*$1)::int) from generate_series(1,$2);
   $$ language sql strict;

5. 在10万个标签内随机提取8个标签。示例如下：

   select gen_rand_tags(100000, 8);
   
                      gen_rand_tags                   
   ---------------------------------------------------
    {43494,46038,74102,25308,99129,40893,33653,29690}
   (1 row)

6. 给2000万个用户打标，每个用户64个随机标签，其中男、女各一半。示例如下：

   insert into t_user_tags 
   select generate_series(1,10000000), 
   array_append(gen_rand_tags(100000, 63),1), now();
   
   insert into t_user_tags 
   select generate_series(10000001,20000000), 
   array_append(gen_rand_tags(100000, 63),2), now();

7. 创建人群标签字段倒排索引。示例如下：

   create index idx_t_user_tags_1 on t_user_tags using gin (tags);

8. 查询包含标签1和3的人群。示例如下：

   -- 人群数量
   select count(uid) from t_user_tags where tags @> array[1,3];
   
   -- 提取人群ID
   select uid from t_user_tags where tags @> array[1,3];

9. 查询包含标签1、3、10或200的人群。示例如下：

   -- 人群数量
   select count(uid) from t_user_tags where tags && array[1,3,10,200];
   
   -- 提取人群ID
   select uid from t_user_tags where tags && array[1,3,10,200];

方案三

本方案通过roaringbitmap插件实现快速查询，相关说明请参见位图计算（roaringbitmap）。

方案概览

• 表结构：

  KEY：标签ID
  VALUES: 用户bitmap

• 表索引：

  标签ID字段: Btree索引

• 搜索方法：

  聚合bitmap: 与、或、非
  and_agg(bitmaps) where KEY in (标签s) -- 与
  or_agg(bitmaps) where KEY in (标签s) -- 或
  except(bitmap1,bitmap2) -- 非

• 方案优势：

  • 表存储占用空间小。

  • 索引存储占用空间小。仅需一个Btree索引，索引记录数少（有多少标签，就有多少条记录，通常标签数在百万以内）。

  • 新增一个用户群体标签时，不需要更新大量数据，仅需新增一条人群的bitmap记录。

  • 查询性能极好。

• 方案缺陷：

  • bitmap最大长度为1GB，用户数超过长度时需要使用offset，方法如下：

    offset0_bitmap, offset1gb_bitmap, ... 

  • 用户ID需要是数字（建议连续数值），如果没有数值型UID，需要建立映射表。

操作示例

1. 安装插件。示例如下：

   create extension roaringbitmap;

2. 创建用户标签bitmap表。示例如下：

   create table t_tag_users (
     tagid int primary key,   -- 用户标签（人群）id 
     uid_offset int,          -- 由于userid是int8类型，roaringbitmap内部使用int4存储，需要转换一下。   
     userbits roaringbitmap,     -- 用户ID聚合的bitmap  
     mod_time timestamp       -- 时间 
   );

3. 生成UID标签bitmap表。示例如下：

   insert into t_tag_users 
   select tagid, uid_offset, rb_build_agg(uid::int) as userbits from 
   (
   select 
     unnest(tags) as tagid, 
     (uid / (2^31)::int8) as uid_offset, 
     mod(uid, (2^31)::int8) as uid 
   from t_user_tags 
   ) t 
   group by tagid, uid_offset; 

4. 查询包含标签1和3的人群。示例如下：

   -- 人群数量
   select sum(ub) from 
   (
   select uid_offset,rb_and_cardinality_agg(userbits) as ub 
   from t_tag_users 
   where tagid in (1,3) 
   group by uid_offset
   ) t;
   
   -- 提取人群ID
   select uid_offset,rb_and_agg(userbits) as ub 
   from t_tag_users 
   where tagid in (1,3) 
   group by uid_offset;

5. 查询包含标签1、3、10或200的人群。示例如下：

   -- 人群数量
   select sum(ub) from 
   (
   select uid_offset,rb_or_cardinality_agg(userbits) as ub 
   from t_tag_users 
   where tagid in (1,3,10,200) 
   group by uid_offset
   ) t;
   
   -- 提取人群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;