阿里云PostgreSQL案例精选1 - 实时精准营销、人群圈选

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

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

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

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();  
Copy

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

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  
Copy

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)

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)  
Copy

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)

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)  
Copy

public | t_user_tag         | table | postgres | 62 GB   |   
 public | t_user_tag_pkey    | index | postgres | t_user_tag    | 61 GB  |  
Copy

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

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();  
Copy

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

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

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

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();  
Copy

create index idx_t_user_tags_1 on t_user_tags using gin (tags);  
Copy

select count(uid) from t_user_tags where tags @> array[1,3];

select uid from t_user_tags where tags @> array[1,3];  
Copy

select count(uid) from t_user_tags where tags && array[1,3,10,200];

select uid from t_user_tags where tags && array[1,3,10,200];  
Copy

SELECT roaringbitmap('{1,2,3}') | roaringbitmap('{3,4,5}');  
SELECT roaringbitmap('{1,2,3}') & roaringbitmap('{3,4,5}');  
SELECT roaringbitmap('{1,2,3}') # roaringbitmap('{3,4,5}');  
SELECT roaringbitmap('{1,2,3}') - roaringbitmap('{3,4,5}');

SELECT rb_build_agg(e) FROM generate_series(1,100) e;  
SELECT rb_or_agg(bitmap) FROM t1;  
SELECT rb_and_agg(bitmap) FROM t1;  
SELECT rb_xor_agg(bitmap) FROM t1;

rb_or_cardinality_agg  
rb_and_cardinality_agg  
rb_xor_cardinality_agg

Opperator   Input   Output  Desc    Example Result  
@>  roaringbitmap,roaringbitmap bool    contains    roaringbitmap('{1,2,3}') @> roaringbitmap('{3,4,5}')    f  
@>  roaringbitmap,integer   bool    contains    roaringbitmap('{1,2,3,4,5}') @> 3   t  
<@  roaringbitmap,roaringbitmap bool    is contained by roaringbitmap('{1,2,3}')    f  
<@  integer,roaringbitmap   bool    is contained by 3   t  
&&  roaringbitmap,roaringbitmap bool    overlap (have elements in common)   roaringbitmap('{1,2,3}') && roaringbitmap('{3,4,5}')    t
=   roaringbitmap,roaringbitmap bool    equal   roaringbitmap('{1,2,3}') = roaringbitmap('{3,4,5}') f  
<>  roaringbitmap,roaringbitmap bool    not equal   roaringbitmap('{1,2,3}') <> roaringbitmap('{3,4,5}')    t  
Copy

create table t_tag_users (  
  tagid int primary key,   -- 用户标签（人群）id   
  uid_offset int,          -- 由于userid是int8类型，roaringbitmap内部使用int4

userbits roaringbitmap,     -- 用户id聚合的 bitmap    
mod_time timestamp       -- 时间   
);  
Copy

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

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;

select uid_offset,rb_and_agg(userbits) as ub   
from t_tag_users   
where tagid in (1,3)   
group by uid_offset;  
Copy

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;

select uid_offset,rb_or_agg(userbits) as ub   
from t_tag_users   
where tagid in (1,3,10,200)   
group by uid_offset;   
Copy