Bitwise and bitmap aggregations

ClickHouse function reference

groupBitAnd

Applies bitwise AND operation to a series of numbers. Syntax:

groupBitAnd(expr)

Arguments:

expr (UInt* or Int*): The expression to aggregate.

Returns:

The result of bitwise AND operation on all input values, with the same data type as the input.

Example:

SELECT
    groupBitAnd(taco_code) AS shared_ingredients
FROM (
    SELECT 44 AS taco_code  -- Beef taco:  00101100
    UNION ALL
    SELECT 28 AS taco_code  -- Chicken taco: 00011100
    UNION ALL
    SELECT 13 AS taco_code  -- Fish taco: 00001101
    UNION ALL
    SELECT 85 AS taco_code  -- Veggie taco: 01010101
) AS taco_recipes;

Result:

| shared_ingredients |
|--------------------|
| 4                  |

In this example, assuming taco_code represents ingredients as bit flags:

binary   decimal
00101100 = 44 (Beef taco)
00011100 = 28 (Chicken taco)
00001101 = 13 (Fish taco)
01010101 = 85 (Veggie taco)

The result 4 (binary 00000100) indicates that only the third bit is common across all taco recipes, which might represent a shared ingredient like “tortilla”. This function is useful for finding common elements or properties across multiple items when those properties are represented as bit flags.

groupBitmap

Calculates the bitmap from an unsigned integer column and returns the cardinality (number of unique elements). Syntax:

groupBitmap(expr)

Arguments:

expr (UInt*): An expression that results in an unsigned integer type.

Returns:

The cardinality of the bitmap as a UInt64 value.
If the -State suffix is added (e.g., groupBitmapState), it will return a bitmap object instead of the cardinality.

Example:

SELECT
    groupBitmap(taco_id) AS unique_tacos
FROM
    (
        SELECT 1 AS taco_id
        UNION ALL
        SELECT 2 AS taco_id
        UNION ALL
        SELECT 1 AS taco_id
        UNION ALL
        SELECT 3 AS taco_id
        UNION ALL
        SELECT 2 AS taco_id
        UNION ALL
        SELECT 4 AS taco_id
        UNION ALL
        SELECT 5 AS taco_id
        UNION ALL
        SELECT 1 AS taco_id
    ) AS taco_orders;

Result:

| unique_tacos |
|--------------|
| 5            |

In this example, groupBitmap calculates the number of unique taco IDs in the taco_orders rows. The result shows that there are 5 unique types of tacos ordered.

groupBitmapAnd

Calculates the bitwise AND of a bitmap column and returns the cardinality of the result. Syntax:

groupBitmapAnd(expr)

Arguments:

expr (AggregateFunction(groupBitmap, UInt*)): An expression that results in AggregateFunction(groupBitmap, UInt*) type.

Returns:

The cardinality of the resulting bitmap as UInt64.
If the -State suffix is added (e.g., groupBitmapAndState), it returns a bitmap object.

Example:

-- Self-contained example using groupBitmapAnd and groupBitmapAndState
WITH taco_ingredients AS (
    SELECT
        'classic' AS taco_id,
        bitmapBuild(cast([1,2,3,4,5] as Array(UInt32))) AS ingredients
    UNION ALL
    SELECT
        'spicy' AS taco_id,
        bitmapBuild(cast([3,4,5,6,7] as Array(UInt32))) AS ingredients
    UNION ALL
    SELECT
        'veggie' AS taco_id,
        bitmapBuild(cast([2,4,5,8,9] as Array(UInt32))) AS ingredients
)
SELECT
    -- Count common ingredients across all tacos
    groupBitmapAnd(ingredients) AS common_ingredient_count,
    -- Get the common ingredients across all tacos
    arraySort(bitmapToArray(groupBitmapAndState(ingredients))) AS common_ingredients
FROM taco_ingredients;

Result:

| common_ingredient_count | common_ingredients  |
|-------------------------|---------------------|
| 2                       | [4,5]               |

In this example:

Each taco’s ingredients are represented as a bitmap.
groupBitmapAnd calculates the number of ingredients common to all tacos.
groupBitmapAndState combined with bitmapToArray and arraySort shows the actual common ingredients.

The groupBitmapAnd function is useful for finding intersections in sets represented as bitmaps, which can be more efficient than using arrays for large datasets.

groupBitmapOr

Calculates the bitwise OR of a bitmap column and returns the cardinality of the result. Syntax:

groupBitmapOr(expr)

Arguments:

expr (AggregateFunction(groupBitmap, UInt*)): An expression that results in AggregateFunction(groupBitmap, UInt*) type.

Returns:

The cardinality of the resulting bitmap as UInt64.
If the -State suffix is added (e.g., groupBitmapOrState), it returns a bitmap object. Example:

WITH taco_preferences AS (
    SELECT 'customer1' AS customer_id, bitmapBuild(cast([1,2,3,4,5] as Array(UInt32))) AS taco_ids
    UNION ALL
    SELECT 'customer2' AS customer_id, bitmapBuild(cast([3,4,5,6,7] as Array(UInt32))) AS taco_ids
    UNION ALL
    SELECT 'customer3' AS customer_id, bitmapBuild(cast([5,6,7,8,9] as Array(UInt32))) AS taco_ids
)
SELECT
    groupBitmapOr(taco_ids) AS unique_taco_count,
    arraySort(bitmapToArray(groupBitmapOrState(taco_ids))) AS all_unique_taco_ids
