选择直到行在Postgres中匹配 [英] Select until row matches in Postgres

问题描述

给出以下数据结构：

 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'，直到状态包含其他内容。

I want to retrieve the last messages with state='error' until the state contains something else.

它应返回以下内容：

 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

在和后来，，我在下面出现了以下查询：

Following this question and later this one, I ended up with this query below:

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状态查询只是忽略了所有不包含错误的状态，而是显示了此内容：

However, given that I added PARTITION BY state the query is simply ignoring all state which does not contain error and showing this instead:

 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？

How should the query be made in order to 'stop' after finding a different state and matching following the example described on the top only the ids 4 and 5?

推荐答案

如果我正确理解，则需要这样做：

If I correctly understand, you need this:

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

假定 id 是唯一的（PK？）列，而较高的id则表示最新记录。

Assuming that id is unique (PK ?) column and higher id means latest record.

编辑

没错，就像@Marth提到的那样，可能您需要添加 ...且子查询中的subscription_id = 1

Thats correct, as @Marth mentioned, probably you need add ... AND subscription_id = 1 in subquery

这篇关于选择直到行在Postgres中匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！