Bit & Bitmap 函数
请注意:
下文中的一些示例引用自 ClickHouse 社区文档 并经过一定修改确保可以在 ByteHouse 中正常使用。
bitTest
Takes any integer and converts it into [binary form].
Syntax
SELECT bitTest(number, index)
Arguments
-
number
– Integer number. -
index
– Position of bit.
Returned values
Returns a value of bit at specified position.
Type: UInt8
.
Example
For example, the number 43 in base-2 (binary) numeral system is 101011.
Query:
SELECT bitTest(43, 1);
Result:
┌─bitTest(43, 1)─┐
│ 1 │
└────────────────┘
Another example:
Query:
SELECT bitTest(43, 2);
Result:
┌─bitTest(43, 2)─┐
│ 0 │
└────────────────┘
bitTestAll
Returns result of [logical conjuction] (AND operator) of all bits at given positions. The countdown starts from 0 from the right to the left.
The conjuction for bitwise operations:
0 AND 0 = 0
0 AND 1 = 0
1 AND 0 = 0
1 AND 1 = 1
Syntax
SELECT bitTestAll(number, index1, index2, index3, index4, ...)
Arguments
-
number
– Integer number. -
index1
,index2
,index3
,index4
– Positions of bit. For example, for set of positions (index1
,index2
,index3
,index4
) is true if and only if all of its positions are true (index1
⋀index2
, ⋀index3
⋀index4
).
Returned values
Returns result of logical conjuction.
Type: UInt8
.
Example
For example, the number 43 in base-2 (binary) numeral system is 101011.
Query:
SELECT bitTestAll(43, 0, 1, 3, 5);
Result:
┌─bitTestAll(43, 0, 1, 3, 5)─┐
│ 1 │
└────────────────────────────┘
Another example:
Query:
SELECT bitTestAll(43, 0, 1, 3, 5, 2);
Result:
┌─bitTestAll(43, 0, 1, 3, 5, 2)─┐
│ 0 │
└───────────────────────────────┘
bitTestAny
Returns result of [logical disjunction](OR operator) of all bits at given positions. The countdown starts from 0 from the right to the left.
The disjunction for bitwise operations:
0 OR 0 = 0
0 OR 1 = 1
1 OR 0 = 1
1 OR 1 = 1
Syntax
SELECT bitTestAny(number, index1, index2, index3, index4, ...)
Arguments
-
number
– Integer number. -
index1
,index2
,index3
,index4
– Positions of bit.
Returned values
Returns result of logical disjuction.
Type: UInt8
.
Example
For example, the number 43 in base-2 (binary) numeral system is 101011.
Query:
SELECT bitTestAny(43, 0, 2);
Result:
┌─bitTestAny(43, 0, 2)─┐
│ 1 │
└──────────────────────┘
Another example:
Query:
SELECT bitTestAny(43, 4, 2);
Result:
┌─bitTestAny(43, 4, 2)─┐
│ 0 │
└──────────────────────┘
bitmapAnd
Two bitmap and calculation, the result is a new bitmap.
Syntax
bitmapAnd(bitmap,bitmap)
Arguments
bitmap
– Bitmap object.
Returned value
- The Bitmap object
Example
SELECT bitmapToArray(bitmapAnd(bitmapBuild([1,2,3]),bitmapBuild([3,4,5]))) AS res;
Result:
┌─res─┐
│ [3] │
└─────┘
bitmapAndCardinality
Two bitmap and calculation, return cardinality of type UInt64.
Syntax
bitmapAndCardinality(bitmap,bitmap)
Arguments
bitmap
– Bitmap object.
Returned value
- The cardinality in type
UInt64
.
Type:Uint64
Example
SELECT bitmapAndCardinality(bitmapBuild([1,2,3]),bitmapBuild([3,4,5])) AS res;
Result:
┌─res─┐
│ 1 │
└─────┘
bitmapAndnot
Two bitmap andnot calculation, the result is a new bitmap.
Syntax
bitmapAndnot(bitmap,bitmap)
Arguments
bitmap
– Bitmap object.
Returned value
- The Bitmap object
Type: Bitmap object
.
Example
SELECT bitmapToArray(bitmapAndnot(bitmapBuild([1,2,3]),bitmapBuild([3,4,5]))) AS res;
Result:
┌─res────┐
│ [1, 2] │
└────────┘
bitmapAndnotCardinality
Two bitmap andnot calculation, return cardinality of type UInt64.
Syntax
bitmapAndnotCardinality(bitmap,bitmap)
Arguments
bitmap
– Bitmap object.
Returned value
- The cardinality in
UInt64
.
Type: UInt64
Example
SELECT bitmapAndnotCardinality(bitmapBuild([1,2,3]),bitmapBuild([3,4,5])) AS res;
Result:
┌─res─┐
│ 2 │
└─────┘
bitmapBuild
Build a bitmap from unsigned integer array.
Syntax
bitmapBuild(array)
Arguments
array
– Unsigned integer array.
Returned value
- The bitmap object
Type: Bitmap object
.
Example
SELECT toTypeName(bitmapBuild([1, 2, 3, 4, 5]));
Result:
┌─toTypeName(bitmapBuild([1, 2, 3, 4, 5]))─┐
│ AggregateFunction(groupBitmap, UInt8) │
└──────────────────────────────────────────┘
bitmapCardinality
Return bitmap cardinality of type UInt64.
Syntax
bitmapCardinality(bitmap)
Arguments
bitmap
– Bitmap object.
Returned value
- The bitmap cardinality in type
UInt64
.
Type: UInt64
.
Example
SELECT bitmapCardinality(bitmapBuild([1, 2, 3, 4, 5])) AS res;
Result:
┌─res─┐
│ 5 │
└─────┘
bitmapContains
Checks whether the bitmap contains an element.
Syntax
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;
Result:
┌─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.
Syntax
bitmapHasAll(bitmap,bitmap)
Arguments
bitmap
– Bitmap object.
Returned value
1
, returns 1 if the first bitmap contains all the elements of the second one or If the second argument is an empty bitmap .0
, otherwise.
Type: UInt8
Example
SELECT bitmapHasAll(bitmapBuild([1,2,3]),bitmapBuild([3,4,5])) AS res;
Result:
┌─res─┐
│ 0 │
└─────┘
bitmapHasAny
Checks whether two bitmaps have intersection by some elements.
Syntax
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;
Result:
┌─res─┐
│ 1 │
└─────┘
bitmapMax
Return the greatest value of type UInt64 in the set, 0 if the set is empty.
Syntax
bitmapMax(bitmap)
Arguments
bitmap
– Bitmap object.
Return values
- Return the greatest value of type UInt64 in the set.
Type: UInt64
Example
SELECT bitmapMax(bitmapBuild([1, 2, 3, 4, 5])) AS res;
Result:
┌─res─┐
│ 5 │
└─────┘
bitmapMin
Return the smallest value of type UInt64 in the set, UINT32_MAX if the set is empty.
Syntax
bitmapMin(bitmap)
Arguments
bitmap
– Bitmap object.
Return values
- Return the smallest value of type UInt64 in the set.
Type: UInt64
Example
SELECT bitmapMin(bitmapBuild([1, 2, 3, 4, 5])) AS res;
Result:
┌─res─┐
│ 1 │
└─────┘
bitmapOr
Two bitmap or calculation, the result is a new bitmap.
bitmapOr(bitmap,bitmap)
Arguments
bitmap
– Bitmap object.
Returned value
- The Bitmap object
Type: Bitmap object
.
Example
SELECT bitmapToArray(bitmapOr(bitmapBuild([1,2,3]),bitmapBuild([3,4,5]))) AS res;
Result:
┌─res─────────────┐
│ [1, 2, 3, 4, 5] │
└─────────────────┘
bitmapOrCardinality
Two bitmap or calculation, return cardinality of type UInt64.
Syntax
bitmapOrCardinality(bitmap,bitmap)
Arguments
bitmap
– Bitmap object.
Return values
- Return or calculation result in cardinality.
Type:UInt64
Example
SELECT bitmapOrCardinality(bitmapBuild([1,2,3]),bitmapBuild([3,4,5])) AS res;
Result:
┌─res─┐
│ 5 │
└─────┘
bitmapSubsetInRange
Return subset in specified range (not include the range_end).
Syntax
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].
Return values
- Return or calculation result.
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;
Result:
┌─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] │
└─────────────────────────────────┘
bitmapToArray
Convert bitmap to integer array.
Syntax
bitmapToArray(bitmap)
Arguments
bitmap
– Bitmap object.
Returned value
- An array.
Type: array
.
Example
SELECT bitmapToArray(bitmapBuild([1, 2, 3, 4, 5])) AS res;
Result:
┌─res─────────────┐
│ [1, 2, 3, 4, 5] │
└─────────────────┘
bitmapXor
Two bitmap xor calculation, the result is a new bitmap.
bitmapXor(bitmap,bitmap)
Arguments
bitmap
– Bitmap object.
Returned value
- The Bitmap object
Type: Bitmap object
.
Example
SELECT bitmapToArray(bitmapXor(bitmapBuild([1,2,3]),bitmapBuild([3,4,5]))) AS res;
Result:
┌─res──────────┐
│ [1, 2, 4, 5] │
└──────────────┘
bitmapXorCardinality
Two bitmap xor calculation, return cardinality of type UInt64.
Syntax
bitmapXorCardinality(bitmap,bitmap)
Arguments
-
bitmap
– Bitmap object. -
Returned value
-
The Bitmap object
Type: Bitmap object
.
Example
SELECT bitmapXorCardinality(bitmapBuild([1,2,3]),bitmapBuild([3,4,5])) AS res;
Result:
┌─res─┐
│ 4 │
└─────┘
Updated over 2 years ago