FROM taco_preferences
WHERE customer_id LIKE 'customer%';

Result:

| unique_taco_count | all_unique_taco_ids     |
|-------------------|-------------------------|
| 9                 | [1,2,3,4,5,6,7,8,9]     |

In this example, groupBitmapOr calculates the total number of unique taco IDs across all customers and groupBitmapOrState returns the actual bitmap state.

groupBitmapOr is equivalent to groupBitmapMerge in functionality.

groupBitmapXor

Calculates the XOR (exclusive OR) of a bitmap column and returns the cardinality of the result. Syntax:

groupBitmapXor(expr)

Arguments:

expr (AggregateFunction(groupBitmap, UInt*)): An expression that results in AggregateFunction(groupBitmap, UInt*) type.

Returns:

The cardinality of the XOR result as UInt64.

Example:

SELECT
    groupBitmapXor(taco_bitmap) AS unique_taco_preferences,
    arraySort(bitmapToArray(groupBitmapXorState(taco_bitmap))) AS xor_taco_preferences
FROM
  (
    SELECT
      'customer1' AS customer_id,
      bitmapBuild(cast([1,2,3,4,5] as Array(UInt32))) AS taco_bitmap
    UNION ALL
    SELECT
      'customer2' AS customer_id,
      bitmapBuild(cast([4,5,6,7,8] as Array(UInt32))) AS taco_bitmap
    UNION ALL
    SELECT
      'customer3' AS customer_id,
      bitmapBuild(cast([1,3,5,7,9] as Array(UInt32))) AS taco_bitmap
  ) AS taco_preferences
WHERE
  like(customer_id, 'customer%');

Result:

unique_taco_preferences	xor_taco_preferences
5	[2,5,6,8,9]


In this example, `groupBitmapXor` calculates the XOR of taco preferences across all customers, resulting in 5 unique preferences and `groupBitmapXorState` returns the actual bitmap state.



### groupBitOr

Applies bitwise OR operation to a series of numbers.

**Syntax:**

```sql
groupBitOr(expr)

Arguments:

expr (UInt* or Int*): An expression that results in UInt* or Int* type.

Returns: The result of bitwise OR operation, with the same type as the input (UInt* or Int*). Example: Let’s say we have a table taco_orders with a column spice_level representing the spiciness of each taco order in binary format:

SELECT
	spice_level,
	groupBitOr(spice_level) OVER () AS combined_spice
FROM
(
    SELECT 0b101100 AS spice_level
    UNION ALL
    SELECT 0b011100 AS spice_level
    UNION ALL
    SELECT 0b001101 AS spice_level
    UNION ALL
    SELECT 0b1010101 AS spice_level
) AS taco_orders;

Result:

| spice_level | combined_spice |
|-------------|----------------|
| 44          | 125            |
| 13          | 125            |
| 85          | 125            |
| 28          | 125            |

In this example:

Each spice_level represents different combinations of spices (each bit could represent a specific spice).
groupBitOr combines all spice levels, resulting in a value that represents all spices used across all orders.
The result 125 (binary 1111101) indicates that almost all spices were used in at least one order, except for the second least significant bit.

This function can be useful for combining flags or properties represented as bit fields across multiple rows.

groupBitXor

Applies bitwise XOR operation to a series of numbers. Syntax:

groupBitXor(expr)

Arguments:

expr (UInt* or Int*): An expression that results in UInt* or Int* type.

Returns: The result of bitwise XOR operation. Type is the same as the input (UInt* or Int*). Example: Let’s consider a table taco_sales with a column daily_sales representing daily taco sales encoded as bit flags:

SELECT
    groupBitXor(daily_sales) AS combined_sales_flags
FROM (
    SELECT 0b00101100 AS daily_sales  -- 44 in decimal
    UNION ALL
    SELECT 0b00011100 AS daily_sales  -- 28 in decimal
    UNION ALL
    SELECT 0b00001101 AS daily_sales  -- 13 in decimal
    UNION ALL
    SELECT 0b01010101 AS daily_sales  -- 85 in decimal
) AS taco_sales;

Result:

| combined_sales_flags |
|----------------------|
| 104                  |

In this example:

The groupBitXor function performs a bitwise XOR across all these values.
The result 104 (01101000 in binary) represents the combined sales flags for the week.

This function can be useful for combining bit flags or performing certain types of data analysis where XOR operations are relevant.

Introduction

Statements

Bitwise and bitmap aggregations

ClickHouse function reference

groupBitAnd

groupBitmap

groupBitmapAnd

groupBitmapOr

groupBitmapXor

groupBitXor

Introduction

Statements

​ClickHouse function reference

​groupBitAnd

​groupBitmap

​groupBitmapAnd

​groupBitmapOr

​groupBitmapXor

​groupBitXor

ClickHouse function reference

groupBitAnd

groupBitmap

groupBitmapAnd

groupBitmapOr

groupBitmapXor

groupBitXor