根据字段值限制查询结果

Question

我有一个具有闲置结构的表account：

| agg_type  | agg_id  | sequence | payload | is_snapshot | timestamp |
| "account" | "agg_1" | 1        | "..."   | false       | ...       |
| "account" | "agg_1" | 2        | "..."   | true        | ...       |
| "account" | "agg_1" | 3        | "..."   | false       | ...       |
| "account" | "agg_1" | 4        | "..."   | false       | ...       |
| "account" | "agg_1" | 5        | "..."   | false       | ...       |
| "account" | "agg_1" | 6        | "..."   | false       | ...       |
| "account" | "agg_1" | 7        | "..."   | true        | ...       |
| "account" | "agg_1" | 8        | "..."   | false       | ...       |

我需要编写一个查询，从特定聚合的最新快照开始，从该表中检索所有行。例如，在这个表的情况下，查询将返回最后两行(序列7和8(。

我认为查询会像一样

SELECT * FROM account 
WHERE
agg_type='account'
AND agg_id='agg_1'
ORDER BY sequence ASC
LIMIT (???);

我不太确定如何实现(???)部分。

Obs:

• 如果有任何帮助的话，我会使用Postgres
• (agg_type，agg_id，sequence(组合是主键

Answer 1

简单地说，我们可以检索序列大于或等于快照的最高序列id的所有帐户

SELECT * FROM account a
WHERE
a.agg_type='account'
AND a.agg_id='agg_1' 
AND a.sequence >= 
(SELECT MAX(sequence) FROM account b WHERE a.agg_type = b.agg_type AND a.agg_id = b. agg_id AND b.is_snapshot = true)

如果你想做所有的事情，把它写成一个连接可能会更清楚：

SELECT a.* 
FROM 
account a
INNER JOIN
(
SELECT 
agg_type, 
agg_id, 
MAX(sequence) as maxseq 
FROM account b 
GROUP BY agg_type, add_id
) maxes
ON 
a.agg_type = maxes.agg_type and
maxes.agg_id = a.max_id and
a.sequence >= maxes.maxseq

这并不是说我们不能用任何一种形式完成任何一项任务(内部postgres可能无论如何都会执行相同的任务(，但我一直觉得使用联接作为"的限制；这里有10000行，我只想要满足这1000行所规定的标准的2000行；最清楚地认为是结合在一起的数据块

Answer 2

使用AS(选择*，row_number((over(partition BY a.agg_type，a.agg_id ORDER BY a."SEQUENCE"DESC(rnkFROM帐户a)从WHERE选择*a.rnk<=2.

Answer 3

窗口函数只需一种排序即可为所有(agg_type, agg_id)组合提取此项：

with mark as (
select *, 
bool_or(is_snapshot) over w as trail_true
from account
window w as (partition by agg_type, agg_id 
order by sequence
rows between 1 following
and unbounded following)
)
select *
from mark
where not coalesce(trail_true, false)
order by agg_type, agg_id, sequence