Bitmap

bitmapAnd

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

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

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

Introduced in version 20.1.0.

bitmapAndCardinality

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

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

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

Introduced in version 20.1.0.

bitmapAndnot

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

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

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

Introduced in version 20.1.0.

bitmapAndnotCardinality

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

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

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

Introduced in version 20.1.0.

bitmapBuild

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

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

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

Signed bitmap

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

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

Introduced in version 20.1.0.

bitmapCardinality

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

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

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

Introduced in version 20.1.0.

bitmapContains

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. (U)Int8/16/32/64

Returned value

Returns 1 if the bitmap contains the specified value, otherwise 0 UInt8

Examples

Usage example

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

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

Signed bitmap

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

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

Introduced in version 20.1.0.

bitmapHasAll

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

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

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

Introduced in version 20.1.0.

bitmapHasAny

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

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

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

Introduced in version 20.1.0.

bitmapMax

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

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

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

Signed bitmap

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

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

Introduced in version 20.1.0.

bitmapMin

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

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

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

Signed bitmap

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

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

Introduced in version 20.1.0.

bitmapOr

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

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

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

Introduced in version 20.1.0.

bitmapOrCardinality

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

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

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

Introduced in version 20.1.0.

bitmapSubsetInRange

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 range (inclusive). UInt* - end — End of the range (exclusive). UInt*

Returned value

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

Examples

Usage example

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

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

Introduced in version 20.1.0.

bitmapSubsetLimit

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 — Start of the range (inclusive). UInt32 - cardinality_limit — Maximum cardinality of the subset. UInt32

Returned value

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

Examples

Usage example

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

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

Introduced in version 20.1.0.

bitmapToArray

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

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

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

Signed bitmap

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

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

Introduced in version 20.1.0.

bitmapTransform

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 original set bits to be replaced. Array(T). - to_array — Array of new set bits to replace with. Array(T).

Returned value

Returns a bitmap with elements transformed according to the given mapping AggregateFunction(groupBitmap, T)

Examples

Usage example

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

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

Signed bitmap

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

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

Introduced in version 20.1.0.

bitmapXor

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

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

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

Introduced in version 20.1.0.

bitmapXorCardinality

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

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

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

Introduced in version 20.1.0.

subBitmap

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 set bits to skip from the beginning (zero-based). UInt32 - cardinality_limit — Maximum number of set bits to include in the subset. UInt32

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

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

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

Introduced in version 21.9.0.