hive sql 多行转为一行&&collect_set&&collect_list的区别

最新推荐文章于 2025-03-01 22:47:36 发布

原创

最新推荐文章于 2025-03-01 22:47:36 发布 · 604 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#collect_set #collect_list #hive sql

本文介绍了Hive SQL中的collect_list和collect_set函数，用于将多行数据转换为一行。collect_list保留顺序但可能包含重复值，而collect_set则会去除重复值但顺序不保证。

1.collect_list 多行转一行，有序不去重

数据

select 123 uid,'a' name, int(1) type
from t
union all
select 123 uid,'b' name, int(2) type
from t
union all
select 123 uid,'c' name, int(3) type
from t
union all
select 123 uid,'d' name, int(4) type
from t
union all
select 122 uid,'a' name, int(1) type
from t
union all
select 122 uid,'b' name, int(2) type
from t
union all
select 122 uid,'c' name, int(3) type
from t
union all
select 124 uid,'a' name, int(1) type
from t
union all
select 124 uid,'b' name, int(3) type
from t

表

TEMP t= select uid,name from temp_2019123102 distribute by uid sort by type 
OUTPUT select uid,concat_ws('->',collect_list(name)) names from t group by uid