BITMAP 用户分析

概述

BITMAP（位图）是一种高效的集合数据结构，用整数位来表示集合成员。在用户分析场景中，BITMAP 的核心优势是：对大规模用户 ID 集合做交集、并集、差集运算，速度远快于 JOIN 或 IN 子查询。

典型场景：

计算 DAU / MAU（日活/月活去重用户数）
多标签圈人：同时满足"VIP 且近 7 天活跃"的用户数
留存分析：连续 N 天都活跃的用户数
漏斗交集：完成步骤 A 且完成步骤 B 的用户数

核心函数速查

函数	输入类型	返回类型	用途
`bitmap_build(array)` bitmap_build(array)	`ARRAY<BIGINT>` ARRAY<BIGINT>	`bitmap` bitmap	从数组构建 bitmap 对象
`bitmap_count(bm)` bitmap_count(bm)	`bitmap` bitmap	`BIGINT` BIGINT	返回 bitmap 中的元素个数（基数）
`bitmap_to_array(bm)` bitmap_to_array(bm)	`bitmap` bitmap	`ARRAY<BIGINT>` ARRAY<BIGINT>	将 bitmap 转为数组，查看成员
`TO_BITMAP(n)` TO_BITMAP(n)	`BIGINT` BIGINT	`bitmap` bitmap	构建只含单个元素的 bitmap
`group_bitmap(id)` group_bitmap(id)	`BIGINT` BIGINT	`BIGINT` BIGINT	聚合函数，返回分组内唯一 ID 的基数
`group_bitmap_state(id)` group_bitmap_state(id)	`BIGINT` BIGINT	`bitmap` bitmap	聚合函数，返回 bitmap 对象（用于两阶段聚合）
`group_bitmap_or(bm)` group_bitmap_or(bm)	`bitmap` bitmap	`BIGINT` BIGINT	聚合函数，对多个 bitmap 求并集基数
`group_bitmap_and(bm)` group_bitmap_and(bm)	`bitmap` bitmap	`BIGINT` BIGINT	聚合函数，对多个 bitmap 求交集基数
`group_bitmap_xor(bm)` group_bitmap_xor(bm)	`bitmap` bitmap	`BIGINT` BIGINT	聚合函数，对多个 bitmap 求异或基数
`group_bitmap_merge(bm)` group_bitmap_merge(bm)	`bitmap` bitmap	`BIGINT` BIGINT	聚合函数，合并多个 bitmap state，返回并集基数
`bm1 & bm2` bm1 & bm2	`bitmap` bitmap	`bitmap` bitmap	交集（AND）
`bm1 \| bm2` bm1 \| bm2	`bitmap` bitmap	`bitmap` bitmap	并集（OR）
`bm1 bm2` bm1 bm2	`bitmap` bitmap	`bitmap` bitmap	异或（XOR，对称差集）

group_bitmap

group_bitmap

BIGINT

BIGINT

（基数），不是 bitmap 对象，无法再做位运算。需要 bitmap 对象时用

group_bitmap_state

group_bitmap_state

。

前置数据

-- 用户标签表（每行一个用户-标签关系） CREATE TABLE IF NOT EXISTS doc_bitmap_user_tags ( tag_name VARCHAR(50), user_id BIGINT ); INSERT INTO doc_bitmap_user_tags VALUES ('vip', 1001), ('vip', 1002), ('vip', 1003), ('vip', 1004), ('vip', 1005), ('active_7d', 1001), ('active_7d', 1003), ('active_7d', 1005), ('active_7d', 1007), ('active_7d', 1009), ('buyer', 1002), ('buyer', 1003), ('buyer', 1004), ('buyer', 1006), ('buyer', 1008); -- 每日活跃用户表（每行存一天的活跃用户 ID 数组） CREATE TABLE IF NOT EXISTS doc_bitmap_daily_active ( dt DATE, user_ids ARRAY<BIGINT> ); INSERT INTO doc_bitmap_daily_active VALUES (CAST('2024-01-01' AS DATE), ARRAY(1001,1002,1003,1004,1005)), (CAST('2024-01-02' AS DATE), ARRAY(1001,1003,1005,1007,1009)), (CAST('2024-01-03' AS DATE), ARRAY(1002,1003,1004,1006,1008));

场景一：每日活跃用户数（DAU）

SELECT dt, BITMAP_COUNT(bitmap_build(user_ids)) AS dau FROM doc_bitmap_daily_active ORDER BY dt;

结果：

dt	dau
2024-01-01	5
2024-01-02	5
2024-01-03	5

场景二：多日去重用户数（MAU）

计算 3 天内的去重活跃用户总数（并集）。

SELECT group_bitmap_or(bitmap_build(user_ids)) AS mau_3days FROM doc_bitmap_daily_active;

结果：

mau_3days
9

group_bitmap_or

group_bitmap_or

对所有行的 bitmap 求并集，返回并集的基数（去重用户数）。

场景三：连续 N 天留存用户数

计算连续 3 天都活跃的用户数（交集）。

SELECT group_bitmap_and(bitmap_build(user_ids)) AS retained_3days FROM doc_bitmap_daily_active;

结果：

retained_3days
1

group_bitmap_and

group_bitmap_and

对所有行的 bitmap 求交集，返回交集的基数。只有 user_id=1003 在三天中都出现。

场景四：两天之间的留存（指定日期交集）

