做数据分析的人大概都经历过这种需求销售要一份当天的精确订单用户数字面意义上的一个不能多、一个不能少。你最开始会写uniqExact发现还能忍可一旦要算次日留存、连续 7 天活跃、不同渠道的人群重合需要的就不再是一个数字而是集合。我第一次尝试用arrayIntersect去维护几百万规模的用户 ID 数组时任务直接跑崩了。后来在 ClickHouse 里接触到bitmap系列函数才意识到位图这种数据结构在 OLAP 场景里能玩出这么多花样。这篇文章把我从会用到敢在生产环境用的过程完整梳理一遍覆盖常用函数、业务 SQL、分布式和物化视图的坑希望给正在用 ClickHouse 做留存、圈选和漏斗的同学一些实在参考。1. 精确去重为什么绕不开bitmap1.1 计数函数三兄弟的边界ClickHouse 给精确/近似去重提供了入口uniq、uniqExact、uniqCombined。uniq是基于 HyperLogLog 的近似算法误差一般在 1% 以内内存占用固定且很小适合大基数但可容忍误差的报表uniqExact把每个出现的值都存进内存去重结果精确但内存随基数线性增长几十亿行下很容易把内存吃掉uniqCombined是在基数的比较小的时候用精确集合基数大了再切到 HyperLogLog算是一种折中。多数人只把它们当返回一个数的函数用所以不会觉得有问题。但当业务问题变成求 A 集合和 B 集合的交集人数时这三个函数都无从下手。uniqExact虽然内部持有完整集合但这个集合既不能导出也不能跨查询复用更不能直接参与集合运算。你只能把用户 ID 落到临时表再用 JOIN 去求交集。两个千万级集合做一次 JOIN代价有多大跑过的人都知道。所以真正的问题不是如何精确计数而是如何把用户集合这个对象以可复用、可运算、低内存的方式长期保存。这正是 bitmap 存在的理由。1.2 ClickHouse bitmap 的真实形态ClickHouse 中的 bitmap 并不是一个独立的表引擎或者专门的字段类型它本质上是一段二进制的序列化数据由底层库 RoaringBitmap 生成。你可以把它理解成一个被打包好的集合对象普通表里可以存在String字段里也可以更规范地存在AggregateFunction(groupBitmap, UInt64)字段里。因为底层存储的是无符号整数集合所以元素只能是整数不能是 UUID、字符串、负数如果业务 ID 是雪花 ID 这类 64 位整数需要使用 Bitmap64 后端ClickHouse 会根据输入自动选择。这种集合可存储、可传输、可运算的特性让 bitmap 在分析场景里非常灵活。同一份活跃用户集合可以先算好第二天继续拿来做留存、圈选、漏斗不用每次重新扫描几亿行原始事件表。1.3 RoaringBitmap 的压缩原理如果直接用普通位图比如把user_id直接按下标铺成一个 0/1 数组在 ID 范围极大的场景下内存会爆炸。RoaringBitmap 的思路是分桶把整数的高 16 位作为桶号最多分成 65536 个桶低 16 位在各自桶内部处理。每个桶内部根据数据密度选择三种容器ArrayContainer桶内数据稀疏时直接用数组存低 16 位值节省空间BitmapContainer桶内数据超过 4096 个时切换成 65536 位固定长度的位图访问快RunContainer桶内出现大段连续整数时用 Run-Length 编码把1,2,3,4,...,10000压成起始值和长度。这种设计很像仓库管理书少的时候用小卡片登记书多了换成整面打孔板如果是连续一排的成套书就给一个区间标记。ClickHouse 直接受益于这种压缩一个千万级用户集合从原始Set存储到 bitmap内存通常能降一个数量级以上这也是它在 OLAP 里适配度高的核心原因。理解这点后你就能预判什么时候 bitmap 会失效——当 ID 极其稀疏且跳跃剧烈时容器会退化成大量 ArrayContainer压缩率下降后文会专门讲应对办法。2. 构造一个bitmap四种常见姿势和适用边界2.1 临时集合bitmapBuildbitmapBuild从数组构建一个临时 bitmap通常在 SQL 内部或测试场景使用。比如SELECT bitmapBuild([1, 2, 3, 4, 5]) AS b, bitmapCardinality(b) AS card;数组元素要求是非负整数型不能传重复值进去。它适合构造小型集合去验证函数逻辑我现在写 demo 都会用它快速捏两个集合做交集但在生产环境里很少从一个大数组手工构建 bitmap因为数组本身已经占内存了成本并不比去重低。如果你拿到的是一个已有集合想临时转成 bitmap 再参与运算也可以用bitmapBuild包住子查询结果。不过要注意这种写法本质上是在内存里先物化一份数组行数大到一定程度时并不划算建议优先考虑预聚合的方式。2.2 聚合构建groupBitmap生产环境使用最多的构建方式是groupBitmap它是一个聚合函数在GROUP BY的过程中直接把同一分组的整数收集成 bitmapSELECT day, bitmapCardinality(groupBitmap(user_id)) AS uv FROM events WHERE day 2024-06-01 AND day 2024-06-02 GROUP BY day;groupBitmap(user_id)返回的不是一个数字而是一个序列化后的 bitmap 对象本质上也是 String/聚合状态后面必须跟bitmapCardinality或者其他 bitmap 函数才能继续使用。如果你只求一个精确 UV负责任的写法是在事件明细表上直接做先 groupBitmap 再 cardinality它比uniqExact在精确度和内存之间平衡得更稳定尤其适合用户 ID 连续度好的场景。2.3 预聚合状态groupBitmapState 与 AggregateFunction 字段与groupBitmap相对的是groupBitmapState它专门用于生成聚合状态形态上是一段可以被 ClickHouse 直接存储和二次 merge 的二进制状态。建表时可以定义这样的字段CREATE TABLE daily_uv_state ( day Date, uid_state AggregateFunction(groupBitmap, UInt64) ) ENGINE AggregatingMergeTree ORDER BY day;插入用groupBitmapState查询用groupBitmapMerge把状态解出来INSERT INTO daily_uv_state SELECT day, groupBitmapState(user_id) FROM events GROUP BY day; SELECT day, bitmapCardinality(groupBitmapMerge(uid_state)) AS uv FROM daily_uv_state GROUP BY day;这种做法的核心价值是算一次反复用。每天只要构建一次当天的用户集合状态之后无论算留存、圈选还是做交叉都不需要再回原始事件表扫描数据。聚合状态还能被 ClickHouse 的分区物化视图自动维护生产环境通常就是这么起量的。2.4 构建前先做 ID 映射这是我最想强调的一点。RoaringBitmap 的压缩能力跟 ID 分布强相关当你把随机的雪花 ID 直接塞进 UInt64 的 bitmap 时虽然也能运行但高 16 位分桶会散得非常开每个桶里只有零星几个元素大量 ArrayContainer 会显著拉低压缩率和运算速度。最理想的输入是从 1 开始的连续自增整数。我的习惯是维护一张user_id - uid_int的映射维度表比如CREATE TABLE user_id_map ( user_id UInt64, uid_int UInt32 ) ENGINE MergeTree ORDER BY user_id;回填时用窗口函数row_number()或者业务侧自增序列给每个用户分配一个连续整数。之后所有 bitmap 的构建都基于uid_int而非原始user_id。这样集合在高位分桶时非常紧凑很多情况下会直接命中 RunContainer 或高密度 BitmapContainer整体内存和计算性能能拉开好几倍差距。这一步在数据刚进入数仓时就做最划算后面再回填会非常痛苦。3. 常用bitmap函数实战过一遍3.1 基数与最值三个最基础函数bitmapCardinality(b)返回集合中元素个数等价于精确去重人数。bitmapMin(b)/bitmapMax(b)返回最小/最大元素常用于快速判断集合的数值范围是否异常。示例SELECT bitmapCardinality(bitmapBuild([3, 1, 2])) AS card, bitmapMin(bitmapBuild([3, 1, 2])) AS min_val, bitmapMax(bitmapBuild([3, 1, 2])) AS max_val;这三个函数基本是所有 bitmap SQL 的骨架特别是bitmapCardinality几乎每个场景都会用到。3.2 集合运算四件套交集、并集、差集、异或分别对应bitmapAnd、bitmapOr、bitmapAndnot、bitmapXorWITH bitmapBuild([1, 2, 3, 4]) AS a, bitmapBuild([3, 4, 5]) AS b SELECT bitmapToArray(bitmapAnd(a, b)) AS inter_ab, -- [3,4] bitmapToArray(bitmapOr(a, b)) AS union_ab, -- [1,2,3,4,5] bitmapToArray(bitmapAndnot(a, b)) AS a_not_b, -- [1,2] bitmapToArray(bitmapXor(a, b)) AS xor_ab; -- [1,2,5]bitmapAndnot是在 A 中但不在 B 中做人群排除时会高频使用比如活跃用户里剔除黑名单用户。bitmapXor用得少一些但在做差异对比、两侧用户互斥场景时很顺手。3.3 只想要个数带 Cardinality 后缀的版本很多时候我们只需要交并差集的元素个数不需要完整集合回传客户端。ClickHouse 提供了一批直达基数的函数bitmapAndCardinality、bitmapOrCardinality、bitmapXorCardinality、bitmapAndnotCardinality例如SELECT bitmapAndCardinality(bitmapBuild([1,2,3]), bitmapBuild([2,3,4])) AS inter_cnt, bitmapOrCardinality(bitmapBuild([1,2,3]), bitmapBuild([2,3,4])) AS union_cnt;这样写比先bitmapAnd再bitmapCardinality少一次中间序列化传输特别是在分布式查询时减少协调节点传输的数据量性能差别明显。3.4 判断类函数Contains / HasAny / HasAllbitmapContains(b, key)单个元素是否在集合中。bitmapHasAny(a, b)两个集合是否有任意交集很快适合做相关性粗筛。bitmapHasAll(a, b)A 是否包含 B 的全部元素做权限/套餐包含校验很有用。WITH bitmapBuild([1, 2, 3]) AS a, bitmapBuild([2, 5]) AS b SELECT bitmapContains(a, 2) AS contains_2, -- 1 bitmapHasAny(a, b) AS has_any_ab, -- 1 bitmapHasAll(a, b) AS has_all_ab; -- 03.5 容易误用的辅助函数bitmapToArray可以把集合还原成数组适合抽样查看集合内容但大数据集上尽量别用一次全量数组物化很容易把查询内存打爆我一般在调试阶段限制LIMIT或加bitmapSubsetInRange才敢用。bitmapSubsetInRange(b, range_start, range_end)返回位图中位于某数值区间内的子集bitmapSubsetLimit(b, range_start, limit)则是在某个起始值后截取最多 limit 个元素这两个函数很适合对集合做分页或抽样核对。还有一个bitmapNegate它是对传入上界做全集取反比如bitmapNegate(bitmapBuild([1,2,3]), 10)会得到包含 0、4、5、6、7、8、9 的 bitmap。看起来很酷但上界一放宽就可能生成一个巨大的位图内存风险很高生产环境里我基本不碰它。4. 三个高频业务场景的SQL拆解4.1 留存计算两天集合求交集留存率本质上是第一天活跃的用户集合 ∩ 第二天活跃的用户集合的人数占第一天总人数比例。有了预聚合状态表SQL 可以写得非常清爽WITH (SELECT groupBitmapMerge(uid_state) FROM daily_uv_state WHERE day 2024-06-01) AS d0, (SELECT groupBitmapMerge(uid_state) FROM daily_uv_state WHERE day 2024-06-02) AS d1, (SELECT groupBitmapMerge(uid_state) FROM daily_uv_state WHERE day 2024-06-03) AS d2 SELECT bitmapCardinality(d0) AS day0_uv, bitmapAndCardinality(d0, d1) AS day0_day1_stay, bitmapAndCardinality(bitmapAnd(d0, d1), d2) AS day0_day1_day2_stay;这里有一个细节连续多天的交集不要写成一大串bitmapAnd(bitmapAnd(d0,d1),d2)后再算bitmapCardinality虽然可行但会先物化中间集合不如直接在更外层套bitmapAndCardinality(bitmapAnd(d0, d1), d2)让基数计算尽量贴近底层。若要求 N 日留存可以程序动态生成这种 CTE 组合或者把每天的集合横向展开后做递归合并核心思路都是状态表 集合运算。4.2 标签圈选集合的拼装游戏用户标签场景下每个标签都可以预聚合出一个用户集合。假设有一张user_tag_state表字段是tag String和uid_state AggregateFunction(groupBitmap, UInt64)圈选逻辑就是集合的拼装-- VIP 且最近 30 天活跃 SELECT bitmapAndCardinality( (SELECT groupBitmapMerge(uid_state) FROM user_tag_state WHERE tag vip), (SELECT groupBitmapMerge(uid_state) FROM user_tag_state WHERE tag active_30d) ) AS target_uv; -- 活跃但排除黑名单 SELECT bitmapAndnotCardinality( (SELECT groupBitmapMerge(uid_state) FROM user_tag_state WHERE tag active_30d), (SELECT groupBitmapMerge(uid_state) FROM user_tag_state WHERE tag blacklist) ) AS valid_uv;这种写法比把两个标签的用户明细表 JOIN 再去重快得多关键是标签状态是一份只算一次的可复用资产。实际项目里我通常会把标签集合做成一个带tag_id的状态表再配合标签组合配置表动态拼 SQL圈选需求从几个到几百个标签都很稳定。4.3 漏斗转化按步骤聚合用户集合漏斗场景里每个步骤都可以独立聚合用户集合假设事件表里有step_name预先写入漏斗状态表INSERT INTO funnel_state SELECT step_name, groupBitmapState(user_id) FROM events WHERE step_name IN (view_item, add_cart, checkout, pay_success) GROUP BY step_name;查询每一层的累计去重人数WITH (SELECT groupBitmapMerge(uid_state) FROM funnel_state WHERE step_name view_item) AS s1, (SELECT groupBitmapMerge(uid_state) FROM funnel_state WHERE step_name add_cart) AS s2, (SELECT groupBitmapMerge(uid_state) FROM funnel_state WHERE step_name checkout) AS s3, (SELECT groupBitmapMerge(uid_state) FROM funnel_state WHERE step_name pay_success) AS s4 SELECT bitmapCardinality(s1) AS step1_uv, bitmapAndCardinality(s1, s2) AS step1_2_uv, bitmapAndCardinality(bitmapAnd(s1, s2), s3) AS step1_2_3_uv, bitmapAndCardinality(bitmapAnd(bitmapAnd(s1, s2), s3), s4) AS step1_2_3_4_uv;这里要特别说明一个口径问题bitmap 交集表达的是整体用户集合的重叠它不保证用户是先看了商品加了购物车再支付这个时间顺序。如果你需要严格会话内顺序漏斗需要提前在明细层把用户的步骤路径压缩成一个序列或者用窗口函数按会话拆分bitmap 适合的是各步去重人数 总体重叠人数这个最常见的日报口径。理解了边界SQL 才不会用错。5. 生产环境踩坑分布式、物化视图与内存控制5.1 分布式表上不能裸查 bitmap 字段在分布式表上直接SELECT一个存储 bitmap 二进制的 String 字段拿到的只是某个分片的局部数据ClickHouse 不会自动把多分片的二进制按位运算合并。正确做法是用状态列-- 错误示范分布表字段是 Stringbitmap 二进制直接查 SELECT bitmapCardinality(uid_bitmap) FROM distributed_daily_uv; -- 正确示范字段是 AggregateFunction(groupBitmap, UInt64) SELECT day, bitmapCardinality(groupBitmapMerge(uid_state)) AS uv FROM distributed_daily_uv_state GROUP BY day;把列定义成AggregateFunction(groupBitmap, UInt64)后ClickHouse 在分布式执行时会把groupBitmapMerge下推到每个分片做局部合并再在协调节点汇总状态得到的是全集群正确的 bitmap。如果历史遗留已经是 String 字段建议按分片各自算完再用bitmapOrCardinality手动汇总切勿让上层直接把二进制字符串拼接。5.2 物化视图里状态字段的用法很多人在物化视图里建uid_state AggregateFunction(groupBitmap, UInt64)后不清不楚地直接INSERT普通表数据结果查询时groupBitmapMerge出来的数据一直不对。根本原因在于AggregatingMergeTree 的字段存储的是聚合状态不是普通列插入必须走SELECT groupBitmapState GROUP BY的聚合路径同时查询必须用groupBitmapMerge还原不能用bitmapCardinality(uid_state)这种普通函数直接读。一个完整可维护的例子CREATE MATERIALIZED VIEW events_daily_uv_mv ENGINE AggregatingMergeTree PARTITION BY toYYYYMM(day) ORDER BY day AS SELECT day, groupBitmapState(user_id) AS uid_state FROM events GROUP BY day; SELECT day, bitmapCardinality(groupBitmapMerge(uid_state)) AS uv FROM events_daily_uv_mv GROUP BY day;后台聚合本身是由 MERGE 机制异步完成的所以查询结果可能不是实时值需要根据业务容忍度选择延迟或强一致方案。这一点在报表系统里很容易被忽略但却是生产稳定性的关键。5.3 超大集合的内存控制策略全网规模一旦上亿直接在events明细表上执行groupBitmapState会有一个明显的内存峰值因为同一个分组的 bitmap 会一直膨胀到聚合结束。最简单的降峰方案是先分桶、再合并INSERT INTO daily_uv_bucket SELECT day, cityHash64(user_id) % 64 AS bucket, groupBitmapState(user_id) AS uid_state FROM events GROUP BY day, bucket; SELECT day, bitmapCardinality(groupBitmapMerge(uid_state)) AS uv FROM daily_uv_bucket GROUP BY day;每个桶的数据量是原来的 1/64状态内存自然被分摊最后合并时虽然多一次 scan但整体峰值可控。桶数量可以根据数据量和服务器内存调我常用的范围是 16 到 128。如果你的业务对实时性要求高还可以用groupBitmapAnd、groupBitmapOr这类聚合函数结合状态列做增量合并。5.4 版本升级与序列化兼容性bitmap 的底层序列化由 ClickHouse 的AggregateFunction机制承载官方在多数版本升级中保证了兼容性但并不意味着可以完全放飞。我遇到过几次升级后bitmapToArray返回结果正常、但groupBitmapMerge偶发异常的情况最终都是靠重建预聚合状态表解决的。我的建议是升级前先读官方 Changelog 中 bitmap 相关条目若集群较大先在测试环境跑一遍历史状态数据查询重算脚本再灰度升级。预聚合状态表本质上是可重算的缓存资产万一出问题宁可推倒重建也不要尝试手工改二进制状态。6. 什么时候别用bitmap与其他去重方案的取舍6.1 横向对比处理去重场景时ClickHouse 给的选择并不少我把常见方案放在一张表里方案精确性内存/存储集合可复用性典型场景uniq近似1% 内很小无大屏、趋势报表uniqExact精确随基数增长无单点精确计数uniqCombined精确/近似混合较小无折中场景bitmapBuild/groupBitmap精确压缩率高依赖 ID 连续度可存储、可交集/并集/差集留存、圈选、漏斗数组 arrayIntersect精确高随集合大小线性膨胀弱小规模集合如果你的需求真的只是求一个数uniq系列是最省事的但如果集合要反复参与运算或者你要保留历史集合做后续交叉bitmap 是唯一能兼顾精确性和复用性的方案。6.2 我踩过的三个误区第一不加映射直接塞原始 ID。雪花 ID 直接进 bitmap 后高位分桶散开很多场景下压缩率并不理想跑起来比uniqExact还慢。解决办法就是第 2.4 节那套映射方案。第二把 UUID 硬转整数来用。UUID 本身是 128 位的非数值类型转成某种数字映射后分布往往极差也不符合 RoaringBitmap 的压缩假设更稳妥的方式是给用户建自增整型映射。第三混淆 bitmap 函数和位图索引。ClickHouse 里CREATE TABLE ... INDEX idx TYPE bitmap是按列值构建位图索引服务于快速过滤而本文所有groupBitmap/bitmapAnd操作是把用户集合当对象做计算两者完全是两码事面试和生产排查时都比较容易踩混。6.3 一个我反复用的小经验最后分享一个排查技巧。如果你发现某个 bitmap 相关查询突然变慢别急着调服务器参数先看一下 ID 分布是不是出了空洞。方法很简单抽几组数据分别看bitmapCardinality和bitmapToArray返回的元素数量如果集合规模不大但序列化字节数异常高多半是原始 ID 跳跃太厉害导致大量稀疏容器。这时候重新走一遍 ID 映射通道效果通常立竿见影。我在几个项目里靠这个技巧省了很多排查时间也希望能帮你少踩些坑。