Skip to main content

Bitmap Functions

Extreme DPS provides a series of Bitmap functions to help users work with roaring bitmaps.

RB_BUILD​

Converts an integer array into a roaring bitmap.

Syntax​

RB_BUILD(<int_array>)

Arguments​

<int_array>: an array of integers.

Returns​

A roaringbitmap value.

Examples​

SELECT RB_TO_STRING(RB_BUILD(ARRAY [1,2,3]));
-> {1,2,3}
SELECT RB_TO_STRING(RB_BUILD('{-1,2,555555,-4}'::int[]));
-> {2,555555,-4,-1}
Data representation

Arrays can be defined in two formats:

  1. Using the ARRAY keyword followed by a list of comma-separated elements enclosed in square brackets.
  2. As a string with '::int[]', where the string contains comma-separated elements enclosed in curly braces.

For more details, refer to Arrays.

ROARINGBITMAP​

Converts a string into a roaring bitmap.

Syntax​

ROARINGBITMAP(<varchar>)

Arguments​

<varchar>: a varchar string that can be expressed in the following formats:

  • A string enclosed in curly braces {}, with elements separated by commas, e.g., "{1,2,3}"
  • A valid hexadecimal string starting with \x, e.g., "\x3a30000000000000"

Returns​

A roaringbitmap value.

Examples​

SELECT RB_TO_STRING(ROARINGBITMAP('{1,2,3}'));
-> {1,2,3}
SELECT RB_TO_STRING(ROARINGBITMAP('\x3a30000000000000'));
-> {}

RB_FROM_STRING​

Same as the ROARINGBITMAP function, this function converts a string into a roaring bitmap.

Syntax​

RB_FROM_STRING(<varchar>)

Arguments​

<varchar>: a varchar string that can be expressed in the following formats:

  • A string enclosed in curly braces {}, with elements separated by commas, e.g., "{1,2,3}"
  • A valid hexadecimal string starting with \x, e.g., "\x3a30000000000000"

Returns​

A roaringbitmap value.

Examples​

SELECT RB_TO_STRING(RB_FROM_STRING('{1,2,3}'));
-> {1,2,3}
SELECT RB_TO_STRING(RB_FROM_STRING('\x3a30000000000000'));
-> {}

RB_FROM_BYTEA​

Converts a bytea value to a roaring bitmap.

Syntax​

RB_FROM_BYTEA(<bytea>)

Arguments​

<bytea>: the bytea value to convert.

Returns​

A roaringbitmap value.

Examples​

SELECT RB_TO_STRING(RB_FROM_BYTEA('\x3a30000000000000'::bytea));
-> {}
SELECT RB_TO_STRING(RB_FROM_BYTEA('\x3a3000000100000000000000100000000100'::bytea));
-> {1}

RB_TO_ARRAY​

Converts a roaring bitmap to an integer array. This is the inverse operation of RB_BUILD.

Syntax​

RB_TO_ARRAY(<roaringbitmap>)

Arguments​

<roaringbitmap>: the roaring bitmap to convert.

Returns​

An integer array.

Examples​

SELECT RB_TO_ARRAY(RB_FROM_STRING('{1,2,3}'));
-> {1,2,3}

RB_TO_STRING​

Converts a roaring bitmap to a string. This is the inverse operation of RB_FROM_STRING.

Syntax​

RB_TO_STRING(<roaringbitmap>)

Arguments​

<roaringbitmap>: the roaring bitmap to convert.

Returns​

A string value.

Examples​

SELECT RB_TO_STRING(RB_FROM_STRING('{1,2,3}'));
-> {1,2,3}

RB_TO_BYTEA​

Converts a roaring bitmap to a bytea value, the inverse operation of RB_FROM_BYTEA.

Syntax​

RB_TO_BYTEA(<roaringbitmap>)

Arguments​

<roaringbitmap>: the roaring bitmap to convert.

Returns​

A bytea value.

Examples​

SELECT RB_TO_BYTEA(RB_FROM_BYTEA('{1,2,3}'));
-> \x3a300000010000000000020010000000010002000300

RB_CARDINALITY​

Calculates the cardinality (i.e., the number of distinct values) of a roaring bitmap.

Syntax​

RB_CARDINALITY(<roaringbitmap>)

Arguments​

<roaringbitmap>: the roaring bitmap whose cardinality is to be calculated.

