ClickHouse/clickhouse-docs-embeddings huggingface dataset:
entropy

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

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

Introduced in: v20.1.0

Calculates the [Shannon entropy](https://en.wikipedia.org/wiki/Entropy_(information_theory)) for a column of values.

**Syntax**

```
entropy(val)
```

**Arguments**

- `val` — Column of values of any type. [`Any`](/docs/sql-reference/data-types)

**Returned value**

Returns Shannon entropy. [`Float64`](/docs/sql-reference/data-types/float)

**Examples**

**Basic entropy calculation**

```
CREATE TABLE entropy (`vals` UInt32,`strings` String)
ENGINE = Memory;

INSERT INTO entropy VALUES (1, 'A'), (1, 'A'), (1,'A'), (1,'A'), (2,'B'), (2,'B'), (2,'C'), (2,'D');

SELECT entropy(vals), entropy(strings) FROM entropy
```

```
┌─entropy(vals)─┬─entropy(strings)─┐
│             1 │             1.75 │
└───────────────┴──────────────────┘
```

- [entropy](#entropy)

Was this page helpful?
title_wip

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

It is an alias for [`any`](/docs/sql-reference/aggregate-functions/reference/any) but it was introduced for compatibility with [Window Functions](/docs/sql-reference/window-functions), where sometimes it's necessary to process `NULL` values (by default all ClickHouse aggregate functions ignore NULL values).

It supports declaring a modifier to respect nulls (`RESPECT NULLS`), both under [Window Functions](/docs/sql-reference/window-functions) and in normal aggregations.

As with `any`, without Window Functions the result will be random if the source stream is not ordered and the return type
matches the input type (Null is only returned if the input is Nullable or -OrNull combinator is added).

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

```
CREATE TABLE test_data
(
    a Int64,
    b Nullable(Int64)
)
ENGINE = Memory;

INSERT INTO test_data (a, b) VALUES (1,null), (2,3), (4, 5), (6,null);
```

### Example 1[​](#example1 "Direct link to Example 1")

By default, the NULL value is ignored.

```
SELECT first_value(b) FROM test_data;
```

```
┌─any(b)─┐
│      3 │
└────────┘
```

### Example 2[​](#example2 "Direct link to Example 2")

The NULL value is ignored.

```
SELECT first_value(b) ignore nulls FROM test_data
```

```
┌─any(b) IGNORE NULLS ─┐
│                    3 │
└──────────────────────┘
```

### Example 3[​](#example3 "Direct link to Example 3")

The NULL value is accepted.

```
SELECT first_value(b) respect nulls FROM test_data
```

```
┌─any(b) RESPECT NULLS ─┐
│                  ᴺᵁᴸᴸ │
└───────────────────────┘
```

### Example 4[​](#example4 "Direct link to Example 4")

Stabilized result using the sub-query with `ORDER BY`.

```
SELECT
    first_value_respect_nulls(b),
    first_value(b)
FROM
(
    SELECT *
    FROM test_data
    ORDER BY a ASC
)
```

```
┌─any_respect_nulls(b)─┬─any(b)─┐
│                 ᴺᵁᴸᴸ │      3 │
└──────────────────────┴────────┘
```

- [examples](#examples)
  - [Example 1](#example1)- [Example 2](#example2)- [Example 3](#example3)- [Example 4](#example4)

Was this page helpful?
groupArray

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

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

Introduced in: v1.1.0

Creates an array of argument values.
Values can be added to the array in any (indeterminate) order.

The second version (with the `max_size` parameter) limits the size of the resulting array to `max_size` elements. For example, `groupArray(1)(x)` is equivalent to `[any(x)]`.

In some cases, you can still rely on the order of execution. This applies to cases when `SELECT` comes from a subquery that uses `ORDER BY` if the subquery result is small enough.

The `groupArray` function will remove `NULL` values from the result.

**Syntax**

```
groupArray(x)
groupArray(max_size)(x)
```

**Aliases**: `array_agg`

**Parameters**

- `max_size` — Optional. Limits the size of the resulting array to `max_size` elements. [`UInt64`](/docs/sql-reference/data-types/int-uint)

**Arguments**

- `x` — Argument values to collect into an array. [`Any`](/docs/sql-reference/data-types)

**Returned value**

Returns an array of argument values. [`Array`](/docs/sql-reference/data-types/array)

**Examples**

**Basic usage**

```
SELECT id, groupArray(10)(name) FROM default.ck GROUP BY id;
```

```
┌─id─┬─groupArray(10)(name)─┐
│  1 │ ['zhangsan','lisi']  │
│  2 │ ['wangwu']           │
└────┴──────────────────────┘
```

- [groupArray](#groupArray)

Was this page helpful?
title_wip

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

Aggregates arrays into a larger array of those arrays.
Combines the [`groupArray`](/docs/sql-reference/aggregate-functions/reference/grouparray) function with the [Array](/docs/sql-reference/aggregate-functions/combinators#-array) combinator.

Alias: `array_concat_agg`

**Example**

We have data which captures user browsing sessions. Each session records the sequence of pages a specific user visited.
We can use the `groupArrayArray` function to analyze the patterns of page visits for each user.

```
CREATE TABLE website_visits (
    user_id UInt32,
    session_id UInt32,
    page_visits Array(String)
) ENGINE = Memory;

INSERT INTO website_visits VALUES
(101, 1, ['homepage', 'products', 'checkout']),
(101, 2, ['search', 'product_details', 'contact']),
(102, 1, ['homepage', 'about_us']),
(101, 3, ['blog', 'homepage']),
(102, 2, ['products', 'product_details', 'add_to_cart', 'checkout']);
```

```
SELECT
    user_id,
    groupArrayArray(page_visits) AS user_session_page_sequences
FROM website_visits
GROUP BY user_id;
```

```
   ┌─user_id─┬─user_session_page_sequences───────────────────────────────────────────────────────────────┐
1. │     101 │ ['homepage','products','checkout','search','product_details','contact','blog','homepage'] │
2. │     102 │ ['homepage','about_us','products','product_details','add_to_cart','checkout']             │
   └─────────┴───────────────────────────────────────────────────────────────────────────────────────────┘
```

Was this page helpful?
groupArrayInsertAt

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

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

Introduced in: v1.1.0

Inserts a value into the array at the specified position.

If in one query several values are inserted into the same position, the function behaves in the following ways:

- If a query is executed in a single thread, the first one of the inserted values is used.
- If a query is executed in multiple threads, the resulting value is an undetermined one of the inserted values.

**Syntax**

```
groupArrayInsertAt(default_x, size)([x, pos])
```

**Parameters**

- `default_x` — Optional. Default value for substituting in empty positions. [`Any`](/docs/sql-reference/data-types)
- `size` — Optional. Length of the resulting array. When using this parameter, the default value `default_x` must be specified. [`UInt32`](/docs/sql-reference/data-types/int-uint)

**Arguments**

- `x` — Value to be inserted. [`Any`](/docs/sql-reference/data-types)
- `pos` — Position at which the specified element `x` is to be inserted. Index numbering in the array starts from zero. [`UInt32`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns an array with inserted values. [`Array`](/docs/sql-reference/data-types/array)

**Examples**

**Basic usage without parameters**

```
SELECT groupArrayInsertAt(toString(number), number * 2) FROM numbers(5);
```

```
┌─groupArrayInsertAt(toString(number), multiply(number, 2))─┐
│ ['0','','1','','2','','3','','4']                         │
└───────────────────────────────────────────────────────────┘
```

**Usage with default value parameter**

```
SELECT groupArrayInsertAt('-')(toString(number), number * 2) FROM numbers(5);
```

```
┌─groupArrayInsertAt('-')(toString(number), multiply(number, 2))─┐
│ ['0','-','1','-','2','-','3','-','4']                          │
└────────────────────────────────────────────────────────────────┘
```

**Usage with default value and size parameters**

```
SELECT groupArrayInsertAt('-', 5)(toString(number), number * 2) FROM numbers(5);
```

```
┌─groupArrayInsertAt('-', 5)(toString(number), multiply(number, 2))─┐
│ ['0','-','1','-','2']                                             │
└───────────────────────────────────────────────────────────────────┘
```

**Multi-threaded insertion into same position**

```
SELECT groupArrayInsertAt(number, 0) FROM numbers_mt(10) SETTINGS max_block_size = 1;
```

```
┌─groupArrayInsertAt(number, 0)─┐
│ [7]                           │
└───────────────────────────────┘
```

- [groupArrayInsertAt](#groupArrayInsertAt)

Was this page helpful?
groupArrayIntersect

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

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

Introduced in: v24.2.0

Return an intersection of given arrays (Return all items of arrays, that are in all given arrays).

**Syntax**

```
groupArrayIntersect(x)
```

**Arguments**

- `x` — Argument (column name or expression). [`Any`](/docs/sql-reference/data-types)

**Returned value**

Returns an array that contains elements that are in all arrays. [`Array`](/docs/sql-reference/data-types/array)

**Examples**

**Usage example**

```
-- Create table with Memory engine
CREATE TABLE numbers (
    a Array(Int32)
) ENGINE = Memory;

-- Insert sample data
INSERT INTO numbers VALUES
    ([1,2,4]),
    ([1,5,2,8,-1,0]),
    ([1,5,7,5,8,2]);

SELECT groupArrayIntersect(a) AS intersection FROM numbers;
```

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

- [groupArrayIntersect](#groupArrayIntersect)

Was this page helpful?
groupArrayLast

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

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

Introduced in: v23.1.0

Creates an array of the last argument values.
For example, `groupArrayLast(1)(x)` is equivalent to `[anyLast(x)]`.
In some cases, you can still rely on the order of execution.
This applies to cases when SELECT comes from a subquery that uses ORDER BY if the subquery result is small enough.

**Syntax**

```
groupArrayLast(max_size)(x)
```

**Parameters**

- `max_size` — Maximum size of the resulting array. [`UInt64`](/docs/sql-reference/data-types/int-uint)

**Arguments**

- `max_size` — Maximum size of the resulting array. [`UInt64`](/docs/sql-reference/data-types/int-uint)
- `x` — Argument (column name or expression). [`Any`](/docs/sql-reference/data-types)

**Returned value**

Returns an array of the last argument values. [`Array(T)`](/docs/sql-reference/data-types/array)

**Examples**

**Usage example**

```
SELECT groupArrayLast(2)(number+1) numbers FROM numbers(10);
```

```
┌─numbers─┐
│ [9,10]  │
└─────────┘
```

**Comparison with groupArray**

```
-- Compare with groupArray (first values)
SELECT groupArray(2)(number+1) numbers FROM numbers(10);
```

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

- [groupArrayLast](#groupArrayLast)

Was this page helpful?
groupArrayMovingAvg

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

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

Introduced in: v20.1.0

Calculates the moving average of input values.

Note

The function uses rounding towards zero.
It truncates the decimal places insignificant for the resulting data type.

**Syntax**

```
groupArrayMovingAvg(numbers_for_summing)
groupArrayMovingAvg(window_size)(numbers_for_summing)
```

**Parameters**

- `window_size` — Size of the calculation window. If left unspecified, the function takes the window size equal to the number of rows in the column. [`UInt64`](/docs/sql-reference/data-types/int-uint)

**Arguments**

- `numbers_for_summing` — Expression resulting in a numeric data type value. [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/float) or [`Decimal`](/docs/sql-reference/data-types/decimal)

**Returned value**

Returns an array of the same size and type as the input data. [`Array`](/docs/sql-reference/data-types/array)

**Examples**

**Usage example**

```
CREATE TABLE t
(
    `int` UInt8,
    `float` Float32,
    `dec` Decimal32(2)
)
ENGINE = Memory;

INSERT INTO t VALUES (1, 1.1, 1.10), (2, 2.2, 2.20), (4, 4.4, 4.40), (7, 7.77, 7.77);

SELECT
    groupArrayMovingAvg(int) AS I,
    groupArrayMovingAvg(float) AS F,
    groupArrayMovingAvg(dec) AS D
FROM t;
```

```
┌─I─────────┬─F───────────────────────────────────┬─D─────────────────────┐
│ [0,0,1,3] │ [0.275,0.82500005,1.9250001,3.8675] │ [0.27,0.82,1.92,3.86] │
└───────────┴─────────────────────────────────────┴───────────────────────┘
```

**With window size**

```
SELECT
    groupArrayMovingAvg(2)(int) AS I,
    groupArrayMovingAvg(2)(float) AS F,
    groupArrayMovingAvg(2)(dec) AS D
FROM t;
```

```
┌─I─────────┬─F────────────────────────────────┬─D─────────────────────┐
│ [0,1,3,5] │ [0.55,1.6500001,3.3000002,6.085] │ [0.55,1.65,3.30,6.08] │
└───────────┴──────────────────────────────────┴───────────────────────┘
```

- [groupArrayMovingAvg](#groupArrayMovingAvg)

Was this page helpful?
groupArrayMovingSum

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

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

Introduced in: v20.1.0

Calculates the moving sum of input values.

The function can take the window size as a parameter. If left unspecified, the function takes the window size equal to the number of rows in the column.

**Syntax**

```
groupArrayMovingSum(numbers_for_summing)
groupArrayMovingSum(window_size)(numbers_for_summing)
```

**Parameters**

- `window_size` — Size of the calculation window. If left unspecified, the function takes the window size equal to the number of rows in the column. [`UInt64`](/docs/sql-reference/data-types/int-uint)

**Arguments**

- `numbers_for_summing` — Expression resulting in a numeric data type value. [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/float) or [`Decimal`](/docs/sql-reference/data-types/decimal)

**Returned value**

Returns an array of the same size and type as the input data. [`Array`](/docs/sql-reference/data-types/array)

**Examples**

**Usage example**

```
CREATE TABLE t
(
    `int` UInt8,
    `float` Float32,
    `dec` Decimal32(2)
)
ENGINE = Memory;

INSERT INTO t VALUES (1, 1.1, 1.10), (2, 2.2, 2.20), (4, 4.4, 4.40), (7, 7.77, 7.77);

SELECT
    groupArrayMovingSum(int) AS I,
    groupArrayMovingSum(float) AS F,
    groupArrayMovingSum(dec) AS D
FROM t;
```

```
┌─I──────────┬─F───────────────────────────────┬─D──────────────────────┐
│ [1,3,7,14] │ [1.1,3.3000002,7.7000003,15.47] │ [1.10,3.30,7.70,15.47] │
└────────────┴─────────────────────────────────┴────────────────────────┘
```

**With window size**

```
SELECT
    groupArrayMovingSum(2)(int) AS I,
    groupArrayMovingSum(2)(float) AS F,
    groupArrayMovingSum(2)(dec) AS D
FROM t;
```

```
┌─I──────────┬─F───────────────────────────────┬─D──────────────────────┐
│ [1,3,6,11] │ [1.1,3.3000002,6.6000004,12.17] │ [1.10,3.30,6.60,12.17] │
└────────────┴─────────────────────────────────┴────────────────────────┘
```

- [groupArrayMovingSum](#groupArrayMovingSum)

Was this page helpful?
groupArraySample

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

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

Introduced in: v20.3.0

Creates an array of sample argument values.
The size of the resulting array is limited to `max_size` elements.
Argument values are selected and added to the array randomly.

**Syntax**

```
groupArraySample(max_size[, seed])(x)
```

**Parameters**

- `max_size` — Maximum size of the resulting array. [`UInt64`](/docs/sql-reference/data-types/int-uint)
- `seed` — Optional. Seed for the random number generator. Default value: 123456. [`UInt64`](/docs/sql-reference/data-types/int-uint)
- `x` — Argument (column name or expression). [`Any`](/docs/sql-reference/data-types)

**Arguments**

- `array_column` — Column containing arrays to be aggregated. [`Array`](/docs/sql-reference/data-types/array)

**Returned value**

Array of randomly selected x arguments. [`Array(T)`](/docs/sql-reference/data-types/array)

**Examples**

**Usage example**

```
CREATE TABLE default.colors (
    id Int32,
    color String
) ENGINE = Memory;

INSERT INTO default.colors VALUES
(1, 'red'),
(2, 'blue'),
(3, 'green'),
(4, 'white'),
(5, 'orange');

SELECT groupArraySample(3)(color) as newcolors FROM default.colors;
```

```
┌─newcolors──────────────────┐
│ ['white','blue','green']   │
└────────────────────────────┘
```

**Example using a seed**

```
-- Query with column name and different seed
SELECT groupArraySample(3, 987654321)(color) as newcolors FROM default.colors;
```

```
┌─newcolors──────────────────┐
│ ['red','orange','green']   │
└────────────────────────────┘
```

**Using an expression as an argument**

```
-- Query with expression as argument
SELECT groupArraySample(3)(concat('light-', color)) as newcolors FROM default.colors;
```

```
┌─newcolors───────────────────────────────────┐
│ ['light-blue','light-orange','light-green'] │
└─────────────────────────────────────────────┘
```

- [groupArraySample](#groupArraySample)

Was this page helpful?
groupArraySorted

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

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

Introduced in: v24.2.0

Returns an array with the first N items in ascending order.

**Syntax**

```
groupArraySorted(N)(column)
```

**Parameters**

- `N` — The number of elements to return. [`UInt64`](/docs/sql-reference/data-types/int-uint)

**Arguments**

- `column` — Column for which to group into an array. [`Any`](/docs/sql-reference/data-types)

**Returned value**

Returns an array with the first N items in ascending order. [`Array`](/docs/sql-reference/data-types/array)

**Examples**

**Getting first 10 numbers**

```
SELECT groupArraySorted(10)(number) FROM numbers(100);
```

```
┌─groupArraySorted(10)(number)─┐
│ [0,1,2,3,4,5,6,7,8,9]        │
└──────────────────────────────┘
```

**String sorting example**

```
SELECT groupArraySorted(5)(str) FROM (SELECT toString(number) AS str FROM numbers(5));
```

```
┌─groupArraySorted(5)(str)─┐
│ ['0','1','2','3','4']    │
└──────────────────────────┘
```

- [groupArraySorted](#groupArraySorted)

Was this page helpful?
groupBitAnd

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

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

Introduced in: v1.1.0

Applies bitwise AND for series of numbers.

**Syntax**

```
groupBitAnd(expr)
```

**Aliases**: `BIT_AND`

**Arguments**

- `expr` — Expression of `(U)Int*` type. [`(U)Int*`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns a value of `(U)Int*` type. [`(U)Int*`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Bitwise AND example**

```
CREATE TABLE t (num UInt32) ENGINE = Memory;
INSERT INTO t VALUES (44), (28), (13), (85);

-- Test data:
-- binary     decimal
-- 00101100 = 44
-- 00011100 = 28
-- 00001101 = 13
-- 01010101 = 85

SELECT groupBitAnd(num) FROM t;
```

```
-- Result:
-- binary     decimal
-- 00000100 = 4

┌─groupBitAnd(num)─┐
│                4 │
└──────────────────┘
```

- [groupBitAnd](#groupBitAnd)

Was this page helpful?
groupBitmap

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

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

Introduced in: v20.1.0

Creates a bitmap (bit array) from a column of unsigned integers, then returns the count of unique values (cardinality) in that bitmap.
By appending the `-State` combinator suffix, instead of returning the count, it returns the actual [bitmap object](/docs/sql-reference/functions/bitmap-functions).

**Syntax**

```
groupBitmap(expr)
groupBitmapState(expr)
```

**Arguments**

- `expr` — Expression that results in a `UInt*` type. [`UInt*`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns the count of type UInt64 type, or a bitmap object when using `-State`. [`UInt64`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Usage example**

```
CREATE TABLE t (UserID UInt32) ENGINE = Memory;
INSERT INTO t VALUES (1), (1), (2), (3);

SELECT groupBitmap(UserID) AS num FROM t;
```

```
┌─num─┐
│   3 │
└─────┘
```

- [groupBitmap](#groupBitmap)

Was this page helpful?
groupBitmapAnd

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

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

Introduced in: v20.1.0

Calculates the AND of a bitmap column and returns it's cardinality.
If suffix combinator [`-State`](/docs/sql-reference/aggregate-functions/combinators#-state) is added, then it returns a bitmap object.

**Syntax**

```
groupBitmapAnd(expr)
groupBitmapAndState(expr)
```

**Arguments**

- `expr` — Expression that results in an `AggregateFunction(groupBitmap, UInt*)` type. [`AggregateFunction(groupBitmap, UInt*)`](/docs/sql-reference/data-types/aggregatefunction)

**Returned value**

Returns a count of type `UInt64`, or a bitmap object when using `-State`. [`UInt64`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Usage example**

```
CREATE TABLE bitmap_column_expr_test2
(
    tag_id String,
    z AggregateFunction(groupBitmap, UInt32)
)
ENGINE = MergeTree
ORDER BY tag_id;

INSERT INTO bitmap_column_expr_test2 VALUES ('tag1', bitmapBuild(cast([1,2,3,4,5,6,7,8,9,10] AS Array(UInt32))));
INSERT INTO bitmap_column_expr_test2 VALUES ('tag2', bitmapBuild(cast([6,7,8,9,10,11,12,13,14,15] AS Array(UInt32))));
INSERT INTO bitmap_column_expr_test2 VALUES ('tag3', bitmapBuild(cast([2,4,6,8,10,12] AS Array(UInt32))));

SELECT groupBitmapAnd(z) FROM bitmap_column_expr_test2 WHERE like(tag_id, 'tag%');
```

```
┌─groupBitmapAnd(z)─┐
│               3   │
└───────────────────┘
```

**Using -State combinator**

```
SELECT arraySort(bitmapToArray(groupBitmapAndState(z))) FROM bitmap_column_expr_test2 WHERE like(tag_id, 'tag%');
```

```
┌─arraySort(bitmapToArray(groupBitmapAndState(z)))─┐
│ [6,8,10]                                         │
└──────────────────────────────────────────────────┘
```

- [groupBitmapAnd](#groupBitmapAnd)

Was this page helpful?