- Categories:
Aggregate functions (Bitwise) , Window functions (General) , Bitwise expression functions
BITOR_AGGΒΆ
Returns the bitwise OR value of all non-NULL numeric records in a group.
For each bit position, if at least one row has the bit set to 1, then the bit is set to 1 in the result. If all 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:
BITORAGG, BIT_OR_AGG, BIT_ORAGG
- See also:
SyntaxΒΆ
Aggregate function
BITOR_AGG( <expr1> )
Window function
BITOR_AGG( <expr1> ) OVER ( [ PARTITION BY <expr2> ] )
ArgumentsΒΆ
expr1
This expression must evaluate to a numeric value or a value of a data type that can be cast to a numeric value.
expr2
This expression is used to group the rows in partitions.
ReturnsΒΆ
The data type of the returned value is NUMBER(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.
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');
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 |
+-------+---------+------+------+
Query the data:
SELECT BITOR_AGG(k),
BITOR_AGG(d),
BITOR_AGG(s1)
FROM bitwise_example;
+--------------+--------------+---------------+
| BITOR_AGG(K) | BITOR_AGG(D) | BITOR_AGG(S1) |
|--------------+--------------+---------------|
| 31 | 15 | 14 |
+--------------+--------------+---------------+
Query the data and use a GROUP BY clause:
SELECT s2,
BITOR_AGG(k),
BITOR_AGG(d)
FROM bitwise_example group by s2
ORDER BY 3;
+------+--------------+--------------+
| S2 | BITOR_AGG(K) | BITOR_AGG(D) |
|------+--------------+--------------|
| one | 15 | 1 |
| two | 30 | 7 |
| nine | NULL | 9 |
| null | 14 | NULL |
+------+--------------+--------------+
If you pass this function strings that canβt be converted to NUMBER values, an error is returned:
SELECT BITOR_AGG(s2) FROM bitwise_example;
100038 (22018): Numeric value 'one' is not recognized