hive_collect_list的排序，取值_nanyangjx

最新推荐文章于 2025-07-24 11:11:36 发布

原创最新推荐文章于 2025-07-24 11:11:36 发布 · 6k 阅读

2 ·

CC 4.0 BY-SA版权

hive 同时被 2 个专栏收录

4 篇文章

订阅专栏

SQL

2 篇文章

订阅专栏

本文探讨了SQL中使用聚合函数和数组操作的高级技巧，包括如何利用sort_array、collect_list、find_in_set等函数进行数据处理，如获取时间戳列表的首尾值和PV值的极值，以及通过find_in_set定位最小和最大时间戳对应的PV值。

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;