计算 1 月 1 日活跃且 1 月 2 日也活跃的用户数。

SELECT BITMAP_COUNT( bitmap_build(d1.user_ids) & bitmap_build(d2.user_ids) ) AS day1_day2_common FROM (SELECT user_ids FROM doc_bitmap_daily_active WHERE dt = CAST('2024-01-01' AS DATE)) d1, (SELECT user_ids FROM doc_bitmap_daily_active WHERE dt = CAST('2024-01-02' AS DATE)) d2;

结果：

day1_day2_common
3

1 月 1 日活跃用户：1001,1002,1003,1004,1005；1 月 2 日活跃用户：1001,1003,1005,1007,1009；交集：1001,1003,1005（3 人）。

场景五：多标签圈人（交集 / 并集 / 差集）

5.1 交集：同时满足多个标签的用户数

SELECT BITMAP_COUNT( bitmap_build(a.user_ids) & bitmap_build(b.user_ids) ) AS vip_and_active FROM (SELECT COLLECT_LIST(user_id) AS user_ids FROM doc_bitmap_user_tags WHERE tag_name = 'vip') a, (SELECT COLLECT_LIST(user_id) AS user_ids FROM doc_bitmap_user_tags WHERE tag_name = 'active_7d') b;

结果：

vip_and_active
3

查看具体是哪些用户：

SELECT bitmap_to_array( bitmap_build(a.user_ids) & bitmap_build(b.user_ids) ) AS vip_and_active_users FROM (SELECT COLLECT_LIST(user_id) AS user_ids FROM doc_bitmap_user_tags WHERE tag_name = 'vip') a, (SELECT COLLECT_LIST(user_id) AS user_ids FROM doc_bitmap_user_tags WHERE tag_name = 'active_7d') b;

结果：

vip_and_active_users
["1001", "1003", "1005"]

5.2 并集：满足任一标签的用户数（去重）

SELECT BITMAP_COUNT( bitmap_build(a.user_ids) | bitmap_build(b.user_ids) ) AS vip_or_active FROM (SELECT COLLECT_LIST(user_id) AS user_ids FROM doc_bitmap_user_tags WHERE tag_name = 'vip') a, (SELECT COLLECT_LIST(user_id) AS user_ids FROM doc_bitmap_user_tags WHERE tag_name = 'active_7d') b;

结果：

vip_or_active
7

5.3 差集：属于 A 但不属于 B 的用户数

云器 Lakehouse 不支持

取反运算符，差集用

A XOR (A AND B)

A XOR (A AND B)

等价实现：

SELECT BITMAP_COUNT( bitmap_build(a.user_ids) (bitmap_build(a.user_ids) & bitmap_build(b.user_ids)) ) AS vip_not_active FROM (SELECT COLLECT_LIST(user_id) AS user_ids FROM doc_bitmap_user_tags WHERE tag_name = 'vip') a, (SELECT COLLECT_LIST(user_id) AS user_ids FROM doc_bitmap_user_tags WHERE tag_name = 'active_7d') b;

结果：

vip_not_active
2

vip 用户（1001-1005）中，不在 active_7d（1001,1003,1005,1007,1009）中的是 1002 和 1004，共 2 人。

场景六：每个标签的用户数（GROUP_BITMAP）

SELECT tag_name, GROUP_BITMAP(user_id) AS user_count FROM doc_bitmap_user_tags GROUP BY tag_name ORDER BY tag_name;

结果：

tag_name	user_count
active_7d	5
buyer	5
vip	5

GROUP_BITMAP

GROUP_BITMAP

直接返回基数（BIGINT），适合只需要计数的场景。

场景七：两阶段聚合（大数据量优化）

当数据量很大时，先用

group_bitmap_state

group_bitmap_state

按分区生成中间 bitmap，再用

group_bitmap_merge

group_bitmap_merge

合并，可以减少单次聚合的内存压力。

-- 先按天构建 bitmap state，再合并计算 3 日 MAU SELECT group_bitmap_merge(daily_state) AS mau_3days FROM ( SELECT dt, group_bitmap_state(user_id) AS daily_state FROM ( SELECT dt, EXPLODE(user_ids) AS user_id FROM doc_bitmap_daily_active ) t GROUP BY dt ) agg;

结果：

mau_3days
9

两阶段聚合与直接

group_bitmap_or

group_bitmap_or

结果相同，但适合分布式场景下的预聚合优化。

注意事项

group_bitmap
group_bitmap
返回 BIGINT，不是 bitmap 对象：不能对其结果再做
```
&
```
&
、
```
|
```
|
、`` 运算。需要 bitmap 对象时，改用
```
group_bitmap_state
```
group_bitmap_state
。
差集没有直接运算符：
```
~
```
~
取反不支持。差集
```
A - B
```
A - B
用
```
A  (A & B)
```
A (A & B)
实现。
bitmap_build
bitmap_build
接受
ARRAY<BIGINT>
ARRAY<BIGINT>
：如果数据是逐行存储的用户 ID，先用
```
COLLECT_LIST
```
COLLECT_LIST
聚合成数组，再传给
```
bitmap_build
```
bitmap_build
。
用户 ID 必须是非负整数：BITMAP 基于整数位图，不支持负数或非整数 ID。
bitmap_to_array
bitmap_to_array
结果是字符串数组：返回的数组元素类型为
```
STRING
```
STRING
，如需数值比较需要
```
CAST
```
CAST
。

联系我们