如何使用AWS Athena提取特定事件前后的时间序列数据

实现思路 #

通过窗口函数定位level 1→2的变更事件时间点，再关联原表筛选该时间点前后30秒的所有记录即可，Athena 基于 Presto 引擎，直接使用内置时间函数和窗口函数就能完成需求。

完整查询语句 #

WITH level_change_events AS (
    -- 筛选所有level从1变为2的变更事件，记录事件发生时间
    SELECT 
        event_time
    FROM (
        SELECT 
            timestamp AS event_time,
            level,
            -- 按时间升序取上一行的level值
            LAG(level) OVER (ORDER BY timestamp ASC) AS prev_level
        FROM <替换为你的表名>
        -- 可在此处添加时间范围过滤，缩小扫描数据量，提升查询效率
        -- WHERE timestamp >= '2021-04-01T00:00:00+00:00' AND timestamp < '2021-04-02T00:00:00+00:00'
    ) t
    WHERE prev_level = 1 AND level = 2
)
SELECT 
    s.id,
    s.timestamp,
    s.value,
    s.level
FROM <替换为你的表名> s
CROSS JOIN level_change_events e
-- 筛选事件前后30秒的记录
WHERE date_diff('second', s.timestamp, e.event_time) BETWEEN -30 AND 30
ORDER BY s.timestamp ASC;

场景匹配说明 #

你给出的示例数据中，level 1→2的变更事件发生在id=61的行，对应事件时间为2021-04-01T12:10:10+00:00，前后30秒的时间范围为2021-04-01T12:09:40+00:00 到 2021-04-01T12:10:40+00:00，刚好对应id从58到64的记录，符合预期输出要求。

注意事项 #

• 若你的表中包含多组独立时间序列（如多个设备、多个监测点），需要在LAG()窗口函数的OVER子句中添加PARTITION BY 分组字段，避免不同分组的level值错位匹配
• 若你的timestamp字段为字符串类型，需要先用parse_datetime(timestamp, 'yyyy-MM-dd''T''HH:mm:ssXXX')转换为timestamp类型再参与计算
• 海量数据场景下建议优先添加时间范围过滤条件，减少数据扫描量，降低查询成本

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