bitmap_and_agg aggregate function

Applies to: Databricks Runtime 18.0 and above

Returns the bitwise AND of all BINARY input values in the group.

The function is commonly used to count the number of distinct integral numbers in combination with the bitmap_bucket_number(), bitmap_count(), bitmap_bit_position(), and bitmap_construct_agg() functions.

To aggregate bitmaps in form of integral numerics, use the bit_and() aggregate function. To aggregate bit positions into a BINARY bitmap, use bitmap_construct_agg().

Syntax

bitmap_and_agg(expr) [FILTER ( WHERE cond ) ]

This function can also be invoked as a window function using the OVER clause.

Arguments

• expr: A BINARY of length <= 4096 representing a bitmap. Azure Databricks truncates longer values to 4096.
• cond: An optional boolean expression filtering the rows used for aggregation.

Returns

A BINARY of length 4096.

Examples

> SELECT hex(trim(TRAILING X'00' FROM bitmap_and_agg(val))) FROM VALUES(x'F0'), (x'70'), (x'30') AS T(val);
 30

Related functions

• bit_or aggregate function
• bitmap_bit_position function
• bitmap_bucket_number function
• bitmap_construct_agg aggregate function
• bitmap_count function
• bitmap_or_agg aggregate function