ARRAY_SORT

功能

若不指定lambda函数，则对数组元素按升序排序。否则按照lambda函数进行排序。

语法

• ARRAY_SORT(arr)
• ARRAY_SORT(lambda, arr)

参数

• lambda: lambda 表达式，用于定义排序规则，返回值应为-1， 0， 1（分别表示小于，等于，大于）。
• arr：ARRAY<T>，T 可为数值、布尔、字符串、日期时间、IP 等。

返回值

• 若不包含lambda表达式
  • 返回与输入同类型的 ARRAY<T>。
  • NULL 元素放在返回的数组最前面。
• 若包含lambda表达式，按照lambda表达式进行排序，返回与输入同类型的 ARRAY<T>。

使用说明

• 若不包含lambda表达式
  • 若输入为 NULL，返回 NULL; 若输入为空数组 []，返回空数组。
  • ARRAY_SORT 是升序排序，ARRAY_REVERSE_SORT 是降序排序。
• 若包含lambda表达式
  • lambda表达式的值应为-1，0，1，若存在NULL值，应明确说明NULL值应排在数组前还是数组后。

示例

1. 基本: NULL 元素放在返回的数组最前面

SELECT ARRAY_SORT([2,1,3,null]);

+--------------------------+
| ARRAY_SORT([2,1,3,null]) |
+--------------------------+
| [null, 1, 2, 3]          |
+--------------------------+

2. 输入为 NULL，返回 NULL; 输入为空数组 []，返回空数组。

SELECT ARRAY_SORT(NULL);

+------------------+
| ARRAY_SORT(NULL) |
+------------------+
| NULL             |
+------------------+

SELECT ARRAY_SORT([]);

+----------------+
| ARRAY_SORT([]) |
+----------------+
| []             |
+----------------+

3. 使用lambda表达式

SELECT array_sort((x, y) -> IF(x < y, 1, IF(x = y, 0, -1)), [3, 2, 5, 1, 2]);

+-----------------------------------------------------------------------+
| array_sort((x, y) -> IF(x < y, 1, IF(x = y, 0, -1)), [3, 2, 5, 1, 2]) |
+-----------------------------------------------------------------------+
| [5, 3, 2, 2, 1]                                                       |
+-----------------------------------------------------------------------+

SELECT array_sort((x, y) -> IF(x < y, 1, IF(x = y, 0, -1)), ['bc', 'ab', 'dc']);

+--------------------------------------------------------------------------+
| array_sort((x, y) -> IF(x < y, 1, IF(x = y, 0, -1)), ['bc', 'ab', 'dc']) |
+--------------------------------------------------------------------------+
| ["dc", "bc", "ab"]                                                       |
+--------------------------------------------------------------------------+

SELECT array_sort((x, y) -> CASE WHEN x IS NULL THEN -1
                              WHEN y IS NULL THEN 1
                              WHEN x < y THEN 1
                              WHEN x = y THEN 0
                              ELSE -1 END,
                              [3, 2, null, 5, null, 1, 2]);

+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| array_sort((x, y) -> CASE WHEN x IS NULL THEN -1
                              WHEN y IS NULL THEN 1
                              WHEN x < y THEN 1
                              WHEN x = y THEN 0
                              ELSE -1 END,
                              [3, 2, null, 5, null, 1, 2]) |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| [null, null, 5, 3, 2, 2, 1]                                                                                                                                                                                                                                                                                |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

SELECT array_sort((x, y) -> CASE WHEN x IS NULL THEN 1
                              WHEN y IS NULL THEN -1
                              WHEN x < y THEN 1
                              WHEN x = y THEN 0
                              ELSE -1 END,
                              [3, 2, null, 5, null, 1, 2]);

+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| array_sort((x, y) -> CASE WHEN x IS NULL THEN 1
                              WHEN y IS NULL THEN -1
                              WHEN x < y THEN 1
                              WHEN x = y THEN 0
                              ELSE -1 END,
                              [3, 2, null, 5, null, 1, 2]) |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| [5, 3, 2, 2, 1, null, null]                                                                                                                                                                                                                                                                                |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

SELECT array_sort((x, y) -> IF(length(x) < length(y), -1,
                            IF(length(x) = length(y), 0, 1)),
                            ['a', 'abcd', 'abc']);

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| array_sort((x, y) -> IF(length(x) < length(y), -1,
                            IF(length(x) = length(y), 0, 1)),
                            ['a', 'abcd', 'abc']) |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ["a", "abc", "abcd"]                                                                                                                                               |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+

SELECT array_sort((x, y) -> IF(cardinality(x) < cardinality(y), -1,
                            IF(cardinality(x) = cardinality(y), 0, 1)),
                            [[2, 3, 1], [4, 2, 1, 4], [1, 2]]);

+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| array_sort((x, y) -> IF(cardinality(x) < cardinality(y), -1,
                            IF(cardinality(x) = cardinality(y), 0, 1)),
                            [[2, 3, 1], [4, 2, 1, 4], [1, 2]]) |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| [[1, 2], [2, 3, 1], [4, 2, 1, 4]]                                                                                                                                                                   |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+