Skip to main content
Version: 2.5

bitmap_intersect

Description​

Aggregation function, used to calculate the bitmap intersection after grouping. Common usage scenarios, such as calculating user retention rate.

Syntax​

BITMAP BITMAP_INTERSECT(BITMAP value)

Enter a set of bitmap values, find the intersection of this set of bitmap values, and return the result.

Example​

Table structure

KeysType: AGG_KEY
Columns: tag varchar, date datetime, user_id bitmap bitmap_union
-- Calculate users retention under different tags today and yesterday. 
select tag, bitmap_intersect(user_id)
from (
    select tag, date, bitmap_union(user_id) user_id
    from table
    where date in ('2020-05-18', '2020-05-19')
    group by tag, date) a
group by tag;

Use with bitmap_to_string function to obtain the specific data of the intersection.

--Find out users retained under different tags today and yesterday. 
select tag, bitmap_to_string(bitmap_intersect(user_id))
from (
    select tag, date, bitmap_union(user_id) user_id
    from table where date in ('2020-05-18', '2020-05-19')
    group by tag, date) a
group by tag;

keyword​

BITMAP_INTERSECT, BITMAP