Bitmap functions¶

The following functions are used to work with bitmaps.

You can construct bitmaps in two ways: by using the groupBitmap aggregation function with -State, or by constructing a bitmap from an Array object.

bitmapBuild¶

Creates a bitmap from an array of unsigned integers.

Syntax¶

bitmapBuild(array)

Arguments¶

• array: Array. An array of unsigned integers to build the bitmap from.

Returns¶

A bitmap object representing the input array. AggregateFunction(groupBitmap, UInt8).

Example¶

SELECT bitmapBuild([1, 2, 3, 4, 5]) AS res, toTypeName(res)

Result:

┌─res─┬─toTypeName(bitmapBuild([1, 2, 3, 4, 5]))─────┐
│     │ AggregateFunction(groupBitmap, UInt8)        │
└─────┴──────────────────────────────────────────────┘

bitmapToArray¶

Converts a bitmap object back into an array of unsigned integers.

Syntax¶

bitmapToArray(bitmap)

Arguments¶

• bitmap: Bitmap object. The bitmap to convert.

Returns¶

An array of unsigned integers contained in the bitmap. Array.

Example¶

SELECT bitmapToArray(bitmapBuild([1, 2, 3, 4, 5])) AS res

Result:

┌─res─────────┐
│ [1,2,3,4,5] │
└─────────────┘

bitmapSubsetInRange¶

Extracts a new bitmap containing only the elements from the original bitmap that fall within a specified numerical range.

Syntax¶

bitmapSubsetInRange(bitmap, range_start, range_end)

Arguments¶

• bitmap: Bitmap object. The source bitmap.
• range_start: UInt32. The inclusive lower bound of the range.
• range_end: UInt32. The exclusive upper bound of the range.

Returns¶

A new bitmap containing elements within the specified range. Bitmap object.

Example¶

SELECT bitmapToArray(bitmapSubsetInRange(bitmapBuild([0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,100,200,500]), toUInt32(30), toUInt32(200))) AS res

Result:

┌─res───────────────┐
│ [30,31,32,33,100] │
└───────────────────┘

bitmapSubsetLimit¶

Creates a new bitmap containing a limited number of elements from the original bitmap, starting from a specified value.

Syntax¶

bitmapSubsetLimit(bitmap, range_start, cardinality_limit)

Arguments¶

• bitmap: Bitmap object. The source bitmap.
• range_start: UInt32. The inclusive starting value for the subset.
• cardinality_limit: UInt32. The maximum number of elements to include in the subset.

Returns¶

A new bitmap containing the limited subset of elements. Bitmap object.

Example¶

SELECT bitmapToArray(bitmapSubsetLimit(bitmapBuild([0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,100,200,500]), toUInt32(30), toUInt32(200))) AS res

Result:

┌─res───────────────────────┐
│ [30,31,32,33,100,200,500] │
└───────────────────────────┘

subBitmap¶

Extracts a subset of a bitmap based on an offset and a maximum cardinality limit.

Syntax¶

subBitmap(bitmap, offset, cardinality_limit)

Arguments¶

• bitmap: Bitmap object. The source bitmap.
• offset: UInt32. The zero-based starting position for the subset.
• cardinality_limit: UInt32. The maximum number of elements to include in the subset.

Returns¶

A new bitmap containing the specified subset of elements. Bitmap object.

Example¶

SELECT bitmapToArray(subBitmap(bitmapBuild([0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,100,200,500]), toUInt32(10), toUInt32(10))) AS res

Result:

┌─res─────────────────────────────┐
│ [10,11,12,13,14,15,16,17,18,19] │
└─────────────────────────────────┘

bitmapContains¶

Checks if a specific unsigned integer value is present within a bitmap.

Syntax¶

bitmapContains(bitmap, needle)

Arguments¶

• bitmap: Bitmap object. The bitmap to search within.
• needle: UInt32. The unsigned integer value to search for.

Returns¶

1 if the needle is found in the bitmap, 0 otherwise. UInt8.

Example¶

SELECT bitmapContains(bitmapBuild([1,5,7,9]), toUInt32(9)) AS res

Result:

┌─res─┐
│  1  │
└─────┘

bitmapHasAny¶

Determines if two bitmaps share any common elements.

If bitmap2 contains exactly one element, consider using bitmapContains instead as it works more efficiently.

