The ClickHouse community provides bitmap data Processing capabilities. For details, see Bitmap Functions | ClickHouse Docs. Around the same time, ByteHouse independently developed a set of types and related functions for Processing bitmap Data. There are certain similarities and differences between the two in Data storage and use methods. More Contrast and Intro about BitMap Type Data can be found in BitMap64 Type>#00956554 Document.
ByteHouse offers a rich set of function interfaces to facilitate Search of BitMap64 Type Data. These can be roughly categorized as Normal functions, Aggregate functions, and high-level Aggregate functions. The following provides the interface list and Instructions for ByteHouse's BitMap functions.

ByteHouse's BitMap Normal functions are Delivered a row Record or a BitMap64 object and Back a row As a result.
For this Part of the function interfaces, except for the first function arrayToBitmap, all others are consistent with the community function interfaces.

| | | | | | \

bitmapAnd #

Adds two bitmaps together, As a result is a new bitmap.
Syntax

bitmapAnd(bitmap,bitmap)

Param

• bitmap– Bitmap object.

Return value

• Bitmap object

Example

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

As a result:

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

bitmapAndCardinality #

Calculates the Sum of two bitmaps and returns the cardinality of Type UInt64.
Syntax

bitmapAndCardinality(bitmap,bitmap)

Param

• bitmap– Bitmap object.

Return value

• The cardinality of Type UInt64.

Type: Uint64
Example

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

As a result:

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

bitmapAndnot #

The Sum of two bitmaps is not calculated, As a result is a new bitmap.
Syntax

bitmapAndnot(bitmap,bitmap)

Param

• bitmap– Bitmap object.

Return value

• Bitmap object

Type: Bitmap object.
Example

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

As a result:

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

bitmapAndnotCardinality #

The and of two bitmaps is not calculated, returns the cardinality of Type UInt64.
Syntax

bitmapAndnotCardinality(bitmap,bitmap)

Param

• bitmap– Bitmap object.

Return value

• The cardinality of UInt64.

Type: UInt64
Example

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

As a result:

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

bitmapBuild #

Builds a bitmap from an unsigned Integer set.
Syntax

bitmapBuild(array)

Param

• array– unsigned Integer set.

Return value

• Bitmap object

Type: Bitmap object.
Example

SELECT toTypeName(bitmapBuild([1, 2, 3, 4, 5]));

As a result:

┌─toTypeName(bitmapBuild([1, 2, 3, 4, 5]))─┐ 
│ AggregateFunction(groupBitmap, UInt8)    │ 
└──────────────────────────────────────────┘

bitmapCardinality #

Returns the bitmap cardinality of Type UInt64.
Syntax

bitmapCardinality(bitmap)

Param

• bitmap– Bitmap object.

Return value

• The bitmap cardinality of Type UInt64.

Type: UInt64.
Example

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

As a result:

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

bitmapContains #

Checks whether the bitmap contains an Element.
Syntax

bitmapContains(haystack, needle)

Param

• haystack– [Bitmap object], the function Search Of which.
• needle– Value the function searches for. Type: [UInt32].

Return value

• 0 — If haystack does Not included needle.
• 1 — If haystack contains needle.

Type: UInt8.
Example

SELECT bitmapContains(bitmapBuild([1,5,7,9]), toUInt32(9)) AS res;

As a result:

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

bitmapHasAll #

hasAll(array, array)If the first bitmap contains all Elements of the second bitmap, then returns 1; Otherwise returns 0.
If the second param is an empty bitmap, then returns 1.
Syntax

bitmapHasAll(bitmap,bitmap)

Param

• bitmap– Bitmap object.

Return value

• 1, Back 1 if the first bitmap contains all elements of the second bitmap or the second parameter is an empty bitmap.
• 0, Otherwise.

Type: UInt8
Example

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

As a result:

┌─res─┐ 
│ 0   │ 
└─────┘

bitmapHasAny #

Check whether two bitmaps have an intersection of certain elements.
Syntax

bitmapHasAny(bitmap1, bitmap2)

If you Confirm bitmap2 only contains one Element, consider use the [bitmapContains] function. It works more efficiently.
Param

• bitmap*– Bitmap object.

Return value

• 1, if bitmap1 and bitmap2 have at least one similar Element.
• 0, Otherwise.

Example

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

As a result:

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

bitmapMax #

Back the Max Value of Type UInt64 in the set. If the set Is empty, Back 0.
Syntax

bitmapMax(bitmap)

Param

• bitmap– Bitmap object.

Return value

• Back the Max Value of Type UInt64 in the set.

Type: UInt64
Example

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

As a result:

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

bitmapMin #

Back the Low Value of Type UInt64 in the set. If the set Is empty, Back UINT32_MAX.
Syntax

bitmapMin(bitmap)

Param

• bitmap– Bitmap object.

Return value

• Back the Low Value of Type UInt64 in the set.

Type: UInt64
Example

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

As a result:

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

bitmapOr #

Carry on or Calculate for two bitmaps. The Result is a new bitmap.

bitmapOr(bitmap,bitmap)

Param

• bitmap– Bitmap object.

Return value

• Bitmap object

Type: Bitmap object.
Example

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

As a result:

┌─res─────────────┐ 
│ [1, 2, 3, 4, 5] │ 
└─────────────────┘

