作者
digoal
日期
2019-11-18
标签
PostgreSQL , roaring bitmap , tag , label , 检索
背景
roaringbitmap是roaring bitmap库在PG数据库中的一种类型实现，支持roaring bitmap的存取、集合操作，聚合等运算。
通常被用在用户画像等标签操作场景。
例如，
- 包含某些标签等人群集合，
- 某些人的共同点、不同点，
- 某人是否包含某标签。
- 某标签中是否包含某人。
- 同时包含某些标签的有多少人
- 某个标签有多少人
安装
su - postgres
export PATH=$PGHOME/bin:$PATH
USE_PGXS=1 make
USE_PGXS=1 make install
psql -c "create extension roaringbitmap;"
USE_PGXS=1 make installcheck
类型
roaringbitmap
操作符
与、或、疑惑、加、减、包含、相等、不相等、相交等
函数
加、减、包含、提取、剪切、转换等
聚合
与、或、异或、集合数量
限制
一个roaring bitmap可以存储40亿个元素，不能超出int32范畴。
范围超过40亿怎么办，offset 分段。例如使用多个字段表示。
create table tags(uid int, tag_off0 roaringbitmap, tag_off1 roaringbitmap)
设计举例
正向关系
user, tags
create table t1 (userid int, tags roaringbitmap);
反向
tag, users
create table t2 (tag int, uids roaringbitmap);
操作
1、roaringbitmap类型输出形态
```
postgres=# show roaringbitmap.output_format ;
roaringbitmap.output_format
bytea
(1 row)
postgres=# set roaringbitmap.output_format ='a';
psql: ERROR: 22023: invalid value for parameter "roaringbitmap.output_format": "a"
HINT: Available values: array, bytea.
LOCATION: parse_and_validate_value, guc.c:6682
postgres=# set roaringbitmap.output_format ='array';
SET
```
2、build roaringbitmap
```
postgres=# select roaringbitmap('{1,2,-1,-10000}');
roaringbitmap
{1,2,-10000,-1}
(1 row)
```
3、增
```
postgres=# select roaringbitmap('{1,2,-1,-10000}') | 123;
?column?
{1,2,123,-10000,-1}
(1 row)
或
postgres=# select rb_add(roaringbitmap('{1,2,-1,-10000}') , 123);
rb_add
{1,2,123,-10000,-1}
(1 row)
```
4、删
```
postgres=# select roaringbitmap('{1,2,3}') - 3;
?column?
{1,2}
(1 row)
或
postgres=# select rb_remove(roaringbitmap('{1,2,3}'),1);
rb_remove
{2,3}
(1 row)
postgres=# select rb_remove(roaringbitmap('{1,2,3}'),4);
rb_remove
{1,2,3}
(1 row)
```
5、判断(包含，相交，相等，不相等)
```
postgres=# select roaringbitmap('{1,2,3,4,5}') @> 3;
?column?
t
(1 row)
postgres=# select roaringbitmap('{1,2,3,4,5}') @> 30;
?column?
f
(1 row)
postgres=# select roaringbitmap('{1,2,3,4,5}') @> roaringbitmap('{1,2,3}');
?column?
t
(1 row)
postgres=# select roaringbitmap('{1,2,3,4,5}') @> roaringbitmap('{1,2,3,9}');
?column?
f
(1 row)
postgres=# select roaringbitmap('{1,2,3,4,5}') && roaringbitmap('{1,2,3,9}');
?column?
t
(1 row)
postgres=# select roaringbitmap('{1,2,3,4,5}') = roaringbitmap('{1,2,3,9}');
?column?
f
(1 row)
postgres=# select roaringbitmap('{1,2,3,4,5}') = roaringbitmap('{1,5,4,3,2,1,1}');
?column?
t
(1 row)
postgres=# select roaringbitmap('{1,2,3,4,5}') <> roaringbitmap('{1}');
?column?
t
(1 row)
```
6、取值
```
postgres=# SELECT rb_iterate('{1,2,3}'::roaringbitmap);
rb_iterate
1 2 3复制
(3 rows)
postgres=# SELECT unnest(rb_to_array('{1,2,3}'::roaringbitmap));
unnest
1 2 3复制
(3 rows)
postgres=# SELECT unnest(rb_to_array('{1,2,3,3,3}'::roaringbitmap));
unnest
1 2 3复制
(3 rows)
```
值的索引(从0开始offset)。顺序：
0, 1, ..., 2147483647, -2147483648, -2147483647,..., -1
返回有序值存放的index
```
Return the 0-based index of element in this roaringbitmap, or -1 if do not exsits
postgres=# select rb_index('{-1,1,2,3}',3);
rb_index
2复制
(1 row)
postgres=# select rb_index('{100,-1,1,2,3}',3);
rb_index
2复制
(1 row)
postgres=# select rb_index('{0,100,-1,1,2,3}',3);
rb_index
3复制
(1 row)
postgres=# select rb_index('{0,100,-1,1,2,3}',-1);
rb_index
5复制
(1 row)
postgres=# select rb_index('{0,100,-1,1,2,3,-2}',-1);
rb_index
6复制
(1 row)
```
按索引取值(从0开始offset)
顺序：
0, 1, ..., 2147483647, -2147483648, -2147483647,..., -1
```
Return subset [bitset_offset,bitset_offset+bitset_limit) of bitmap between range [range_start,range_end)
从index 5开始取数，取2个。
postgres=# select rb_select('{-1,-999,100,1,2,3,102,5,6,7,8,9}',2,5);
rb_select
{7,8}
(1 row)
从index 2开始取数，取5个。
postgres=# select rb_select('{-1,-999,100,1,2,3,102,5,6,7,8,9}',5,2);
rb_select
{3,5,6,7,8}
(1 row)
```
聚合计算
1、多个rb共同点集合
select rb_and_agg(bitmap)
from (values (roaringbitmap('{1,2,3}')),
(roaringbitmap('{2,3,4}'))
) t(bitmap)
2、多个rb不同点集合
select rb_xor_agg(bitmap)
from (values (roaringbitmap('{1,2,3}')),
(roaringbitmap('{2,3,4}'))
) t(bitmap)
3、多个rb集合点
select rb_or_agg(bitmap)
from (values (roaringbitmap('{1,2,3}')),
(roaringbitmap('{2,3,4}'))
) t(bitmap)
4、两个rb的差值
roaringbitmap('{1,2,3}') - roaringbitmap('{3,4,5}')
5、集合有多大
select rb_or_cardinality_agg(bitmap)
from (values (roaringbitmap('{1,2,3}')),
(roaringbitmap('{2,3,4}'))
) t(bitmap)
6、有多少共同点
select rb_and_cardinality_agg(bitmap)
from (values (roaringbitmap('{1,2,3}')),
(roaringbitmap('{2,3,4}'))
) t(bitmap)
7、有多少不同点
select rb_xor_cardinality_agg(bitmap)
from (values (roaringbitmap('{1,2,3}')),
(roaringbitmap('{2,3,4}'))
) t(bitmap)
8、将多个值聚合为roaringbitmap
select rb_build_agg(id)
from (values (1),(2),(3)) t(id)
详细参考：
https://pgxn.org/dist/pg_roaringbitmap/0.5.0/
参考
https://pgxn.org/dist/pg_roaringbitmap/0.5.0/
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
