Bitmap Functions

Bitmaps can be constructed in two ways. The first way is constructed by aggregation function groupBitmap with -State, the other way is to constructed a bitmap from an Array object.

bitmapAnd

Introduced in: v20.1.0 Computes the logical conjunction (AND) of two bitmaps. Syntax

bitmapAnd(bitmap1, bitmap2)

Arguments

bitmap1 — First bitmap object. AggregateFunction(groupBitmap, T). - bitmap2 — Second bitmap object. AggregateFunction(groupBitmap, T).

Returned value Returns a bitmap containing bits present in both input bitmaps AggregateFunction(groupBitmap, T) Examples Usage example

Query

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

Response

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

bitmapAndCardinality

Introduced in: v20.1.0 Returns the cardinality of the logical conjunction (AND) of two bitmaps. Syntax

bitmapAndCardinality(bitmap1, bitmap2)

Arguments

bitmap1 — First bitmap object. AggregateFunction(groupBitmap, T). - bitmap2 — Second bitmap object. AggregateFunction(groupBitmap, T).

Returned value Returns the number of set bits in the intersection of the two bitmaps UInt64 Examples Usage example

Query

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

Response

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

bitmapAndnot

Introduced in: v20.1.0 Computes the set difference A AND-NOT B of two bitmaps. Syntax

bitmapAndnot(bitmap1, bitmap2)

Arguments

bitmap1 — First bitmap object. AggregateFunction(groupBitmap, T). - bitmap2 — Second bitmap object. AggregateFunction(groupBitmap, T).

Returned value Returns a bitmap containing set bits present in the first bitmap but not in the second AggregateFunction(groupBitmap, T) Examples Usage example

Query

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

Response

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

bitmapAndnotCardinality

Introduced in: v20.1.0 Returns the cardinality of the AND-NOT operation of two bitmaps. Syntax

bitmapAndnotCardinality(bitmap1, bitmap2)

Arguments

bitmap1 — First bitmap object. AggregateFunction(groupBitmap, T). - bitmap2 — Second bitmap object. AggregateFunction(groupBitmap, T).

Returned value Returns the number of set bits in the result of bitmap1 AND-NOT bitmap2 UInt64 Examples Usage example

Query

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

Response

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

bitmapBuild

Introduced in: v20.1.0 Builds a bitmap from an integer array. Supported element types are signed and unsigned integers of 8, 16, 32, or 64 bits. It is the opposite of function bitmapToArray. Syntax

bitmapBuild(array)

Arguments

array — Integer array. Array((U)Int*)

Returned value Returns a bitmap from the provided array AggregateFunction(groupBitmap, T) Examples Usage example

Query

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

Response

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

Signed bitmap

Query

SELECT bitmapBuild([-128, -1]::Array(Int8)) AS res, toTypeName(res);

Response

┌─res─┬─toTypeName(bitmapBuild([-128, -1]::Array(Int8)))─┐
│     │ AggregateFunction(groupBitmap, Int8)             │
└─────┴──────────────────────────────────────────────────┘

bitmapCardinality

Introduced in: v20.1.0 Returns the number of bits set (the cardinality) in the bitmap. Syntax

bitmapCardinality(bitmap)

Arguments

bitmap — Bitmap object. AggregateFunction(groupBitmap, T).

Returned value Returns the number of bits set in the bitmap UInt64 Examples Usage example

Query

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

Response

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

bitmapContains

Introduced in: v20.1.0 Checks if the bitmap contains a specific element. The value is compared as an unsigned integer of the bitmap element type. For signed bitmaps, a negative element matches its unsigned counterpart (for example, Int8 value -1 matches 255). Syntax

bitmapContains(bitmap, value)

Arguments

bitmap — Bitmap object. AggregateFunction(groupBitmap, T). - value — Element to check for, interpreted as an unsigned integer of the bitmap element type. (U)Int8/16/32/64

Returned value Returns 1 if the bitmap contains the specified value, otherwise 0 UInt8 Examples Usage example

Query

SELECT bitmapContains(bitmapBuild([1, 2, 3]), 2) AS res;

Response

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

Signed bitmap

Query

SELECT bitmapContains(bitmapBuild([-1]::Array(Int8)), 255) AS res;

Response

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

bitmapHasAll

Introduced in: v20.1.0 Checks if the first bitmap contains all set bits of the second bitmap. Syntax

bitmapHasAll(bitmap1, bitmap2)

Arguments

bitmap1 — First bitmap object. AggregateFunction(groupBitmap, T). - bitmap2 — Second bitmap object. AggregateFunction(groupBitmap, T).

Returned value Returns 1 if all set bits of the second bitmap are present in the first bitmap, otherwise 0 UInt8 Examples Usage example

Query

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

Response

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

bitmapHasAny

Introduced in: v20.1.0 Checks if the first bitmap contains any set bits of the second bitmap. Syntax

bitmapHasAny(bitmap1, bitmap2)

