coalesce
Syntax
coalesce(X1, X2, args…)
Arguments
X1 is a scalar or vector.
X2 is a scalar or vector of the same data type as X1. If X1 is a scalar, X2 must be a scalar; If X1 is a vector, X2 can be a non-NULL scalar or a vector of the same length as X1.
args (optional) can be one or more arguments taking the same data type/form as X2.
Details
The function fills NULL values in X1 and returns a scalar or vector of the same dimension as X1.
For each element in X1,
If not NULL, return the element;
If NULL, check the element at the same position in X2:
If not NULL, fill the NULL value in X1 with it;
If NULL, conduct the aforementioned calculation on the subsequent args until a non-NULL element is returned; Otherwise return NULL.
Usage:
Merge multiple columns of a table into one column;
An alternative to complex
case
expression. For example,select coalesce (expr1, expr2, 1) from t
is equivalent toselect case when vol1 is not null then vol1 when vol2 is not null then vol2 else 1 end from t
.
Examples
$ coalesce(int(NULL), int(NULL), 1, 3)
1
$ coalesce(-1 NULL 4 3, NULL 2 NULL 1, 1 4 5 2)
[-1,2,4,3]
$ vol1 = [3.3, 2.2, 2.1, NULL, 1.2]
$ vol2 = [NULL, 1.8, 1.9, 2.3, 3.2]
$ sym = `a`a`b`a`c
$ t = table(sym, vol1, vol2)
$ select sym, coalesce(vol1, vol2) as vol from t
sym,vol
a,3.3
a,2.2
b,2.1
a,2.3
c,1.2