查找没有通知的响应，也没有对不到1小时前创建通知的同一个端点的先前响应。

Question

背景

作为一个用户，我可以设置一个具有预期响应状态的端点，然后告诉web服务在给定的时间间隔(例如，每30分钟一次)平这个端点。

问题

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

我发明了一把小提琴来证明我想要什么。见http://sqlfiddle.com/#!15/0927b/1。

在我的小提琴中，我希望SQL查询返回ID 2和5的响应，正如您所看到的，我返回ID 2、4和5。

如有任何帮助或指示，我们将不胜感激。

Answer 1

您可以使用“不存在”查找与要筛选的条件匹配的行，也可以在子subselect上再次使用与要筛选的条件匹配的行左联接。

当情况不正常时，第一种情况可能表现得更好，因为只有在其他测试失败时才进行测试(状态加上没有通知)：

    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