bitmapOrCardinality #

Carry on or Calculate for two bitmaps, Back the cardinality of Type UInt64.
Syntax

bitmapOrCardinality(bitmap,bitmap)

Param

• bitmap– Bitmap object.

Return value

• Back the Result in Form of cardinality.

Type: UInt64
Example

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

As a result:

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

bitmapSubsetInRange #

Back the subset within the specified Scope (excluding range_end).
Syntax

bitmapSubsetInRange(bitmap, range_start, range_end)

Param

• bitmap– [Bitmap object].
• range_start– Scope start. Type: [UInt32].
• range_end– Scope end (excluded). Type: [UInt32].

Return value

• Back the Result of or Calculate.

Type: array
Example

SELECT bitmapToArray(bitmapSubsetInRange(bitmapBuild([0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,100,200,500]), toUInt32(30), toUInt32(200))) AS res;

As a result:

┌─res───────────────────┐ 
│ [30, 31, 32, 33, 100] │ 
└───────────────────────┘

bitmapSubsetLimit #

create a subset of the bitmap, Of which contains n items between range_start and cardinality_limit.
Syntax

bitmapSubsetLimit(bitmap, range_start, cardinality_limit)

Param

• bitmap– [Bitmap object].
• range_start– Start of the subset. Type: [UInt32].
• cardinality_limit– Cap of subset cardinality. Type: [UInt32].

Return value

• Subset.

Type: Bitmap object.
Example

SELECT bitmapToArray(bitmapSubsetLimit(bitmapBuild([0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,100,200,500]), toUInt32(30), toUInt32(200))) AS res;

As a result:

┌─res─────────────────────────────┐ 
│ [30, 31, 32, 33, 100, 200, 500] │ 
└─────────────────────────────────┘

bitmapToArray #

Convert the bitmap to an Integer set.
Syntax

bitmapToArray(bitmap)

Param

• bitmap– Bitmap object.

Return value

• Integer set.

Type: array.
Example

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

As a result:

┌─res─────────────┐ 
│ [1, 2, 3, 4, 5] │ 
└─────────────────┘

bitmapXor #

Carry on XOR Calculate for two bitmaps. The Result is a new bitmap.

bitmapXor(bitmap,bitmap)

Param

• bitmap– Bitmap object.

Return value

• Bitmap object

Type: Bitmap object.
Example

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

As a result:

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

bitmapXorCardinality #

Carry on XOR Calculate for two bitmaps, Back the cardinality of Type UInt64.
Syntax

bitmapXorCardinality(bitmap,bitmap)

Param

• bitmap– Bitmap object.

Return value

• Bitmap object

Type: Bitmap object.
Example

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

As a result:

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

subBitmap #

Extract subset data from the specified index range within the bitmap, starting from the offset position. The maximum cardinality of the returned bitmap is cardinality_limit.
Syntax

subBitmap(bitmap, offset, cardinality_limit)

Parameters

• bitmap - [bitmap object]
• offset - Position of the first element in the subset, Type: UInt32
• cardinality_limit - Maximum cardinality of the subset, Type: UInt32

Caution: offset starts from 0, representing the first valid value actually stored, similar to an array index. Both offset and cardinality_limit are meaningless if they exceed the number of elements in the bitmap.
Return value

• Subset

Example

SELECT bitmapToArray(subBitmap(bitmapBuild([0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,100,200,500]), toUInt32(10), toUInt32(10))) AS res; 

--- Equivalent to the following ByteHouse BitMap64 type syntax 
SELECT bitmapToArray(subBitmap(arrayToBitmap([0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 100, 200, 500]), toUInt32(10), toUInt32(10))) AS res

┌─res─────────────────────────────┐ 
│ [10,11,12,13,14,15,16,17,18,19] │ 
└─────────────────────────────────┘

The following aggregate functions receive multiple rows, perform aggregation, and return a single result.

| | | | | | \

The following are specific function examples:

bitmapColumAnd #

Calculates the intersection of a bitmap column.
Syntax

bitmapColumnAnd(bitmap_column)

Parameters
bitmap_column - Data column of BitMap64 type.
Return value

• A BitMap64 object

Example

select bitmapColumnAnd(rbm) as result 
from ( 
    select arrayToBitmap([1,2,3]) as rbm 
    union all 
    select arrayToBitmap([2,3,4]) as rbm 
)

Result:

┌─result─┐ 
│ {2,3}  │ 
└────────┘

bitmapColumnOr #

Calculates the union of a bitmap column.
Syntax

bitmapColumnOr(bitmap_column)

Parameters
bitmap_column - Data column of BitMap64 type.
Return value

• A BitMap64 object

Example

select bitmapColumnOr(rbm) as result 
from ( 
    select arrayToBitmap([1,2,3]) as rbm 
    union all 
    select arrayToBitmap([2,3,4]) as rbm 
)

Result:

┌─result────┐ 
│ {1,2,3,4} │ 
└───────────┘

bitmapColumnXor #

Calculates the xor set of a bitmap column.
Syntax

bitmapColumnXor(bitmap_column)

Parameters
bitmap_column - Data column of BitMap64 type.
Return value

• A BitMap64 object

Example

select bitmapColumnXor(rbm) as result 
from ( 
    select arrayToBitmap([1,2,3]) as rbm 
    union all 
    select arrayToBitmap([2,3,4]) as rbm 
)

