ARRAY_SORTBY
描述
首先将 key 列升序排列，然后将 src 列按此顺序排序后的对应列做为结果返回; 如果输入数组 src 为 NULL，则返回 NULL。 如果输入数组 key 为 NULL，则直接返回 src 数组。 如果输入数组 key 元素包含 NULL, 则输出的排序数组会将 NULL 放在最前面。
语法
ARRAY_SORTBY(<src>, <key>)
ARRAY_SORTBY(<lambda>, <arr> [, ...])
参数
| 参数 | 说明 |
|---|---|
<lambda> | lambda 表达式，表达式中输入的参数为 1 个或多个，必须和后面的输入 array 列数量一致。在 lambda 中可以执行合法的标量函数，不支持聚合函数等。 |
<arr> | ARRAY 数组 |
返回值
返回一个排序后的 ARRAY 类型的结果
举例
-- setup
create table test_array_sortby(id int, c_array1 array<int>, c_array2 array<int>) distributed by hash(id) buckets 1 properties ("replication_num"="1");
insert into test_array_sortby values (0,null,[2]),(1,[1,2,3,4,5],[10,20,-40,80,-100]),(2,[6,7,8],[10,12,13]),(3,[1],[-100]),(4,null,null),(5,[3],null),(6,[1,2],[2,1]),(7,[null],[null]),(8,[1,2,3],[3,2,1]);
create table array_test2(id int, c_array1 array<int>, c_array2 array<int>) distributed by hash(id) buckets 1 properties ("replication_num"="1");
insert into array_test2 values (1,[1,2,3],[10,11,12]),(2,[4,3,5],[10,20,30]),(3,[-40,30,-100],[30,10,20]);
select array_sortby(['a','b','c'],[3,2,1]);
+----------------------------------------------------+
| array_sortby(ARRAY('a', 'b', 'c'), ARRAY(3, 2, 1)) |
+----------------------------------------------------+
| ['c', 'b', 'a'] |
+----------------------------------------------------+
select array_sortby([1,2,3,4,5],[10,5,1,20,80]);
+-------------------------------------------------------------+
| array_sortby(ARRAY(1, 2, 3, 4, 5), ARRAY(10, 5, 1, 20, 80)) |
+-------------------------------------------------------------+
| [3, 2, 1, 4, 5] |
+-------------------------------------------------------------+
select *,array_sortby(c_array1,c_array2) from test_array_sortby order by id;
+------+-----------------+-------------------------+--------------------------------------+
| id | c_array1 | c_array2 | array_sortby(`c_array1`, `c_array2`) |
+------+-----------------+-------------------------+--------------------------------------+
| 0 | NULL | [2] | NULL |
| 1 | [1, 2, 3, 4, 5] | [10, 20, -40, 80, -100] | [5, 3, 1, 2, 4] |
| 2 | [6, 7, 8] | [10, 12, 13] | [6, 7, 8] |
| 3 | [1] | [-100] | [1] |
| 4 | NULL | NULL | NULL |
| 5 | [3] | NULL | [3] |
| 6 | [1, 2] | [2, 1] | [2, 1] |
| 7 | [NULL] | [NULL] | [NULL] |
| 8 | [1, 2, 3] | [3, 2, 1] | [3, 2, 1] |
+------+-----------------+-------------------------+--------------------------------------+
select *, array_map((x,y)->(y+x),c_array1,c_array2) as arr_sum,array_sortby((x,y)->(y+x),c_array1,c_array2) as arr_sort from array_test2;
+------+-----------------+--------------+----------------+-----------------+
| id | c_array1 | c_array2 | arr_sum | arr_sort |
+------+-----------------+--------------+----------------+-----------------+
| 1 | [1, 2, 3] | [10, 11, 12] | [11, 13, 15] | [1, 2, 3] |
| 2 | [4, 3, 5] | [10, 20, 30] | [14, 23, 35] | [4, 3, 5] |
| 3 | [-40, 30, -100] | [30, 10, 20] | [-10, 40, -80] | [-100, -40, 30] |
+------+-----------------+--------------+----------------+-----------------+
