Bitmap Functions 

Bitmap functions work for two bitmaps Object value calculation, it is to return new bitmap or cardinality while using formula calculation, such as and, or, xor, and not, etc.

There are 2 kinds of construction methods for Bitmap Object. One is to be constructed by aggregation function groupBitmap with -State, the other is to be constructed by Array Object. It is also to convert Bitmap Object to Array Object.

RoaringBitmap is wrapped into a data structure while actual storage of Bitmap objects. When the cardinality is less than or equal to 32, it uses Set objet. When the cardinality is greater than 32, it uses RoaringBitmap object. That is why storage of low cardinality set is faster.

For more information on RoaringBitmap, see: CRoaring.

bitmapBuild 

Build a bitmap from unsigned integer array.

bitmapBuild(array)

Arguments

  • array – Unsigned integer array.

Example

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

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

bitmapToArray 

Convert bitmap to integer array.

bitmapToArray(bitmap)

Arguments

  • bitmap – Bitmap object.

Example

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

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

bitmapSubsetInRange 

Return subset in specified range (not include the range_end).

bitmapSubsetInRange(bitmap, range_start, range_end)

Arguments

  • bitmap – Bitmap object.
  • range_start – Range start point. Type: UInt32.
  • range_end – Range end point (excluded). Type: UInt32.

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;

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

bitmapSubsetLimit 

Creates a subset of bitmap with n elements taken between range_start and cardinality_limit.

Syntax

bitmapSubsetLimit(bitmap, range_start, cardinality_limit)

Arguments

  • bitmap – Bitmap object.
  • range_start – The subset starting point. Type: UInt32.
  • cardinality_limit – The subset cardinality upper limit. Type: UInt32.

Returned value

The subset.

Type: Bitmap object.

Example

Query:

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;

Result:

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

subBitmap 

Returns the bitmap elements, starting from the offset position. The number of returned elements is limited by the cardinality_limit parameter. Analog of the substring) string function, but for bitmap.

Syntax

subBitmap(bitmap, offset, cardinality_limit)

Arguments

  • bitmap – The bitmap. Type: Bitmap object.
  • offset – The position of the first element of the subset. Type: UInt32.
  • cardinality_limit – The maximum number of elements in the subset. Type: UInt32.

Returned value

The subset.

Type: Bitmap object.

Example

Query:

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;

Result:

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

bitmapContains 

Checks whether the bitmap contains an element.

bitmapContains(haystack, needle)

Arguments

  • haystack – Bitmap object, where the function searches.
  • needle – Value that the function searches. Type: UInt32.

Returned values

  • 0 — If haystack does not contain needle.
  • 1 — If haystack contains needle.

Type: UInt8.

Example

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

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

bitmapHasAny 

Checks whether two bitmaps have intersection by some elements.

bitmapHasAny(bitmap1, bitmap2)

If you are sure that bitmap2 contains strictly one element, consider using the bitmapContains function. It works more efficiently.

Arguments

  • bitmap* – Bitmap object.

Return values

  • 1, if bitmap1 and bitmap2 have one similar element at least.
  • 0, otherwise.

Example

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

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

bitmapHasAll 

Analogous to hasAll(array, array) returns 1 if the first bitmap contains all the elements of the second one, 0 otherwise.
If the second argument is an empty bitmap then returns 1.

bitmapHasAll(bitmap,bitmap)

Arguments

  • bitmap – Bitmap object.

Example

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

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

bitmapCardinality 

Retrun bitmap cardinality of type UInt64.

bitmapCardinality(bitmap)

Arguments

  • bitmap – Bitmap object.

Example

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

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

bitmapMin 

Retrun the smallest value of type UInt64 in the set, UINT32_MAX if the set is empty.

bitmapMin(bitmap)

Arguments

  • bitmap – Bitmap object.

Example

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

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

bitmapMax 

Retrun the greatest value of type UInt64 in the set, 0 if the set is empty.

bitmapMax(bitmap)

Arguments

  • bitmap – Bitmap object.

Example

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

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

bitmapTransform 

Transform an array of values in a bitmap to another array of values, the result is a new bitmap.

bitmapTransform(bitmap, from_array, to_array)

Arguments

  • bitmap – Bitmap object.
  • from_array – UInt32 array. For idx in range [0, from_array.size()), if bitmap contains from_array[idx], then replace it with to_array[idx]. Note that the result depends on array ordering if there are common elements between from_array and to_array.
  • to_array – UInt32 array, its size shall be the same to from_array.

Example

SELECT bitmapToArray(bitmapTransform(bitmapBuild([1, 2, 3, 4, 5, 6, 7, 8, 9, 10]), cast([5,999,2] as Array(UInt32)), cast([2,888,20] as Array(UInt32)))) AS res;

 ┌─res───────────────────┐
 │ [1,3,4,6,7,8,9,10,20] │
 └───────────────────────┘

bitmapAnd 

Two bitmap and calculation, the result is a new bitmap.

bitmapAnd(bitmap,bitmap)

Arguments

  • bitmap – Bitmap object.

Example

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

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

bitmapOr 

Two bitmap or calculation, the result is a new bitmap.

bitmapOr(bitmap,bitmap)

Arguments

  • bitmap – Bitmap object.

Example

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

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

bitmapXor 

Two bitmap xor calculation, the result is a new bitmap.

bitmapXor(bitmap,bitmap)

Arguments

  • bitmap – Bitmap object.

Example

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

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

bitmapAndnot 

Two bitmap andnot calculation, the result is a new bitmap.

bitmapAndnot(bitmap,bitmap)

Arguments

  • bitmap – Bitmap object.

Example

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

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

bitmapAndCardinality 

Two bitmap and calculation, return cardinality of type UInt64.

bitmapAndCardinality(bitmap,bitmap)

Arguments

  • bitmap – Bitmap object.

Example

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

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

bitmapOrCardinality 

Two bitmap or calculation, return cardinality of type UInt64.

bitmapOrCardinality(bitmap,bitmap)

Arguments

  • bitmap – Bitmap object.

Example

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

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

bitmapXorCardinality 

Two bitmap xor calculation, return cardinality of type UInt64.

bitmapXorCardinality(bitmap,bitmap)

Arguments

  • bitmap – Bitmap object.

Example

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

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

bitmapAndnotCardinality 

Two bitmap andnot calculation, return cardinality of type UInt64.

bitmapAndnotCardinality(bitmap,bitmap)

Arguments

  • bitmap – Bitmap object.

Example

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

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

Rating: 4.8 - 13 votes

Was this content helpful?
★★★★★
©2016–2021 ClickHouse, Inc.
Built from 2bb586bed3