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 the set of bitmap values, and return.

example

Table schema

  1. KeysType: AGG_KEY
  2. Columns: tag varchar, date datetime, user_id bitmap bitmap_union
  1. Find the retention of users between 2020-05-18 and 2020-05-19 under different tags.
  2. mysql> 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;

Used in combination with the bitmap_to_string function to obtain the specific data of the intersection

  1. Who are the users retained under different tags between 2020-05-18 and 2020-05-19?
  2. mysql> 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

  1. BITMAP_INTERSECT, BITMAP