AWS Athena执行SQL报错Column 'amount' cannot be resolved如何解决

该报错是SQL执行顺序和别名作用域的规则导致的：

• AWS Athena的SQL执行逻辑遵循 FROM → WHERE → GROUP BY → 聚合计算 → HAVING → SELECT → ORDER BY 的优先级顺序
• 你在SELECT子句中定义的别名amount，是在HAVING子句执行之后才生成的，因此HAVING阶段无法识别这个别名，就会报列无法解析的错误
• 你代码里的ORDER BY amount可以正常生效，因为ORDER BY的执行顺序在SELECT之后，能够读取到别名定义

有两种常用修复方式：

方式1：HAVING子句直接复用聚合逻辑 #

不需要改整体结构，直接把HAVING里的别名替换成实际的聚合计算表达式即可，修改后的SQL如下：

select licence, count(distinct (id)) as amount
from "database_name" 
where YEAR(column_year) = 2021
group by licence
having count(distinct (id)) > 10
order by amount desc

方式2：用子查询/CTE提前计算结果 #

如果聚合逻辑复杂不想重复写，可以先把统计结果查询出来，外层再做过滤，示例如下：

-- 用CTE封装统计结果
with stats as (
    select licence, count(distinct (id)) as amount
    from "database_name" 
    where YEAR(column_year) = 2021
    group by licence
)
select * from stats 
where amount > 10
order by amount desc

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