Syntax¶

bitmapHasAny(bitmap1, bitmap2)

Arguments¶

• bitmap1: Bitmap object. The first bitmap.
• bitmap2: Bitmap object. The second bitmap.

Returns¶

1 if there is at least one shared element between bitmap1 and bitmap2, 0 otherwise. UInt8.

Example¶

SELECT bitmapHasAny(bitmapBuild([1,2,3]),bitmapBuild([3,4,5])) AS res

Result:

┌─res─┐
│  1  │
└─────┘

bitmapHasAll¶

Checks if the first bitmap contains all elements present in the second bitmap.

If the second bitmap is empty, the function returns 1. Also see hasAll(array, array).

Syntax¶

bitmapHasAll(bitmap1, bitmap2)

Arguments¶

• bitmap1: Bitmap object. The bitmap to check for containment.
• bitmap2: Bitmap object. The bitmap whose elements must be present in bitmap1.

Returns¶

1 if bitmap1 contains all elements of bitmap2, 0 otherwise. UInt8.

Example¶

SELECT bitmapHasAll(bitmapBuild([1,2,3]),bitmapBuild([3,4,5])) AS res

Result:

┌─res─┐
│  0  │
└─────┘

bitmapCardinality¶

Calculates the number of unique elements (cardinality) within a bitmap.

Syntax¶

bitmapCardinality(bitmap)

Arguments¶

• bitmap: Bitmap object. The bitmap to measure.

Returns¶

The count of unique elements in the bitmap. UInt64.

Example¶

SELECT bitmapCardinality(bitmapBuild([1, 2, 3, 4, 5])) AS res

Result:

┌─res─┐
│   5 │
└─────┘

bitmapMin¶

Finds the smallest unsigned integer value stored in a bitmap.

Syntax¶

bitmapMin(bitmap)

Arguments¶

• bitmap: Bitmap object. The bitmap to inspect.

Returns¶

The minimum value in the bitmap, or UINT32_MAX if the bitmap is empty. UInt32.

Example¶

SELECT bitmapMin(bitmapBuild([1, 2, 3, 4, 5])) AS res

Result:

┌─res─┐
│   1 │
└─────┘

bitmapMax¶

Finds the largest unsigned integer value stored in a bitmap.

Syntax¶

bitmapMax(bitmap)

Arguments¶

• bitmap: Bitmap object. The bitmap to inspect.

Returns¶

The maximum value in the bitmap, or 0 if the bitmap is empty. UInt32.

Example¶

SELECT bitmapMax(bitmapBuild([1, 2, 3, 4, 5])) AS res

Result:

┌─res─┐
│   5 │
└─────┘

bitmapTransform¶

Modifies a bitmap by replacing specific elements with new values based on two corresponding arrays.

Syntax¶

bitmapTransform(bitmap, from_array, to_array)

Arguments¶

• bitmap: Bitmap object. The bitmap to transform.
• from_array: Array(UInt32). An array of values to be replaced.
• to_array: Array(UInt32). An array of new values to replace the corresponding from_array elements. Must have the same size as from_array.

Returns¶

A new bitmap with the specified transformations applied. Bitmap object.

Example¶

SELECT bitmapToArray(bitmapTransform(bitmapBuild([1, 2, 3, 4, 5, 6, 7, 8, 9, 10]), cast([5,999,2] as Array(UInt32)), cast([2,888,20] as Array(UInt32)))) AS res

Result:

┌─res───────────────────┐
│ [1,3,4,6,7,8,9,10,20] │
└───────────────────────┘

bitmapAnd¶

Performs a bitwise AND operation between two bitmaps, returning a new bitmap containing only the common elements.

Syntax¶

bitmapAnd(bitmap1, bitmap2)

Arguments¶

• bitmap1: Bitmap object. The first bitmap.
• bitmap2: Bitmap object. The second bitmap.

Returns¶

A new bitmap containing elements present in both input bitmaps. Bitmap object.

Example¶

SELECT bitmapToArray(bitmapAnd(bitmapBuild([1,2,3]),bitmapBuild([3,4,5]))) AS res

Result:

┌─res─┐
│ [3] │
└─────┘

bitmapOr¶

Performs a bitwise OR operation between two bitmaps, returning a new bitmap containing all unique elements from both.

