BITMAP_AND_CARDINALITY

Description

The BITMAP_AND_CARDINALITY function is used to calculate the intersection of two bitmap type parameters and return the number of elements in the result set. This function has a high performance advantage when processing large amounts of data, making it particularly suitable for scenarios that require quick calculation of set relationships.

Syntax


bitmap_and_cardinality(left, right)

Parameter Description

left (bitmap type): The first input bitmap data.
right (bitmap type): The second input bitmap data.

Return Result

Returns a value of bigint type, representing the number of elements in the intersection of the two input bitmaps.

Usage Example

Example 1: Calculate the number of elements in the intersection of two simple bitmaps


SELECT bitmap_and_cardinality(bitmap_build(array(1, 2, 3)), bitmap_build(array(2, 3, 4)));

Results:

In this example, the two bitmaps are {1, 2, 3} and {2, 3, 4}, their intersection is {2, 3}, so the result is 2.

Example 2: Calculate the number of intersection elements with actual data tables Suppose we have a data table named users, which contains the following columns: id, username, interests. The interests column stores the user's hobbies, and the data type is bitmap.

Now, we want to calculate the number of users who like "basketball" and "football". This can be achieved using the following SQL query:


SELECT bitmap_and_cardinality(
    bitmap_or(
        bitmap_and(interests, 'basketball'),
        bitmap_and(interests, 'football')
    ),
    interests
) AS shared_interests_count
FROM users;

In this example, we first use the bitmap_and function to filter out users who like "basketball" and "football", then use the bitmap_or function to calculate the union of these two sets. Finally, we use the bitmap_and_cardinality function to calculate the number of elements in the union, that is, the number of users who like "basketball" and "football".

Notes

Ensure that the data type of the input parameters is bitmap, otherwise the function will fail.
When the input bitmap is empty, the function returns 0.