Skip to main content
Last updated on

bitmap_and_agg aggregate function

Applies to: check marked yes 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. Databricks truncates longer values to 4096.
  • cond: An optional boolean expression filtering the rows used for aggregation.

Returns​

A BINARY of length 4096.

Examples​

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