Hive SQL 的 collect_list 和 collect_set 函数

Hive SQL 提供了数据分组后多个聚合方式，其中 collect_list 和 collect_set 是最为常用的数据集合方式（collect），它们都可以将分组数据排成列表，区别在于 collect_set 会对组内的元素去重。

语法

collect_list 会返回分组后，此组内包含重复项的对象列表，此方法从 Hive 0.13.0 开始支持。

-- 将 colname 指定的列值聚合为一个数组
array collect_list(<colname>);
-- 将 colname 指定的列值聚合为一个无重复元素的数组
array collect_set(<colname>)

语法为：

SELECT
    a,
    collect_list(b) as list
FROM
    tb
group by
    a

在表 tb 中，a 列是被分组的列，a 列对应的 b 列元素会被返回成一个数组（array）数据类型，可以起别名为 list，list 具备数组 array 的所有特性，比如可以做切片索引 list[0]。

collect_set 的语法的 collect_list 的语法相同，不过会去掉重复的元素：

SELECT
    a,
    collect_set(b) as set_b
FROM
    tb
group by
    a

这两个方法可以实现将分组内的不同行转为一行。

理解

如果对 Python 的数据类型有所了解的话，就非常容易理解 collect_list 和 collect_set 的区别（见Python的数据类型），可以把它们理解成 list（列表）和 set（集合），这两个基本的数据类型的区分特点就是 list 元素支持重复 set 不能重复。

案例

有以下数据：

'''
class|name|
-----|----|
    1|张涛  |
    2|王琳  |
    1|赵丹丹 |
    2|李成  |
    1|张涛  |
'''

我们将数据中的班级（class）列进行分组，并用 collect_list 将本班的名单显示对应在班级行中：

SELECT
    class,
    collect_list(name) as list
FROM
    students
group by class

运行后的结果为如下，我看看到同一组的元素形成了一个数组：

'''
class|list             |
-----|-----------------|
    1|[张涛,赵丹丹,张涛] |
    2|[王琳,李成]       |
'''

同时，我们注意到 class 1 中的「张涛」忠实地出现了两次，如果只想保留一个的话可以使用 collect_set：

SELECT
    class,
    collect_set(name) as set_name
FROM
    students
group by class

这样，结果就只保留一个值，「张涛」仅出现一次：

'''
class|set_name    |
-----|------------|
    1|[张涛,赵丹丹] |
    2|[王琳,李成]   |
'''

突破行数限制

有些数据查询平台在设计时，为了数据安全、性能等原因会限制 SQL 查询数据的返回条数，那么我们可以利用 collect_list 对数据进行分组将同组数据折叠在一行中，导出数据后，再利用 Excel、Python 等其他工具恢复原样，希望你能理解我说的是什么。

作为 array 处理

collect_list 和 collect_set 返回的数据是一个 array 结构的数据，因此支持 array 的相差操作，比如，我们只希望返回每组的第一条，则可以：

SELECT
    class,
    collect_list(name)[0] as first
FROM
    students
group by class

其他的用法可以参考 array 类型可用的操作方法。

不分组使用

在不用 group by 分组的情况下可以直接筛选数据使用 collect_list 和 collect_set，返回一个数组：

SELECT collect_list(name) FROM students where class in (1,2);
SELECT collect_set(name) FROM students

DISTINCT 的使用

我们知道 DISTINCT 可以对数据去重，那么 collect_list 配合 DISTINCT 可以实现 collect_set 的功能，即以下两行代码执行出的元素内容是一样的：

SELECT collect_list(DISTINCT name) FROM students;
SELECT collect_set(name) FROM students

但，元素的顺序可能是不一样的。

元素排序

如果需要 array 中的元素保持一定的顺序，可以使用 sort_array 函数：

select sort_array(collect_set(col)) AS col

sort_array(Array<T> a) 根据数组元素的自然顺序按升序对输入数组排序并返回它。