hive sql 分组拼接同一列字符串和hive sql 分组多行拼接为一行的实现方式

下面图示的结果用hive sql怎么实现？换句话描述就是：hive sql 如何实现分组后拼接同一列的数据呢？

其实，拼接几列字符串并不难，用concat函数就可以实现，concat(col1,col2,col3)  就会将第一列，第二列，第三列字符拼接起来，sql代码：

select concat('a','_','b','_','c') as cct;

sql执行结果：

需要注意的是：concat 函数在连接字符串的时候，只要其中一个是NULL，那么将返回NULL，sql代码：

select concat('a','_','b','_',null) cct;

 sql执行结果：

 但如果要拼接一列中的数据呢？

构建临时表，sql代码：

1.创建测试表
create table tmp.tmp1(id int comment 'ID',name string comment '人名');

2.插入测试数据
insert into tmp.tmp1
select 
1 as book_name,"韩立" as character_name
union all
select
1 as book_name,"厉飞雨" as character_name
union all
select
1 as book_name,"南宫婉" as character_name
union all
select
1 as book_name,"紫灵仙子" as character_name
union all
select
2 as book_name,"银月" as character_name
union all
select
2 as book_name,"元瑶" as character_name
union all
select
2 as book_name,"董萱儿" as character_name
union all
select
3 as book_name,"墨彩环" as character_name
union all
select
3 as book_name,"冰凤" as character_name
;

3.查看测试表数据
select * from tmp.tmp1;

sql执行结果：

实现方式1：

-- collect_set 只能返回不重复的集合
select id,concat_ws('/',collect_set(name)) as name from tmp.tmp1 group by id;


-- collect_list 返回带重复的集合
SELECt id,concat_ws('/',collect_list(name)) as name from tmp.tmp1 group by id;

sql执行结果：

 实现方式2：

-- sql有待网友验证，我用的工具不支持group_concat函数，一直报错
select id,group_concat(distinct(name),'/') as name from tmp.tmp1 group by id;

sql执行结果：