clickhouse 七（位图函数）

系统管理员 2021-05-10 11:18 1661阅读 0赞

![在这里插入图片描述][20201201113506267.png]  
先了解一下什么是bitmap（位图）：

> 位图，就是位的集合，是一种数据结构，用bit位来存储数据的某种状态。在java中，一个int整型需要占据4个字节也就是32 bit位，这在运算时很方便。但如果在bitmap中，我们可以用对应的32 bit位对应存储32个十进制数，是最佳的数据压缩储存方式之一。

clickhosue对位图函数的讲解：

> 位图函数用于对两个位图对象进行计算，对于任何一个位图函数，它都将返回一个位图对象，例如and，or，xor，not等，可理解为与或非操作来实现的交集并集计算。

> 位图对象有两种构造方法。一个是由聚合函数groupBitmapState构造的，另一个是由Array Object构造的。同时还可以将位图对象转化为数组对象。我们使用RoaringBitmap实际存储位图对象，当基数小于或等于32时，clickhouse使用Set保存。当基数大于32时，clickhouse使用RoaringBitmap保存。这也是为什么低基数集的存储更快的原因。

## 1 位图的构建与转化 ##

*  **bitmapBuild(array)** → 将无符号整数数组构建位图对象。
 *  **bitmapToArray(bitmap)** → 将位图转换为整数数组。
 *  **bitmapSubsetInRange(bitmap, range\_start, range\_end)** → 将位图指定范围（不包含range\_end）转换为另一个位图。bitmap – 位图对象，range\_start – 范围起始点（含），range\_end – 范围结束点（不含）。
 *  **bitmapSubsetLimit(bitmap, range\_start, limit)** → 将位图指定范围（起始点和数目上限）转换为另一个位图。bitmap – 位图对象，range\_start – 范围起始点（含），limit – 子位图基数上限

**测试：**

WITH [toUInt32(1), toUInt32(2), toUInt32(5), toUInt32(7), toUInt32(35)] AS arr
    SELECT 
        bitmapBuild(arr) AS bitmapBuild,
        bitmapToArray(bitmapBuild(arr)) AS bitmapToArray,
        bitmapToArray(bitmapSubsetInRange(bitmapBuild(arr), toUInt32(2), toUInt32(7))) AS bitmapSubsetInRange,
        bitmapToArray(bitmapSubsetLimit(bitmapBuild(arr), toUInt32(2), toUInt32(7))) AS bitmapSubsetLimit
    
    ┌─bitmapBuild─┬─bitmapToArray─┬─bitmapSubsetInRange─┬─bitmapSubsetLimit─┐
    │ # │ [1,2,5,7,35] │ [2,5] │ [2,5,7,35] │
    └─────────────┴───────────────┴─────────────────────┴───────────────────┘

*  **arrayJoin** → 将数组转化为明细数据，行转列

**测试：**

SELECT arrayJoin(bitmapToArray(bitmapBuild([1, 2, 3, 4, 5]))) AS uid
    
    ┌─uid─┐
    │   1 │
    │   2 │
    │   3 │
    │   4 │
    │   5 │
    └─────┘

*  **groupBitmapState** → 聚合函数，可将id列值压缩bitmap  
    **测试：**

SELECT bitmapToArray(groupBitmapState(arrayJoin([1, 2, 3, 4, 5]))) AS groupBitmapState
    
    ┌─groupBitmapState─┐
    │ [1,2,3,4,5]      │
    └──────────────────┘

## 2 位图的计算操作 ##

*  **bitmapHasAny(bitmap1,bitmap2)** → 如果位图有任何公共元素则返回1，否则返回0。对于空位图，返回0。
 *  **bitmapHasAll(bitmap1,bitmap2)** → 如果第一个位图包含第二个位图的所有元素，则返回1，否则返回0。如果第二个参数是空位图，则返回1。

**测试：**

# 语法格式
    WITH 
        bitmapBuild([toUInt32(1), toUInt32(2), toUInt32(3), toUInt32(4), toUInt32(5)]) AS bitmap1,
        bitmapBuild([toUInt32(4), toUInt32(5), toUInt32(9), toUInt32(17), toUInt32(35)]) AS bitmap2
    SELECT 
        bitmapHasAny(bitmap1, bitmap2) AS bitmapHasAny,
        bitmapHasAll(bitmap1, bitmap2) AS bitmapHasAll
    
    ┌─bitmapHasAny─┬─bitmapHasAll─┐
    │            1 │            0 │
    └──────────────┴──────────────┘

