SQL 查询应用于子查询行的谓词计数

Question

我要执行一个有点复杂的 SQL 查询，我不确定正确的策略是什么。

考虑模型:

event
  foreignId Int
  time      UTCTime
  success   Bool

假设我有一个谓词，我们可以称之为 trailingSuccess，如果最后一个 n events 为 True code> 成功。我想测试这个谓词。也就是说，我想对 event 运行查询，返回 foreignId 的计数，event 是 成功的 最后 n 次(或更多次)event 被记录。

如果重要的话，我正在使用 Postgres，但如果可能的话，我宁愿留在 ANSI 片段中。

计算此查询的明智策略是什么？

到目前为止，我的代码如下:

SELECT count (*)
  FROM (SELECT e.foreignId 
          FROM event e
          ...
          ORDER BY e.time ASC
          LIMIT n)

显然，我并没有走多远。我不确定如何表达对多行进行量化的谓词。

对于假设的用法，n = 4 就可以了。

示例数据:

foreign_id    time     success
1             14:00    True
1             15:00    True
1             16:00    True
1             17:00    True
2             14:00    False
2             15:00    True
2             16:00    True
2             17:00    True
3             14:00    True
3             15:00    True
3             16:00    True

对于示例数据，查询应该返回 1，因为有 n = 4 个成功事件且 foreign_id = 1。foreign_id 2 不算，因为最后 4 个中有一个 False。 foreign_id 3 不算数，因为没有足够的 foreign_id = 3 事件。

Answer 1

尝试使用简单的 GROUP BY 子句查找每个 foreignID 的最新“不成功”条目。在子查询中使用它，您可以将它连接回表，计算有多少记录(对于每个 foreignID)匹配 foreignID 并且具有较新的时间。

类似于:

SELECT lastn.foreignID, count(*)
FROM 
 (SELECT foreignID, MAX(time) AS lasttime
 FROM event
 WHERE success = 'n'
 GROUP BY foreignID
 ) AS lastn
JOIN event AS e
 ON e.foreignID = lastn.foreignID
 AND e.time > lastn.lasttime
GROUP BY lastn.foreignID;

您可以尝试使用左连接等来根据您的需要进行调整。