Блог

У меня есть таблица 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) является первичным ключом.

Упрощенно мы можем просто получить все учетные записи, где последовательность больше или равна самому высокому идентификатору последовательности, который является моментальным снимком

 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)
  

Если бы вы хотели выполнить их все, было бы понятнее записать это как join:

 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 строк, и я хочу, чтобы только 2000 строк соответствовали критериям, установленным этими 1000 строками», наиболее четко мыслится в терминах блоков данных, которые соединяются вместе

С помощью AS ( ВЫБЕРИТЕ * ,row_number() поверх (разделение ПО типу.agg_type, ПОРЯДОК.agg_id В ПОРЯДКЕ.»SEQUENCE» DESC) rnk ИЗ учетной записи a ) ВЫБЕРИТЕ * ИЗ a, ГДЕ a.rnk <= 2;

Оконная функция может извлекать это для всех (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