多行转一行

原创已于 2023-12-21 10:08:40 修改 · 1.1k 阅读

9 ·

CC 4.0 BY-SA版权

文章标签：

#sql

于 2023-12-21 09:50:31 首次发布

本文介绍了Hive中的collect_list和collect_set函数，以及Doris中的array_agg函数，用于对表字段分组并合并其他字段，特别关注了去重和排序的特性。

Hive

对表中某些字段分组，将其他字段合并。在Hive中通常可以使用collect_list()或collect_set()来实现，通常与concat_ws()函数连用
collect_list()----不去重
collect_set()----去重

语法

复制

collect_set(expr) [FILTER ( WHERE cond ) ]

还可以使用 OVER 子句将此函数作为窗口函数调用。

参数说明

expr：一个任意类型的表达式。
cond：一个可选的布尔表达式，可筛选用于聚合的行。

返回值说明

参数类型的 ARRAY。

数组中元素的顺序不确定。排除 NULL 值。

示例

hive> select * from t;
+------+------+------+
| a    | name | pv   |
+------+------+------+
|   11 |      |   33 |
|    2 | NULL |  334 |
|    1 | fzh  |    3 |
|    1 | fff  |    4 |
|    1 | fff  |    5 |
+------+------+------+

hive> select a, concat_ws(',', collect_set(pv)) as collect_set_pv from t group by a;
+------+---------------+
| a    | collect_set_pv |
+------+---------------+
|   11 | [33]           |
|    2 | [334]          |
|    1 | [3,4,5]        |
+------+---------------+

Doris

对表中某些字段分组，将其他字段合并。在Doris中通常可以使用array_agg()实现

语法

ARRAY_AGG(col [order by col0 [desc | asc] [nulls first | nulls last] ...])

参数说明

col：需要进行数值串联的列。支持的数据类型为 BOOLEAN、TINYINT、SMALLINT、INT、BIGINT、LARGEINT、FLOAT、DOUBLE、VARCHAR、CHAR、DATETIME、DATE、ARRAY (3.1 及以后)、MAP (3.1 及以后)、STRUCT (3.1 及以后)。
col0: 排序列，决定 col 中元素的顺序。可以有多个排序列。
[desc | asc]: 对数组元素进行排序时，是基于 col0 的升序还是降序进行排列。默认升序。
[nulls first | nulls last]: null 值排在元素最前面还是最后面。

返回值说明

返回值的数据类型为 ARRAY。

注意事项

如果不指定 ORDER BY，数组中元素的顺序是随机的，不能保证与原来列值的顺序相同。
返回数组中元素的类型与 col 类型一致。
如果没有满足条件的输入值，返回 NULL。

示例

mysql> select * from t;
+------+------+------+
| a    | name | pv   |
+------+------+------+
|   11 |      |   33 |
|    2 | NULL |  334 |
|    1 | fzh  |    3 |
|    1 | fff  |    4 |
|    1 | fff  |    5 |
+------+------+------+

mysql> select a, array_agg(pv) from t group by a;
+------+---------------+
| a    | array_agg(pv) |
+------+---------------+
|   11 | [33]          |
|    2 | [334]         |
|    1 | [3,4,5]       |
+------+---------------+