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 containneedle
. - 1 β If
haystack
containsneedle
.
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
, ifbitmap1
andbitmap2
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 β
βββββββ