Returns​

A bigint value, which indicates the number of distinct elements in the roaring bitmap.

Examples​

SELECT RB_CARDINALITY(RB_FROM_STRING('{1,2,3}'));
-> 3
SELECT RB_CARDINALITY(RB_FROM_STRING('{4,5}'));
-> 2
SELECT RB_CARDINALITY(RB_FROM_STRING('{1,2,3,3,3}'));
-> 3

RB_MIN​

Calculates the minimum value of all elements in a roaring bitmap. NULL will be returned if the roaring bitmap is NULL or empty.

Syntax​

RB_MIN(<roaringbitmap>)

Arguments​

<roaringbitmap>: the roaring bitmap in which minimum value is to be calculated.

Returns​

An integer value representing the minimum value.

Examples​

SELECT RB_MIN(RB_FROM_STRING('{1,2,3}'));
-> 1
SELECT RB_MIN(RB_FROM_STRING('{4,5}'));
-> 4
SELECT RB_MIN(RB_FROM_STRING('{-1,4,5}'));
-> 4
info

In a roaringbitmap, negative numbers are interpreted as larger unsigned integers. For example, -1 is actually 4294967295 (i.e., 0xFFFFFFFF) in unsigned integer representation. Therefore, in the example '{-1,4,5}', the minimum value is 4, not -1.

RB_MAX​

Calculates the maximum value of all elements in a Roaring Bitmap. Returns NULL if the Roaring Bitmap is NULL or empty.

Syntax​

RB_MAX(<roaringbitmap>)

Arguments​

<roaringbitmap>: The Roaring Bitmap whose maximum value is to be calculated.

Returns​

An integer value representing the maximum value.

Examples​

SELECT RB_MAX(RB_FROM_STRING('{1,2,3}'));
-> 3
SELECT RB_MAX(RB_FROM_STRING('{4,5}'));
-> 5
SELECT RB_MAX(RB_FROM_STRING('{-1,4,5}'));
-> -1

RB_IS_EMPTY​

Checks whether a roaring bitmap is empty.

Syntax​

RB_IS_EMPTY(<roaringbitmap>)

Arguments​

<roaringbitmap>: the roaring bitmap to check.

Returns​

A boolean value.

Examples​

SELECT RB_IS_EMPTY(RB_FROM_STRING('{}'));
-> t
SELECT RB_IS_EMPTY(RB_FROM_STRING('{1}'));
-> f

RB_AND​

Returns the intersection of two roaring bitmaps.

Syntax​

RB_AND(<roaringbitmap_1>, <roaringbitmap_2>)

Arguments​

  • <roaringbitmap_1>

    The first roaring bitmap.

  • <roaringbitmap_2>

    The second roaring bitmap.

Returns​

A roaringbitmap value, which represents the intersection of the two roaring bitmaps.

Examples​

SELECT RB_TO_STRING(RB_AND(RB_FROM_STRING('{1,10,100}'), RB_FROM_STRING('{2}')));
-> {}
SELECT RB_TO_STRING(RB_AND(RB_FROM_STRING('{1,10,100}'), RB_FROM_STRING('{1,10}')));
-> {1,10}

RB_AND_CARDINALITY​

Calculates the intersection of two roaring bitmaps and returns the cardinality of the intersection.

Syntax​

RB_AND_CARDINALITY(<roaringbitmap_1>, <roaringbitmap_2>)

Arguments​

  • <roaringbitmap_1>

    The first roaring bitmap.

  • <roaringbitmap_2>:

    The second roaring bitmap.

Returns​

A bigint value.

Examples​

SELECT RB_AND_CARDINALITY(RB_FROM_STRING('{1,10,100}'), RB_FROM_STRING('{2}'));
-> 0
SELECT RB_AND_CARDINALITY(RB_FROM_STRING('{1,10,100}'), RB_FROM_STRING('{1,10}'));
-> 2

RB_ANDNOT​

Calculates the difference between two roaring bitmaps, and returns a new roaring bitmap that contains elements that are in the first roaring bitmap but not in the second.

Syntax​

RB_ANDNOT(<roaringbitmap_1>, <roaringbitmap_2>)

Arguments​

  • <roaringbitmap_1>

    The first roaring bitmap.

  • <roaringbitmap_2>:

    The second roaring bitmap.

Returns​

A roaringbitmap value.

Examples​

