mysql中的group_concat分组连接功能相当强大,可以先分组再连接成字符串,还可以进行排序连接。但是hive中并没有这个函数,那么hive中怎么实现这个功能呢?
这里要用到:concat_ws函数和collect_list、collect_set 函数。
- 建立测试表(无分区表):
create table if not exists db_name.test_tb(id string,content string,comment string) row format delimited fields terminated by '\1' stored as textfile
- 插入几条数据:
insert into db_name.test_tb values('1','Tom','测试1')
insert into db_name.test_tb values('1','Bob','测试2')
insert into db_name.test_tb values('1','Wendy','测试3')
insert into db_name.test_tb values('2','Bob','测试22')
insert into db_name.test_tb values('2','Tom','测试11')

- 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

结果:无序且不对应(con_con与con_com的位置) —— 但是注意 collect_set会将重复的数据删除,因为集合的性质。
每次运行连接的结果顺序可能不同。
[En]
The order of the results for each run of the connection may be different.
- 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

结果:对应(con_con与con_com的位置)但无序。
- 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 db_name.test_tb
(
select
id,
content,
comment,
row_number() over(partition by id order by content asc) as rn
from db_name.test_tb
)
group by id

结果:对应(con_con与con_com的位置)且有序。
https://www.cnblogs.com/zhangqian27/p/12836126.html
https://blog.csdn.net/changzoe/article/details/81181820
Original: https://www.cnblogs.com/qi-yuan-008/p/13583959.html
Author: 落日峡谷
Title: hive中实现group_concat
原创文章受到原创版权保护。转载请注明出处:https://www.johngo689.com/6869/
转载文章受原作者版权保护。转载请注明原作者出处!