sql - son - Selección de la versión más reciente y específica en cada grupo de registros, para grupos múltiples

script para restaurar base de datos sql server

Question

El problema:
Tengo una tabla que registra las filas de datos en foo . Cada vez que se actualiza la fila, se inserta una nueva fila junto con un número de revisión. La mesa se ve como:

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

Tenga en cuenta que en la tabla el último registro es una versión más reciente de la primera fila.

¿Alguien sabe de una manera eficiente de consultar la última versión de las filas, y una versión específica de los registros? Por ejemplo, una consulta para rev=2 devolvería las filas 2, 3 y 4ª (no la primera fila reemplazada), mientras que una consulta para rev=1 produce esas filas con rev <= 1 y, en caso de identificaciones duplicadas, la única con el número de revisión más alto se elige (registro: 1, 2, 3).

No estoy seguro de si esto es posible incluso en SQL Server ...

No preferiría devolver el resultado de forma iterativa.

Answer 1

¿Qué tal esto?

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

Para consultar una revisión específica, por ejemplo, la revisión 1,

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

Answer 2

Aquí, una solución alternativa tiene un costo de actualización, pero es mucho más eficiente para leer las últimas filas de datos, ya que evita la computación MAX(rev) . También funciona cuando estás haciendo actualizaciones masivas de subconjuntos de la tabla. Necesitaba este patrón para asegurarme de poder cambiar de manera eficiente a un nuevo conjunto de datos que se actualizó a través de una actualización por lotes de ejecución prolongada sin ninguna ventana de tiempo en la que los datos parcialmente actualizados estuvieran visibles.

Envejecimiento

• Reemplace la columna rev con una columna de age
• Cree una vista de los últimos datos actuales con filtro: age = 0
• Para crear una nueva versión de tus datos ...
  • INSERTAR nuevas filas con age = -1 : este fue mi proceso por lotes de ejecución lenta y larga.
  • UPDATE table-name SET age = age + 1 para todas las filas del subconjunto. Esto cambia la vista a los nuevos datos más recientes (fila = 0) y también envejece los datos más antiguos.
  • ELIMINAR filas que tienen age > N en el subconjunto - Opcionalmente, purgar datos antiguos

Indexación

• Cree un índice compuesto con age y luego id para que la vista sea agradable y rápida, y también se puede usar para buscar por id. Aunque esta clave es efectivamente única, no es única temporalmente cuando está envejeciendo las filas (durante la UPDATE SET age=age+1 ), por lo tanto, tendrá que hacerla no única e idealmente el índice agrupado. Si necesita encontrar todas las versiones de un id determinado, es posible que necesite un índice adicional en el id .

Finalmente ... Digamos que está teniendo un mal día y que se interrumpe el procesamiento por lotes. Puede volver rápidamente a una versión anterior del conjunto de datos ejecutando:

• UPDATE table-name SET age = age - 1 - Retroceder una versión
• DELETE table-name WHERE age < 0 - Limpiar cosas malas

Nota: Recomiendo nombrar el nombre de columna RowAge lugar de la age para indicar que se está utilizando este patrón, ya que es más claro que se trata de un valor relacionado con la base de datos y que complementa la convención de nombres de RowVersion SQL Server. Tampoco entrará en conflicto con una columna o vista que deba devolver la edad de una persona.

A diferencia de otras soluciones, este patrón funciona para bases de datos que no son de SQL Server.

Answer 3

Así es como lo haría. ROW_NUMBER() requiere SQL Server 2005 o posterior

Data de muestra:

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'' )

La consulta:

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

El SELECT interno devuelve todos los registros relevantes, y dentro de cada grupo de id (que es la PARTITION BY ), calcula el número de fila cuando se ordena por rev . rev .

El SELECT externo simplemente selecciona el primer miembro (por lo tanto, el que tiene la mayor rev ) de cada grupo de id .

Salida cuando @desiredRev = 2 :

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

Salida cuando @desiredRev = 1 :

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

Answer 4

Para obtener solamente las últimas revisiones:

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

Para obtener una revisión específica, en este caso 1 (y si un elemento no tiene la revisión aún la próxima revisión más pequeña):

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

Puede que no sea la forma más eficiente de hacer esto, pero en este momento no puedo encontrar una mejor manera de hacerlo.

Answer 5

Si desea las últimas revisiones de cada campo, puede usar

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

En el caso de tu ejemplo, eso volvería.

 rev field
 1   fsdfs   
 1   jfds   
 2   test2

Answer 6

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

Answer 7

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