SELECT RB_TO_STRING(RB_ANDNOT(RB_FROM_STRING('{1,10,100}'), RB_FROM_STRING('{2}')));
-> {1,10,100}
SELECT RB_TO_STRING(RB_ANDNOT(RB_FROM_STRING('{1,10,100}'), RB_FROM_STRING('{1,10}')));
-> {100}

RB_ANDNOT_CARDINALITY​

Calculates the difference between two roaring bitmaps and returns the cardinality of the resulting roaring bitmap.

Syntax​

RB_ANDNOT_CARDINALITY(<roaringbitmap_1>, <roaringbitmap_2>)

Arguments​

  • <roaringbitmap_1>

    The first roaring bitmap.

  • <roaringbitmap_2>:

    The second roaring bitmap.

Returns​

A bigint value.

Examples​

SELECT RB_ANDNOT_CARDINALITY(RB_FROM_STRING('{1,10,100}'), RB_FROM_STRING('{2}'));
-> 3
SELECT RB_ANDNOT_CARDINALITY(RB_FROM_STRING('{1,10,100}'), RB_FROM_STRING('{1,10}'));
-> 1

RB_OR​

Returns the union of two roaring bitmaps.

Syntax​

RB_OR(<roaringbitmap_1>, <roaringbitmap_2>)

Arguments​

  • <roaringbitmap_1>

    The first roaring bitmap.

  • <roaringbitmap_2>:

    The second roaring bitmap.

Returns​

A roaringbitmap value.

Examples​

SELECT RB_TO_STRING(RB_OR(RB_FROM_STRING('{1,10,100}'), RB_FROM_STRING('{2}')));
-> {1,2,10,100}
SELECT RB_TO_STRING(RB_OR(RB_FROM_STRING('{1,10,100}'), RB_FROM_STRING('{1,10}')));
-> {1,10,100}

RB_OR_CARDINALITY​

Calculates the union of two roaring bitmaps and returns the cardinality of the resulting roaring bitmap.

Syntax​

RB_OR_CARDINALITY(<roaringbitmap_1>, <roaringbitmap_2>)

Arguments​

  • <roaringbitmap_1>

    The first roaring bitmap.

  • <roaringbitmap_2>:

    The second roaring bitmap.

Returns​

A bigint value.

Examples​

SELECT RB_OR_CARDINALITY(RB_FROM_STRING('{1,10,100}'), RB_FROM_STRING('{2}'));
-> 4
SELECT RB_OR_CARDINALITY(RB_FROM_STRING('{1,10,100}'), RB_FROM_STRING('{1,10}'));
-> 3

RB_XOR​

Performs an XOR operation between two roaring bitmaps and returns the resulting roaring bitmap.

Syntax​

RB_XOR(<roaringbitmap_1>, <roaringbitmap_2>)

Arguments​

  • <roaringbitmap_1>

    The first roaring bitmap.

  • <roaringbitmap_2>:

    The second roaring bitmap.

Returns​

A roaringbitmap value.

Examples​

SELECT RB_TO_STRING(RB_XOR(RB_FROM_STRING('{1,10,100}'), RB_FROM_STRING('{2}')));
-> {1,2,10,100}
SELECT RB_TO_STRING(RB_XOR(RB_FROM_STRING('{1,10,100}'), RB_FROM_STRING('{1,10}')));
-> {100}

RB_XOR_CARDINALITY​

Performs an XOR operation between two roaring bitmaps and returns the cardinality of the resulting roaring bitmap.

Syntax​

RB_XOR_CARDINALITY(<roaringbitmap_1>, <roaringbitmap_2>)

Arguments​

  • <roaringbitmap_1>

    The first roaring bitmap.

  • <roaringbitmap_2>:

    The second roaring bitmap.

Returns​

A bigint value.

Examples​

SELECT RB_XOR_CARDINALITY(RB_FROM_STRING('{1,10,100}'), RB_FROM_STRING('{2}'));
-> 4
SELECT RB_XOR_CARDINALITY(RB_FROM_STRING('{1,10,100}'), RB_FROM_STRING('{1,10}'));
-> 1

RB_EQUALS​

Checks whether two roaring bitmaps are equal.

Syntax​

RB_EQUALS(<roaringbitmap_1>, <roaringbitmap_2>)

Arguments​

  • <roaringbitmap_1>

    The first roaring bitmap.

  • <roaringbitmap_2>:

    The second roaring bitmap.

