互联网用户画像，精准营销，数仓有妙招

本文分享自华为云社区《互联网用户画像，精准营销，GaussDB(DWS)来支招》，作者： fudgefactor。

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

• 在电商行业中，商家在进行营销活动前，需要根据活动的目的，圈选一批满足特定特征的目标用户群体进行广告推送。

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

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

这些业务场景都有一些共同的特点：

• 数据量庞大，运算量极大。

• 用户规模庞大，标签多，字段多，占用存储空间也多。

• 圈选的特征条件多样化，很难找到固定索引，如果每个字段一个索引，存储空间又会暴增。

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

• 数据更新时效要求高，用户画像几乎要求实时更新。

针对上述业务场景特点，GaussDB(DWS)的 roaringbitmap 可以高效生成、压缩、解析位图数据，支持最常见的位图聚合操作（与、或、非、异或），满足用户在亿级以上用户、千万级标签的大数据量下实时精准营销、快速圈选用户的需求。

下面先通过两个示例来理解 Roaringbitmap 在用户画像场景中的使用方法。

示例一：

  假设有一张用户浏览网页的流水信息表 userinfo，表中的字段如下：

CREATE TABLE userinfo(userid int,age int,gender text,salary int,hobby  text)with (orientation=column);

userinfo 表中的数据会随着用户信息的变化不断增长，同时，如果用户有多个"爱好"(hobby)，那么就有多条记录对应同一个 userid。

假设要筛选出所有“收入大于 10000 元的男性，年龄大于 30 岁，爱好钓鱼”的群体，向这些目标群体推送特定的消息。

传统的方法是直接在原表上执行查询，语句如下：

select distinct userid from userinfo where salary > 10000 and age > 30 and gender ='m' and hobby ='fishing';

当 userinfo 表的数据量不大的时候，可以通过在 salary, age, gender，hobby 列上建立索引来满足需求。但是如果 userinfo 表的数据量非常大，同时一张表的标签数非常多（比如有 100 个属性，需要对应有 100 个列）的时候，上述语句就不能满足诉求，因为如下原因：

• 由于不确定会按照那些属性做过滤，需要创建的索引会非常多。

• 求 distinct 的性能比较差。

这种场景下使用 roaringbitmap 就会有比较好的效果。

1. 新建一张 Roaringbitmap 表：

CREATE TABLE userinfoset( age int,gender text,salary int,hobby  text,userset roaringbitmap,PRIMARY KEY(age,gender,salary,hobby))with (orientation=column);

    2. 所有 userinfo 表中的数据要通过标签列聚合到 userinfoset 表中。可以采用对全量数据进行聚合的方法（如下命令所示）。

insert into userinfoset usselect age, gender, salary, hobby, rb_build_agg(userid)from userinfogroup by age, gender, salary, hobby;

   3. 直接查询 userinfoset 表获得用户筛选信息。

select rb_iterate(rb_or_agg(userset)) from userinfoset where salary > 10000 and age > 30 and gender ='m' and hobby ='fishing';

数据进行聚合后的 userinfoset 的数据量相比源表小了很多，基表 scan 的性能会快很多，同时基于 Roaringbitmap 的优势，计算 rb_or_agg 和 rb_iterate 的性能也很好，相比传统的方法，性能明显提升。

示例二：

由于 DWS 规格的限制，每张表最大可以有 1600 列，如果描述用户的属性有 10000 个，我们无法通过创建一个有 10000 列的表来实现这个方案，那么示例一中的方案就不再有效了。为此，我们可以这样设计我们的表结构：

create table userinfoset(    tag_value_id int,    userset roaringbitmap)with(orientation=column);

其中 tag_value_id 表示属性值对应的 id，比如，性别这一属性，有”男“，”女“两个值，我们可以把它编码为 1,2；学历这个属性的取值是”专科“，”本科“，”硕士“，“博士”，那么分别编码为 3,4,5,6，等等。将不同的属性值编码为不同的 id 值。userset 列表示的是满足 tag_value_id 所对应属性值的用户 id 的集合。比如 tag_value_id=1 这条记录对应的 userset 就是所有性别为男的用户的集合。

数据加工：这个表的数据一般是需要通过加工得到的，假设原始数据的表结构如下(一共有 10 张表)：

create table origin_1 (userid int,tag_value_id1 int,tag_value_id2 int,tag_value_id3 int,...tag_value_id998 int,tag_value_id999 int,tag_value_id1000 int)with(orientation=column);...
create table origin_10(userid int,tag_value_id9001 int,tag_value_id9002 int,tag_value_id9003 int,...tag_value_id9998 int,tag_value_id9999 int,tag_value_id10000 int)with(orientation=column);

我们可以通过类似以下的语句将数据加工到目标表中：

insert into userinfoset selecttag_value_id1,rb_build_agg(userid) origin_1 from origingroup by tag_value_id1;...insert into userinfoset selecttag_value_id10000,rb_build_agg(userid) origin_10 from origingroup by tag_value_id10000;

查询：假设需要圈选性别为男，学历为本科的用户的的个数有哪些，可以用以下语句实现：

select rb_or_cardinality_agg(userset) from userinfoset where tag_value_id in (1,4);

如果用户要圈选的人群有更多的特征，将对应的 tag_value_id 加入到 in 子句中即可。如果想要知道这些用户的具体的 userid，可以通过如下函数实现：

select rb_iterate(rb_or_cardinality_agg(userset)) from userinfoset where tag_value_id in (1,4);

可以通过查询产品手册实现更加多样化的人群圈选策略。

点击关注，第一时间了解华为云新鲜技术~​