Syntax¶

bitmapOr(bitmap1, bitmap2)

Arguments¶

• bitmap1: Bitmap object. The first bitmap.
• bitmap2: Bitmap object. The second bitmap.

Returns¶

A new bitmap containing all unique elements from either input bitmap. Bitmap object.

Example¶

SELECT bitmapToArray(bitmapOr(bitmapBuild([1,2,3]),bitmapBuild([3,4,5]))) AS res

Result:

┌─res─────────┐
│ [1,2,3,4,5] │
└─────────────┘

bitmapXor¶

Performs a bitwise XOR operation between two bitmaps, returning a new bitmap with elements present in one but not both.

Syntax¶

bitmapXor(bitmap1, bitmap2)

Arguments¶

• bitmap1: Bitmap object. The first bitmap.
• bitmap2: Bitmap object. The second bitmap.

Returns¶

A new bitmap containing elements that are in bitmap1 or bitmap2, but not in both. Bitmap object.

Example¶

SELECT bitmapToArray(bitmapXor(bitmapBuild([1,2,3]),bitmapBuild([3,4,5]))) AS res

Result:

┌─res───────┐
│ [1,2,4,5] │
└───────────┘

bitmapAndnot¶

Performs a bitwise AND NOT operation, returning a new bitmap with elements present in the first bitmap but not in the second.

Syntax¶

bitmapAndnot(bitmap1, bitmap2)

Arguments¶

• bitmap1: Bitmap object. The first bitmap.
• bitmap2: Bitmap object. The second bitmap.

Returns¶

A new bitmap containing elements from bitmap1 that are not present in bitmap2. Bitmap object.

Example¶

SELECT bitmapToArray(bitmapAndnot(bitmapBuild([1,2,3]),bitmapBuild([3,4,5]))) AS res

Result:

┌─res───┐
│ [1,2] │
└───────┘

bitmapAndCardinality¶

Calculates the cardinality (number of elements) of the intersection between two bitmaps.

Syntax¶

bitmapAndCardinality(bitmap1, bitmap2)

Arguments¶

• bitmap1: Bitmap object. The first bitmap.
• bitmap2: Bitmap object. The second bitmap.

Returns¶

The count of common elements between the two bitmaps. UInt64.

Example¶

SELECT bitmapAndCardinality(bitmapBuild([1,2,3]),bitmapBuild([3,4,5])) AS res

Result:

┌─res─┐
│   1 │
└─────┘

bitmapOrCardinality¶

Calculates the cardinality (number of elements) of the union of two bitmaps.

Syntax¶

bitmapOrCardinality(bitmap1, bitmap2)

Arguments¶

• bitmap1: Bitmap object. The first bitmap.
• bitmap2: Bitmap object. The second bitmap.

Returns¶

The count of all unique elements across both bitmaps. UInt64.

Example¶

SELECT bitmapOrCardinality(bitmapBuild([1,2,3]),bitmapBuild([3,4,5])) AS res

Result:

┌─res─┐
│   5 │
└─────┘

bitmapXorCardinality¶

Calculates the cardinality (number of elements) of the symmetric difference (XOR) between two bitmaps.

Syntax¶

bitmapXorCardinality(bitmap1, bitmap2)

Arguments¶

• bitmap1: Bitmap object. The first bitmap.
• bitmap2: Bitmap object. The second bitmap.

Returns¶

The count of elements that are in one bitmap but not in both. UInt64.

Example¶

SELECT bitmapXorCardinality(bitmapBuild([1,2,3]),bitmapBuild([3,4,5])) AS res

Result:

┌─res─┐
│   4 │
└─────┘

bitmapAndnotCardinality¶

Calculates the cardinality (number of elements) of the difference between two bitmaps (elements in the first but not the second).

Syntax¶

bitmapAndnotCardinality(bitmap1, bitmap2)

Arguments¶

• bitmap1: Bitmap object. The first bitmap.
• bitmap2: Bitmap object. The second bitmap.

Returns¶

The count of elements present in bitmap1 but not in bitmap2. UInt64.

Example¶

SELECT bitmapAndnotCardinality(bitmapBuild([1,2,3]),bitmapBuild([3,4,5])) AS res

Result:

┌─res─┐
│   2 │
└─────┘