Declaration
BIT_XOR(expr) OVER (analytic_clause)
Description
Returns the bitwise XOR of all bits in expr.
The result type depends on whether the function parameter values are evaluated as binary strings or numbers. Binary string evaluation occurs when a parameter value is of the binary string type and the parameter is not a hexadecimal literal, a bit literal, or a NULL literal. Otherwise, numeric computation occurs, converting the parameter value to an unsigned 64-bit integer if necessary.
If no matching rows are found, BIT_XOR() returns a neutral value of the same length as the parameter values (with all bits set to 0). 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 tbl1 (year YEAR (4), month INT(2)UNSIGNED ZEROFILL, day INT(2) UNSIGNED ZEROFILL);
INSERT INTO tbl1 VALUES(2021,1,1),(2021,1,22),(2021,1,3),(2021,2,2), (2021,2,23),(2021,2,23);
obclient> SELECT * FROM tbl1;
+------+-------+------+
| year | month | day |
+------+-------+------+
| 2021 | 01 | 01 |
| 2021 | 01 | 22 |
| 2021 | 01 | 03 |
| 2021 | 02 | 02 |
| 2021 | 02 | 23 |
| 2021 | 02 | 23 |
+------+-------+------+
6 rows in set
obclient> SELECT year,month,BIT_XOR(day) OVER (PARTITION BY year, month) AS days FROM tbl1;
+------+-------+------+
| year | month | days |
+------+-------+------+
| 2021 | 01 | 20 |
| 2021 | 01 | 20 |
| 2021 | 01 | 20 |
| 2021 | 02 | 2 |
| 2021 | 02 | 2 |
| 2021 | 02 | 2 |
+------+-------+------+
6 rows in set