Athena查询需求：查找时间戳数据中缺失的月份与日期

问题背景 #

数据以按月份分区的JSON格式存储在S3路径s3://monitoring-v0-test-new-files-per-day/CPUUtilization/，示例数据：

{"AccountID":"607780019502","CPUUtilization":"0.338983","EC2Instance":"i-0765e8787747b9aff","Region":"us-east-1","TimeStamp":"2023-01-05T23:00:00Z","month":"1"}

该数据已导入Athena引擎3的test_db.test_table，字段包括CPUUtilization（字符串）、AccountID（字符串）、Region（字符串）、TimeStamp（字符串）、month（字符串），表按month分区。需要编写查询找出数据中缺失的月份和日期，但原查询报错：

line 10:54: mismatched input 'seq'. Expecting: ','

错误原因 #

原查询存在两处核心问题：

1. UNNEST(SEQUENCE(...))语法错误：Athena（基于Presto）中交叉连接UNNEST时需明确关联，且SEQUENCE生成的整数序列直接用于DATE_ADD的逻辑有误
2. array_difference用法错误：all_months是单个日期值而非数组，无法直接参与数组差集计算

修正后的查询语句 #

WITH date_range AS (
    -- 获取数据覆盖的最小/最大月份，确定检查范围
    SELECT
        DATE_TRUNC('month', MIN(CAST(date_parse(TimeStamp, '%Y-%m-%dT%H:%i:%sZ') AS DATE))) AS min_month,
        DATE_TRUNC('month', MAX(CAST(date_parse(TimeStamp, '%Y-%m-%dT%H:%i:%sZ') AS DATE))) AS max_month
    FROM test_db.test_table
),
all_months AS (
    -- 生成时间范围内的所有月份
    SELECT
        DATE_ADD(min_month, INTERVAL (seq - 1) MONTH) AS month_start
    FROM date_range,
         UNNEST(SEQUENCE(1, DATEDIFF('month', min_month, max_month) + 1)) AS t(seq)
),
all_days_in_month AS (
    -- 生成每个月份的所有日期
    SELECT
        month_start,
        DATE_ADD(month_start, INTERVAL (day_seq - 1) DAY) AS day_date
    FROM all_months,
         UNNEST(SEQUENCE(1, DATE_DIFF('day', month_start, DATE_ADD(month_start, INTERVAL 1 MONTH)))) AS d(day_seq)
),
existing_days AS (
    -- 提取现有数据中已存在的日期（去重）
    SELECT
        DATE_TRUNC('month', CAST(date_parse(TimeStamp, '%Y-%m-%dT%H:%i:%sZ') AS DATE)) AS month_start,
        CAST(date_parse(TimeStamp, '%Y-%m-%dT%H:%i:%sZ') AS DATE) AS day_date
    FROM test_db.test_table
    GROUP BY month_start, day_date
)
-- 对比找出缺失的月份和日期
SELECT
    am.month_start AS month,
    -- 标记月份是否缺失
    CASE WHEN ed.month_start IS NULL THEN '缺失' ELSE '存在' END AS month_status,
    -- 列出该月份缺失的日期
    ARRAY_AGG(adi.day_date ORDER BY adi.day_date) FILTER (WHERE ed.day_date IS NULL) AS missing_days,
    -- 列出该月份已存在的日期
    ARRAY_AGG(ed.day_date ORDER BY ed.day_date) AS available_days
FROM all_months am
LEFT JOIN all_days_in_month adi ON am.month_start = adi.month_start
LEFT JOIN existing_days ed ON adi.month_start = ed.month_start AND adi.day_date = ed.day_date
GROUP BY am.month_start, ed.month_start
ORDER BY am.month_start;

逻辑说明 #

1. date_range：锁定数据覆盖的时间边界，避免生成无关月份
2. all_months：生成边界内的所有完整月份，确保无遗漏
3. all_days_in_month：为每个月份生成当月的所有日期，用于每日数据校验
4. existing_days：去重提取已有数据的日期，减少重复计算
5. 最终左连接对比，明确标记缺失月份，并输出每个月份的缺失/存在日期列表

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