Result:

┌─result─┐ 
│ {1,4}  │ 
└────────┘

bitmapColumnCardinality #

Calculates the number of elements contained in the union of a bitmap column.
Syntax

bitmapColumnCardinality(bitmap_column)

Parameters
bitmap_column - Data column of BitMap64 type.
Return value

• A UInt64 number, representing the number of bitmap elements.

Example

select bitmapColumnCardinality(rbm) as result 
from ( 
    select arrayToBitmap([1,2,3]) as rbm 
    union all 
    select arrayToBitmap([2,3,4]) as rbm 
)

Result:

┌─result─┐ 
│      4 │ 
└────────┘

bitmapColumnHas #

Determines whether a bitmap column contains the specified numerical value.
Syntax

bitmapColumnHas(bitmap_column, integer_number)

Parameters
bitmap_column - Data column of BitMap64 type.
integer_number - A constant numerical value. Type: positive integer within 64 bits.
Return value

• A Boolean value: 0 indicates that the given numerical value does not exist, 1 indicates that it exists.
• The difference between this function and the bitmapContains function is that this function returns only one row value for a bitmap column, while bitmapContains returns multiple rows.

Example

select bitmapColumnHas(rbm, 3) as result 
from ( 
    select arrayToBitmap([1,2,3]) as rbm 
    union all 
    select arrayToBitmap([2,3,4]) as rbm 
)

As a result:

┌─result─┐ 
│      1 │ 
└────────┘

bitmapFromColumn #

Construct a BitMap64 object from a column of numerical values.
Syntax

bitmapFromColumn(integer_column)

Param

• integer_column - A column of positive integers within 64 bits.

Return value

• A BitMap64 object

Example

select bitmapFromColumn(number) as res from numbers(10);

As a result:

┌─res───────────────────┐ 
│ {0,1,2,3,4,5,6,7,8,9} │ 
└───────────────────────┘

These functions use two sets of parentheses to pass parameters:

• The first set is called the parameter, used to control the aggregate function's behaviour (control flow).
• The second set is called the argument, used to pass input data columns to the aggregate function (data flow).

ByteHouse provides a series of aggregate functions for batch processing complex calculations between large numbers of bitmaps. By assigning an order number to each bitmap to be calculated and using the order number to express set intersection, union, and difference operations, ByteHouse can perform the corresponding bitmap operations according to the specified operation expression, directly visualizing and simplifying the calculation, greatly improving the SQL writing experience!
A simple illustration is as follows:

| | | | | | \

The following are specific function Descriptions.

BitmapCount #

According to the calculation Expression specified by the User, Run the corresponding bitmap operation and Back the number of Elements in the final bitmap.
Syntax

BitMapCount(expr)(idx_column, bitmap_column)

Param

• expr - The and (&), or (|), difference (~) operations expressed by Number or String, & represents bitmap intersection, | represents bitmap union, ~ represents bitmap difference; this function only supports one Expression.
• idx_column - The Data Column used to Mark the bitmap, which can be an Integer Column (usually Int16/32/64 Type), or a String Column.
• bitmap_column - BitMap64 Data Column.

Note 1: It is not required for idx_column Data and bitmap_column Data to correspond one-to-one. If idx_column contains Repeated Data, multiple rows of bitmap with the same Value will first be unioned, Merged into a unique bitmap for calculation.
Note 2: When the idx_column Column Type is String, must not contain the following reserved characters: &, |, ~, ,, #, (, ), _, and English space.
Return value

• UInt64 - The number of Elements in the calculated bitmap.

Example

SELECT bitmapCount('a')(tag_value, uids) AS res 
FROM 
( 
    SELECT 
        'a' AS tag_value, 
        arrayToBitmap([1, 2, 3]) AS uids 
)

As a result:

┌─res─┐ 
│   3 │ 
└─────┘

SELECT 
    BitmapCount('((a&b)|c) ~ d')(tag_value, uids) AS res1, 
    BitmapCount('((1&2)|3)~4')(multiIf(tag_value = 'a', toInt16(1), tag_value = 'b', toInt16(2), tag_value = 'c', toInt16(3), tag_value = 'd', toInt16(4), toInt16(0)) AS idx, uids) AS res2 
FROM 
( 
    SELECT 
        'a' AS tag_value, 
        arrayToBitmap([1, 2, 3, 4, 5]) AS uids 
    UNION ALL 
    SELECT 
        'b' AS tag_value, 
        arrayToBitmap([3, 4, 5, 6]) AS uids 
    UNION ALL 
    SELECT 
        'c' AS tag_value, 
        arrayToBitmap([5, 7]) AS uids 
    UNION ALL 
    SELECT 
        'd' AS tag_value, 
        arrayToBitmap([7]) AS uids 
)

As a result

┌─res1─┬─res2─┐ 
│    3 │    3 │ 
└──────┴──────┘

BitmapExtract #

According to the calculation expression specified by the user, perform the corresponding bitmap operation and return the final bitmap.
Syntax

BitMapExtract(expr)(idx_column, bitmap_column)

Parameters

