在AWS Athena中使用FILTER WHERE与WHERE为何结果不同？

我正在调试一条简单的AWS Athena查询，发现结果不符合预期：

第一段SQL查询（使用FILTER WHERE） #

WITH a AS (
    SELECT
        id,
        reference_date,
        date,
        COALESCE(failed_sum, 0) AS failed_sum,
        COALESCE(succ_sum, 0) AS succ_sum,
        COALESCE(failed_sum, 0) + COALESCE(succ_sum, 0) AS total_sum
    FROM transactions
    WHERE id = 'ABC'
)
SELECT id,
       SUM(failed_sum) / SUM(total_sum) FILTER (WHERE date >= DATE_ADD('day', -30, reference_date) AND date < reference_date) AS ratio
FROM a
GROUP BY 1;

此查询得到结果6，但这是失败数与总数的比值，预期应小于1。

第二段SQL查询（使用WHERE子句） #

WITH a AS (
    SELECT
        id,
        reference_date,
        date,
        COALESCE(failed_sum, 0) AS failed_sum,
        COALESCE(succ_sum, 0) AS succ_sum,
        COALESCE(failed_sum, 0) + COALESCE(succ_sum, 0) AS total_sum
    FROM transactions
    WHERE id = 'ABC'
)
SELECT id,
       SUM(failed_sum) / SUM(total_sum) AS ratio
FROM a
WHERE date >= DATE_ADD('day', -30, reference_date) AND date < reference_date
GROUP BY 1;

得到预期结果0.5。我原本认为这两个查询逻辑一致，但执行计划存在差异，请问我忽略了什么关键点？

问题原因分析 #

核心差异在于FILTER子句的作用范围：

• 第一个查询中，SUM(failed_sum) / SUM(total_sum) FILTER (...) 的逻辑是：仅对SUM(failed_sum)应用过滤条件，计算符合条件的失败数总和；但SUM(total_sum)没有过滤，仍然是计算所有行的总数总和。如果符合条件的失败数远大于符合条件的总数，就会出现比值大于1的情况（比如符合条件的fail_sum总和是6，而所有行的total_sum总和是1，结果就是6）。
• 第二个查询中，WHERE子句先过滤了整个数据集，再对过滤后的行计算SUM(failed_sum)和SUM(total_sum)，此时两个聚合函数都是基于同一批过滤后的行计算的，得到的是「符合条件的失败数总和」除以「符合条件的总数总和」，这才是你需要的逻辑。

如果你想使用FILTER子句实现和第二个查询相同的效果，需要对两个SUM聚合函数分别应用过滤条件：

WITH a AS (
    SELECT
        id,
        reference_date,
        date,
        COALESCE(failed_sum, 0) AS failed_sum,
        COALESCE(succ_sum, 0) AS succ_sum,
        COALESCE(failed_sum, 0) + COALESCE(succ_sum, 0) AS total_sum
    FROM transactions
    WHERE id = 'ABC'
)
SELECT id,
       SUM(failed_sum) FILTER (WHERE date >= DATE_ADD('day', -30, reference_date) AND date < reference_date)
       /
       SUM(total_sum) FILTER (WHERE date >= DATE_ADD('day', -30, reference_date) AND date < reference_date) AS ratio
FROM a
GROUP BY 1;

这样分子和分母都会基于同一过滤条件的行进行聚合，结果会和第二个查询一致。

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