ck的bitmap函数

静听山水

已于 2023-02-26 23:36:37 修改

阅读量356

点赞数 1

分类专栏： Clickhouse 文章标签： sql clickhouse Powered by 金山文档

于 2023-02-06 19:39:56 首次发布

本文链接：https://blog.csdn.net/qq_41081716/article/details/128907159

版权

Clickhouse 专栏收录该内容

34 篇文章 0 订阅

订阅专栏

参考文章：位图函数

利用下面这段代码可以查出所有关于bitmap的函数

select * from system.functions
where name like '%bitmap%'

按照日常使用习惯，归类如下：

1、bitmapMax、bitmapMin

## bitmapMax
select bitmapMax(bitmapBuild([1, 2, 3, 4, 5])) as res  --5  最大的为5

## bitmapMin
select bitmapMin(bitmapBuild([1, 2, 3, 4, 5])) as res  --1  最小的为5
select bitmapMin(bitmapBuild([-10, 2, 3, 4, 5])) as res  --2 剔除负数，最小的2
select bitmapMin(bitmapBuild([-1, 0, 3, 4, 5])) as res --0 剔除负数，最小的0

2、bitmapToArray

select  bitmapToArray(bitmapBuild([1, 2, 3, 4, 5])) as res  ----[1,2,3,4,5]

3、bitmapCardinality

select  bitmapCardinality(bitmapBuild([1, 2, 3, 4, 5])) as res  --5  5个元素
select  bitmapCardinality(bitmapBuild([0])) as res  --1  1个元素
select  bitmapCardinality(bitmapBuild([-10])) as res  --1  1个元素

4、bitmapOr、bitmapXor、bitmapAnd、bitmapAndnot

## bitmapOr
select bitmapToArray(bitmapOr(bitmapBuild([1,2,3]),bitmapBuild([3,4,5]))) 
--[1,2,3,4,5]

## bitmapXor
select bitmapToArray(bitmapXor(bitmapBuild([1,2,3]),bitmapBuild([3,4,5])))  
--[1,2,4,5]

## bitmapAnd
select bitmapToArray(bitmapAndnot(bitmapBuild([1,2,3]),bitmapBuild([3,4,5])))  --[1,2]

## bitmapAndnot
select bitmapToArray(bitmapAndnot(bitmapBuild([1,2,3]),bitmapBuild([3,4,5])))
--[1,2]

5、bitmapContains、bitmapHasAll、bitmapHasAny

## bitmapContains
select  bitmapContains(bitmapBuild([1,5,7,9]), toUInt32(9))  --1
select  bitmapContains(bitmapBuild([1,5,7,9]), toUInt32(2))  --0

## bitmapHasAll
select bitmapHasAll(bitmapBuild([1,2,3]),bitmapBuild([3,4,5])) as res  --0
select bitmapHasAll(bitmapBuild([1,2,3]),bitmapBuild([3])) as res  --1

## bitmapHasAny
select bitmapHasAny(bitmapBuild([1,2,3]),bitmapBuild([3,4,5])) as res  --1

举例：
select bitmapContains(bitmapBuild([1,5,7,9]), toUInt32(9)) as res;  --1

select bitmapHasAny(bitmapBuild([1,2,3]),bitmapBuild([3,4,5])) as res;  --1

select bitmapHasAll(bitmapBuild([1,2,3]),bitmapBuild([3,2])) as res;  --1

6、bitmapOrCardinality、bitmapXorCardinality、bitmapAndCardinality、bitmapAndnotCardinality

## bitmapOrCardinality
select bitmapOrCardinality(bitmapBuild([1,2,3]),bitmapBuild([3,4,5])) as res;  --5 5个元素

## bitmapXorCardinality
select bitmapXorCardinality(bitmapBuild([1,2,3]),bitmapBuild([3,4,5])) --4;  4个元素
select bitmapXorCardinality(bitmapBuild([1,2,3]),bitmapBuild([3,4,5,8]))  --5 5个元素

## bitmapAndCardinality
select  bitmapAndCardinality(bitmapBuild([1,2,3]),bitmapBuild([3,4,5])) as res;  --1 1个元素
select  bitmapAndCardinality(bitmapBuild([1,2,3]),bitmapBuild([4,5])) as res;  --0 0个元素

## bitmapAndnotCardinality
select bitmapAndnotCardinality(bitmapBuild([0,1,3,6]),bitmapBuild([3,4,5])); --3  3个元素
select bitmapAndnotCardinality(bitmapBuild([0,1,3]),bitmapBuild([3,4,5]));  --2  2个元素

7、bitmapSubsetInRange

select bitmapToArray(bitmapSubsetInRange(bitmapBuild([26,27,28,29,30,31,100,200,500]), toUInt32(30), toUInt32(200)))   -- [30,31,100]  左开又闭，200不取