Hive / ClickHouse 行转列函数 collect_set() / groupUniqArray() 入门

Hive / ClickHouse 行转列函数 collect_set() / groupUniqArray() 入门

在数据处理和分析中，我们经常会遇到需要将一行数据转换为多列的情况。在 Hive 和 ClickHouse 中，可以使用 ​​collect_set()​​ 和 ​​groupUniqArray()​​ 函数来实现行转列操作。

collect_set()

1. 功能说明 ​​collect_set()​​ 函数用于将一列的数据转换为一个无重复元素的数组。 2. 语法

sqlCopy codecollect_set(column_name)

3. 示例 假设我们有一个包含学生姓名及其对应的科目的表格。

sqlCopy codeSELECT
    学生姓名,
    collect_set(科目) AS 科目列表
FROM
    学生成绩表
GROUP BY
    学生姓名;

输出结果为：

groupUniqArray()

1. 功能说明 ​​groupUniqArray()​​ 函数用于将多列数据转换为一个无重复元素的数组。 2. 语法

sqlCopy codegroupUniqArray(column1, column2, ...)

3. 示例 假设我们有一个包含学生姓名、科目和对应成绩的表格。

sqlCopy codeSELECT
    学生姓名,
    groupUniqArray(科目, 成绩) AS 科目成绩列表
FROM
    学生成绩表
GROUP BY
    学生姓名;

输出结果为：

假设我们有一个订单表，记录每个用户购买的商品信息。

sqlCopy codeSELECT
    用户ID,
    collect_set(商品名称) AS 购买的商品列表
FROM
    订单表
GROUP BY
    用户ID;

输出结果为:

假设我们有一个用户表，记录每个用户的技能和对应的级别。

sqlCopy codeSELECT
    用户ID,
    groupUniqArray(技能, 级别) AS 技能级别列表
FROM
    用户表
GROUP BY
    用户ID;

输出结果为：

collect_set() 函数的缺点：

1. 不保留原始数据的顺序：collect_set() 函数将数据转换为一个无重复元素的数组，但不保留原始数据的顺序。这对于一些需要按照特定顺序分析数据的场景可能不适用。
2. 数组类型限制：collect_set() 函数将数据转换为一个数组，但数组中的元素必须是相同类型的。如果原始数据中存在不同类型的元素，则无法正确转换。
3. 只能应用于单列数据：collect_set() 函数只能将一列数据转换为一个数组，无法处理多列数据转换的需求。

groupUniqArray() 函数的缺点：

1. 只能应用于多列数据转换：groupUniqArray() 函数是将多列数据转换为一个无重复元素的数组，无法处理单列数据转换的需求。
2. 数组类型限制：与 collect_set() 类似，groupUniqArray() 函数要求转换后的数组中的元素必须是相同类型的。

类似的函数：

1. collect_list() 函数：与 collect_set() 类似，collect_list() 函数用于将一列数据转换为一个数组，但不去重。
2. array_agg() 函数：在 PostgreSQL 中，array_agg() 函数可以将一列数据转换为一个数组，并且可以选择是否去重。
3. pivot() 函数：在 SQL 中，pivot() 函数可以将一列数据透视为多列数据，类似于将行转列的功能，但需要使用动态 SQL。 这些函数都可以用于数据的聚合和分析，具体选择哪个函数取决于实际需求和所使用的数据库。