Skip to content
ClickHouse Docs
ClickHouse DocsClickHouse Docs
49.6kGet Started

Bitmap Functions

View as MarkdownEdit this pageReport an issueSet up docs MCP

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

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

bitmapAndCardinality

Introduced in: v20.1.0

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

bitmapAndnot

Introduced in: v20.1.0

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

bitmapAndnotCardinality

Introduced in: v20.1.0

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

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

Returned value

Returns a bitmap from the provided array AggregateFunction(groupBitmap, T)

Examples

Usage example

-- A bitmap is a binary value, so it is shown with `hex`.
SELECT hex(bitmapBuild([1, 2, 3, 4, 5])) AS res, toTypeName(bitmapBuild([1, 2, 3, 4, 5])) AS type;
┌─res────────────┬─type──────────────────────────────────┐
│ 00050102030405 │ AggregateFunction(groupBitmap, UInt8) │
└────────────────┴───────────────────────────────────────┘

Signed bitmap

-- A bitmap is a binary value, so it is shown with `hex`.
SELECT hex(bitmapBuild([-128, -1]::Array(Int8))) AS res, toTypeName(bitmapBuild([-128, -1]::Array(Int8))) AS type;
┌─res──────┬─type─────────────────────────────────┐
│ 000280FF │ AggregateFunction(groupBitmap, Int8) │
└──────────┴──────────────────────────────────────┘

bitmapCardinality

Introduced in: v20.1.0

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

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

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

bitmapHasAll

Introduced in: v20.1.0

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

bitmapHasAny

Introduced in: v20.1.0

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

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

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

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

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

bitmapOr

Introduced in: v20.1.0

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

bitmapOrCardinality

Introduced in: v20.1.0

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

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

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

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

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

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

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

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

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

bitmapXor

Introduced in: v20.1.0

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

bitmapXorCardinality

Introduced in: v20.1.0

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

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