如何在AWS Athena中对比历史数据并检测配置变更？

实现思路 #

通过窗口函数对数据分组排序，筛选出每个团队的最新两条记录，再通过聚合对比两条记录的config字段是否存在差异。

完整查询语句 #

WITH ranked_data AS (
    SELECT
        id,
        config,
        userid,
        team,
        createdon,
        ROW_NUMBER() OVER (
            PARTITION BY userid, team
            ORDER BY createdon DESC
        ) AS row_num
    FROM your_table_name -- 替换为你的表名
    WHERE userid = 'user-01' -- 指定目标用户ID
),
latest_two_records AS (
    SELECT *
    FROM ranked_data
    WHERE row_num <= 2
)
SELECT
    userid,
    team,
    CASE
        -- 若团队仅存在1条历史数据，默认无变更
        WHEN COUNT(*) = 1 THEN FALSE
        -- 对比最新（row_num=1）和次新（row_num=2）的config值
        WHEN MAX(CASE WHEN row_num = 1 THEN config END) != MAX(CASE WHEN row_num = 2 THEN config END) THEN TRUE
        ELSE FALSE
    END AS configchanged
FROM latest_two_records
GROUP BY userid, team;

语句说明 #

1. ranked_data CTE：

   • 按userid和team分组，对每组内的数据按createdon倒序排序，为每条数据分配行号（最新记录行号为1）
   • 提前过滤指定用户的数据，减少后续计算量

2. latest_two_records CTE：

   • 筛选出每个分组内的前两条数据（即最新的两条历史记录）

3. 最终聚合查询：

   • 通过CASE分支处理两种场景：
     • 当团队只有1条数据时，直接返回FALSE
     • 当有两条数据时，对比两条数据的config值，不一致则返回TRUE，否则返回FALSE

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