Athena高效查询：指定时间范围无匹配时获取Id最近历史条目

原始数据 #

查询需求 #

需要查询start（当前时间戳）到end（2024-04-19 00:00:00）时间范围内的数据，按逆时间顺序排列。当指定的end时间戳没有对应Id的条目时，要额外获取该Id在end时间之前的最新一条数据；如果end时间之后没有任何条目，直接返回现有数据即可。

期望输出 #

技术限制 #

需在Athena中实现，要求高效，优先避免存储过程。

通过窗口函数+集合合并的方式即可实现，无需存储过程，具体代码如下：

WITH params AS (
    -- 统一管理时间参数，方便修改
    SELECT 
        CURRENT_TIMESTAMP AS start_time,
        TIMESTAMP '2024-04-19 00:00:00' AS end_time
),
main_data AS (
    -- 获取时间范围内的主数据
    SELECT 
        id,
        timestamp,
        '' AS comment
    FROM your_table, params
    WHERE timestamp BETWEEN end_time AND start_time
),
supplement_data AS (
    -- 获取结束时间前的最新补充数据（仅当主数据无对应记录时保留）
    SELECT 
        id,
        timestamp,
        '结束时间的上一条最新数据，因无对应结束时间（' || CAST(end_time AS VARCHAR) || '）的行' AS comment
    FROM (
        SELECT 
            t.id,
            t.timestamp,
            params.end_time,
            ROW_NUMBER() OVER (PARTITION BY t.id ORDER BY t.timestamp DESC) AS rn
        FROM your_table t, params
        WHERE t.timestamp < params.end_time
    ) sub
    WHERE rn = 1
    AND NOT EXISTS (
        SELECT 1 FROM main_data m WHERE m.id = sub.id AND m.timestamp = sub.timestamp
    )
),
final_data AS (
    -- 合并数据并添加备注标识
    SELECT 
        id,
        timestamp,
        CASE 
            WHEN ROW_NUMBER() OVER (PARTITION BY id ORDER BY timestamp DESC) = 1 THEN '起始时间（最新可用数据）'
            WHEN timestamp = (SELECT MAX(timestamp) FROM main_data WHERE id = combined.id) THEN '所选范围内的最后一条数据'
            ELSE comment
        END AS comment
    FROM (
        SELECT * FROM main_data
        UNION ALL
        SELECT * FROM supplement_data
    ) combined
)
-- 最终按逆时间顺序输出
SELECT id, timestamp, comment
FROM final_data
ORDER BY timestamp DESC;

方案说明 #

1. 高效性：利用窗口函数ROW_NUMBER()快速定位结束时间前的最新记录，EXISTS判断避免重复数据；如果timestamp字段有分区或索引，性能会进一步提升。
2. 灵活性：时间参数集中在params CTE中，修改时只需调整此处即可。
3. 边界处理：自动判断是否需要补充数据，若end_time之后无任何条目，会直接返回主数据，无需额外逻辑。

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