clickhouse bitmap入门

从数组构造bitmap

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

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

bitmap转array

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

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

取bitmap的一部分

SELECT bitmapToArray(bitmapSubsetInRange(bitmapBuild([10, 11, 28, 30, 31, 32, 33, 100, 200, 500]), toUInt32(30), toUInt32(200))) AS res

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

判断是否包含

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

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

返回基数

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

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

返回最小值

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

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

求交集

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

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

求并集

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

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

参考

Bitmap | ClickHouse Documentation
https://clickhouse.tech/docs/en/sql-reference/functions/bitmap-functions/