Bitmap functions

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/functions/bitmap-functions.md)

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[​](#bitmapAnd "Direct link to 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)`](/docs/sql-reference/data-types/aggregatefunction). - `bitmap2` — Second bitmap object. [`AggregateFunction(groupBitmap, T)`](/docs/sql-reference/data-types/aggregatefunction).

**Returned value**

Returns a bitmap containing bits present in both input bitmaps [`AggregateFunction(groupBitmap, T)`](/docs/sql-reference/data-types/aggregatefunction)

**Examples**

**Usage example**

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

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

## bitmapAndCardinality[​](#bitmapAndCardinality "Direct link to 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)`](/docs/sql-reference/data-types/aggregatefunction). - `bitmap2` — Second bitmap object. [`AggregateFunction(groupBitmap, T)`](/docs/sql-reference/data-types/aggregatefunction).

**Returned value**

Returns the number of set bits in the intersection of the two bitmaps [`UInt64`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Usage example**

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

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

## bitmapAndnot[​](#bitmapAndnot "Direct link to 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)`](/docs/sql-reference/data-types/aggregatefunction). - `bitmap2` — Second bitmap object. [`AggregateFunction(groupBitmap, T)`](/docs/sql-reference/data-types/aggregatefunction).

**Returned value**

Returns a bitmap containing set bits present in the first bitmap but not in the second [`AggregateFunction(groupBitmap, T)`](/docs/sql-reference/data-types/aggregatefunction)

**Examples**

**Usage example**

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

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

## bitmapAndnotCardinality[​](#bitmapAndnotCardinality "Direct link to 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)`](/docs/sql-reference/data-types/aggregatefunction). - `bitmap2` — Second bitmap object. [`AggregateFunction(groupBitmap, T)`](/docs/sql-reference/data-types/aggregatefunction).

**Returned value**

Returns the number of set bits in the result of `bitmap1 AND-NOT bitmap2` [`UInt64`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Usage example**

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

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

## bitmapBuild[​](#bitmapBuild "Direct link to bitmapBuild")

Introduced in: v20.1.0

Builds a bitmap from an unsigned integer array. It is the opposite of function [`bitmapToArray`](/docs/sql-reference/functions/bitmap-functions#bitmapToArray).

**Syntax**

```
bitmapBuild(array)
```

**Arguments**

- `array` — Unsigned integer array. [`Array(UInt*)`](/docs/sql-reference/data-types/array)

**Returned value**

Returns a bitmap from the provided array [`AggregateFunction(groupBitmap, T)`](/docs/sql-reference/data-types/aggregatefunction)

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

## bitmapCardinality[​](#bitmapCardinality "Direct link to 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)`](/docs/sql-reference/data-types/aggregatefunction).

**Returned value**

Returns the number of bits set in the bitmap [`UInt64`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Usage example**

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

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

## bitmapContains[​](#bitmapContains "Direct link to bitmapContains")

Introduced in: v20.1.0

Checks if the bitmap contains a specific element.

**Syntax**

```
bitmapContains(bitmap, value)
```

**Arguments**

- `bitmap` — Bitmap object. [`AggregateFunction(groupBitmap, T)`](/docs/sql-reference/data-types/aggregatefunction). - `value` — Element to check for. [(U)Int8/16/32/64](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns `1` if the bitmap contains the specified value, otherwise `0` [`UInt8`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Usage example**

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

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

## bitmapHasAll[​](#bitmapHasAll "Direct link to 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)`](/docs/sql-reference/data-types/aggregatefunction). - `bitmap2` — Second bitmap object. [`AggregateFunction(groupBitmap, T)`](/docs/sql-reference/data-types/aggregatefunction).

**Returned value**

Returns `1` if all set bits of the second bitmap are present in the first bitmap, otherwise `0` [`UInt8`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Usage example**

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

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

## bitmapHasAny[​](#bitmapHasAny "Direct link to 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)`](/docs/sql-reference/data-types/aggregatefunction). - `bitmap2` — Second bitmap object. [`AggregateFunction(groupBitmap, T)`](/docs/sql-reference/data-types/aggregatefunction).

**Returned value**

Returns `1` if any bits of the second bitmap are present in the first bitmap, otherwise `0` [`UInt8`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Usage example**

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

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

## bitmapMax[​](#bitmapMax "Direct link to bitmapMax")

Introduced in: v20.1.0

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

**Syntax**

```
bitmapMax(bitmap)
```

**Arguments**

- `bitmap` — Bitmap object. [`AggregateFunction(groupBitmap, T)`](/docs/sql-reference/data-types/aggregatefunction).

**Returned value**

Returns the position of the greatest bit set in the bitmap, otherwise `0` [`UInt64`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Usage example**

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

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

## bitmapMin[​](#bitmapMin "Direct link to bitmapMin")

Introduced in: v20.1.0

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**