ReferenceSQL ReferenceFunctions

Bitmap

Bitmap functions reference.

bitmapAnd

Computes the logical conjunction (AND) of two bitmaps.

Syntax

bitmapAnd(bitmap1, bitmap2)

Arguments

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.

bitmapAndCardinality

Returns the cardinality of the logical conjunction (AND) of two bitmaps.

Syntax

bitmapAndCardinality(bitmap1, bitmap2)

Arguments

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.

bitmapAndnot

Computes the set difference A AND-NOT B of two bitmaps.

Syntax

bitmapAndnot(bitmap1, bitmap2)

Arguments

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.

bitmapAndnotCardinality

Returns the cardinality of the AND-NOT operation of two bitmaps.

Syntax

bitmapAndnotCardinality(bitmap1, bitmap2)

Arguments

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.

bitmapBuild

Builds a bitmap from an unsigned integer array. It is the opposite of function bitmapToArray.

Syntax

bitmapBuild(array)

Arguments

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)        │
└─────┴──────────────────────────────────────────────┘

Introduced in version 20.1.

bitmapCardinality

Returns the number of bits set (the cardinality) in the bitmap.

Syntax

bitmapCardinality(bitmap)

Arguments

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.

bitmapContains

Checks if the bitmap contains a specific element.

Syntax

bitmapContains(bitmap, value)

Arguments

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  │
└─────┘

Introduced in version 20.1.

bitmapHasAll

Checks if the first bitmap contains all set bits of the second bitmap.

Syntax

bitmapHasAll(bitmap1, bitmap2)

Arguments

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.

bitmapHasAny

Checks if the first bitmap contains any set bits of the second bitmap.

Syntax

bitmapHasAny(bitmap1, bitmap2)

Arguments

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.

bitmapMax

Returns the position of the greatest bit set in a bitmap, or 0 if the bitmap is empty.

Syntax

bitmapMax(bitmap)

Arguments

Returned value

Returns the position of the greatest bit set in the bitmap, otherwise 0 UInt64

Examples

Usage example

SELECT bitmapMax(bitmapBuild([1, 2, 3, 4, 5])) AS res;
┌─res─┐
│   5 │
└─────┘

Introduced in version 20.1.

bitmapMin

Returns the position of the smallest bit set in a bitmap. If all bits are unset, or UINT32_MAX (UINT64_MAX if the bitmap contains more than 2^64 bits).

Syntax

bitmapMin(bitmap)

Arguments

Returned value

Returns the position of the smallest bit set in the bitmap, or UINT32_MAX/UINT64_MAX UInt64

Examples

Usage example

SELECT bitmapMin(bitmapBuild([3, 5, 2, 6])) AS res;
┌─res─┐
│   2 │
└─────┘

Introduced in version 20.1.

bitmapOr

Computes the logical disjunction (OR) of two bitmaps.

Syntax

bitmapOr(bitmap1, bitmap2)

Arguments

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.

bitmapOrCardinality

Returns the cardinality of the logical disjunction (OR) of two bitmaps.

Syntax

bitmapOrCardinality(bitmap1, bitmap2)

Arguments

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.

bitmapSubsetInRange

Returns a subset of the bitmap, containing only the set bits in the specified range [start, end). Uses 1-based indexing.

Syntax

bitmapSubsetInRange(bitmap, start, end)

Arguments

Returned value

Returns a bitmap containing only the set bits in the specified 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.

bitmapSubsetLimit

Returns a subset of a bitmap from position range_start with at most cardinality_limit set bits. Uses 1-based indexing.

Syntax

bitmapSubsetLimit(bitmap, range_start, cardinality_limit)

Arguments

Returned value

Returns a bitmap containing at most cardinality_limit set bits, starting from range_start AggregateFunction(groupBitmap, T)

Examples

Usage example

SELECT bitmapToArray(bitmapSubsetLimit(bitmapBuild([1, 5, 3, 2, 8]), 3, 2)) AS res;
┌─res────┐
│ [5, 3] │
└────────┘

Introduced in version 20.1.

bitmapToArray

Converts a bitmap to an array of unsigned integers. It is the opposite of function bitmapBuild.

Syntax

bitmapToArray(bitmap)

Arguments

Returned value

Returns an array of unsigned integers contained in the bitmap Array(UInt*)

Examples

Usage example

SELECT bitmapToArray(bitmapBuild([1, 2, 3, 4, 5])) AS res;
┌─res─────────────┐
│ [1, 2, 3, 4, 5] │
└─────────────────┘

Introduced in version 20.1.

bitmapTransform

Changes up to N bits in a bitmap by swapping specific bit values in from_array with corresponding ones in to_array.

Syntax

bitmapTransform(bitmap, from_array, to_array)

Arguments

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] │
└───────────────────┘

Introduced in version 20.1.

bitmapXor

Computes the symmetric difference (XOR) of two bitmaps.

Syntax

bitmapXor(bitmap1, bitmap2)

Arguments

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.

bitmapXorCardinality

Returns the cardinality of the XOR (symmetric difference) of two bitmaps.

Syntax

bitmapXorCardinality(bitmap1, bitmap2)

Arguments

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.

subBitmap

Returns a subset of the bitmap, starting from position offset. 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 limit set bits, starting after skipping offset set bits in ascending 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.

Bit

Bit functions reference.

Comparison

Comparison functions reference.

On this page

bitmapAndbitmapAndCardinalitybitmapAndnotbitmapAndnotCardinalitybitmapBuildbitmapCardinalitybitmapContainsbitmapHasAllbitmapHasAnybitmapMaxbitmapMinbitmapOrbitmapOrCardinalitybitmapSubsetInRangebitmapSubsetLimitbitmapToArraybitmapTransformbitmapXorbitmapXorCardinalitysubBitmap