★ FEATURED ARTICLE “collect_set 如何有序”这个问题几乎是我这些年被问得最多的 SQL 问题之一。每次我都会先反问一句你要的“有序”到底是什么序如果回答是“就是数据原本的插入顺序”那问题会麻烦一些如果回答是“按某个字段排个序就行”那反而简单。先说结论如果你是直接collect_set然后指望它自己按某个顺序吐出来基本不可能。collect_set的无序不是 Hive 的 Bug而是 Set 这种数据结构的语义决定的。想要有序必须在聚合之前就为每个元素准备好“顺序标签”或者干脆换用collect_list配合预处理。这篇文章把我踩过的坑和三种真正能落地的解法一起讲清楚。我会用一个非常典型的业务场景贯穿全文一张订单商品明细表一个订单有多条商品记录商品可能重复出现。需求是——每个订单去重后的商品 ID按商品创建时间升序输出成一个数组。比如订单o1有记录商品 A10:00、B10:05、C09:59、A10:30期望结果是[C, A, B]。所有示例 SQL 都围绕这个场景写方便你直接套用。1. 先说结论collect_set 生而无序这是语义问题不是 Bug1.1 Set 的语义只保证不重复不保证顺序很多人第一次看到collect_set的乱序结果时第一反应是“这个函数是不是有 bug”。真不是。collect_set从名字就能看出来它生成的是一个“集合”而数学上的“集合”天然没有顺序这个概念。你去翻任何一本集合论教材都不会给你定义一个“有顺序的集合”因为这两件事在定义上是互斥的。collect_set在 Hive 内部的实现是基于哈希集合HashSet的。哈希集合在存储元素时会根据元素的哈希值和当前数组容量计算一个存放位置。遍历这个集合的时候元素的输出顺序完全依赖哈希槽位的排列方式和插入顺序没有关系。今天跑是这个顺序明天同样的数据跑可能就是另一个顺序因为内存布局、并行度、数据量都会影响哈希集合的迭代结果。把这个逻辑翻译成生活化的话collect_set相当于一个布袋你把 A、B、C 挨个放进去然后伸手进去掏掏出来是什么顺序完全看运气布袋不会记录你放进去的顺序。collect_list则是一个筐你放进去什么它就在里面保持什么先后顺序。1.2 Hive 里 collect_set 与 collect_list 的本质差异我经常跟团队里的新人说理解这两个函数可以简单记成一句话collect_list是“顺序敏感的追加”collect_set是“顺序无关的去重”。这也是为什么我们在思考“有序 collect_set”时第一反应不能是“让 set 排序”而应该是“怎么绕开 set 的乱序”。从使用场景看两者的差异非常明显collect_list(col)按输入行的顺序把每行的 col 追加进数组不去重。同一个值可能出现多次。collect_set(col)按集合语义去重同一个值只保留一个。但保留哪个“位置”上的值、输出顺序是什么都不保证。如果你要的是“去重后的有序数组”本质上就是两个需求的叠加先要有序再去重。而collect_set把“去重”做了把“顺序”彻底扔了。collect_list把“顺序”保住了但没有去重能力。所以你的任务其实是用 collect_list 保住顺序然后用其他手段去重或者把顺序信息编码进元素让 collect_set 排序后还能还原。这里还要说一个新手常犯的认知误区有人觉得“collect_set 就是 collect_list 加了个 distinct 而已”。不对。distinct 是在有序列表上删掉重复项顺序可以保留但哈希集合从一开始就不关心顺序。这两个函数在引擎层面的实现路径都不一样不能简单类比。1.3 为什么这个问题这么容易踩坑因为大多数业务场景里“去重”和“排序”是同时出现的。比如我前面提到的订单商品聚合你必须先按时间排好再去重才能拿到“每个商品最早出现的位置”。但是 SQL 里最容易写的写法恰恰是反过来的select order_id, collect_set(item_id) as items from order_items group by order_id这条 SQL 能跑结果看着也像那么回事但items数组里的顺序是完全随机的。你把它接到报表里第一次看是[A, B, C]第二次刷新变成[C, B, A]业务方立刻就来质问数据是不是有问题。这种“看起来能用、一上线就翻车”的问题才是它成为经典坑的原因。2. 三种“我看别人都这么写”的伪有序写法为什么一跑就乱2.1 误区一外层 ORDER BY 摆尾数组内顺序纹丝不动最常见的错误写法是在group by后面加一个order by order_id以为这样数组里就有序了select order_id, collect_set(item_id) as items from order_items group by order_id order by order_id这条 SQL 里的order by作用仅仅是让最终输出的行按order_id排好跟items数组内部元素的顺序没有半毛钱关系。因为聚合动作发生在group by那一层collect_set(item_id)在那个瞬间就已经执行完了order by只是对聚合后的结果集做输出排序根本碰不到数组内部。我见过有人在客户端反复执行同一条 SQL发现数组顺序每次都不一样以为是集群不稳定或者数据被改了。其实只是哈希集合的迭代顺序受运行时环境影响数据没变但内存布局变了输出就变了。这属于典型的“把引擎的随机行为当成玄学”。2.2 误区二sort_array(collect_set(x)) 只做得到字典序第二种常见写法是看到sort_array这个函数以为万事大吉select order_id, sort_array(collect_set(item_id)) as items from order_items group by order_id这条 SQL 确实能让数组“有序”但只是字典序。如果item_id是字符串那就是按 A、B、C 这种字母序排如果是数值那就是按数值大小排。而业务上真正想要的往往是“按创建时间排”“按点击次数排”“按配置的权重排”sort_array一概不认。换句话说sort_array(collect_set(x))解决的是“对元素本身排序”不是“按你指定的业务字段排序”。回到我们的场景里商品 ID 是C开头的字典序排完可能是[A, B, C]但按创建时间排应该是[C, A, B]完全对不上。如果你的业务“有序”恰好就是字典序那没问题但绝大多数业务要的都不是字典序。顺带提一句sort_array默认升序如果想要降序Hive 2.2 以上支持sort_array(array, false)这个第二参数。但是别高兴太早它依然只是“元素自身的序”解决不了“按另一个字段排序”的问题。2.3 误区三先 collect_set 再想办法排序信息已经丢了还有一种思路是把问题推到下游解决先collect_set拿到数组之后再排序。比如把数组导出到 Python 里sorted()或者用 Spark 的array_sort再处理。这种思路最大的问题是当你调用 collect_set 的那一刻每行记录对应的排序字段已经被丢弃了。数组里只有item_id没有create_time下游无论怎么排都不可能还原“按时间排好序的商品 ID 序列”。除非你的排序依据能从item_id本身推导出来否则这就是一条死路。我见过有人很执着试图在数组里“用正则匹配”提取出时间信息这基本是在给自己挖坑。数组元素里根本没有时间字段正则再厉害也无中生有。正确的方法一定是在聚合之前就把排序字段保留下来把它和值一起收进数组排序完成之后再卸掉包袱。3. 正解一内层去重 distribute by sort by让 collect_list 照单全收3.1 核心思路把顺序问题转化为“进入聚合算子前的行序问题”既然collect_list是按输入行的顺序追加元素的那么只要我们能保证“进入聚合算子之前同一个分组的行已经按目标字段排好序”collect_list拿到的数组自然就是有序的。这就是方案一的核心逻辑。但还有一个前置问题collect_list不去重。所以我们需要先在子查询里对(order_id, item_id)做一次去重保留每个商品最早出现的那条记录然后再排序、收集。顺序必须是“先去重再排序最后 collect_list”不能先 collect_list 再想办法去重因为一旦把重复值都收进数组再去重时保留哪一条仍然不确定。3.2 完整 SQL 与逐步拆解直接上可运行的 SQLselect order_id, collect_list(item_id) as ordered_items from ( select order_id, item_id, create_time from ( select order_id, item_id, create_time, row_number() over ( partition by order_id, item_id order by create_time ) as rn_dup from order_items ) t1 where rn_dup 1 distribute by order_id sort by order_id, create_time ) t2 group by order_id拆开来看这个 SQL 分了三层第一层最内层子查询用row_number()按order_id, item_id分组并按create_time排序给每个组内的记录编号。这样同一个商品在同一个订单里的多条记录只有最早那条的rn_dup 1。第二层中间子查询where rn_dup 1过滤掉重复商品实现去重。然后关键来了——distribute by order_id保证同一个订单的数据进入同一个 reducersort by order_id, create_time保证在 reducer 内部按创建时间排好序。第三层外层group by order_id执行聚合collect_list(item_id)按 reducer 收到的行顺序追加元素。由于行已经按create_time排好数组天然有序。3.3 为什么这个方案在生产环境最常用方案一最大的优势是性能好。它没有做全局排序而是把排序压力分散到每个 reducer 内部属于分布式环境下的“局部有序”。数据量越大这种“distribute by sort by”的优势越明显。相比之下如果为了排序用全局order by所有数据都要经过一个 reducer基本等于单机排序数据量一大就卡死。但我也要诚实说一句这个方案能保证顺序依赖一个前提——collect_list 在进入聚合算子时保持输入行的顺序。这在 Hive 的 MapReduce、Tez 和 LLAP 引擎下对于已经通过distribute by分组、sort by排序的行流实践上是稳定可靠的。我在多个 Hive 版本上验证过同一分组的数据在第二阶段通常能保持第一阶段的有序输出。不过如果你在做的是审计、对账这类对结果一致性有极强要求的场景或者你们的集群会把同一个分组的输出文件拆成多个输入块导致多个 map 任务并行读取我建议你用方案二来获取更严格的确定性。注意很多人在这个方案里漏掉去重这一步直接对原始表做distribute by和collect_list结果数组里全是重复商品然后又回来问“为什么 collect_list 不去重”。先去重再收集次序不能反。4. 正解二把排序键“缝”进元素里交给 sort_array 做确定性排序4.1 定长前缀拼接的原理让字符串排序变成数值排序方案二的思想是既然sort_array能把数组按字典序排好那我就把“排序键”和“真实值”拼成一个字符串让字典序排序的结果等价于按排序键数值排序的结果。实现方法很巧妙把排序字段转成固定长度的字符串用lpad左补零然后和真实值用分隔符拼接放进数组。sort_array排序时会比较每个元素的完整字符串由于前缀长度固定前缀的字典序就等价于数值的大小序。排序完成后再用正则把前缀剥掉剩下就是有序的真实值数组。关键点是“固定长度”。如果前缀长度不固定比如时间戳 10 位和 13 位混在一起字典序就会出错——100小于99但在数值上 100 大于 99。所以必须用lpad补零让所有前缀位数一致。时间戳转成秒级整数目前是 10 位毫秒级是 13 位我习惯补到 20 位留足余量。前缀和真实值之间的分隔符选一个业务数据中不会出现的字符。4.2 完整 SQL 与剥前缀细节还是订单商品场景的完整 SQLselect order_id, concat_ws(,, regexp_replace( concat_ws(,, sort_array( collect_list( concat( lpad(cast(create_time_sec as string), 20, 0), #, item_id ) ) ) ), [0-9]{20}#, ) ) as ordered_str from ( select order_id, item_id, create_time_sec from ( select order_id, item_id, unix_timestamp(create_time) as create_time_sec, row_number() over ( partition by order_id, item_id order by create_time ) as rn_dup from order_items ) t1 where rn_dup 1 ) t2 group by order_id执行过程是这样的最内层子查询里先把create_time转成秒级整数create_time_sec并用row_number()按(order_id, item_id)去重保证同一个商品只保留最早那条。collect_list收集concat(lpad(..., 20, 0), #, item_id)得到一堆形如00000000000000000001#C、00000000000000000010#A的字符串。sort_array按字符串字典序排序。由于前缀固定 20 位数字排序结果等价于按create_time_sec数值升序排。两层concat_ws把数组拼成字符串再用regexp_replace把[0-9]{20}#这种前缀全局替换掉得到最终的C,A,B。如果你希望输出的是数组而不是字符串可以在外层再加一层split(ordered_str, ,)把字符串转回数组。4.3 这个方案的适用场景和注意事项方案二的优点是确定性极强。它不依赖执行引擎内部的任何“潜规则”纯粹靠sort_array的字典序语义完成排序不管底层是 MapReduce、Tez 还是别的引擎结果都一样。对于对账、报表、需要严格一致性的场景我优先推荐它。它的缺点是性能开销比方案一高。每个元素都要拼接前缀、排序完还要剥前缀字符串处理量明显增加。如果单个分组内的元素数量很大sort_array本身是内存内排序复杂度 O(N log N)再加上字符串拼接和正则替换整体会比较慢。所以方案二更适合“分组数很多、但每组元素数量不大”的场景。写这个方案时还有几个细节容易踩坑前缀位数一定要够。如果时间戳超过 20 位lpad会截断排序结果就会错。秒级时间戳没问题毫秒级也远没到 20 位。分隔符#要保证业务数据里不会出现。如果字段是自由文本建议用\u0001这类不可见控制字符但正则匹配时要注意转义写法。如果要在sort_array基础上做降序可以用sort_array(..., false)但注意它会把整个数组降序排列剥前缀的正则不用改。同一个商品在原始表里有多条记录时必须先row_number去重。如果直接交给collect_set去重保留哪一条是不确定的排序结果可能不符合业务预期。5. 正解三窗口函数版 collect_list最简洁但要注意窗口帧5.1 窗口版 SQL 与默认 frame 的坑Hive 2.2 之后collect_list可以作为窗口函数使用并且支持在over子句里写order by。这给我们提供了一种非常简洁的有序聚合写法select order_id, ordered_items from ( select order_id, collect_list(item_id) over ( partition by order_id order by create_time rows between unbounded preceding and unbounded following ) as ordered_items, row_number() over ( partition by order_id order by create_time ) as row_num from ( select order_id, item_id, create_time, row_number() over ( partition by order_id, item_id order by create_time ) as rn_dup from order_items ) t1 where rn_dup 1 ) t2 where row_num 1这里最关键的坑是窗口帧。如果你在over里写了order by但不指定rows between默认的窗口帧是range between unbounded preceding and current row也就是从分区起点到当前行。这会导致collect_list只收集到当前行之前的元素每一行的数组都在变化取哪一行都不对。所以必须显式写成rows between unbounded preceding and unbounded following让窗口覆盖整个分区。这样每一行拿到的ordered_items都是完整的、按create_time排序后的商品数组。最后用row_number() 1随便取一行即可。内层子查询的row_number() over (partition by order_id, item_id order by create_time)负责去重和方案一、方案二里的逻辑一致。这个方案是三个方案里写起来最短的语义也最接近“按业务需求直接表达”。性能上窗口函数会在执行计划中做分区排序和distribute by sort by类似也是局部排序。但它有一个隐含代价collect_list在窗口内会把整个数组复制到每一行如果单个分区内的行数很多内存和 IO 开销会成倍增加。建议在分区内行数不大的场景使用。5.2 Spark SQL 里的类似场景如果你用的是 Spark SQL或者数据跑在基于 Spark 的平台上同样的问题也会出现。Spark 的collect_set同样不保证顺序依赖它的结果顺序和依赖运气差不多。Spark SQL 里有一个额外的函数array_distinct。它可以在保留数组元素顺序的情况下去掉重复项用途正好是“有序去重”select order_id, array_distinct(collect_list(item_id)) as items from ( select order_id, item_id from order_items distribute by order_id sort by order_id, create_time ) t group by order_id但要注意array_distinct保留的顺序是collect_list输入行的顺序而 Spark 的 map 端部分聚合可能打乱这个顺序。如果你需要强保证还是用窗口函数版本或者把collect_list放进带order by的窗口里select order_id, collect_list(item_id) over ( partition by order_id order by create_time rows between unbounded preceding and unbounded following ) as items from ...这个写法和 Hive 的窗口版几乎一样。总之在 Spark 里也不要寄希望于collect_set的“隐性顺序”它只是实现细节版本一变可能就没了。6. 实战选型三种正解怎么挑以及几个进阶变体6.1 三种方案的对比与选型把三种方案放在一起看各自的定位很清楚方案保序方式确定性性能特点适合场景方案一distribute by sort by collect_list依赖执行引擎对有序行流的传递实践中可靠但非绝对语义保证局部排序性能最优数据量大、无强一致审计要求的生产聚合方案二前缀拼接 sort_array 剥前缀纯粹利用字典序函数强保证绝对确定字符串处理开销大组内元素多时偏慢分组多、每组元素少、强一致性要求高方案三窗口函数 collect_list 显式 frameSQL 窗口语义保证强保证分区排序但每行复制完整数组逻辑简洁分区内行数不大我的选择习惯是如果 Hive 版本支持窗口collect_list且分区内行数可控优先方案三因为代码最短、语义最清楚。如果数据量很大、分区内行数也很多用方案一但会先在小数据量上验证顺序稳定。如果是对账、财报这类绝对不能出错的场景用方案二稳字当头慢一点能接受。6.2 进阶场景多列排序、降序、自定义权重除了简单的按时间排序业务里还经常出现几种变体这里统一给思路。多列排序。比如要求“先按时间排时间相同再按商品 ID 排”。方案一里直接改成sort by create_time, item_id就行。方案二里把排序键拼接起来concat(lpad(create_time_sec, 20, 0), #, item_id)这样整个排序键已经包含两级信息sort_array会按拼接后的字符串比较效果等价于先按时间、再按 ID 排序。降序排列。方案一里sort by create_time desc。方案二里可以用sort_array(..., false)也可以把排序键取反用一个足够大的基准数减去时间戳再做前缀。方案三里窗口order by create_time desc就行。自定义权重。有些业务不是按时间排序而是按一个配置好的权重字段排序。这个时候方案二的优势特别明显把权重作为前缀lpad补零后拼进元素sort_array直接按权重排。方案一也支持sort by weight即可。方案三同样支持order by weight。最后再说一个很多人问的细节如果你最终要的不是数组而是逗号分隔的字符串方案二其实已经顺手完成了不需要单独再concat_ws。方案一和方案三拿到数组后可以用concat_ws(,, array_col)转换注意 Hive 里concat_ws可以直接吃数组参数。6.3 我的经验总结“collect_set 如何有序”这个问题说透了就是两件事要么你提前保住顺序信息要么你把顺序信息编码进元素本身。别去试图控制collect_set的哈希迭代顺序那是引擎内部的实现细节今天能用明天未必能用没有任何版本承诺。倒不如把“有序”和“去重”拆开来看用collect_list保序用row_number去重或者用sort_array做确定性排序这才是能长期稳定运行的方案。我在实际项目里还养成了一个习惯写聚合 SQL 之前先问自己三个问题——需要去重吗需要的先后顺序是什么顺序的排列依据还在不在 SELECT 的可见范围内这三个问题想清楚基本就不会再写出“看似有序、实则随机”的collect_set了。
阅读完成 · 觉得有帮助？