*  **bitmapContains(bitmap, needle)** → 检查位图是否包含指定元素。
 *  **bitmapAnd(bitmap1,bitmap2)** → 两个位图对象进行与操作（相当于取交集），返回一个新的位图对象。
 *  **bitmapOr(bitmap1,bitmap2)** → 为两个位图对象进行或操作，返回一个新的位图对象,如果参数是多个的情况下，可以尝试使用groupBitmapMergeState。
 *  **bitmapXor(bitmap1,bitmap2)** → 为两个位图对象进行异或操作，返回一个新的位图对象。
 *  **bitmapAndnot(bitmap1,bitmap2)** → 计算两个位图的差异，返回一个新的位图对象。

**测试：**

WITH 
        bitmapBuild([toUInt32(1), toUInt32(2), toUInt32(3), toUInt32(4), toUInt32(5)]) AS bitmap1,
        bitmapBuild([toUInt32(4), toUInt32(5), toUInt32(9), toUInt32(17), toUInt32(35)]) AS bitmap2
    SELECT 
        bitmapContains(bitmap1, toUInt32(5)) AS bitmapContains,
        bitmapToArray(bitmapAnd(bitmap1, bitmap2)) AS bitmapAnd,
        bitmapToArray(bitmapOr(bitmap1, bitmap2)) AS bitmapOr,
        bitmapToArray(bitmapXor(bitmap1, bitmap2)) AS bitmapXor,
        bitmapToArray(bitmapAndnot(bitmap1, bitmap2)) AS bitmapAndnot
    
    ┌─bitmapContains─┬─bitmapAnd─┬─bitmapOr────────────┬─bitmapXor───────┬─bitmapAndnot─┐
    │              1 │ [4,5]     │ [1,2,3,4,5,9,17,35] │ [1,2,3,9,17,35] │ [1,2,3]      │
    └────────────────┴───────────┴─────────────────────┴─────────────────┴──────────────┘

*  **bitmapCardinality(bitmap)** → 返回一个UInt64类型的数值，表示位图的大小。
 *  **bitmapMin(bitmap)** → 返回一个UInt64类型的数值，表示位图中的最小值。如果位图为空则返回UINT32\_MAX。
 *  **bitmapMax(bitmap)** → 返回一个UInt64类型的数值，表示位图中的最大值。如果位图为空则返回0。

**测试：**

WITH bitmapBuild([toUInt32(4), toUInt32(5), toUInt32(9), toUInt32(17), toUInt32(35)]) AS bitmap
    SELECT 
        bitmapCardinality(bitmap) AS bitmapCardinality,
        bitmapMin(bitmap) AS bitmapMin,
        bitmapMax(bitmap) AS bitmapMax
    
    ┌─bitmapCardinality─┬─bitmapMin─┬─bitmapMax─┐
    │                 5 │         4 │        35 │
    └───────────────────┴───────────┴───────────┘

*  **bitmapOrCardinality(bitmap1,bitmap2)** → 为两个位图进行或运算，返回结果位图的基数。
 *  **bitmapXorCardinality(bitmap1,bitmap2)** → 为两个位图进行异或运算，返回结果位图的基数。
 *  **bitmapAndnotCardinality(bitmap1,bitmap2)** → 计算两个位图的差异，返回结果位图的基数。
 *  **bitmapAndCardinality(bitmap1,bitmap2)** → 计算两个位图的与运算，返回结果位图的基数。

**测试：**

WITH 
        bitmapBuild([toUInt32(1), toUInt32(2), toUInt32(3), toUInt32(4), toUInt32(5)]) AS bitmap1,
        bitmapBuild([toUInt32(4), toUInt32(5), toUInt32(9), toUInt32(17), toUInt32(35)]) AS bitmap2
    SELECT 
        bitmapOrCardinality(bitmap1, bitmap2) AS bitmapOrCardinality,
        bitmapXorCardinality(bitmap1, bitmap2) AS bitmapXorCardinality,
        bitmapAndnotCardinality(bitmap1, bitmap2) AS bitmapAndnotCardinality,
        bitmapAndCardinality(bitmap1, bitmap2) AS bitmapAndCardinality
    
    ┌─bitmapOrCardinality─┬─bitmapXorCardinality─┬─bitmapAndnotCardinality─┬─bitmapAndCardinality─┐
    │                   8 │                    6 │                       3 │                    2 │
    └─────────────────────┴──────────────────────┴─────────────────────────┴──────────────────────┘

## 3 总结 ##

Roaringbitmap可以对数据进行压缩储存以及利用位运算提高查询性能，所以RoaringBitmap常用于处理大量数据的排序、查询以及去重。举例，在用户画像中，利用bitmap储存用户Id的人群包，同时利用位运算的与或非操作来计算多个人群包的交集并集，从而得到新人群。

参考文章：[clickhouse官方文档][clickhouse]

[20201201113506267.png]: /images/20210505/9bcc5700f8944a97af95151862ba34fb.png
[clickhouse]: https://clickhouse.tech/docs/zh/sql-reference/functions/bitmap-functions/