MySQL监控系统：如何筛选符合每周一次通知规则的最新NOK检查记录

核心逻辑 #

要满足「每个用户最新NOK记录 + 最近7天无通知」的筛选条件，需要同时完成两个判断：

1. 定位每个用户最新的失败检查记录（status=NOK）
2. 排除掉最近7天内已有成功通知（notifiedAt不为空且在近7天范围）的用户

方案一：使用窗口函数（高效推荐） #

利用窗口函数一次性完成分组排序和最近通知时间的统计，适合数据量较大的场景：

SELECT id, userId, checkAt, status, notifiedAt
FROM (
    SELECT 
        *,
        -- 为每个用户的NOK记录按检查时间倒序排名，1为最新
        ROW_NUMBER() OVER (PARTITION BY userId ORDER BY checkAt DESC) AS rn,
        -- 统计该用户所有NOK记录中最近的通知时间
        MAX(CASE WHEN notifiedAt IS NOT NULL THEN notifiedAt ELSE NULL END) OVER (PARTITION BY userId) AS last_notified
    FROM Checks
    WHERE status = 'NOK'
) AS filtered
WHERE 
    rn = 1 -- 保留每个用户最新的NOK记录
    -- 判断最近一次通知是否超出7天，或从未发送过通知
    AND (last_notified IS NULL OR last_notified < DATE_SUB(CURDATE(), INTERVAL 7 DAY))

逻辑验证（针对示例数据，查询时间为2024-07-31）

• 用户10的last_notified为2024-06-21，早于7天前的2024-07-24，符合条件，会被选中
• 用户20的last_notified为2024-07-30，在7天范围内，被排除
• 最终结果仅包含id=1的记录，符合预期

方案二：使用EXISTS子查询（逻辑直观） #

通过两个EXISTS子查询分别完成「最新NOK记录」和「近7天无通知」的判断，逻辑更易懂：

SELECT c.*
FROM Checks c
WHERE 
    c.status = 'NOK'
    -- 确保当前记录是该用户最新的NOK记录
    AND NOT EXISTS (
        SELECT 1 FROM Checks c2
        WHERE c2.userId = c.userId AND c2.status = 'NOK' AND c2.checkAt > c.checkAt
    )
    -- 排除近7天内有过通知的用户
    AND NOT EXISTS (
        SELECT 1 FROM Checks c3
        WHERE c3.userId = c.userId AND c3.status = 'NOK' AND c3.notifiedAt >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
    )

逻辑说明

• 第一个EXISTS：确认当前用户没有比这条记录更晚的NOK检查
• 第二个EXISTS：确认当前用户没有任何一条NOK记录的通知时间在最近7天内

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