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

曾经终败给现在 2024-02-20 12:22 53阅读 0赞

**目录**

Hive / ClickHouse 行转列函数 collect\_set() / groupUniqArray() 入门

collect\_set()

groupUniqArray()

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. 示例** 假设我们有一个包含学生姓名及其对应的科目的表格。

<table> 
 <tbody> 
 <tr> 
 <td> 学生姓名 </td> 
 <td> 科目 </td> 
 </tr> 
 <tr> 
 <td> 张三 </td> 
 <td> 数学 </td> 
 </tr> 
 <tr> 
 <td> 张三 </td> 
 <td> 英语 </td> 
 </tr> 
 <tr> 
 <td> 李四 </td> 
 <td> 数学 </td> 
 </tr> 
 <tr> 
 <td> 李四 </td> 
 <td> 物理 </td> 
 </tr> 
 <tr> 
 <td> 李四 </td> 
 <td> 化学 </td> 
 </tr> 
 <tr> 
 <td> 王五 </td> 
 <td> 英语 </td> 
 </tr> 
 <tr> 
 <td> 使用 <code>collect_set()</code> 函数可以将同一个学生的科目转换为数组。 </td> 
 <td></td> 
 </tr> 
 </tbody> 
</table>

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

输出结果为：

### groupUniqArray() ###

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

sqlCopy codegroupUniqArray(column1, column2, ...)

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

<table> 
 <tbody> 
 <tr> 
 <td> 学生姓名 </td> 
 <td> 科目 </td> 
 <td> 成绩 </td> 
 </tr> 
 <tr> 
 <td> 张三 </td> 
 <td> 数学 </td> 
 <td> 80 </td> 
 </tr> 
 <tr> 
 <td> 张三 </td> 
 <td> 英语 </td> 
 <td> 90 </td> 
 </tr> 
 <tr> 
 <td> 李四 </td> 
 <td> 数学 </td> 
 <td> 85 </td> 
 </tr> 
 <tr> 
 <td> 李四 </td> 
 <td> 物理 </td> 
 <td> 70 </td> 
 </tr> 
 <tr> 
 <td> 李四 </td> 
 <td> 化学 </td> 
 <td> 95 </td> 
 </tr> 
 <tr> 
 <td> 王五 </td> 
 <td> 英语 </td> 
 <td> 88 </td> 
 </tr> 
 <tr> 
 <td> 使用 <code>groupUniqArray()</code> 函数可以将同一个学生的科目和成绩转换为数组。 </td> 
 <td></td> 
 <td></td> 
 </tr> 
 </tbody> 
</table>

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

输出结果为：

<table> 
 <tbody> 
 <tr> 
 <td> 学生姓名 </td> 
 <td> 科目成绩列表 </td> 
 </tr> 
 <tr> 
 <td> 张三 </td> 
 <td> [[数学, 80], [英语, 90]] </td> 
 </tr> 
 <tr> 
 <td> 李四 </td> 
 <td> [[数学, 85], [物理, 70], [化学, 95]] </td> 
 </tr> 
 <tr> 
 <td> 王五 </td> 
 <td> [[英语, 88]] </td> 
 </tr> 
 <tr> 
 <td> 以上就是在 Hive 和 ClickHouse 中使用 <code>collect_set()</code> 和 <code>groupUniqArray()</code> 函数实现行转列操作的介绍和示例。通过这些函数，我们可以方便地进行数据聚合和分析工作。 </td> 
 <td></td> 
 </tr> 
 </tbody> 
</table>

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

<table> 
 <tbody> 
 <tr> 
 <td> 订单ID </td> 
 <td> 用户ID </td> 
 <td> 商品名称 </td> 
 </tr> 
 <tr> 
 <td> 1 </td> 
 <td> 1001 </td> 
 <td> 商品A </td> 
 </tr> 
 <tr> 
 <td> 1 </td> 
 <td> 1001 </td> 
 <td> 商品B </td> 
 </tr> 
 <tr> 
 <td> 1 </td> 
 <td> 1002 </td> 
 <td> 商品A </td> 
 </tr> 
 <tr> 
 <td> 2 </td> 
 <td> 1002 </td> 
 <td> 商品C </td> 
 </tr> 
 <tr> 
 <td> 2 </td> 
 <td> 1003 </td> 
 <td> 商品A </td> 
 </tr> 
 <tr> 
 <td> 2 </td> 
 <td> 1003 </td> 
 <td> 商品C </td> 
 </tr> 
 <tr> 
 <td> 使用 collect_set() 函数可以将每个用户购买的商品名称转换为一个数组: </td> 
 <td></td> 
 <td></td> 
 </tr> 
 </tbody> 
</table>

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

输出结果为:

<table> 
 <tbody> 
 <tr> 
 <td> 用户ID </td> 
 <td> 购买的商品列表 </td> 
 </tr> 
 <tr> 
 <td> 1001 </td> 
 <td> ["商品A", "商品B"] </td> 
 </tr> 
 <tr> 
 <td> 1002 </td> 
 <td> ["商品A", "商品C"] </td> 
 </tr> 
 <tr> 
 <td> 1003 </td> 
 <td> ["商品A", "商品C"] </td> 
 </tr> 
 </tbody> 
</table>

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

<table> 
 <tbody> 
 <tr> 
 <td> 用户ID </td> 
 <td> 技能 </td> 
 <td> 级别 </td> 
 </tr> 
 <tr> 
 <td> 1001 </td> 
 <td> 技能A </td> 
 <td> 初级 </td> 
 </tr> 
 <tr> 
 <td> 1001 </td> 
 <td> 技能B </td> 
 <td> 中级 </td> 
 </tr> 
 <tr> 
 <td> 1002 </td> 
 <td> 技能A </td> 
 <td> 中级 </td> 
 </tr> 
 <tr> 
 <td> 1002 </td> 
 <td> 技能C </td> 
 <td> 高级 </td> 
 </tr> 
 <tr> 
 <td> 1003 </td> 
 <td> 技能A </td> 
 <td> 初级 </td> 
 </tr> 
 <tr> 
 <td> 1003 </td> 
 <td> 技能C </td> 
 <td> 高级 </td> 
 </tr> 
 <tr> 
 <td> 使用 groupUniqArray() 函数可以将每个用户的技能和级别转换为一个数组： </td> 
 <td></td> 
 <td></td> 
 </tr> 
 </tbody> 
</table>

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

输出结果为：

<table> 
 <tbody> 
 <tr> 
 <td> 用户ID </td> 
 <td> 技能级别列表 </td> 
 </tr> 
 <tr> 
 <td> 1001 </td> 
 <td> [["技能A", "初级"], ["技能B", "中级"]] </td> 
 </tr> 
 <tr> 
 <td> 1002 </td> 
 <td> [["技能A", "中级"], ["技能C", "高级"]] </td> 
 </tr> 
 <tr> 
 <td> 1003 </td> 
 <td> [["技能A", "初级"], ["技能C", "高级"]] </td> 
 </tr> 
 <tr> 
 <td> 以上就是在 Hive 和 ClickHouse 中使用 collect_set() 和 groupUniqArray() 函数实现行转列操作的介绍和示例。通过这些函数，可以方便地进行数据聚合和分析工作。 </td> 
 <td></td> 
 </tr> 
 </tbody> 
</table>

### 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。 这些函数都可以用于数据的聚合和分析，具体选择哪个函数取决于实际需求和所使用的数据库。