Выбор последней и конкретной версии в каждой группе записей для нескольких групп

Проблема:
У меня есть таблица, которая записывает строки данных в foo. Каждый раз, когда строка обновляется, добавляется новая строка вместе с номером ревизии. Таблица выглядит так:

id  rev field
1   1   test1
2   1   fsdfs
3   1   jfds
1   2   test2

Обратите внимание, что в таблице последняя запись представляет собой более новую версию первой строки.

Кто-нибудь знает об эффективном способе запроса последней версии строк и конкретной версии записей? Например, запрос для rev=2 будет возвращать 2, 3 и 4 строки (но не замененную 1-ю строку), тогда как запрос для rev=1 дает эти строки с rev <= 1, а в случае дублированных идентификаторов выбирается один с более высоким номером ревизии (запись: 1, 2, 3).

Я действительно не уверен, что это возможно даже в SQL Server...

Я бы не хотел возвращать результат итеративным способом.

Ответы

Ответ 1

Чтобы получить только последние версии:

SELECT * from t t1
WHERE t1.rev = 
  (SELECT max(rev) FROM t t2 WHERE t2.id = t1.id)

Чтобы получить конкретную ревизию, в этом случае 1 (и если элемент не имеет ревизии, но следующая небольшая ревизия):

SELECT * from foo t1
WHERE t1.rev = 
  (SELECT max(rev) 
   FROM foo t2 
   WHERE t2.id = t1.id
   AND t2.rev <= 1)

Это может быть не самый эффективный способ сделать это, но сейчас я не могу найти лучшего способа сделать это.

Ответ 2

Вот как я это сделаю. ROW_NUMBER() требуется SQL Server 2005 или более поздняя версия

Пример данных:

DECLARE @foo TABLE (
    id int,
    rev int,
    field nvarchar(10)
)

INSERT @foo VALUES
    ( 1, 1, 'test1' ),
    ( 2, 1, 'fdsfs' ),
    ( 3, 1, 'jfds' ),
    ( 1, 2, 'test2' )

Запрос:

DECLARE @desiredRev int

SET @desiredRev = 2

SELECT * FROM (
SELECT 
    id,
    rev,
    field,
    ROW_NUMBER() OVER (PARTITION BY id ORDER BY rev DESC) rn
FROM @foo WHERE rev <= @desiredRev 
) numbered
WHERE rn = 1

Внутренний SELECT возвращает все соответствующие записи, и внутри каждой группы id (что PARTITION BY) вычисляет номер строки при упорядочении по убыванию rev.

Внешний SELECT просто выбирает первый элемент (так, тот, который имеет наибольший rev) из каждой группы id.

Вывод, когда @desiredRev = 2:

id          rev         field      rn
----------- ----------- ---------- --------------------
1           2           test2      1
2           1           fdsfs      1
3           1           jfds       1

Вывод, когда @desiredRev = 1:

id          rev         field      rn
----------- ----------- ---------- --------------------
1           1           test1      1
2           1           fdsfs      1
3           1           jfds       1

Ответ 3

Если вам нужны все последние версии каждого поля, вы можете использовать

SELECT C.rev, C.fields FROM (
  SELECT MAX(A.rev) AS rev, A.id
  FROM yourtable A
  GROUP BY A.id) 
AS B
INNER JOIN yourtable C
ON B.id = C.id AND B.rev = C.rev

В случае вашего примера это вернет

 rev field
 1   fsdfs   
 1   jfds   
 2   test2

Ответ 4

Здесь альтернативное решение требует затрат на обновление, но гораздо более эффективно для чтения последних строк данных, поскольку позволяет избежать вычислений MAX(rev). Это также работает, когда вы делаете массовые обновления подмножеств таблицы. Мне нужен был этот шаблон, чтобы гарантировать, что я мог эффективно переключиться на новый набор данных, который был обновлен через длительное пакетное обновление без каких-либо окон времени, когда мы частично обновляли видимые данные.

старение

  • Заменить столбец rev столбец age
  • Создать представление текущих последних данных с фильтром: age = 0
  • Чтобы создать новую версию ваших данных...
    • INSERT: новые строки с age = -1 - это был мой медленный длительный пакетный процесс.
    • ОБНОВЛЕНИЕ: UPDATE table-name SET age = age + 1 для всех строк в подмножестве. Это переключает представление на новые последние данные (age = 0), а также устаревает старые данные в одной транзакции.
    • УДАЛИТЬ: строки, имеющие age > N в подмножестве - при необходимости удалить старые данные

индексирование

  • Создайте составной индекс с age а затем id чтобы представление было красивым и быстрым, и его также можно использовать для поиска по идентификатору. Хотя этот ключ по сути уникален, он временно не уникален, когда вы стареете строки (во время UPDATE SET age=age+1), поэтому вам нужно сделать его неуникальным и в идеале кластеризованным индексом. Если вам нужно найти все версии данного id упорядоченные по age, вам может понадобиться дополнительный неуникальный индекс по id затем по age.

Наконец... Допустим, у вас плохой день, и пакетная обработка прерывается. Вы можете быстро вернуться к предыдущей версии набора данных, выполнив:

  • UPDATE table-name SET age = age - 1 - Откатить версию
  • DELETE table-name WHERE age < 0 - Убирать плохие вещи

Примечание. Я рекомендую называть имя столбца RowAge вместо age чтобы указать, что этот шаблон используется, поскольку он более понятен, чем его значение, связанное с базой данных, и дополняет RowVersion об именах SQL Server RowVersion. Это также не будет конфликтовать с колонкой или представлением, которое должно возвращать возраст человека.

В отличие от других решений, этот шаблон работает для баз данных, отличных от SQL Server.

Ответ 5

SELECT
  MaxRevs.id,
  revision.field
FROM
  (SELECT
     id,
     MAX(rev) AS MaxRev
   FROM revision
   GROUP BY id
  ) MaxRevs
  INNER JOIN revision 
    ON MaxRevs.id = revision.id AND MaxRevs.MaxRev = revision.rev

Ответ 6

SELECT foo.* from foo 
left join foo as later 
on foo.id=later.id and later.rev>foo.rev 
where later.id is null;

Ответ 7

Как насчет этого?

select id, max(rev), field from foo group by id

Для запроса конкретной ревизии, например, ревизии 1,

select id, max(rev), field from foo where rev <= 1 group by id