文档首页/ 数据仓库服务 GaussDB(DWS)/ SQL语法参考/ SQL语法参考(8.1.3.x)/ 函数和操作符/ 位图函数和操作符/ Roaring Bitmap聚合函数
更新时间：2024-11-11 GMT+08:00
查看PDF
分享

Roaring Bitmap聚合函数

GaussDB(DWS)自8.1.3集群版本开始，支持高效的位图聚合函数，可用于用户画像，精准营销等场景，极大的提高了查询性能。

rb_build_agg(int)

描述：将分组内的int值聚合成一个roaringbitmap值。

返回值类型：roaringbitmap

示例：

1
2
3
4
5
6
 
CREATE TABLE t1 (a int ,b int);
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using round-robin as the distribution mode by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
CREATE TABLE
INSERT INTO t1 SELECT generate_series(1,10),generate_series(1,20,2);
INSERT 0 10

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
 
SELECT rb_iterate(rb_build_agg(b)) FROM t1;
rb_iterate
------------
1
3
5
7
9
11
13
15
17
19
(10 rows)

rb_and_agg(roaringbitmap)

描述：将分组内的roaringbitmap数据按照交的操作聚合成一个roaringbitmap集合。

示例：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
 
CREATE TABLE r1(a int ,b roaringbitmap);
INSERT INTO r1 SELECT a, rb_build_agg(b) FROM t1 GROUP BY a;
INSERT INTO t1 SELECT generate_series(1,10),generate_series(1,20,4);
INSERT INTO r1 SELECT a, rb_build_agg(b) FROM t1 GROUP BY a;
SELECT a, rb_to_array(rb_and_agg(b)) FROM r1 GROUP BY a ORDER BY a;
 a  | rb_to_array
----+-------------
 1  | {1}
 2  | {3}
 3  | {5}
 4  | {7}
 5  | {9}
 6  | {11}
 7  | {13}
 8  | {15}
 9  | {17}
10  | {19}
(10 rows)

rb_or_agg(roaringbitmap)

描述：将分组内的roaringbitmap按照并的逻辑组合成一个roaringbitmap。

示例：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
 
SELECT a, rb_to_array(rb_or_agg(b)) FROM r1 GROUP BY a ORDER BY a;
 a  | rb_to_array
----+-------------
 1  | {1}
 2  | {3,5}
 3  | {5,9}
 4  | {7,13}
 5  | {9,17}
 6  | {1,11}
 7  | {5,13}
 8  | {9,15}
 9  | {13,17}
 10 | {17,19}
(10 rows)

rb_xor_agg(roaringbitmap)

描述：将分组内的roaringbitmap按照异或的逻辑组合成一个roaringbitmap。

示例：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
 
SELECT a, rb_to_array(rb_xor_agg(b)) FROM r1 GROUP BY a ORDER BY a;
a   | rb_to_array
----+-------------
 1  | {}
 2  | {5}
 3  | {9}
 4  | {13}
 5  | {17}
 6  | {1}
 7  | {5}
 8  | {9}
 9  | {13}
 10 | {17}
(10 rows)

rb_and_cardinality_agg(roaringbitmap)

描述：分组内的roaringbitmap按照交集计算后的基数。

返回值类型：int

示例：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
 
SELECT a, rb_and_cardinality_agg(b) FROM r1 GROUP BY a ORDER BY 1;
 a  | rb_and_cardinality_agg
----+------------------------
 1  |                      1
 2  |                      1
 3  |                      1
 4  |                      1
 5  |                      1
 6  |                      1
 7  |                      1
 8  |                      1
 9  |                      1
 10 |                      1
(10 rows)

rb_or_cardinality_agg(roaringbitmap)

描述：将分组内的roaringbitmap按照并集计算后的基数。

示例：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
 
SELECT a, rb_or_cardinality_agg(b) FROM r1 GROUP BY a ORDER BY 1;
 a  | rb_or_cardinality_agg
----+-----------------------
 1  |                     1
 2  |                     2
 3  |                     2
 4  |                     2
 5  |                     2
 6  |                     2
 7  |                     2
 8  |                     2
 9  |                     2
 10 |                     2
(10 rows)

rb_xor_cardinality_agg(roaringbitmap)

描述：将分组内的roaringbitmap按照异或的逻辑合并后的基数。

示例：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
 
SELECT a, rb_xor_cardinality_agg(b) FROM r1 GROUP BY a ORDER BY 1;
 a  | rb_xor_cardinality_agg
----+------------------------
 1  |                      0
 2  |                      1
 3  |                      1
 4  |                      1
 5  |                      1
 6  |                      1
 7  |                      1
 8  |                      1
 9  |                      1
 10 |                      1
(10 rows)