collect_set 分组数据合并为数组

HIVE 中将分组中的一列信息组织成数组形式，类似的还有 collect_list。

示例表：

CREATE TABLE test(
`id` BIGINT,
`name` STRING,
`age` BIGINT
) COMMENT '测试表'
PARTITIONED BY (dt STRING);

INSERT INTO test values(1, 'zhangsan', 28, '2022-10-20');
INSERT INTO test values(2, 'lisi', 29, '2022-10-20');
INSERT INTO test values(3, 'wangwu', 39, '2022-10-21');
INSERT INTO test values(4, 'zhaoliu', 34, '2022-10-20');
INSERT INTO test values(5, 'chenqi', 21, '2022-10-21');

查询：

hive> select collect_set(concat(name, '-', age)) nameAndAge from test group by dt;

nameandage                            |
--------------------------------------+
["zhangsan-28","lisi-29","zhaoliu-34"]|
["wangwu-39","chenqi-21"]             |

取数组的第一个值：

hive> select nameAndAge[0]

from (

select collect_set(concat(name, '-', age)) nameAndAge from test group by dt

) new

zhangsan-28|
wangwu-39  |