Arguments

bitmap1 — First bitmap object. AggregateFunction(groupBitmap, T). - bitmap2 — Second bitmap object. AggregateFunction(groupBitmap, T).

Returned value Returns 1 if any bits of the second bitmap are present in the first bitmap, otherwise 0 UInt8 Examples Usage example

Query

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

Response

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

bitmapMax

Introduced in: v20.1.0 Returns the greatest element in a bitmap, interpreted as an unsigned integer of the bitmap element type. For signed bitmaps, negative values are treated as their unsigned counterparts (for example, Int8 value -1 is 255). Returns 0 if the bitmap is empty. Syntax

bitmapMax(bitmap)

Arguments

bitmap — Bitmap object. AggregateFunction(groupBitmap, T).

Returned value Returns the greatest element as an unsigned value of the bitmap element type, or 0 if the bitmap is empty UInt64 Examples Usage example

Query

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

Response

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

Signed bitmap

Query

SELECT bitmapMax(bitmapBuild([-128, -1]::Array(Int8))) AS res;

Response

┌─res─┐
│ 255 │
└─────┘

bitmapMin

Introduced in: v20.1.0 Returns the smallest element in a bitmap, interpreted as an unsigned integer of the bitmap element type. For signed bitmaps, negative values are treated as their unsigned counterparts (for example, Int8 value -128 is 128). If the bitmap is empty, returns UINT32_MAX (UINT64_MAX if the bitmap element type is wider than 32 bits). Syntax

bitmapMin(bitmap)

Arguments

bitmap — Bitmap object. AggregateFunction(groupBitmap, T).

Returned value Returns the smallest element as an unsigned value of the bitmap element type, or UINT32_MAX/UINT64_MAX if the bitmap is empty UInt64 Examples Usage example

Query

SELECT bitmapMin(bitmapBuild([3, 5, 2, 6])) AS res;

Response

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

Signed bitmap

Query

SELECT bitmapMin(bitmapBuild([-128, -1]::Array(Int8))) AS res;

Response

┌─res─┐
│ 128 │
└─────┘

bitmapOr

Introduced in: v20.1.0 Computes the logical disjunction (OR) of two bitmaps. Syntax

bitmapOr(bitmap1, bitmap2)

Arguments

bitmap1 — First bitmap object. AggregateFunction(groupBitmap, T). - bitmap2 — Second bitmap object. AggregateFunction(groupBitmap, T).

Returned value Returns a bitmap containing set bits present in either input bitmap AggregateFunction(groupBitmap, T) Examples Usage example

Query

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

Response

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

bitmapOrCardinality

Introduced in: v20.1.0 Returns the cardinality of the logical disjunction (OR) of two bitmaps. Syntax

bitmapOrCardinality(bitmap1, bitmap2)

Arguments

bitmap1 — First bitmap object. AggregateFunction(groupBitmap, T). - bitmap2 — Second bitmap object. AggregateFunction(groupBitmap, T).

Returned value Returns the number of set bits in the union of the two bitmaps UInt64 Examples Usage example

Query

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

Response

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

bitmapSubsetInRange

Introduced in: v20.1.0 Returns a subset of the bitmap containing elements in the value range [start, end). Element values are compared as unsigned integers of the bitmap element type. Syntax

bitmapSubsetInRange(bitmap, start, end)

Arguments

bitmap — Bitmap to extract the subset from. AggregateFunction(groupBitmap, T). - start — Start of the value range (inclusive). UInt* - end — End of the value range (exclusive). UInt*

Returned value Returns a bitmap containing only the elements in the specified value range AggregateFunction(groupBitmap, T) Examples Usage example

Query

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

Response

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

bitmapSubsetLimit

Introduced in: v20.1.0 Returns a subset of at most cardinality_limit elements whose values are greater than or equal to range_start, selecting the smallest such values in unsigned order. Syntax

bitmapSubsetLimit(bitmap, range_start, cardinality_limit)

Arguments

bitmap — Bitmap object. AggregateFunction(groupBitmap, T). - range_start — Minimum element value to include (inclusive). UInt* - cardinality_limit — Maximum cardinality of the subset. UInt*

Returned value Returns a bitmap containing at most cardinality_limit elements with unsigned value at least range_start AggregateFunction(groupBitmap, T) Examples Usage example

Query

SELECT arraySort(bitmapToArray(bitmapSubsetLimit(bitmapBuild([1, 5, 3, 2, 8]), 3, 2))) AS res;

Response

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

bitmapToArray

Introduced in: v20.1.0 Converts a bitmap to an array of its elements. The array element type matches the bitmap element type T (signed or unsigned integer). It is the opposite of function bitmapBuild. Syntax

bitmapToArray(bitmap)

Arguments

bitmap — Bitmap to convert. AggregateFunction(groupBitmap, T).

Returned value Returns an array of the elements contained in the bitmap Array(T) Examples Usage example

Query

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

Response

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

Signed bitmap

