Group Bitmap User Overlap Analysis: Practical Guide
Scenario: Achieve sub-second funnel conversion calculation, retention analysis, multi-dimensional audience segmentation, and A/B test audience isolation checks on massive user datasets (hundreds of millions of records).
Core technique: Use Bitmap bitwise operations (AND/OR/XOR) to replace traditional JOIN or COUNT DISTINCT, reducing computational complexity from O(N) to O(1) — a performance improvement of over 100x.
Calculate the conversion rate for "view -> cart -> pay". Traditional SQL requires multiple JOINs, which easily times out on large datasets.
Bitmap Implementation
WITH funnel_steps AS (
SELECT
-- Generate a Bitmap for each funnel step
group_bitmap_state(CASE WHEN event_name = 'view' THEN user_id END) AS step_view,
group_bitmap_state(CASE WHEN event_name = 'cart' THEN user_id END) AS step_cart,
group_bitmap_state(CASE WHEN event_name = 'pay' THEN user_id END) AS step_pay
FROM user_events
WHERE event_date = CAST('2026-05-01' AS DATE)
)
SELECT
bitmap_cardinality(step_view) AS view_uv,
bitmap_cardinality(step_cart) AS cart_uv,
bitmap_cardinality(step_pay) AS pay_uv,
-- Conversion rate: current step count / previous step count
ROUND(bitmap_cardinality(step_cart) * 100.0 / bitmap_cardinality(step_view), 2) AS view_to_cart_rate,
ROUND(bitmap_cardinality(step_pay) * 100.0 / bitmap_cardinality(step_cart), 2) AS cart_to_pay_rate
FROM funnel_steps;
Output:
view_uv
cart_uv
pay_uv
view_to_cart_rate
cart_to_pay_rate
4
3
2
75.00
66.67
Advantage: Regardless of whether the data volume is 1 million or 1 billion, bitwise operation time is nearly constant and always completes in milliseconds.
Scenario 2: Retained User Analysis (N-day Retention)
Problem
Calculate how many users active on May 1 were also active on May 2 and May 3.
Bitmap Implementation
WITH daily_users AS (
-- Aggregate by day to generate Bitmaps
SELECT event_date, group_bitmap_state(user_id) AS daily_bm
FROM user_events
GROUP BY event_date
),
base_day AS (
SELECT daily_bm AS base_bm FROM daily_users WHERE event_date = CAST('2026-05-01' AS DATE)
)
SELECT
d.event_date,
bitmap_cardinality(d.daily_bm) AS daily_uv,
-- Retention: intersection of the current day's Bitmap with the baseline day's Bitmap
bitmap_cardinality(bitmap_and(d.daily_bm, b.base_bm)) AS retained_uv,
ROUND(bitmap_cardinality(bitmap_and(d.daily_bm, b.base_bm)) * 100.0 / bitmap_cardinality(b.base_bm), 2) AS retention_rate
FROM daily_users d
CROSS JOIN base_day b
WHERE d.event_date >= CAST('2026-05-01' AS DATE)
ORDER BY d.event_date;
A marketer needs to query in real time: "How many users came from the App channel and made a payment?"
Bitmap Implementation
Step 1: Pre-compute tag Bitmaps (Dynamic Table or scheduled job)
-- Example: pre-compute Bitmaps by channel and event type
CREATE TABLE tag_bitmaps AS
SELECT
channel,
event_name,
group_bitmap_state(user_id) AS bm
FROM user_events
GROUP BY channel, event_name;
Step 2: Real-time query (millisecond response)
-- Query: users from the App channel who also made a payment
-- Logic: (Bitmap of all App users) AND (Bitmap of all paying users)
WITH app_users AS (
SELECT group_bitmap_state(user_id) AS bm FROM user_events WHERE channel = 'app'
),
pay_users AS (
SELECT group_bitmap_state(user_id) AS bm FROM user_events WHERE event_name = 'pay'
)
SELECT
bitmap_cardinality(bitmap_and(a.bm, b.bm)) AS target_uv
FROM app_users a, pay_users b;
Output:
target_uv
2
Advantage: No need to scan the raw event log table. Bitwise operations run directly on the pre-computed Bitmap table and support any combination of dimensions.
Scenario 4: A/B Test Audience Isolation Check
Problem
Verify that the control group and treatment group in an A/B test do not share any users (overlap contaminates experiment results).
Bitmap Implementation
WITH groups AS (
SELECT
group_bitmap_state(CASE WHEN group_name = 'control' THEN user_id END) AS control_bm,
group_bitmap_state(CASE WHEN group_name = 'treatment' THEN user_id END) AS treatment_bm
FROM ab_test_users
)
SELECT
bitmap_cardinality(bitmap_and(control_bm, treatment_bm)) AS overlap_count,
CASE
WHEN bitmap_cardinality(bitmap_and(control_bm, treatment_bm)) > 0 THEN '⚠️ Overlap detected — experiment invalid'
ELSE '✅ Audiences are isolated — experiment valid'
END AS check_result
FROM groups;
Output:
overlap_count
check_result
0
✅ Audiences are isolated — experiment valid
Common Issues
1. group_bitmap vs group_bitmap_state
-- Wrong: group_bitmap returns a cardinality (INT) and cannot be used in bitwise operations
SELECT bitmap_and(group_bitmap(user_id), ...) -- error
-- Correct: use group_bitmap_state to produce a Bitmap object
SELECT bitmap_and(group_bitmap_state(user_id), ...)
2. Handling negative IDs
-- Bitmap only supports non-negative integers. If user_id contains negatives or strings, convert first.
-- Wrong: group_bitmap_state(user_id) WHERE user_id = -1
-- Correct: ensure ID >= 0, or use a hash function to convert to a positive integer
SELECT group_bitmap_state(abs(hash(user_id))) FROM users;
3. Memory limits
-- A single Bitmap object is usually small in memory (compressed), but in extreme cases
-- (e.g., the full user base) it may consume significant memory.
-- Recommendation: shard Bitmaps by time or business domain to avoid oversized single Bitmaps.
Performance Optimization Tips
Scenario
Optimization strategy
High-frequency queries
Store Bitmap results in a Dynamic Table with REFRESH INTERVAL 1 HOUR for automatic updates
Storage optimization
Store Bitmap columns using the BITMAP type; Singdata Lakehouse automatically applies RoaringBitmap compression
Query acceleration
Build a Bloom Filter index on Bitmap columns to speed up bitmap_cardinality queries
-- Recommended architecture: ODS -> DWD (detail) -> DWS (Bitmap aggregation) -> ADS (application queries)
CREATE DYNAMIC TABLE dws_user_bitmaps
REFRESH INTERVAL 1 HOUR
AS
SELECT
DATE_TRUNC('DAY', event_time) AS event_date,
channel,
group_bitmap_state(user_id) AS user_bm
FROM dwd_user_events
GROUP BY 1, 2;