• expr - Use numbers or strings to represent and (&), or (|), difference (~) operations. & indicates bitmap intersection, | indicates bitmap union, ~ indicates bitmap difference. This function only supports a single expression.
• idx_column - The data column used to mark the bitmap. It can be an integer column (usually of Int16/32/64 type) or a string column.
• bitmap_column - BitMap64 data column.

Return value

• BitMap64 - The calculated bitmap object.

Example

SELECT bitmapExtract('a')(tag_value, uids) AS res 
FROM 
( 
    SELECT 
        'a' AS tag_value, 
        arrayToBitmap([1, 2, 3]) AS uids 
)

Result

┌─res─────┐ 
│ {1,2,3} │ 
└─────────┘

SELECT 
    BitmapExtract('((a&b)|c) ~ d')(tag_value, uids) AS res1, 
    BitmapExtract('((1&2)|3)~4')(multiIf(tag_value = 'a', toInt16(1), tag_value = 'b', toInt16(2), tag_value = 'c', toInt16(3), tag_value = 'd', toInt16(4), toInt16(0)) AS idx, uids) AS res2 
FROM 
( 
    SELECT 
        'a' AS tag_value, 
        arrayToBitmap([1, 2, 3, 4, 5]) AS uids 
    UNION ALL 
    SELECT 
        'b' AS tag_value, 
        arrayToBitmap([3, 4, 5, 6]) AS uids 
    UNION ALL 
    SELECT 
        'c' AS tag_value, 
        arrayToBitmap([5, 7]) AS uids 
    UNION ALL 
    SELECT 
        'd' AS tag_value, 
        arrayToBitmap([7]) AS uids 
)

Result:

┌─res1────┬─res2────┐ 
│ {3,4,5} │ {3,4,5} │ 
└─────────┴─────────┘

BitmapMultiCount #

According to a set of calculation expressions specified by the user, perform the corresponding bitmap operation for each group and return the number of elements in the final bitmap of each group.
Syntax

BitmapMultiCount(expr1, expr2, expr3,...)(idx_column, bitmap_column)

Parameters

• expr - Use numbers or strings to represent and (&), or (|), difference (~) operations. & indicates bitmap intersection, | indicates bitmap union, ~ indicates bitmap difference. This function supports multiple expressions.
• idx_column - The data column used to mark the bitmap. It can be an integer column (usually of Int16/32/64 type) or a string column.
• bitmap_column - BitMap64 data column.

Return value

• UInt64 array - The number of elements in the bitmap calculated for each expression. The array size is equal to the number of expressions.

Example

SELECT BitmapMultiCount('a')(tag_value, uids) AS res 
FROM 
( 
    SELECT 
        'a' AS tag_value, 
        arrayToBitmap([1, 2, 3]) AS uids 
)

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

SELECT BitmapMultiCount('1&2', '(1&2)|3', '_1|3', '((1&2)|3) ~ 4', '_2~4')(tag_value, uids) AS res 
FROM 
( 
    SELECT 
        toInt32(1) AS tag_value, 
        arrayToBitmap([1, 2, 3, 4, 5]) AS uids 
    UNION ALL 
    SELECT 
        toInt32(2) AS tag_value, 
        arrayToBitmap([3, 4, 5, 6]) AS uids 
    UNION ALL 
    SELECT 
        toInt32(3) AS tag_value, 
        arrayToBitmap([5, 7]) AS uids 
    UNION ALL 
    SELECT 
        toInt32(4) AS tag_value, 
        arrayToBitmap([7]) AS uids 
)

Result:

┌─res─────────┐ 
│ [3,4,4,3,3] │ 
└─────────────┘

BitmapMultiExtract #

According to a set of calculation expressions specified by the user, perform the corresponding bitmap operation for each group and return the final bitmap of each group.
Syntax

BitmapMultiExtract(expr1, expr2, expr3,...)(idx_column, bitmap_column)

Parameters

• expr - Use numbers or strings to represent and (&), or (|), and difference (~) operations. & indicates bitmap intersection, | indicates bitmap union, and ~ indicates bitmap difference. This function supports multiple expressions.
• idx_column - Data column used to mark the bitmap. It can be an integer column (typically of int16/32/64 type), or a string column.
• bitmap_column - BitMap64 data column.

Return value

• BitMap64 array - Each bitmap calculated from an expression. The array size equals the number of expressions.

Example

SELECT BitmapMultiExtract('a')(tag_value, uids) AS res 
FROM 
( 
    SELECT 
        'a' AS tag_value, 
        arrayToBitmap([1, 2, 3]) AS uids 
)

┌─res───────┐ 
│ [{1,2,3}] │ 
└───────────┘

SELECT BitmapMultiExtract('1&2', '(1&2)|3', '_1|3', '((1&2)|3) ~ 4', '_2~4')(tag_value, uids) AS res 
FROM 
( 
    SELECT 
        toInt32(1) AS tag_value, 
        arrayToBitmap([1, 2, 3, 4, 5]) AS uids 
    UNION ALL 
    SELECT 
        toInt32(2) AS tag_value, 
        arrayToBitmap([3, 4, 5, 6]) AS uids 
    UNION ALL 
    SELECT 
        toInt32(3) AS tag_value, 
        arrayToBitmap([5, 7]) AS uids 
    UNION ALL 
    SELECT 
        toInt32(4) AS tag_value, 
        arrayToBitmap([7]) AS uids 
)

As a result:

