Syntax
BIT_AND(expr) OVER (analytic_clause)
Purpose
Returns the bitwise AND of all bits in expr.
The result type depends on whether the function arguments are evaluated as binary strings or numbers. If the arguments are binary strings and not hexadecimal literals, bit literals, or NULL literals, binary string evaluation occurs. Otherwise, numeric computation occurs, with the arguments converted to unsigned 64-bit integers as needed.
If no rows match, BIT_AND() returns a neutral value with the same length as the argument value (all bits set to 1). NULL values do not affect the result unless all values are NULL, in which case the result is a neutral value with the same length as the argument value.
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