Hive 利用collect

Hive 利用collect

工作中遇到的一个数据治理问题：治理出某地常住人口的地址变更内容，并按时间升序后合并展示。

举个例子，小明2019年住在A地，2020年搬到B地，2021年搬到C地，小华2019年住在E地，2021年住在F地，原始数据表（human_address）为

namedetail_address_namelast_modification_time小明A2019小明B2020小明C2021小华E2019小华F2021

治理后的展示结果为

nameaddress_change小明A,B,C小华E,F

有兴趣的朋友可以先试试，不急着往后看解析。

解决思路为：

先利用row_number开窗函数按时间先后对地址进行排序，然后利用concat函数将序号和地址进行拼接，拼接后利用collect_set函数、sort_array函数和concat_ws函数进行排序合并，合并后再利用regexp_replace函数将多余的序号去掉。

有几个注意点需要提一下：

• rn字段为int类型，需要转换为string类型后才能用concat拼接。
• 为什么要对rn值为1,2,3…,9的前面加一个0呢，这是因为如果地址数量大于10，不加0的话，结果显示会有问题，比如有12个地址，在不去除序号时，显示的结果会是11M,12N,1A,2B……这并不是我们想要的。当然，就例子的数据不加0也是OK的。
• sort_array函数默认升序，如果要降序，需要将属性设为false。
• sort_array函数好像并不能按具体某字段进行排序，如果可以，sql代码就不用这么麻烦了。
• 去掉序号不能一步到位，先去掉最前面的序号，再去掉逗号后的序号。

具体实现代码如下：

select
name,
regexp_replace(
	regexp_replace(
		concat_ws(',',
			sort_array(
				collect_set(
					concat(case when length(rn) = 1 then concat('0',cast(rn as string)) else cast(rn as string) end,detail_address_name)
							)
					  )
				 )
	,'^[0-9]*','')		              
,',[0-9]*',',') as address_change		
from
(
	select
	name,
	detail_address_name,
	row_number() over(partition by name order by last_modification_time asc) as rn			
	from human_address
)a
group by name;

如果大佬有简单的方法可以实现，还望指点一二。
有任何问题也欢迎留言交流，互相学习，共同进步。