Declaration
BIT_AND(expr) OVER (analytic_clause)
Description
Returns the bitwise AND of all bits in expr.
The result type depends on whether the function parameters are evaluated as binary strings or numbers. Binary string evaluation occurs when a parameter is of the binary string type and is not a hexadecimal literal, a bit literal, or a NULL literal. Otherwise, numerical computation occurs, converting the parameter value to an unsigned 64-bit integer if necessary.
If no matching rows are found, BIT_AND() returns a neutral value of the same length as the parameter values (with all bits set to 1). A NULL value does not affect the result unless all values are NULL. In that case, the result is a neutral value of the same length as the parameter values.
Examples
CREATE TABLE product_information(supplier_id INT, product_id INT,list_price numeric, min_price numeric);
INSERT INTO PRODUCT_INFORMATION VALUES ('102050', '1659', '45', NULL);
INSERT INTO PRODUCT_INFORMATION VALUES ('102050', '1770', NULL, '70');
INSERT INTO PRODUCT_INFORMATION VALUES ('102050', '2370', '305', '247');
INSERT INTO PRODUCT_INFORMATION VALUES ('102050', '2380', '750', '731');
INSERT INTO PRODUCT_INFORMATION VALUES ('102050', '3255', NULL, NULL);
INSERT INTO PRODUCT_INFORMATION VALUES ('102050', '1659', '35', NULL);
obclient> SELECT * FROM product_information;
+-------------+------------+------------+-----------+
| supplier_id | product_id | list_price | min_price |
+-------------+------------+------------+-----------+
| 102050 | 1659 | 45 | NULL |
| 102050 | 1770 | NULL | 70 |
| 102050 | 2370 | 305 | 247 |
| 102050 | 2380 | 750 | 731 |
| 102050 | 3255 | NULL | NULL |
| 102050 | 2380 | 750 | 731 |
| 102050 | 1659 | 35 | NULL |
+-------------+------------+------------+-----------+
7 rows in set
obclient> SELECT product_id, BIT_AND(min_price) OVER (partition by product_id) BITS FROM product_information;
+------------+----------------------+
| product_id | BITS |
+------------+----------------------+
| 1659 | 18446744073709551615 |
| 1659 | 18446744073709551615 |
| 1770 | 70 |
| 2370 | 247 |
| 2380 | 731 |
| 3255 | 18446744073709551615 |
+------------+----------------------+
6 rows in set