基于字段值的限制查询结果

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 (???);

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

国家统计局：

如果有任何帮助的话，我正在使用

• 。
• The (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可能会以相同的方式执行它们)，但我一直认为，使用一个join作为“这里有10000行，而且我只想让2000行符合这1000行所规定的标准”的限制，是最清楚地考虑到数据块连接在一起的。

Answer 2

使用AS ( SELECT *，row_number() over( a.agg_type分区，a.agg_id顺序按帐户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