Returns​

A boolean value.

Examples​

SELECT RB_EQUALS(RB_FROM_STRING('{1,2,3}'), RB_FROM_STRING('{1,2,3}'));
-> t
SELECT RB_EQUALS(RB_FROM_STRING('{1,2,3}'), RB_FROM_STRING('{2,3,4}'));
-> f

RB_NOT_EQUALS​

Checks whether two roaring bitmaps are not equal.

Syntax​

RB_NOT_EQUALS(<roaringbitmap_1>, <roaringbitmap_2>)

Arguments​

  • <roaringbitmap_1>: The first Roaring Bitmap.
  • <roaringbitmap_2>: The second Roaring Bitmap.

Returns​

A boolean value.

Examples​

SELECT RB_NOT_EQUALS(RB_FROM_STRING('{1,2,3}'), RB_FROM_STRING('{1,2,3}'));
-> f
SELECT RB_NOT_EQUALS(RB_FROM_STRING('{1,2,3}'), RB_FROM_STRING('{2,3,4}'));
-> t

RB_JACCARD_DIST​

Returns the Jaccard distance (or Jaccard similarity coefficient) between two Roaring Bitmaps.

Syntax​

RB_JACCARD_DIST(<roaringbitmap_1>, <roaringbitmap_2>)

Arguments​

  • <roaringbitmap_1>

    The first roaring bitmap.

  • <roaringbitmap_2>:

    The second roaring bitmap.

Returns​

A double precision value.

Examples​

SELECT RB_JACCARD_DIST(RB_FROM_STRING('{1,2,3}'), RB_FROM_STRING('{3,4}'));
-> 0.25

RB_CONTAINS​

Checks whether a roaring bitmap contains a specific value or another roaring bitmap.

Syntax​

RB_CONTAINS(<roaringbitmap>, <value>)

Arguments​

  • <roaringbitmap>

    The Roaring Bitmap to search.

  • <value>

    The value to check for, which can be an integer or another roaring bitmap.

Returns​

A boolean value.

Examples​

SELECT RB_CONTAINS(RB_FROM_STRING('{1,2,3}'), 2);
-> t
SELECT RB_CONTAINS(RB_FROM_STRING('{1,2,3}'), 4);
-> f
SELECT RB_CONTAINS(RB_FROM_STRING('{1,2,3}'), RB_FROM_STRING('{1,3}'));
-> t
SELECT RB_CONTAINS(RB_FROM_STRING('{1,2,3}'), RB_FROM_STRING('{1,4}'));
-> f

RB_CONTAINEDBY​

Checks if a specific integer value is an element of a specific roaring bitmap or if a roaring bitmap is a subset of a specific roaring bitmap.

Syntax​

RB_CONTAINEDBY(<value>, <roaringbitmap>)

Arguments​

  • <value>

    The value to check for, which can be an integer or a roaring bitmap.

  • <roaringbitmap>

    The roaring bitmap to check against.

Returns​

A boolean value.

Examples​

SELECT RB_CONTAINEDBY(2, RB_FROM_STRING('{1,2,3}'));
-> t
SELECT RB_CONTAINEDBY(4, RB_FROM_STRING('{1,2,3}'));
-> f
SELECT RB_CONTAINEDBY(RB_FROM_STRING('{1,3}'), RB_FROM_STRING('{1,2,3}'));
-> t
SELECT RB_CONTAINEDBY(RB_FROM_STRING('{1,4}'), RB_FROM_STRING('{1,2,3}'));
-> f

RB_INDEX​

Returns the index of an element in a roaring bitmap (starting from 0), or -1 if the element does not exist.

Syntax​

RB_INDEX(<roaringbitmap>, <int>)

Arguments​

  • <roaringbitmap>

    The roaring bitmap to search.

  • <int>

    The element to search for, of type integer.

Returns​

An integer value.

Examples​

SELECT RB_INDEX(RB_FROM_STRING('{1,2,3}'), 3);
-> 2
SELECT RB_INDEX(RB_FROM_STRING('{1,2,3}'), 4);
-> -1

Here’s the refined version for the new set of functions:

RB_REMOVE​

Removes a specified element from a roaring bitmap.

Syntax​

RB_REMOVE(<roaringbitmap>, <int>)

