迁移SQL Server查询至AWS Athena时first_value/last_value结果异常求助

问题描述 #

将SQL Server中的查询迁移至AWS Athena后，大量使用first_value()和last_value()的查询每次执行结果不一致。尝试调整partition by参数、ROWS子句范围及排序方式后仍无法解决。示例查询：

select distinct a.col1, a.col2, 
last_value(a.col3) over (partition by a.col1, a.col2 order by a.col1,a.col2  ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) as col3
FROM table1 a
INNER JOIN table2 b
ON (a.col1 = b.col1
AND a.col2 = b.col2)

问题根源 #

示例查询中order by字段与partition by完全一致，导致分区内的行排序没有唯一确定性。Athena基于Trino分布式引擎，遇到重复排序键时，不会保证固定的行处理顺序；而SQL Server可能依赖存储引擎的物理顺序或隐含规则，因此结果稳定。这种非确定性排序是导致每次结果不一致的核心原因。

解决方法 #

1. 添加唯一排序键 #

在窗口函数的order by中加入能唯一标识行的字段（如主键、自增ID、时间戳等），确保分区内的行顺序完全固定。例如：

select distinct a.col1, a.col2, 
last_value(a.col3) over (partition by a.col1, a.col2 order by a.col1, a.col2, a.id  ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) as col3
FROM table1 a
INNER JOIN table2 b
ON (a.col1 = b.col1
AND a.col2 = b.col2)

2. 改用稳定的聚合方式 #

如果需求是获取分区内最后一条（或第一条）的col3值，可使用keep配合聚合函数替代窗口函数，同时避免distinct的额外开销：

select a.col1, a.col2, 
max(a.col3) keep (dense_rank last order by a.id) as col3
FROM table1 a
INNER JOIN table2 b
ON (a.col1 = b.col1
AND a.col2 = b.col2)
group by a.col1, a.col2

3. 验证底层数据稳定性 #

确认table1和table2的底层数据没有频繁更新（如分区追加、文件修改），如果数据本身在变化，查询结果自然会不一致。

总结 #

不少Athena用户都遇到过类似问题，核心都是排序键不唯一导致分布式执行顺序不确定。分布式引擎不会像单节点数据库那样依赖物理存储顺序，必须显式指定唯一排序规则才能保证结果稳定。

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