Query

SELECT arraySort(bitmapToArray(bitmapBuild([-128, -1]::Array(Int8)))) AS res;

Response

┌─res────────┐
│ [-128, -1] │
└────────────┘

bitmapTransform

Introduced in: v20.1.0 Replaces elements in a bitmap according to a mapping from from_array to to_array. Values are interpreted as unsigned integers of the bitmap element type (same domain as bitmapContains). For signed bitmaps, a negative element matches its unsigned counterpart (for example, Int8 value -1 matches 255). A value in to_array that does not fit into the bitmap element type raises BAD_ARGUMENTS. A value in from_array that does not fit is simply not found, so the corresponding replacement does not apply. Syntax

bitmapTransform(bitmap, from_array, to_array)

Arguments

bitmap — Bitmap object. AggregateFunction(groupBitmap, T). - from_array — Array of element values to replace, interpreted as unsigned integers of the bitmap element type. Array(UInt*). - to_array — Array of replacement values, interpreted as unsigned integers of the bitmap element type. Must have the same length as from_array. Array(UInt*).

Returned value Returns a bitmap with elements transformed according to the given mapping AggregateFunction(groupBitmap, T) Examples Usage example

Query

SELECT bitmapToArray(bitmapTransform(bitmapBuild([1, 2, 3, 4, 5]), [2, 4], [20, 40])) AS res;

Response

┌─res───────────────┐
│ [1, 3, 5, 20, 40] │
└───────────────────┘

Signed bitmap

Query

SELECT arraySort(bitmapToArray(bitmapTransform(bitmapBuild([-1, 0]::Array(Int8)), [255], [10]))) AS res;

Response

┌─res─────┐
│ [0, 10] │
└─────────┘

bitmapXor

Introduced in: v20.1.0 Computes the symmetric difference (XOR) of two bitmaps. Syntax

bitmapXor(bitmap1, bitmap2)

Arguments

bitmap1 — First bitmap object. AggregateFunction(groupBitmap, T). - bitmap2 — Second bitmap object. AggregateFunction(groupBitmap, T).

Returned value Returns a bitmap containing set bits present in either input bitmap, but not in both AggregateFunction(groupBitmap, T) Examples Usage example

Query

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

Response

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

bitmapXorCardinality

Introduced in: v20.1.0 Returns the cardinality of the XOR (symmetric difference) of two bitmaps. Syntax

bitmapXorCardinality(bitmap1, bitmap2)

Arguments

bitmap1 — First bitmap object. AggregateFunction(groupBitmap, T). - bitmap2 — Second bitmap object. AggregateFunction(groupBitmap, T).

Returned value Returns the number of set bits in the symmetric difference of the two bitmaps UInt64 Examples Usage example

Query

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

Response

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

subBitmap

Introduced in: v21.9.0 Returns a subset of the bitmap after skipping offset elements in ascending unsigned value order. The maximum cardinality of the returned bitmap is cardinality_limit. Syntax

subBitmap(bitmap, offset, cardinality_limit)

Arguments

bitmap — Bitmap object. AggregateFunction(groupBitmap, T). - offset — Number of elements to skip in ascending unsigned value order (zero-based). UInt* - cardinality_limit — Maximum number of elements to include in the subset. UInt*

Returned value Returns a bitmap containing at most cardinality_limit elements after skipping offset elements in ascending unsigned value order AggregateFunction(groupBitmap, T) Examples Usage example

Query

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

Response

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

SQL Reference

Data Types

Engines

Functions

Formats

Settings

System Tables

Data Lakes

bitmapAnd

bitmapAndCardinality

bitmapAndnot

bitmapAndnotCardinality

bitmapBuild

bitmapCardinality

bitmapContains

bitmapHasAll

bitmapHasAny

bitmapMax

bitmapMin

bitmapOr

bitmapOrCardinality

bitmapSubsetInRange

bitmapSubsetLimit

bitmapToArray

bitmapTransform

bitmapXor

bitmapXorCardinality

subBitmap

​bitmapAnd

​bitmapAndCardinality

​bitmapAndnot

​bitmapAndnotCardinality

​bitmapBuild

​bitmapCardinality

​bitmapContains

​bitmapHasAll

​bitmapHasAny

​bitmapMax

​bitmapMin

​bitmapOr

​bitmapOrCardinality

​bitmapSubsetInRange

​bitmapSubsetLimit

​bitmapToArray

​bitmapTransform

​bitmapXor

​bitmapXorCardinality

​subBitmap

bitmapAnd

bitmapAndCardinality

bitmapAndnot

bitmapAndnotCardinality

bitmapBuild

bitmapCardinality

bitmapContains

bitmapHasAll

bitmapHasAny

bitmapMax

bitmapMin

bitmapOr

bitmapOrCardinality

bitmapSubsetInRange

bitmapSubsetLimit

bitmapToArray

bitmapTransform

bitmapXor

bitmapXorCardinality

subBitmap