选择直到PostgreSQL中的行匹配？ [英] Select until row matches in postgresql?
问题描述
在满足某些条件之前是否有选择行的方法？即 limit 的类型，但不限于 N 行，而是直到第一行不匹配行的所有行？
Is there a way to select rows until some condition is met? I.e. a type of limit, but not limited to N rows, but to all the rows until the first non-matching row?
例如，说我有这张桌子：
For example, say I have the table:
CREATE TABLE t (id SERIAL PRIMARY KEY, rank INTEGER, value INTEGER);
INSERT INTO t (rank, value) VALUES ( 1, 1), (2, 1), (2,2),(3,1);
即：
test=# SELECT * FROM t;
id | rank | value
----+------+-------
1 | 1 | 1
2 | 2 | 1
3 | 2 | 2
4 | 3 | 1
(4 rows)
我要按等级排序，然后选择直到超过1的第一行。
I want to order by rank, and select up until the first row that is over 1.
即 SELECT * FROM t ORDER BY等级直到值> 1
，我想返回前两行吗？
and I want the first 2 rows back?
一种解决方案是使用子查询和 bool_or ：
One solution is to use a subquery and bool_or:
SELECT * FROM
( SELECT id, rank, value, bool_and(value<2) OVER (order by rank, id) AS ok FROM t ORDER BY rank) t2
WHERE ok=true
但是即使我只想结束所有行少数几个？
BUT wont that end up going through all rows, even if I only want a handful?
（现实环境：我在表中带有时间戳记的事件，我可以使用窗口查询提前/滞后来选择两个事件之间的时间，我想要只要事件发生在相距不到10分钟的时间范围内，从现在开始的所有事件都将追溯到过去– 提前/滞后窗口查询使情况复杂东西，所以这里是简化的示例）
(real world context: I have timestamped events in a table, I can use a window query lead/lag to select the time between two events, I want all event from now going back as long as they happened less than 10 minutes apart – the lead/lag window query complicates things, so simplified example here)
编辑：按 rank，id
推荐答案
您想要的是一种停止条件。据我所知，SQL中至少没有PostgreSQL的方言。
What you want is a sort of stop-condition. As far as I am aware there is no such thing in SQL, at least PostgreSQL's dialect.
您可以做的是使用PL / PgSQL过程从a读取行。光标并返回它们，直到满足停止条件为止。不会很快，但是可以。这只是带有 IF表达式然后退出的查询的。不需要显式游标，因为如果您 FOR 循环； ELSE返回下一个； END IF; FOR 遍历查询，PL / PgSQL将在内部使用一个游标。
What you can do is use a PL/PgSQL procedure to read rows from a cursor and return them until the stop condition is met. It won't be super fast, but it'll be alright. It's just a FOR loop over a query with an IF expression THEN exit; ELSE return next; END IF;. No explicit cursor is required because PL/PgSQL will use one internally if you FOR loop over a query.
另一个一种选择是创建一个游标并在应用程序中从中读取行的大块，然后在满足停止条件后丢弃最后一块的一部分。
Another option is to create a cursor and read chunks of rows from it in the application, then discard part of the last chunk once the stop condition is met.
无论哪种方式，光标将成为您想要的。
Either way, a cursor is going to be what you want.
stop表达式实际上并不太难在PostgreSQL中实现方式。您必须实现一个新的执行程序节点类型，但是新的CustomScan支持将使扩展变得切实可行。然后，您只需评估一个表达式来决定是否继续获取行。
A stop expression wouldn't actually be too hard to implement in PostgreSQL by the way. You'd have to implement a new executor node type, but the new CustomScan support would make that practical to do in an extension. Then you'd just evaluate an expression to decide whether or not to carry on fetching rows.
这篇关于选择直到PostgreSQL中的行匹配？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
