MySQL查询：仅筛选Expires字段变更的分组审计记录

问题 #

我负责维护一个记录生产数据库操作审计追踪的MySQL数据库，该审计日志会为每一次INSERT、UPDATE或DELETE操作生成记录。当前表结构及数据示例如下：

需要生成一份按Group字段分组的报表，仅保留Expires字段发生变更的记录：

• 包含每个Group的首次INSERT记录
• 包含UPDATE中Expires字段与同Group上一条记录不同的情况
• 排除仅更新其他字段的行

期望的查询输出如下：

需要编写实现该需求的SQL语句。

解决方案 #

使用MySQL的窗口函数LAG()获取同Group中上一条记录的Expires值，再筛选符合条件的记录：

SELECT 
    AudID,
    AudAction,
    AudTimestamp,
    `Group`,
    Expires,
    User
FROM (
    SELECT 
        *,
        -- 按Group分组、AudTimestamp排序，获取上一条记录的Expires值
        LAG(Expires) OVER (PARTITION BY `Group` ORDER BY AudTimestamp) AS prev_expires
    FROM audit_log
) AS sub
WHERE 
    -- 保留Group的首次INSERT记录
    (prev_expires IS NULL AND AudAction = 'INSERT')
    -- 保留Expires发生变更的UPDATE记录
    OR (AudAction = 'UPDATE' AND Expires != prev_expires)
ORDER BY `Group`, AudTimestamp;

逻辑说明 #

1. 子查询处理：通过LAG()窗口函数，为每条记录匹配同Group中时间更早的上一条记录的Expires值，命名为prev_expires。
2. 筛选规则：
   • 当prev_expires为NULL且操作是INSERT时，说明这是该Group的第一条记录，直接保留。
   • 当操作是UPDATE且当前Expires与prev_expires不相等时，说明过期时间发生了变更，保留该记录。
3. 结果排序：最终按Group和AudTimestamp排序，与期望输出格式一致。

注意：Group是MySQL关键字，查询时需用反引号（`）包裹避免语法错误。

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