Arguments​

  • <roaringbitmap>

    The original roaring bitmap.

  • <int>

    The element to remove, of type integer.

Returns​

A roaringbitmap value.

Examples​

SELECT RB_TO_STRING(RB_REMOVE(RB_FROM_STRING('{1,3}'), 1));
-> {3}
SELECT RB_TO_STRING(RB_REMOVE(RB_FROM_STRING('{1,3}'), 4));
-> {1,3}

RB_RANK​

Returns the cardinality of the subset in a roaring bitmap that contains all elements smaller than a specified value.

Syntax​

RB_RANK(<roaringbitmap>, <int>)

Arguments​

  • <roaringbitmap>

    The original roaring bitmap.

  • <int>

    The value up to which elements are counted, of type integer. This is the upper bound for the elements considered in the subset.

Returns​

An integer value.

Examples​

SELECT RB_RANK(RB_FROM_STRING('{1,10,100}'), 99);
-> 2

RB_SHIFTRIGHT​

Shifts each element in a roaring bitmap by adding a specified value.

Syntax​

RB_SHIFTRIGHT(<roaringbitmap>, <int>)

Arguments​

  • <roaringbitmap>

    The original roaring bitmap.

  • <int>

    The value to add to each element, of type integer.

Returns​

A roaringbitmap value.

Examples​

SELECT RB_TO_STRING(RB_SHIFTRIGHT(RB_FROM_STRING('{1,2,3}'), 3));
-> {4,5,6}
SELECT RB_TO_STRING(RB_SHIFTRIGHT(RB_FROM_STRING('{1,2,3}'), -1));
-> {0,1,2}

RB_SHIFTLEFT​

Shifts each element in a roaring bitmap by subtracting a specified value.

Syntax​

RB_SHIFTLEFT(<roaringbitmap>, <int>)

Arguments​

  • <roaringbitmap>

    The original roaring bitmap.

  • <int>

    The value to subtract from each element, of type integer.

Returns​

A roaringbitmap value.

Examples​

SELECT RB_TO_STRING(RB_SHIFTLEFT(RB_FROM_STRING('{1,2,3}'), 3));
-> {0}
SELECT RB_TO_STRING(RB_SHIFTLEFT(RB_FROM_STRING('{4,5,6}'), 1));
-> {3,4,5}

RB_FILL​

Adds all integers within a specified range to a roaring bitmap.

Syntax​

RB_FILL(<roaringbitmap>, <range_start>, <range_end>)

Arguments​

  • <roaringbitmap>

    The original roaring bitmap.

  • <range_start>

    The starting value of the range (inclusive), of type bigint.

  • <range_end>

    The ending value of the range (exclusive), of type bigint.

Returns​

A roaringbitmap value.

Examples​

SELECT RB_TO_STRING(RB_FILL(RB_FROM_STRING('{1,10,100}'), 10, 11));
-> {1,10,100}
SELECT RB_TO_STRING(RB_FILL(RB_FROM_STRING('{1,10,100}'), 10, 20));
-> {1,10,11,12,13,14,15,16,17,18,19,100}

RB_RANGE​

Returns the subset in a roaring bitmap that lies within a specified range.

Syntax​

RB_RANGE(<roaringbitmap>, <range_start>, <range_end>)

Arguments​

  • <roaringbitmap>

    The original roaring bitmap.

  • <range_start>

    The starting value of the range (inclusive), of type bigint.

  • <range_end>

    The ending value of the range (exclusive), of type bigint.

Returns​

A roaringbitmap value.

Examples​

SELECT RB_TO_STRING(RB_RANGE(RB_FROM_STRING('{1,10,100}'), 0, 10));
-> {1}

RB_RANGE_CARDINALITY​

Returns the cardinality of the subset in a roaring bitmap that lies within a specified range.

Syntax​

RB_RANGE_CARDINALITY(<roaringbitmap>, <range_start>, <range_end>)

Arguments​

  • <roaringbitmap>

    The original roaring bitmap.

  • <range_start>

    The starting value of the range (inclusive), of type bigint.

  • <range_end>

    The ending value of the range (exclusive), of type bigint.

Returns​

An integer value.

Examples​

SELECT RB_RANGE_CARDINALITY(RB_FROM_STRING('{1,10,100}'), 0, 10);
-> 1

RB_CLEAR​

Removes elements from a roaring bitmap that lie within a specified range.

Syntax​

