sql - 在不到 1 小时前创建通知的同一端点上查找没有通知和先前没有响应的响应

Question

背景

作为用户，我可以设置一个具有预期响应状态的端点，然后告诉 Web 服务以给定的时间间隔(例如，每 30 分钟)对该端点执行 ping 操作。

问题

我想找到所有 status 与关联端点的 expected_status 不匹配的响应。对于每个响应，我都会在数据库中生成一个通知行。如果同一端点的前一个响应行具有不到一小时前的关联通知行，我想忽略此响应行。

我创建了一个 fiddle 来演示我的目的。参见 http://sqlfiddle.com/#!15/0927b/1 .

在我的 fiddle 中，我希望 SQL 查询返回 ID 为 2 和 5 的响应。如您所见，我返回了 ID 2、4 和 5。

感谢任何帮助或指点。

Answer 1

您可以使用 NOT EXISTS 查找与您要过滤的条件相匹配的行，或者使用 LEFT JOIN 再次对子选择使用与您要过滤的条件相匹配的行。

第一种情况在条件异常时可能表现更好，因为测试仅在其他测试失败(状态加上没有通知)时完成:

    SELECT e.id, q.id, r.id, n.id, r.status, e.expected_status, n.response_id
      FROM endpoints e
      JOIN requests q on (q.endpoint_id = e.id)
      JOIN responses r on (r.request_id = q.id)
 LEFT JOIN notifications n on (n.response_id = r.id)
     WHERE r.status <> e.expected_status AND n.response_id IS NULL AND
           NOT EXISTS (SELECT 1
                       FROM requests q2
                       JOIN responses r2 ON (r2.request_id = q2.id)
                       JOIN notifications n2 ON (n2.response_id = r2.id)
                       WHERE q2.endpoint_id = e.id AND
                             r2.created_at < r.created_at AND
                             r2.created_at > r.created_at - INTERVAL '1h')

但在条件为常态的情况下，您可能会受益于第二种情况:

    SELECT e.id, q.id, r.id, n.id, r.status, e.expected_status, n.response_id
      FROM endpoints e
      JOIN requests q ON (q.endpoint_id = e.id)
      JOIN responses r ON (r.request_id = q.id)
 LEFT JOIN notifications n ON (n.response_id = r.id)
 LEFT JOIN (
        SELECT q2.endpoint_id, r2.created_at
        FROM requests q2
        JOIN responses r2 ON (r2.request_id = q2.id)
        JOIN notifications n2 ON (n2.response_id = r2.id)
           ) prev ON (prev.endpoint_id = e.id AND prev.created_at < r.created_at AND prev.created_at > r.created_at - INTERVAL '1h')
     WHERE r.status <> e.expected_status AND
           n.response_id IS NULL AND
           prev.endpoint_id IS NULL