如何在AWS Athena查询中筛选计数大于指定数值？

错误原因 #

你在WHERE子句中使用了聚合函数的别名count，但WHERE是在分组（GROUP BY）之前执行的，此时聚合计算还未完成，数据库无法识别这个别名，因此报错。需要用HAVING子句来过滤分组后的聚合结果。

修正后的查询语句 #

SELECT
    concat(cast(date as varchar),' ',substr(time,1,5)) as datetime,
    requestip,
    count(*) as count 
FROM "tk_logs"."btb-cf-aug-2023" 
WHERE
        uri like '/topics/%'
    and cast(concat(cast(date as varchar),' ',substr(time,1,5)) as varchar) >= '2023-08-07 00:59' 
GROUP BY 1,2
HAVING count >= 100
ORDER BY 1 asc

额外优化建议 #

为了避免重复计算datetime表达式、提升查询效率，可以改用CTE预处理字段：

WITH preprocessed_logs AS (
    SELECT
        concat(cast(date as varchar),' ',substr(time,1,5)) as datetime,
        requestip,
        uri
    FROM "tk_logs"."btb-cf-aug-2023"
    WHERE
        uri like '/topics/%'
        and concat(cast(date as varchar),' ',substr(time,1,5)) >= '2023-08-07 00:59'
)
SELECT
    datetime,
    requestip,
    count(*) as count
FROM preprocessed_logs
GROUP BY datetime, requestip
HAVING count >= 100
ORDER BY datetime asc

如果date是日期类型、time是时间类型，建议用标准日期时间函数处理，更规范且性能更好：

SELECT
    date_trunc('minute', cast(concat(date, ' ', time) as timestamp)) as datetime,
    requestip,
    count(*) as count 
FROM "tk_logs"."btb-cf-aug-2023" 
WHERE
        uri like '/topics/%'
    and cast(concat(date, ' ', time) as timestamp) >= timestamp '2023-08-07 00:59:00' 
GROUP BY 1,2
HAVING count >= 100
ORDER BY 1 asc

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