Categories:

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

BITXOR_AGG¶

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

In each bit position, if an even number of rows have that bit set to 1, then the function returns 0 for that bit, and if an odd number of rows have that bit set to 1, then the function returns 1 for that bit.

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

Aliases:

BITXORAGG , BIT_XOR_AGG, BIT_XORAGG

See also:

BITAND_AGG , BITOR_AGG

BITXOR

Syntax¶

Aggregate function

BITXOR_AGG( [ DISTINCT ] <expr1> )
Copy

Window function

BITXOR_AGG( [ DISTINCT ] <expr1> ) OVER ( [ PARTITION BY <expr2> ] )
Copy

Arguments¶

expr1

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

expr2

This expression is used to group the rows in partitions.

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.

  • When used as a window function:

    • This function does not support:

      • ORDER BY sub-clause in the OVER() clause.

      • Window frames.

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');
Copy

Display the data:

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

Query the data:

select bitxor_agg(k), bitxor_agg(d), bitxor_agg(s1) from bitwise_example;
+---------------+---------------+----------------+
| BITXOR_AGG(K) | BITXOR_AGG(D) | BITXOR_AGG(S1) |
|---------------+---------------+----------------|
|            31 |            12 |              0 |
+---------------+---------------+----------------+
Copy

Query the data and use a GROUP BY:

select s2, bitxor_agg(k), bitxor_agg(d) from bitwise_example group by s2
    order by 3;
+------+---------------+---------------+
| S2   | BITXOR_AGG(K) | BITXOR_AGG(D) |
|------+---------------+---------------|
| one  |            15 |             1 |
| two  |            22 |             4 |
| nine |          NULL |             9 |
| null |             6 |          NULL |
+------+---------------+---------------+
Copy

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

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