RB_CLEAR(<roaringbitmap>, <range_start>, <range_end>)

Arguments​

  • <roaringbitmap>

    The original roaring bitmap.

  • <range_start>

    The starting value of the range (inclusive), of type bigint.

  • <range_end>

    The ending value of the range (exclusive), of type bigint.

Returns​

A roaringbitmap value.

Examples​

SELECT RB_TO_STRING(RB_CLEAR(RB_FROM_STRING('{1,10,100}'), 0, 10));
-> {10,100}

SELECT RB_TO_STRING(RB_CLEAR(RB_FROM_STRING('{1,10,100}'), 9, 9));
-> {1,10,100}

RB_FLIP​

Adds values to the roaring bitmap that are within a specified range but not already present in the roaring bitmap.

Syntax​

RB_FLIP(<roaringbitmap>, <range_start>, <range_end>)

Arguments​

  • <roaringbitmap>

    The original roaring bitmap.

  • <range_start>

    The starting value of the range (inclusive), of type bigint.

  • <range_end>

    The ending value of the range (exclusive), of type bigint.

Returns​

A roaringbitmap value.

Examples​

SELECT RB_TO_STRING(RB_FLIP(RB_FROM_STRING('{1,10,100}'), 1, 15));
-> {2,3,4,5,6,7,8,9,11,12,13,14,100}

RB_SELECT​

Selects a subset of the current roaring bitmap based on specified conditions.

Syntax​

RB_SELECT(<roaringbitmap>, <bitset_limit>, [<bitset_offset>, <reverse>, <range_start>, <range_end>])

Arguments​

  • <roaringbitmap>

    The original roaring bitmap.

  • <bitset_limit>

    The maximum number of elements to return, of type bigint.

  • <bitset_offset>

    The starting offset from where to begin outputting, of type bigint. Defaults to 0.

  • <reverse>

    Whether to output in reverse order, of type boolean. Defaults to false.

  • <range_start>

    The starting value of the range (inclusive), of type bigint. Defaults to 0.

  • <range_end>

    The ending value of the range (exclusive), of type bigint. Defaults to 4294967296.

Returns​

The selected subset of elements from the original Roaring Bitmap.

Examples​

# Return the first three values
SELECT RB_TO_STRING(RB_SELECT(RB_FROM_STRING('{1, 2, 3, 4, 5, 6, 7}'), 3));
-> {1,2,3}

# Offset by 1 and return the first three values
SELECT RB_TO_STRING(RB_SELECT(RB_FROM_STRING('{1, 2, 3, 4, 5, 6, 7}'), 3, 1));
-> {2,3,4}

# Reverse order and offset by one, then return the first three values
SELECT RB_TO_STRING(RB_SELECT(RB_FROM_STRING('{1, 2, 3, 4, 5, 6, 7}'), 3, 1, true));
-> {4,5,6}

# Select the first two values from the range [2, 6)
SELECT RB_TO_STRING(RB_SELECT(RB_FROM_STRING('{1, 2, 3, 4, 5, 6, 7}'), 2, 0, false, 2, 6));
-> {2,3}

# Select the first two values from the range [2, 6) in reverse order with an offset
SELECT RB_TO_STRING(RB_SELECT(RB_FROM_STRING('{1, 2, 3, 4, 5, 6, 7}'), 2, 1, true, 2, 6));
-> {3,4}

RB_BUILD_AGG​

Aggregates a series of values into a single roaring bitmap.

Syntax​

RB_BUILD_AGG(<int>)

Arguments​

  • <int>: an integer or a column of integers.

Returns​

A roaringbitmap value.

Examples​

SELECT RB_TO_STRING(RB_BUILD_AGG(1));
-> {1}

CREATE TABLE build_test (id integer);
INSERT INTO build_test VALUES(1), (2);
SELECT RB_TO_STRING(RB_BUILD_AGG(id)) FROM build_test;
-> {1,2}

RB_AND_AGG​

Calculates the intersection of all roaring bitmaps in a roaringbitmap column and returns the resulting roaring bitmap.

Syntax​

RB_AND_AGG(<roaringbitmap>)

Arguments​

  • <roaringbitmap>: a roaringbitmap value or column.

Returns​

A roaringbitmap value.

Examples​

SELECT RB_TO_STRING(RB_AND_AGG(RB_FROM_STRING('{1,2,3}')));
-> {}

