postgresql - 选择直到 Postgres 中的行匹配

Question

给定以下数据结构:

 id | subscription_id | state |         created_at         | ok 
---------+-----------------+-------+----------------------------+----
  1 |               1 | error | 2015-06-30 15:20:03.041045 | f
  2 |               1 | error | 2015-06-30 15:20:04.582907 | f
  3 |               1 | sent  | 2015-06-30 22:50:04.50478  | f
  4 |               1 | error | 2015-06-30 22:50:06.067279 | f
  5 |               1 | error | 2015-07-01 22:50:02.356113 | f

我想检索带有 state='error' 的最后一条消息，直到 state 包含其他内容。

它应该返回这个:

 id | subscription_id | state |         created_at         | ok 
---------+-----------------+-------+----------------------------+----
  4 |               1 | error | 2015-06-30 22:50:06.067279 | f
  5 |               1 | error | 2015-07-01 22:50:02.356113 | f

正在关注 this question和 later this one ，我最终得到了以下查询:

SELECT * from (select id, subscription_id, state, created_at,
   bool_and(state='error') 
   OVER (PARTITION BY state order by created_at, id) AS ok 
   FROM messages ORDER by created_at) m2 
   WHERE subscription_id = 1;

但是，鉴于我添加了 PARTITION BY state，查询只是忽略了所有不包含 error 的 state 并改为显示:

 id | subscription_id | state |         created_at         | ok 
---------+-----------------+-------+----------------------------+----
  1 |               1 | error | 2015-06-30 15:20:03.041045 | f
  2 |               1 | error | 2015-06-30 15:20:04.582907 | f
  4 |               1 | error | 2015-06-30 22:50:06.067279 | f
  5 |               1 | error | 2015-07-01 22:50:02.356113 | f

在找到不同的状态并按照顶部描述的示例仅匹配 ID 4 和 5 后，应该如何进行查询才能“停止”？

Answer 1

如果我没理解错的话，你需要这个:

select * from messages 
where
id > (select coalesce(max(id), 0) from messages  where state <> 'error')
and
subscription_id = 1

假设 id 是唯一的(PK？)列并且更高的 id 表示最新记录。

编辑

没错，正如@Marth 提到的，您可能需要在子查询中添加 ... AND subscription_id = 1