hive如何取出数据的前几个元素

今天遇到个问题，例如selectcompany_name,concat_ws(',',collect_set(contract_num)) contract_numsfrom tablegroup by contract_num如果这个contract_nums数量有100个，我只显示5个。那么怎么办呢？1.基础版select concat_ws(",",array(array('1','2','

cclovezbf

5418人浏览 · 2022-02-21 14:55:32

cclovezbf · 2022-02-21 14:55:32 发布

今天遇到个问题，例如

select

company_name,concat_ws(',',collect_set(contract_num)) contract_nums

from table

group by contract_num

如果这个contract_nums数量有100个，我只显示5个。

那么怎么办呢？

1.基础版


select concat_ws(",",
	array(
		array('1','2','3','4','5','6','7','8','9','10','11')[0],
		array('1','2','3','4','5','6','7','8','9','10','11')[1],
		array('1','2','3','4','5','6','7','8','9','10','11')[2],
		array('1','2','3','4','5','6','7','8','9','10','11')[3],
		array('1','2','3','4','5','6','7','8','9','10','11')[4] 
	)
)

2.函数版

select substring_index(concat_ws(",",array('1','2','3','4','5','6','7','8','9','10','11')),',',5)

3. row_number 版本

select 
company_name ,concat_ws(",",collect_list(contract_num))contract_nums
from (
select company_name,contract_num,
row_number() over(partition by company_name) rn 
from (
select 'cclovezbf' as company_name, array('1','2','3','4','5','6','7','8','9','10','11') contract_nums
)t lateral  view explode(contract_nums) tmp as contract_num
)t 
where rn <5
group by company_name

这个直接跑不出来，报错 IllegalArgumentException Size requested for unknown type: java.util.Collection:

因为collect_list()这个函数和sum 有点不一样，好像是实现类的方法种要计算元素的个数，换成sum也差不多可以看结果了。

上述只是提供思路，实际执行还是看个人