ARRAY_FILTER
デスクリプション
lambda式を入力パラメータとして使用し、もう一方の入力パラメータのARRAY列のデータを計算およびフィルタリングします。 そして結果から0とNULLの値をフィルタリングして除外します。
Syntax
ARRAY_FILTER(<lambda>, <arr>)
ARRAY_FILTER(<arr>, <filter_column>)
パラメータ
| Parameter | デスクリプション |
|---|---|
<lambda> | 入力パラメータが指定された配列の列数と一致する必要があるラムダ式。この式は有効なスカラー関数を実行できますが、集約関数はサポートしていません。 |
<arr> | ARRAY配列 |
Return Value
入力されたARRAYパラメータの内部データに対して指定された式の計算を実行し、結果から0とNULL値を除外します。
Example
select c_array,array_filter(c_array,[0,1,0]) from array_test;
+-----------------+----------------------------------------------------+
| c_array | array_filter(`c_array`, ARRAY(FALSE, TRUE, FALSE)) |
+-----------------+----------------------------------------------------+
| [1, 2, 3, 4, 5] | [2] |
| [6, 7, 8] | [7] |
| [] | [] |
| NULL | NULL |
+-----------------+----------------------------------------------------+
select array_filter(x->(x > 1),[1,2,3,0,null]);
+----------------------------------------------------------------------------------------------+
| array_filter(ARRAY(1, 2, 3, 0, NULL), array_map([x] -> (x(0) > 1), ARRAY(1, 2, 3, 0, NULL))) |
+----------------------------------------------------------------------------------------------+
| [2, 3] |
+----------------------------------------------------------------------------------------------+
select *, array_filter(x->x>0,c_array2) from array_test2;
+------+-----------------+-------------------------+------------------------------------------------------------------+
| id | c_array1 | c_array2 | array_filter(`c_array2`, array_map([x] -> x(0) > 0, `c_array2`)) |
+------+-----------------+-------------------------+------------------------------------------------------------------+
| 1 | [1, 2, 3, 4, 5] | [10, 20, -40, 80, -100] | [10, 20, 80] |
| 2 | [6, 7, 8] | [10, 12, 13] | [10, 12, 13] |
| 3 | [1] | [-100] | [] |
| 4 | NULL | NULL | NULL |
+------+-----------------+-------------------------+------------------------------------------------------------------+
select *, array_filter(x->x%2=0,c_array2) from array_test2;
+------+-----------------+-------------------------+----------------------------------------------------------------------+
| id | c_array1 | c_array2 | array_filter(`c_array2`, array_map([x] -> x(0) % 2 = 0, `c_array2`)) |
+------+-----------------+-------------------------+----------------------------------------------------------------------+
| 1 | [1, 2, 3, 4, 5] | [10, 20, -40, 80, -100] | [10, 20, -40, 80, -100] |
| 2 | [6, 7, 8] | [10, 12, 13] | [10, 12] |
| 3 | [1] | [-100] | [-100] |
| 4 | NULL | NULL | NULL |
+------+-----------------+-------------------------+----------------------------------------------------------------------+
select *, array_filter(x->(x*(-10)>0),c_array2) from array_test2;
+------+-----------------+-------------------------+----------------------------------------------------------------------------+
| id | c_array1 | c_array2 | array_filter(`c_array2`, array_map([x] -> (x(0) * (-10) > 0), `c_array2`)) |
+------+-----------------+-------------------------+----------------------------------------------------------------------------+
| 1 | [1, 2, 3, 4, 5] | [10, 20, -40, 80, -100] | [-40, -100] |
| 2 | [6, 7, 8] | [10, 12, 13] | [] |
| 3 | [1] | [-100] | [-100] |
| 4 | NULL | NULL | NULL |
+------+-----------------+-------------------------+----------------------------------------------------------------------------+
select *, array_filter(x->x>0, array_map((x,y)->(x>y), c_array1,c_array2)) as res from array_test2;
+------+-----------------+-------------------------+--------+
| id | c_array1 | c_array2 | res |
+------+-----------------+-------------------------+--------+
| 1 | [1, 2, 3, 4, 5] | [10, 20, -40, 80, -100] | [1, 1] |
| 2 | [6, 7, 8] | [10, 12, 13] | [] |
| 3 | [1] | [-100] | [1] |
| 4 | NULL | NULL | NULL |
+------+-----------------+-------------------------+--------+