Athena查询脚本报错：筛选有两种特定金额交易的客户求助

错误原因 #

你的SQL报错核心原因是：Athena的sum()函数不支持对布尔类型值求和。invoice_total_price = cast('14.45' as decimal(20,2))这类比较操作返回的是布尔值（true/false），而sum()仅支持数值类型（如decimal、bigint等），因此触发参数不匹配错误。

修正方案 #

方案1：保留原逻辑，将布尔值转为数值后求和

通过CASE WHEN或IF()函数把比较结果转为1（匹配）或0（不匹配），再用sum()统计，最后判断是否大于0：

select order_customer_id,
sum(CASE WHEN invoice_total_price = cast('14.45' as decimal(20,2)) THEN 1 ELSE 0 END) > 0 as has_14_45_transaction,
sum(CASE WHEN invoice_total_price = cast('17.45' as decimal(20,2)) THEN 1 ELSE 0 END) > 0 as has_17_45_transaction
from orders
where year_month_day between '2022-01-10' and '2022-03-14'
group by order_customer_id

或者用更简洁的IF()函数实现：

select order_customer_id,
sum(IF(invoice_total_price = cast('14.45' as decimal(20,2)), 1, 0)) > 0 as has_14_45_transaction,
sum(IF(invoice_total_price = cast('17.45' as decimal(20,2)), 1, 0)) > 0 as has_17_45_transaction
from orders
where year_month_day between '2022-01-10' and '2022-03-14'
group by order_customer_id

方案2：直接在SQL中筛选符合条件的客户（无需后续Excel处理）

如果目标是直接得到同时有两种金额交易的客户ID，可以用HAVING子句直接过滤，省去后续Excel操作：

select order_customer_id
from orders
where year_month_day between '2022-01-10' and '2022-03-14'
  and invoice_total_price in (cast('14.45' as decimal(20,2)), cast('17.45' as decimal(20,2)))
group by order_customer_id
having count(distinct invoice_total_price) = 2

这个查询会直接返回同时存在14.45和17.45金额交易的客户ID。

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