全部产品
Search
文档中心

云数据库 RDS:实时精准营销(人群圈选)

更新时间:Dec 06, 2023

您可以通过RDS PostgreSQL实现实时精准营销,快速圈选目标用户。

前提条件

背景信息

几乎所有行业(如互联网、游戏、教育等)都有实时精准营销的需求。通过系统生成用户画像,在营销时通过条件组合筛选用户,快速提取目标群体,例如:

  • 电商行业中,商家在运营活动前,需要根据活动的目标群体的特征,圈选出一批目标用户进行广告推送或进行活动条件的判断。

  • 游戏行业中,商家需要根据玩家的某些特征进行圈选,针对性地发放大礼包,提高玩家活跃度。

  • 教育行业中,需要根据学生不同的特征,推送有针对性的习题,帮助学生查缺补漏。

  • 搜索、门户、视频网站等业务中,根据用户的关注热点,推送不同的内容。

这些行业都有一些共同的业务痛点:

  • 数据量庞大,运算量也极大。

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

  • 字段多,可能会超过数据库的字段数限制,一般数据库最多支持1000多个字段。

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

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

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

  • 性能要求高,因为实时营销要求秒级响应。

  • 数据更新时效要求高,用户画像要求近实时的更新,否则可能圈选到不精准用户(例如用户昨天在浏览手机,昨晚已下单,但是数据未实时更新,那么手机卖家圈选时这个用户就会被选中,但是该用户实际上已经不是目标群体)。

常见的产品(如MySQL)无法在有限的资源下满足实时圈选目标群体的需求。

本文介绍如何基于RDS PostgreSQL实现实时精准营销,您可以根据业务需要使用三种方案之一。

实现方案

方案一

说明

PostgreSQL和MySQL都支持方案一。

方案概览

  • 表结构:

    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)

方案二

说明

仅PostgreSQL支持方案二。MySQL不支持数组类型、倒排索引。

方案概览

  • 表结构:

    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];

方案三

说明

仅PostgreSQL支持方案三。MySQL不支持位图功能。

本方案通过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;

方案对比

对比项

方案一

(MySQL/PostgreSQL)

方案二

(PostgreSQL)

方案三

(PostgreSQL)

方案三相比方案一的提升

与查询圈选用户速度

1.5秒

0.042秒

0.0015秒

99900%

或查询圈选用户速度

3.6秒

3秒

0.0017秒

211665%

空间占用(表)

63,488 MB

3,126 MB

1390MB

4467%

空间占用(索引)

62,464 MB

3139 MB

2MB

3123100%

创建索引速度

-

20分钟

极快(约为0秒)

-

说明

方案使用的MySQL 8.0和PostgreSQL 12实例规格均为8核CPU、32GB内存、1500GB ESSD云盘。

总结

RDS PostgreSQL 12或以上版本支持位图功能(roaringbitmap插件),可以高效生成、压缩、解析位图数据,支持最常见的位图聚合操作(与、或、非、异或),满足您在亿级以上用户、千万级标签的大数据量下实时精准营销、快速圈选用户的需求。

相比MySQL的方案,RDS PostgreSQL方案优势非常明显,是一个低成本、高效率的解决方案。