COLLECT_SET

SinceVersion 1.2.0

COLLECT_SET

description

Syntax

ARRAY<T> collect_set(expr[,max_size])

返回一个对expr去重后的数组。可选参数max_size，通过设置该参数能够将结果数组的大小限制为 max_size 个元素。 得到的结果数组中不包含NULL元素，数组中的元素顺序不固定。该函数具有别名group_uniq_array。

notice

1. 仅支持向量化引擎中使用

example

1. mysql> set enable_vectorized_engine=true;

3. mysql> select k1,k2,k3 from collect_set_test order by k1;
4. +------+------------+-------+
5. | k1   | k2         | k3    |
6. +------+------------+-------+
7. |    1 | 2023-01-01 | hello |
8. |    2 | 2023-01-01 | NULL  |
9. |    2 | 2023-01-02 | hello |
10. |    3 | NULL       | world |
11. |    3 | 2023-01-02 | hello |
12. |    4 | 2023-01-02 | doris |
13. |    4 | 2023-01-03 | sql   |
14. +------+------------+-------+

16. mysql> select collect_set(k1),collect_set(k1,2) from collect_set_test;
17. +-------------------------+--------------------------+
18. | collect_set(`k1`)       | collect_set(`k1`,2)      |
19. +-------------------------+--------------------------+
20. | [4,3,2,1]               | [1,2]                    |
21. +----------------------------------------------------+

23. mysql> select k1,collect_set(k2),collect_set(k3,1) from collect_set_test group by k1 order by k1;
24. +------+-------------------------+--------------------------+
25. | k1   | collect_set(`k2`)       | collect_set(`k3`,1)      |
26. +------+-------------------------+--------------------------+
27. |    1 | [2023-01-01]            | [hello]                  |
28. |    2 | [2023-01-01,2023-01-02] | [hello]                  |
29. |    3 | [2023-01-02]            | [world]                  |
30. |    4 | [2023-01-02,2023-01-03] | [sql]                    |
31. +------+-------------------------+--------------------------+

keywords

COLLECT_SET,GROUP_UNIQ_ARRAY,COLLECT_LIST,ARRAY