┌─res───────────────────────────────────────────┐ 
│ [{3,4,5},{3,4,5,7},{3,4,5,7},{3,4,5},{3,4,5}] │ 
└───────────────────────────────────────────────┘

BitmapMultiCountWithDate #

According to a set of calculation expressions specified by the user, each group performs the corresponding bitmap operation and returns the number of elements in the final bitmap of each group. Compared to the BitmapMultiCount function, it adds a date dimension.
Syntax

BitmapMultiCountWithDate(expr1, expr2, expr3,...)(date_column, idx_column, bitmap_column)

Param

• expr - Use numbers or strings to represent and (&), or (|), and difference (~) operations. & indicates bitmap intersection, | indicates bitmap union, and ~ indicates bitmap difference. This function supports multiple expressions. Note that each expression consists of two parts: one part is the date, and one part is the mark, connected by an underscore. For example: 20240101_aaa&20240102_aaa.
• date_colulmn - Used to specify the data date. Requires an int64 type date column in the format YYYYMMDD.
• idx_column - Data column used to mark the bitmap. It can be an integer column (typically of int16/32/64 type), or a string column.
• bitmap_column - BitMap64 data column.

Return value

• UInt64 array - the number of elements in the bitmap after each expression is calculated. The array size is equal to the number of expressions.

Example

SELECT BitmapMultiCountWithDate('20240102_a~20240101_a', '20240101_a&20240102_a')(toInt64(toYYYYMMDD(date)), tag_value, uids) AS res 
FROM 
( 
    SELECT 
        'a' AS tag_value, 
        '2024-01-01' AS date, 
        arrayToBitmap([1, 2, 3]) AS uids 
    UNION ALL 
    SELECT 
        'a' AS tag_value, 
        '2024-01-02' AS date, 
        arrayToBitmap([2, 3, 4, 5, 6]) AS uids 
)

┌─res───┐ 
│ [3,2] │ 
└───────┘

SELECT BitmapMultiCountWithDate('20240101_1 & 20240102_2', '_1 | 20240101_3', '_2 ~ 20240101_4')(toInt64(toYYYYMMDD(date)), tag_value, uids) AS res 
FROM 
( 
    SELECT 
        toInt32(1) AS tag_value, 
        '2024-01-01' AS date, 
        arrayToBitmap([1, 2, 3, 4, 5]) AS uids 
    UNION ALL 
    SELECT 
        toInt32(2) AS tag_value, 
        '2024-01-02' AS date, 
        arrayToBitmap([3, 4, 5, 6]) AS uids 
    UNION ALL 
    SELECT 
        toInt32(3) AS tag_value, 
        '2024-01-01' AS date, 
        arrayToBitmap([5, 7]) AS uids 
    UNION ALL 
    SELECT 
        toInt32(4) AS tag_value, 
        '2024-01-01' AS date, 
        arrayToBitmap([7]) AS uids 
)

As a result:

┌─res─────┐ 
│ [3,4,3] │ 
└─────────┘

BitmapMultiExtractWithDate #

Based on a set of calculation expressions specified by the user, each group performs the corresponding bitmap operation and returns the final bitmap for each group. Compared to the BitmapMultiExtract function, it adds a date dimension.
Syntax

BitmapMultiExtractWithDate(expr1, expr2, expr3,...)(date_column, idx_column, bitmap_column)

Parameters

• expr - and (&), or (|), and difference (~) operations expressed by numbers or strings; & represents bitmap intersection, | represents bitmap union, ~ represents bitmap difference. This function supports multiple expressions. Note that each expression consists of two parts: one part is the date, and the other part is the mark, connected by an underline. For example, 20240101_aaa&20240102_aaa.
• date_colulmn - Used to specify the data date. Requires an Int64 type date column in the format YYYYMMDD.
• idx_column - Used to mark the bitmap data column. Can be an integer column (usually Int16/32/64 type) or a string column.
• bitmap_column - BitMap64 data column.

Return value

• BitMap64 array - the bitmap after each expression is calculated. The array size is equal to the number of expressions.

Example

SELECT BitmapMultiExtractWithDate('20240102_a~20240101_a', '20240101_a&20240102_a')(toInt64(toYYYYMMDD(date)), tag_value, uids) AS res 
FROM 
( 
    SELECT 
        'a' AS tag_value, 
        '2024-01-01' AS date, 
        arrayToBitmap([1, 2, 3]) AS uids 
    UNION ALL 
    SELECT 
        'a' AS tag_value, 
        '2024-01-02' AS date, 
        arrayToBitmap([2, 3, 4, 5, 6]) AS uids 
)

┌─res─────────────┐ 
│ [{4,5,6},{2,3}] │ 
└─────────────────┘

SELECT BitmapMultiExtractWithDate('20240101_1 & 20240102_2', '_1 | 20240101_3', '_2 ~ 20240101_4')(toInt64(toYYYYMMDD(date)), tag_value, uids) AS res 
FROM 
( 
    SELECT 
        toInt32(1) AS tag_value, 
        '2024-01-01' AS date, 
        arrayToBitmap([1, 2, 3, 4, 5]) AS uids 
    UNION ALL 
    SELECT 
        toInt32(2) AS tag_value, 
        '2024-01-02' AS date, 
        arrayToBitmap([3, 4, 5, 6]) AS uids 
    UNION ALL 
    SELECT 
        toInt32(3) AS tag_value, 
        '2024-01-01' AS date, 
        arrayToBitmap([5, 7]) AS uids 
    UNION ALL 
    SELECT 
        toInt32(4) AS tag_value, 
        '2024-01-01' AS date, 
        arrayToBitmap([7]) AS uids 
)

