多行转一行

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]       |
+------+---------------+