array_sortby

SinceVersion 2.0

array_sortby

description

Syntax

  1. ARRAY<T> array_sortby(ARRAY<T> src,Array<T> key)
  2. ARRAY<T> array_sortby(lambda,array....)

首先将key列升序排列，然后将src列按此顺序排序后的对应列做为结果返回; 如果输入数组src为NULL，则返回NULL。 如果输入数组key为NULL，则直接返回src数组。 如果输入数组key元素包含NULL, 则输出的排序数组会将NULL放在最前面。

notice

仅支持向量化引擎中使用

example

  1. mysql [test]>select array_sortby(['a','b','c'],[3,2,1]);
  2. +----------------------------------------------------+
  3. | array_sortby(ARRAY('a', 'b', 'c'), ARRAY(3, 2, 1)) |
  4. +----------------------------------------------------+
  5. | ['c', 'b', 'a']                                    |
  6. +----------------------------------------------------+
  8. mysql [test]>select array_sortby([1,2,3,4,5],[10,5,1,20,80]);
  9. +-------------------------------------------------------------+
  10. | array_sortby(ARRAY(1, 2, 3, 4, 5), ARRAY(10, 5, 1, 20, 80)) |
  11. +-------------------------------------------------------------+
  12. | [3, 2, 1, 4, 5]                                             |
  13. +-------------------------------------------------------------+
  15. mysql [test]>select *,array_sortby(c_array1,c_array2) from test_array_sortby order by id;
  16. +------+-----------------+-------------------------+--------------------------------------+
  17. | id   | c_array1        | c_array2                | array_sortby(`c_array1`, `c_array2`) |
  18. +------+-----------------+-------------------------+--------------------------------------+
  19. |    0 | NULL            | [2]                     | NULL                                 |
  20. |    1 | [1, 2, 3, 4, 5] | [10, 20, -40, 80, -100] | [5, 3, 1, 2, 4]                      |
  21. |    2 | [6, 7, 8]       | [10, 12, 13]            | [6, 7, 8]                            |
  22. |    3 | [1]             | [-100]                  | [1]                                  |
  23. |    4 | NULL            | NULL                    | NULL                                 |
  24. |    5 | [3]             | NULL                    | [3]                                  |
  25. |    6 | [1, 2]          | [2, 1]                  | [2, 1]                               |
  26. |    7 | [NULL]          | [NULL]                  | [NULL]                               |
  27. |    8 | [1, 2, 3]       | [3, 2, 1]               | [3, 2, 1]                            |
  28. +------+-----------------+-------------------------+--------------------------------------+
  30. mysql [test]>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;
  31. +------+-----------------+--------------+----------------+-----------------+
  32. | id   | c_array1        | c_array2     | arr_sum        | arr_sort        |
  33. +------+-----------------+--------------+----------------+-----------------+
  34. |    1 | [1, 2, 3]       | [10, 11, 12] | [11, 13, 15]   | [1, 2, 3]       |
  35. |    2 | [4, 3, 5]       | [10, 20, 30] | [14, 23, 35]   | [4, 3, 5]       |
  36. |    3 | [-40, 30, -100] | [30, 10, 20] | [-10, 40, -80] | [-100, -40, 30] |
  37. +------+-----------------+--------------+----------------+-----------------+

keywords

ARRAY, SORT, ARRAY_SORTBY