group_concat在hive中如何表达
时间: 2023-09-08 17:13:09 浏览: 59
在Hive中,可以使用concat_ws函数结合collect_set或collect_list函数来实现类似于group_concat的功能。具体表达方式如下:
1. 使用concat_ws + collect_set + group by:
```
select id, concat_ws(',', collect_set(content)) as con_con, concat_ws(',', collect_set(comment)) as con_com
from db_name.test_tb
group by id;
```
2. 使用concat_ws + collect_list + group by:
```
select id, concat_ws(',', collect_list(content)) as con_con, concat_ws(',', collect_list(comment)) as con_com
from db_name.test_tb
group by id;
```
3. 使用concat_ws + collect_list + group by + row_number():
```
select id, concat_ws(',', collect_list(content)) as con_con, concat_ws(',', collect_list(comment)) as con_com, concat_ws(',', collect_list(cast(rn as string))) as con_rn
from (
select id, content, comment, row_number() over(partition by id order by content asc) as rn
from db_name.test_tb
)
group by id;
```
这些查询语句可以根据具体的需求和数据表结构进行调整和修改。
#### 引用[.reference_title]
- *1* *2* [Hive 实现group_concat](https://blog.csdn.net/z_ssyy/article/details/126300025)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^control,239^v3^insert_chatgpt"}} ] [.reference_item]
- *3* [hive中实现group_concat](https://blog.csdn.net/qq_36039236/article/details/115629639)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^control,239^v3^insert_chatgpt"}} ] [.reference_item]
[ .reference_list ]