hive_collect_list的排序，取值_nanyangjx

select 
cookieid,
sort_array(collect_list(createtime_detail))[0] as time_listfrist,--拿到的list集合排序并且取最小值
sort_array(collect_list(createtime_detail))[size(collect_list(createtim`在这里插入代码片`e_detail))-1] as time_listend,--拿到的list集合排序并且取最大值
collect_list(pv)[0] as pv_min,--list不排序取第一个值
collect_list(pv)[size(collect_list(pv))-1] as pv_max --list不排序取最后一个值
from cookie
group by cookieid;

select cookieid,
--min(createtime_detail) as r,--求最小值
--find_in_set(min(createtime_detail),concat_ws(',',collect_list(cast(createtime_detail as string))))-1 as r1--求值在数组中的索引
collect_list(createtime_detail)[find_in_set(min(createtime_detail),concat_ws(',',collect_list(cast(createtime_detail as string))))-1] as time_listfrist,--求值
collect_list(createtime_detail)[find_in_set(max(createtime_detail),concat_ws(',',collect_list(cast(createtime_detail as string))))-1] as time_listend,
collect_list(pv)[find_in_set(min(createtime_detail),concat_ws(',',collect_list(cast(createtime_detail as string))))-1] as pv_createtime_detail_min,
collect_list(pv)[find_in_set(max(createtime_detail),concat_ws(',',collect_list(cast(createtime_detail as string))))-1] as pv_createtime_detail_max
from cookie
group by cookieid;