clickhouse聚合函数之groupBitmap

用途

官方点就是计算基数,直白点就是统计不重复的个数

参数为Uint类型,就是不是负数的整数 返回值为Uint64类型

案例

测试数据

UserID
1
1
2
3

SELECT groupBitmap(UserID) as num FROM t;

结果

num
3

再举个例子,比如我们有用户id uid, string类型,相要统计其个数,则

SELECT groupBitmap(toUInt64(uid)) as num from usertable;

+----+
|num |
+----+
|2399|
+----+

等价于如下查询

select count(distinct uid) from usertable;

那么要bitmap做什么呢?就是创建表时如果选择bitmap存储,就会比较节约空间

比如

create table testbit(
    label String,
    name String,
    uv AggregateFunction(groupBitmap,UInt64) comment bitmap存储用户
)engine=AggregatingMergeTree()
partition by label
order by (label,name);

groupBitmapState

可以看出返回值类型是AggregateFunction(groupBitmap,UInt64)

SELECT groupBitmapState(toUInt64(uid)) as num,
       toTypeName(num)
from usertable;

想要知道bitmap存储的是什么东西,直接查是看不出来的,需要转为数组才可以,比如

select bitmapToArray(groupBitmapState(toUInt64(uid))) from usertable;

如果想求基数,如下

select bitmapCardinality(groupBitmapState(toUInt64(uid))) from usertable;

+--------------------------------------------------+
|bitmapCardinality(groupBitmapState(toUInt64(uid)))|
+--------------------------------------------------+
|2399                                              |
+--------------------------------------------------+

参考