Amazon Athena中基于表内count(*)限制SQL查询的高效实现方法

核心优化思路：原方案需要对表进行2次扫描，改用窗口函数可将扫描次数降为1次，大幅提升百亿级数据量下的查询效率，同时简化SQL逻辑。

前置优化说明 #

原方案用固定偏移量取5位accountID，更稳妥的方式是用正则提取，避免字段格式变化导致取值错误：regexp_extract(z, 'accountID: (\\d+)', 1)，如果确认accountID固定是5位、格式完全一致也可以保留原有substr逻辑。

优化后SQL（兼容Athena引擎） #

SELECT
    accountNumber,
    accountID,
    partition_0,
    SUM(CASE WHEN LOWER(status) = 'green' THEN 1 END) AS countGreen,
    SUM(CASE WHEN LOWER(status) = 'red' THEN 1 END) AS countRed
FROM (
    SELECT
        accountNumber,
        regexp_extract(z, 'accountID: (\\d+)', 1) AS accountID,
        status,
        partition_0,
        COUNT(DISTINCT accountID) OVER (PARTITION BY accountNumber) AS accountIdCnt
    FROM example
    -- 可在此处添加partition_0过滤条件，减少扫描数据量
    -- WHERE partition_0 BETWEEN 20211001 AND 20211031
) t
WHERE accountIdCnt = 1
GROUP BY accountNumber, accountID, partition_0

逻辑说明 #

内层查询一次性完成accountID提取、每个账号的关联accountID去重计数两个操作，外层直接过滤掉关联多accountID的异常账号后做聚合，全程仅扫描一次原表，比原CTE方案减少一半的扫描IO开销。

Athena场景额外优化建议 #

• 如果查询有固定的时间范围，一定要在WHERE条件中加入partition_0的范围过滤，Athena会直接跳过不相关的分区目录，大幅减少需要扫描的数据量
• 如果你需要高频执行这类查询，建议对表做预加工：将提取好的accountID作为单独列固化存储，避免每次查询都做重复的字符串运算，可进一步降低CPU消耗、提升查询速度
• 对于超过10亿行的查询，可适当调整Athena的工作组配置，提升可用的计算资源配额

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