使用PostgreSQL/GreenPlum实现通过画像标签进行精准人群圈选

        最近在工作中,接触到了使用GreenPlum实现对公司员工打标签以及进一步的人群圈选工作.在一系列调研之后,发现基于GreenPlum可以比较快接、简单的实现该需求.因此特地写一篇文章来记录与分享一下.

        由于是在本地电脑进行相关演示操作,因此使用了PostgreSQL(GP是基于PGSQL的分布式实现).

1.创建员工信息表,作为员工主数据标,用于员工的基础信息等内容.

create table hr.emp_info_test(
    fcode int primary key,   -- 员工工号
    info text,  -- 员工信息
    create_time timestamp  -- 创建时间
);

        

2.使用内置的随机数生成函数,来生成5万条员工数据.

insert into hr.emp_info_test
select generate_series(1,50000), md5(random()::text), clock_timestamp();

数据概览:

   

  3.创建员工画像标签表,记录每个员工以及各自的标签.

create table hr.emp_tags_test (
fcode int primary key,   -- 员工工号
tags int[],            -- 用户标签
create_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万名员工随机生成32个标签数据
insert into hr.emp_tags_test
select generate_series(1,50000),
gen_rand_tags(1000, 31), now();

数据概览:

5.创建人群标签字段倒排索引

create index emp_tags_1 on hr.emp_tags_test using gin (tags);

6.通过标签来查询员工工号

-- 代码说明
-- where VALUES @> array[标签s] -- 与
-- where VALUES && array[标签s] -- 或
-- where not VALUES @> array[标签s] -- 非

-- 查询包含标签1、301标签的员工总数
select count(1) from hr.emp_tags_test where tags @> array[1,301];

-- 查询包含标签1、301标签的员工个人信息
select
    info.fcode
    ,info.info
    ,tags.tags   
from 
    hr.emp_info_test info
join 
    hr.emp_tags_test tags
on 
    info.fcode = tags.fcode
where 
    tags @> array[1,301];

 数据概览:

该方式的查询速度也是非常快的,在5万条员工数据下匹配出58条员工,用时为ms级别,已经能够非常的满足我司的需求了(毕竟我司也就两三万人~).但是该方式会比较占用存储空间,因为所有标签都需要通过数组进行存储,再加上倒排索引,存储空间会比较大.不过,也确实是简单、好用的人群圈选方案.