As a result:

┌─res─────────────────────────┐ 
│ [{3,4,5},{3,4,5,7},{3,4,5}] │ 
└─────────────────────────────┘

BitEngine scenario encoding and decoding functions #

| | | | | |
|No. |\

If you previously used community BitMap functions and now wish to migrate to ByteHouse, you can refer to the following Rules to replace the community BitMap functions.

ByteHouse adapts to and supports all ClickHouse community bitmap functions. The mapping relationships are as follows:

| | | | \

Create test table #

Example uses table test.bitmap_test.

CREATE TABLE test.bitmap_test ( 
    `p_date` Date, 
    `slice_id` UInt64, 
    `tag_id` Int32, 
    `tag_value` String, 
    `uids` BitMap64 
) ENGINE = CnchMergeTree 
PARTITION BY p_date 
ORDER BY (tag_id, tag_value) 
SETTINGS index_granularity = 128;

Insert data #

Data involves two tags, tag_id=11, with two tag values 'a' and 'b'; tag_id=12, with one tag value 'c'.
Each tag value has two rows, with different slice_id values. slice_id is used to split large bitmaps, reducing the bitmap scale per row, which can optimize construction efficiency and memory usage, and meanwhile provide shard credentials for distributed storage.

insert into test.bitmap_test values ('2024-01-01', 0, 11, 'a', [1,2,3,4,5]), ('2024-01-02', 1, 11,'a', [11,12,13,14,15]); 
insert into test.bitmap_test values ('2024-01-01', 0, 11, 'b', [3,4,5,6,7,8]), ('2024-01-02', 1, 11,'b', [13, 14,15,16]); 
insert into test.bitmap_test values ('2024-01-01', 0, 12, 'c', [3,4,6,7,9]), ('2024-01-02', 1, 12,'c', [13,15,16,18]);

Example: Normal functions #

The use of normal functions, community demo, and other functions is not different. Here are a few examples.

Convert array to bitmap #

SELECT arrayToBitmap([1, 2, 3, 4, 5]) 
FORMAT TSV 

-- As a result: 
{1,2,3,4,5}

Convert bitmap to array #

SELECT bitmapToArray(uids) 
FROM test.bitmap_test 
WHERE tag_value = 'a' 
FORMAT TSV 

-- As a result: 
[11,12,13,14,15] 
[1,2,3,4,5] 


SELECT bitmapToArray(arrayToBitmap([1, 2, 3, 4, 5])) 
FORMAT TSV 

-- As a result: 
[1,2,3,4,5]

Count the number of elements in the bitmap #

SELECT bitmapCardinality(arrayToBitmap([1, 2, 3, 4, 5])) 
FORMAT TSV 

-- As a result: 
5

Example: Aggregate functions #

Intersection/union/xor operations for multiple bitmaps #

-- Calculate the intersection of two bitmaps 
SELECT bitmapColumnAnd(uids) 
FROM test.bitmap_test 
WHERE (slice_id = 0) AND (tag_value IN ('a', 'b')) 
FORMAT TSV 

-- As a result: 
{3,4,5} 

-- Calculate the union of two bitmaps (two functions are synonymous) 
SELECT 
    bitmapCardinality(bitmapColumnOr(uids)), 
    bitmapColumnCardinality(uids) 
FROM test.bitmap_test 
WHERE (slice_id = 0) AND (tag_value IN ('a', 'b')) 
FORMAT TSV 

-- As a result: 
8        8 

-- Judge whether a given element exists in a bitmap 
SELECT bitmapColumnHas(uids, 12) 
FROM test.bitmap_test 
WHERE tag_value = 'a' 
FORMAT TSV 

-- As a result: 
1

Aggregate a bitmap #

SELECT bitmapFromColumn(number) 
FROM 
( 
    SELECT number 
    FROM system.numbers 
    LIMIT 100, 10 
) 
FORMAT TSV 

-- As a result 
{100,101,102,103,104,105,106,107,108,109}

bitmapCount function #

purpose:​Carry on complex intersection, union, and difference operations between multiple bitmaps. User Pass input expression to represent bitmap operations, and the function parses and calculates accordingly. Example refers to the legend in the section [Aggregate functions] - [Advanced aggregate functions] above.
Input:​Index column, bitmap column. The index column is used to identify the bitmap, which can be a field in the table (such as tag_id), or a manually defined variable (such as idx).
Expression:​One index anchors one bitmap. The index expression is and, or, and difference operations, corresponding to intersection, union, and difference operations of bitmaps.
Supported index operators:

• Union operation: use comma ('1,2') or vertical bar ('1|2')
• Intersection operation: use & ('1&2')
• Difference operation: use ('12')

Output:​The number of elements in the resulting bitmap after intersection, union, and difference operations.

Case 1: Calculate the total number of elements for a certain tag 'a':

SELECT bitmapColumnCardinality(uids) 
FROM test.bitmap_test 
WHERE tag_value = 'a' 
FORMAT TSV 

