Categories:

Aggregate Functions (Bitwise) , Window Functions , Bitwise Expression Functions

BITAND_AGG

Returns the bitwise AND value of all non-NULL numeric records in a group.

For each bit position, if all rows have the bit set to 1, then the bit is set to 1 in the result. If any rows have that bit set to zero, the result is zero.

If all records inside the group are NULL, or if the group is empty, the function returns NULL.

Aliases:

BITANDAGG , BIT_AND_AGG , BIT_ANDAGG

See also:

BITAND , BITOR_AGG , BITXOR_AGG

Syntax

BITAND_AGG( <expr> )

Arguments

expr

The input expression must be an expression that can be evaluated to a number or converted to a number.

Returns

The data type of the returned value is NUMERIC(38, 0).

Usage Notes

  • Numeric values are aggregated to the nearest integer data type. Decimal and floating point values are rounded to the nearest integer before aggregation.

  • Aggregating a character/text column (data type VARCHAR, CHAR, STRING, etc.) implicitly casts the input values to FLOAT, then rounds the values to the nearest integer. If the cast is not possible, the value is treated as NULL.

  • The DISTINCT keyword can be specified for these functions, but it does not have any effect.

Examples

Create the table and load the data:

CREATE OR REPLACE TABLE bitwise_example
        (k int, d decimal(10,5), s1 varchar(10), s2 varchar(10));

INSERT INTO bitwise_example VALUES
        (15, 1.1, '12','one'),
        (26, 2.9, '10','two'),
        (12, 7.1, '7.9','two'),
        (14, null, null,'null'),
        (8, null, null, 'null'),
        (null, 9.1, '14','nine');

Display the data:

SELECT k AS k_col, d AS d_col, s1, s2 FROM bitwise_example;
+-------+---------+------+------+
| K_COL |   D_COL | S1   | S2   |
|-------+---------+------+------|
|    15 | 1.10000 | 12   | one  |
|    26 | 2.90000 | 10   | two  |
|    12 | 7.10000 | 7.9  | two  |
|    14 |    NULL | NULL | null |
|     8 |    NULL | NULL | null |
|  NULL | 9.10000 | 14   | nine |
+-------+---------+------+------+

Query the data:

select bitand_agg(k), bitand_agg(d), bitand_agg(s1) from bitwise_example;
+---------------+---------------+----------------+
| BITAND_AGG(K) | BITAND_AGG(D) | BITAND_AGG(S1) |
|---------------+---------------+----------------|
|             8 |             1 |              8 |
+---------------+---------------+----------------+

Query the data and use a GROUP BY:

select s2, bitand_agg(k), bitand_agg(d) from bitwise_example group by s2;
+------+---------------+---------------+
| S2   | BITAND_AGG(K) | BITAND_AGG(D) |
|------+---------------+---------------|
| one  |            15 |             1 |
| two  |             8 |             3 |
| null |             8 |          NULL |
| nine |          NULL |             9 |
+------+---------------+---------------+

If you pass this function strings that can’t be converted to numbers, you’ll get an error:

select bitand_agg(s2) from bitwise_example;
100038 (22018): Numeric value 'one' is not recognized