最近在项目中清洗了一些埋点的数据,要求是将一张表中特定字段的行进行合并,并且不对重复的数据进行去重如下,数据形式如下,要对from字段进行进行合并:
一开始用collect_set函数实现,发现对字段进行去重了,后来发现collect_list函数可以实现,现将两者的区别总结如下:
1、concat_ws和collect_set()函数实现(对某列进行去重)
其作用是将多行某些列的多行进行去重合并,并通过&符号进行连接,代码如下
select subtype
,concat_ws('&',collect_set(cast(from as string))) from
,concat_ws('&',collect_set(cast(id as string))) id
,concat_ws('&',collect_set(cast(name as string)))name
,concat_ws('&',collect_set(cast(type as string))) type
from aaaaaaa
group by subtype;
2、concat_ws和collect_list()函数实现(对某列不进行去重)
select subtype
,concat_ws('&',collect_list(cast(from as string))) from
,concat_ws('&',collect_list(cast(id as string))) id
,concat_ws('&',collect_list(cast(name as string)))name
,concat_ws('&',collect_list(cast(type as string))) type
from aaaaaaa
group by subtype;
原文:https://blog.csdn.net/weixin_37536446/article/details/80597480