如何使用Amazon Athena查询每5分钟流量总和？求助解决SQL报错

原语句的问题 #

1. 函数名拼写错误：date_trun应为date_trunc
2. 重复调用时间转换函数，降低查询效率
3. GROUP BY中重复写复杂表达式，易出错且可读性差
4. 正则表达式存在匹配边界问题（如末尾无斜杠的URL会提取不到域名）

修正后的SQL（简洁版） #

SELECT
  date_trunc('minute', from_iso8601_timestamp(time)) - interval '1' minute * (minute(from_iso8601_timestamp(time)) % 5) AS time_group,
  REGEXP_EXTRACT(request_url, 'https?://([^/]+)') AS domain,
  SUM(received_bytes) AS total_received_bytes,
  SUM(sent_bytes) AS total_sent_bytes
FROM alb_access_logs
GROUP BY 1, 2
ORDER BY time_group, domain

优化版（更高效、可读性更强） #

用CTE提前处理时间转换和域名提取，避免重复计算：

WITH processed_logs AS (
  SELECT
    from_iso8601_timestamp(time) AS log_time,
    -- 调整正则，兼容末尾无斜杠的URL，若需过滤端口可改为https?://([^/:]+)
    REGEXP_EXTRACT(request_url, 'https?://([^/]+)') AS domain,
    received_bytes,
    sent_bytes
  FROM alb_access_logs
)
SELECT
  -- 将时间向下取整到最近的5分钟边界（如10:03归到10:00，10:06归到10:05）
  date_trunc('minute', log_time) - interval '1' minute * (minute(log_time) % 5) AS time_group,
  domain,
  SUM(received_bytes) AS total_received_bytes,
  SUM(sent_bytes) AS total_sent_bytes
FROM processed_logs
GROUP BY time_group, domain
ORDER BY time_group, domain

额外说明 #

• 若time字段本身已是timestamp类型，可直接去掉from_iso8601_timestamp()转换函数
• 域名提取正则可根据实际日志格式调整，比如处理带端口的域名时，可改为https?://([^/:]+)

内容的提问来源于stack exchange，提问作者Jung Kil Hwang