-- As a result: 
10 

-- Calculate the total number of elements for tag 'a'. Equivalent to the calculation of bitmapColumnCardinality 
SELECT bitmapCount('1')(If(tag_value = 'a', toInt32(1), toInt32(999)) AS idx, uids) AS card 
FROM test.bitmap_test 
WHERE tag_value = 'a' 
FORMAT TSV 

-- As a result: 
10 

-- Calculate the total number of elements for tag 'a'. If the tag is a string, you can directly use a string expression 
SELECT bitmapCount('a')(tag_value, uids) 
FROM test.bitmap_test 
WHERE tag_value = 'a' 
FORMAT TSV 

-- As a result: 
10

Case 2: Calculate the intersection of two tags 'a' and 'b'

SELECT bitmapCardinality(bitmapColumnAnd(uids)) AS card 
FROM test.bitmap_test 
WHERE (tag_value IN ('a', 'b')) AND (slice_id = 0) 
FORMAT CSV 

-- As a result: 
10 

-- Equivalent to the calculation of bitmapCardinality(bitmapColumnAnd) 
SELECT bitmapCount('1&2')(multiIf(tag_value = 'a', toInt32(1), tag_value = 'b', 2, toInt32(999)) AS idx, uids) AS card 
FROM test.bitmap_test 
WHERE (tag_value IN ('a', 'b')) AND (slice_id = 0) 
FORMAT TSV 

-- As a result: 
3 

-- Another way to write the above SQL, suitable for complex SQL nesting 
SELECT bitmapCount('1&2')(idx, uids) 
FROM 
( 
    SELECT 
        toInt32(1) AS idx, 
        uids 
    FROM test.bitmap_test 
    WHERE (slice_id = 0) AND (tag_value IN ('a')) 
    UNION ALL 
    SELECT 
        toInt32(2) AS idx, 
        uids 
    FROM test.bitmap_test 
    WHERE (slice_id = 0) AND (tag_value IN ('b')) 
) 
FORMAT TSV 

-- As a result: 
3

Case 3: More Tag intersection, union, and difference operations

-- Method one 
SELECT bitmapCount('1&2|3')(multiIf(tag_value = 'a', toInt32(1), tag_value = 'b', toInt32(2), tag_value = 'c', toInt32(3), toInt32(999)) AS idx, uids) AS card 
FROM test.bitmap_test 
FORMAT TSV 

-- As a result: 
11 

-- Method two: Since tag_value is of String Type, you can use a String expression 
SELECT bitmapCount('a&b|c')(tag_value, uids) 
FROM test.bitmap_test 
FORMAT TSV 

-- As a result: 
11

bitmapExtract #

This function calculates in the same way as bitmapCount, except it Back the Result bitmap, not the count.
As for how to Combine Tags, it is equivalent to the bitmapCount function. bitmapCount uses tag_value as an Example, here is a case using tag_id as an Example: finding the intersection between two Tags:

SELECT bitmapExtract('1&2')(multiIf(tag_id = 11, toInt32(1), tag_id = 12, 2, toInt32(999)) AS idx, uids) AS card 
FROM test.bitmap_test 
WHERE (tag_id IN (1,2)) AND (slice_id = 0) 
FORMAT TSV 

-- As a result (no intersection): 
{} 

Another way to write: 
SELECT bitmapExtract('11&12')(tag_id, uids) 
FROM test.bitmap_test 
WHERE (tag_id IN (1, 2)) AND (slice_id = 0) 
FORMAT TSV 

-- As a result (no intersection): 
{}

bitmapMultiCount function #

This Aggregate function is based on bitmapCount, Support project for multiple groups of Expressions.
purpose:​To realize complex intersection, union, and difference operations between multiple bitmaps, meanwhile supporting multiple groups of operations and operation reuse. The User expresses bitmap operations by Inputting Expressions, and the function performs the corresponding calculation after Analysis. For Examples, refer to the Legend in the section 【Aggregate function】- 【Advanced Aggregate function】above.
Input:​Number Column, bitmap Column. The Number Column is used to identify the bitmap, which can be a Field in the table (such as tag_id) or a manually defined variable (such as idx).
Expression:​One Number anchors one bitmap. The Number Expression involves and, or, and Poor Actions, corresponding to intersection, union, and difference operations of the bitmap. Meanwhile, you can provide multiple groups of Expressions, and the function will Run the corresponding calculation in sequence and return all results together.
Supported Number Operators:

• Union operation: use a comma ('1,2') or vertical bar ('1|2')
• Intersection operation: use & ('1&2')
• Difference operation: use ('12')
• Expression reuse: reuse the result of a certain Expression by underline plus Location number. For example, "_3" means reusing the third Expression (counting from one)

Output:​The number of Elements in the Result bitmap after intersection, union, and difference operations. Each Expression corresponds to one Result, stored in an array.

-- '_3|3' is equivalent to '(1&2)|3' 
SELECT bitmapMultiCount('1', '2', '1&2', '_3|3')(multiIf(tag_value = 'a', toInt32(1), tag_value = 'b', toInt32(2), tag_value = 'c', toInt32(3), toInt32(999)) AS idx, uids) AS card 
FROM test.bitmap_test 
WHERE slice_id = 0 
FORMAT TSV 

-- As a result: 
[5,6,3,6] 

