两个相似Presto SQL查询的offset_list结果差异问题排查

核心原因 #

Presto（以及遵循SQL标准的多数查询引擎）会忽略无LIMIT约束的嵌套子查询中的ORDER BY。SQL的关系模型定义中，未通过LIMIT限定的结果集是无序的，优化器会判定这种排序属于冗余操作，执行时会跳过该步骤。

你的两个查询结果出现差异，本质是优化器处理不同聚合列时选择了不同的执行路径：

• Query1中同时聚合timestamp和offset，优化器碰巧保留了子查询的排序逻辑，让array_agg按预期的时间顺序聚合元素；
• Query2中仅聚合offset，优化器直接跳过了子查询的排序，array_agg的输入行顺序变为随机（你看到的循环排列是数据分布导致的特殊情况），最终生成的数组顺序不符合预期。

这种依赖子查询排序的写法是不可靠的，属于“巧合正确”，后续数据量变化或优化器版本更新都可能导致结果异常。

正确写法 #

要确保array_agg的结果严格按时间顺序排列，必须在**array_agg函数内部显式指定排序规则**，而不是依赖子查询的排序。修改后的查询如下：

修正后的Query1 #

SELECT 
  visitor_id, 
  array_agg(timestamp ORDER BY timestamp) as time, 
  array_agg(offset ORDER BY timestamp) as offset_list 
FROM table_t 
WHERE year = 2023 AND month = 1 AND day = 27 AND request_uri = '/home_page'
GROUP BY visitor_id 
ORDER BY cardinality(offset_list) desc 

修正后的Query2 #

SELECT 
  visitor_id, 
  array_agg(offset ORDER BY timestamp) as offset_list 
FROM table_t 
WHERE year = 2023 AND month = 1 AND day = 27 AND request_uri = '/home_page'
GROUP BY visitor_id 
ORDER BY cardinality(offset_list) desc

关键说明 #

• array_agg(expr ORDER BY col)是Presto支持的语法，会严格按照指定列的顺序聚合元素；
• 去掉了多余的嵌套子查询，简化查询逻辑同时避免优化器的不确定性；
• 这种写法是符合SQL标准的可靠实现，无论聚合多少列，都能保证数组元素的顺序与用户访问时间一致。

内容的提问来源于stack exchange，提问作者Siddhartha