CREATE TABLE and_test (bitmapValue roaringbitmap);
INSERT INTO and_test VALUES(RB_FROM_STRING('{1,2,3}')), (RB_FROM_STRING('{2,3,4}'));
SELECT RB_TO_STRING(RB_AND_AGG(bitmapValue)) FROM and_test;
-> {2,3}

RB_AND_CARDINALITY_AGG​

Returns the cardinality of the intersection of all roaring bitmaps in a roaringbitmap column.

Syntax​

RB_AND_CARDINALITY_AGG(<roaringbitmap>)

Arguments​

  • <roaringbitmap>: a roaringbitmap value or column.

Returns​

An integer value.

Examples​

SELECT RB_AND_CARDINALITY_AGG(RB_FROM_STRING('{1,2,3}'));
-> 0

CREATE TABLE and_test (bitmapValue roaringbitmap);
INSERT INTO and_test VALUES(RB_FROM_STRING('{1,2,3}')), (RB_FROM_STRING('{2,3,4}'));
SELECT RB_AND_CARDINALITY_AGG(bitmapValue) FROM and_test;
-> 2

RB_OR_AGG​

Returns the union of all roaring bitmaps in a roaringbitmap column.

Syntax​

RB_OR_AGG(<roaringbitmap>)

Arguments​

  • <roaringbitmap>: a roaringbitmap value or column.

Returns​

A roaringbitmap value.

Examples​

SELECT RB_TO_STRING(RB_OR_AGG(RB_FROM_STRING('{1,2,3}')));
-> {1,2,3}

CREATE TABLE or_test (bitmapValue roaringbitmap);
INSERT INTO or_test VALUES(RB_FROM_STRING('{1,2,3}')), (RB_FROM_STRING('{2,3,4}'));
SELECT RB_TO_STRING(RB_OR_AGG(bitmapValue)) FROM or_test;
-> {1,2,3,4}

RB_OR_CARDINALITY_AGG​

Returns the cardinality of the union of all roaring bitmaps in a roaringbitmap column.

Syntax​

RB_OR_CARDINALITY_AGG(<roaringbitmap>)

Parameters​

  • <roaringbitmap>: a roaringbitmap value or column.

Returns​

A bigint value.

Example​

SELECT RB_OR_CARDINALITY_AGG(RB_FROM_STRING('{1,2,3}'));
-> 3

CREATE TABLE or_test (bitmapValue roaringbitmap);
INSERT INTO or_test VALUES(RB_FROM_STRING('{1,2,3}')), (RB_FROM_STRING('{2,3,4}'));
SELECT RB_OR_CARDINALITY_AGG(bitmapValue) FROM or_test;
-> 4

RB_XOR_AGG​

Performs an XOR operation on all roaring bitmaps in a roaringbitmap column and returns the resulting roaring bitmap.

Syntax​

RB_XOR_AGG(<roaringbitmap>)

Arguments​

  • <roaringbitmap>: a roaringbitmap value or column.

Returns​

A roaringbitmap value.

Examples​

SELECT RB_TO_STRING(RB_XOR_AGG(RB_FROM_STRING('{1,2,3}')));
-> {1,2,3}

CREATE TABLE xor_test (bitmapValue roaringbitmap);
INSERT INTO xor_test VALUES(RB_FROM_STRING('{1,2,3}')), (RB_FROM_STRING('{2,3,4}'));
SELECT RB_TO_STRING(RB_XOR_AGG(bitmapValue)) FROM xor_test;
-> {1,4}

RB_XOR_CARDINALITY_AGG​

Performs an XOR operation on all roaring bitmaps in a roaringbitmap column and returns the cardinality of the resulting roaring bitmap.

Syntax​

RB_XOR_CARDINALITY_AGG(<roaringbitmap>)

Arguments​

  • <roaringbitmap>: a roaringbitmap value or column.

Returns​

An integer value.

Examples​

SELECT RB_XOR_CARDINALITY_AGG(RB_FROM_STRING('{1,2,3}'));
-> 3

CREATE TABLE xor_test (bitmapValue roaringbitmap);
INSERT INTO xor_test VALUES(RB_FROM_STRING('{1,2,3}')), (RB_FROM_STRING('{2,3,4}'));
SELECT RB_XOR_CARDINALITY_AGG(bitmapValue) FROM xor_test;
-> 2