-- tag_value happens to be of String Type, so you can also use a String expression 
SELECT bitmapMultiCount('a', 'b', 'a&b', '_3|c')(tag_value, uids) AS card 
FROM test.bitmap_test 
WHERE slice_id = 0 
FORMAT TSV 

-- As a result: 
[5,6,3,6]

_3 means reusing the Expression at Location 3, that is, '1&2'.
There is a set of set relationships that can be used to verify correctness. This relationship is
A + B = AUB + A Poor B

SELECT bitmapMultiCount('1', '2', '1&2', '1|2')(multiIf(tag_value = 'a', toInt32(1), tag_value = 'b', toInt32(2), tag_value = 'c', toInt32(3), toInt32(999)) AS idx, uids) AS card 
FROM test.bitmap_test 
WHERE slice_id = 0 
FORMAT TSV 

-- As a result: 
[5,6,3,8] 

-- tag_value happens to be of String Type, so a string expression can also be used 
SELECT bitmapMultiCount('a', 'b', 'a&b', 'a|b')(tag_value, uids) 
FROM test.bitmap_test 
WHERE slice_id = 0 
FORMAT TSV 

-- As a result: 
[5,6,3,8]

bitmapMultiExtract #

The meaning for the User is the same as bitmapMultiCount. The difference is in the Return value, which is Back as bitmap Data.

SELECT bitmapMultiExtract('1', '2', '1&2', '1|2')(multiIf(tag_value = 'a', toInt32(1), tag_value = 'b', toInt32(2), tag_value = 'c', toInt32(3), toInt32(999)) AS idx, uids) AS card 
FROM test.bitmap_test 
WHERE slice_id = 0 
FORMAT TSV 

[{1,2,3,4,5},{3,4,5,6,7,8},{3,4,5},{1,2,3,4,5,6,7,8}] 

-- tag_value happens to be of String Type, so a string expression can also be used 
SELECT bitmapMultiExtract('a', 'b', 'a&b', 'a|b')(tag_value, uids) 
FROM test.bitmap_test 
WHERE slice_id = 0 
FORMAT TSV 

-- As a result: 
[{1,2,3,4,5},{3,4,5,6,7,8},{3,4,5},{1,2,3,4,5,6,7,8}]

bitmapMultiCountWithDate #

purpose:​This function adds a Date Dimension on the basis of bitmapMultiCount, allowing Tag differentiation by Date in the Expression.
Caution: The Date Column needs to be of Int64 Type. Typically, Date uses the Date Type and needs to be converted using the toYYYYMMDD function.
Input:​Date Column, identifier Column, bitmap Column.
Expression:​A combination of a Date and an identifier anchors a bitmap and cannot be used alone. Therefore, Date_identifier must be used as a Whole, as in the Example '20240101_1', which identifies a bitmap. The above function bitmapMultiCount only requires '1', which is the difference between the two.
Output:​The Number of Elements in the result bitmap After bitmap intersection, union, and Poor operations. Each Expression corresponds to a result, stored in an array.

SELECT bitmapMultiCountWithDate('20240101_1', '20240102_2', '20240101_1|20240102_2')(CAST(toYYYYMMDD(p_date), 'Int64') AS date1, multiIf((tag_value = 'a') AND (p_date = '2024-01-01'), toInt64(1), (tag_value = 'b') AND (p_date = '2024-01-02'), toInt64(2), (tag_value = 'c') and (p_date = '2024-01-01'), toInt64(3),toInt32(999)) AS idx, uids) AS card 
FROM test.bitmap_test 
FORMAT TSV 

-- As a result: 
[5,4,9] 

-- tag_value happens to be of String Type, so a string expression can be used 
SELECT bitmapMultiCountWithDate('20240101_a', '20240102_b', '20240101_a|20240102_b')(CAST(toYYYYMMDD(p_date), 'Int64') AS date1, tag_value, uids) AS card 
FROM test.bitmap_test 
FORMAT TSV 

-- As a result: 
[5,4,9]

bitmapMultiExtractWithDate #

Function and usage are the same as bitmapMultiCountWithDate. The difference is in the Return value, which is Back as bitmap Data.

SELECT bitmapMultiExtractWithDate('20240101_1', '20240102_2', '20240101_1|20240102_2')(CAST(toYYYYMMDD(p_date), 'Int64') AS date1, multiIf((tag_value = 'a') AND (p_date = '2024-01-01'), toInt64(1), (tag_value = 'b') AND (p_date = '2024-01-02'), toInt64(2), (tag_value = 'c') and (p_date = '2024-01-01'), toInt64(3),toInt32(999)) AS idx, uids) AS card 
FROM test.bitmap_test 
FORMAT TSV 

-- As a result: 
[{1,2,3,4,5},{13,14,15,16},{1,2,3,4,5,13,14,15,16}] 


-- tag_value happens to be of String Type, so a string expression can be used 
SELECT bitmapMultiExtractWithDate('20240101_a', '20240102_b', '20240101_a|20240102_b')(CAST(toYYYYMMDD(p_date), 'Int64') AS date1, tag_value, uids) AS card 
FROM test.bitmap_test 
FORMAT TSV 

-- As a result: 
[{1,2,3,4,5},{13,14,15,16},{1,2,3,4,5,13,14,15,16}]