resueltos - obtener ultimo id insertado sql server

Consulta SQL para obtener el último precio

Question

Tengo una tabla que contiene los precios de muchas "cosas" diferentes en una tabla de MS SQL 2005. Hay cientos de registros por artículo por día y las diferentes cosas reciben actualizaciones de precios en diferentes momentos.

ID uniqueidentifier not null,
ThingID int NOT NULL,
PriceDateTime datetime NOT NULL,
Price decimal(18,4) NOT NULL

Necesito obtener los últimos precios de hoy para un grupo de cosas. La consulta siguiente funciona, pero estoy recuperando cientos de filas y tengo que recorrerlas y extraer solo la última por ThingID. ¿Cómo puedo (por ejemplo, a través de un GRUPO POR) decir que quiero el último por ThingID? ¿O tendré que usar subconsultas?

SELECT * 
FROM Thing
WHERE ThingID IN (1,2,3,4,5,6)
  AND PriceDate > cast( convert(varchar(20), getdate(), 106) as DateTime) 

ACTUALIZACIÓN: En un intento de ocultar la complejidad, puse la columna ID en un an int. En la vida real es GUID (y no el tipo secuencial). He actualizado la tabla def arriba para usar uniqueidentifier.

Answer 1

Como está utilizando SQL Server 2005, puede usar la nueva cláusula (CROSS | OUTTER) APPLY. La cláusula APPLY te permite unirte a una tabla con una función con valores de tabla.

Para resolver el problema, primero defina una función con valores de tabla para recuperar las n filas superiores de Cosa para un ID específico, fecha pedida:

CREATE FUNCTION dbo.fn_GetTopThings(@ThingID AS GUID, @n AS INT)
  RETURNS TABLE
AS
RETURN
  SELECT TOP(@n) *
  FROM Things
  WHERE ThingID= @ThingID
  ORDER BY PriceDateTime DESC
GO

y luego use la función para recuperar los primeros registros 1 en una consulta:

SELECT *
   FROM Thing t
CROSS APPLY dbo.fn_GetTopThings(t.ThingID, 1)
WHERE t.ThingID IN (1,2,3,4,5,6)

Aquí la magia se realiza mediante la cláusula APPLY, que aplica la función a cada fila en el conjunto de resultados izquierdo, luego se une con el conjunto de resultados devuelto por la función y luego vuelve a generar el conjunto de resultados final. (Nota: para hacer una combinación de la izquierda como aplicar, use la APLICACIÓN DE SALIDA que devuelve todas las filas desde el lado izquierdo, mientras que APLICAR CRUZ solo devuelve las filas que tienen una coincidencia en el lado derecho)

BlaM: Debido a que no puedo publicar comentarios todavía (debido a los puntos de rept bajos) ni siquiera a mis propias respuestas ^^, responderé en el cuerpo del mensaje: -la cláusula APPLY incluso, si usa funciones con valores de tabla, está optimizado internamente por SQL Server de tal manera que no llama a la función para cada fila en el conjunto de resultados izquierdo, sino que toma el sql interno de la función y lo convierte en una cláusula de unión con el resto de la consulta, por lo que el rendimiento es equivalente o incluso mejor (si el plan es elegido por el servidor sql y se pueden realizar más optimizaciones) que la realización de una consulta utilizando subconsultas, y en mi experiencia personal APLICAR no tiene problemas de rendimiento cuando la base de datos está indexados y las estadísticas están actualizadas (al igual que una consulta normal con subconsultas se comporta en tales condiciones)

Answer 2

Como la ID no es secuencial, supongo que tiene un índice único en ThingID y PriceDateTime, por lo que solo un precio puede ser el más reciente para un artículo determinado.

Esta consulta obtendrá todos los artículos en la lista SI fueron tasados ​​hoy. Si elimina la cláusula where de PriceDate obtendrá el último precio independientemente de la fecha.

SELECT * 
FROM Thing thi
WHERE thi.ThingID IN (1,2,3,4,5,6)
  AND thi.PriceDateTime =
     (SELECT MAX(maxThi.PriceDateTime)
      FROM Thing maxThi
      WHERE maxThi.PriceDateTime >= CAST( CONVERT(varchar(20), GETDATE(), 106) AS DateTime)
        AND maxThi.ThingID = thi.ThingID)

Tenga en cuenta que cambié ">" a "> =" ya que podría tener un precio justo al comienzo de un día

Answer 3

Creo que la única solución con la estructura de su tabla es trabajar con una subconsulta:

SELECT *
   FROM Thing
   WHERE ID IN (SELECT max(ID) FROM Thing 
                   WHERE ThingID IN (1,2,3,4)
                   GROUP BY ThingID)

(Dado el ID más alto también significa el precio más nuevo)

Sin embargo, le sugiero que agregue una columna "IsCurrent" que sea 0 si no es el último precio o 1 si es el último. Esto agregará el posible riesgo de datos inconsistentes, pero acelerará todo el proceso cuando la tabla aumente (si está en un índice). Entonces todo lo que tienes que hacer es ...

SELECT *
   FROM Thing
   WHERE ThingID IN (1,2,3,4)
     AND IsCurrent = 1

ACTUALIZAR

De acuerdo, Markus actualizó la pregunta para mostrar que ID es unidireccional, no un int. Eso hace que escribir la consulta sea aún más complejo.

SELECT T.* 
   FROM Thing T
   JOIN (SELECT ThingID, max(PriceDateTime)
            WHERE ThingID IN (1,2,3,4)
            GROUP BY ThingID) X ON X.ThingID = T.ThingID 
                                AND X.PriceDateTime = T.PriceDateTime
   WHERE ThingID IN (1,2,3,4)

Realmente sugeriría utilizar una columna "IsCurrent" o ir con la otra sugerencia encontrada en las respuestas y usar la tabla de "precio actual" y una tabla de "historial de precios" separada (que en última instancia sería la más rápida, porque mantiene el precio la mesa es pequeña).

(Sé que ThingID en la parte inferior es redundante. Solo intente si es más rápido con o sin ese "DÓNDE". No estoy seguro de qué versión será más rápida después de que el optimizador haya hecho su trabajo).

Answer 4

Depende de la naturaleza de cómo se usarán sus datos, pero si los datos de precios anteriores no se usarán casi tan regularmente como los datos de precios actuales, puede haber un argumento aquí para una tabla de historial de precios. De esta forma, los datos no actuales pueden archivarse en la tabla del historial de precios (probablemente por factores desencadenantes) a medida que ingresan los nuevos precios.

Como digo, dependiendo de su modelo de acceso, esta podría ser una opción.

Answer 5

Estoy convirtiendo el identificador único en un binario para que pueda obtener un MAX de él. Esto debería garantizar que no obtendrá duplicados de múltiples registros con ThingIDs y PriceDateTimes idénticos:

SELECT * FROM Thing WHERE CONVERT(BINARY(16),Thing.ID) IN
(
 SELECT MAX(CONVERT(BINARY(16),Thing.ID))
  FROM Thing
  INNER JOIN
   (SELECT ThingID, MAX(PriceDateTime) LatestPriceDateTime FROM Thing
    WHERE PriceDateTime >= CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)
    GROUP BY ThingID) LatestPrices
  ON Thing.ThingID = LatestPrices.ThingID
   AND Thing.PriceDateTime = LatestPrices.LatestPriceDateTime
 GROUP BY Thing.ThingID, Thing.PriceDateTime
) AND Thing.ThingID IN (1,2,3,4,5,6)

Answer 6

Intentaré algo así como la siguiente subconsulta y me olvidaré de cambiar tus estructuras de datos.

SELECT
 *
FROM
 Thing
WHERE 
 (ThingID, PriceDateTime) IN 
 (SELECT 
   ThingID, 
   max(PriceDateTime ) 
  FROM 
   Thing 
  WHERE 
   ThingID IN (1,2,3,4)
  GROUP BY 
   ThingID
 )

Editar lo anterior es ANSI SQL y ahora supongo que tener más de una columna en una subconsulta no funciona para T SQL. Marius, no puedo probar lo siguiente, pero inténtalo;

SELECT
 p.*
FROM
 Thing p,
 (SELECT ThingID, max(PriceDateTime ) FROM Thing WHERE ThingID IN (1,2,3,4) GROUP BY ThingID) m
WHERE 
 p.ThingId = m.ThingId
 and p.PriceDateTime = m.PriceDateTime

Otra opción podría ser cambiar la fecha a una cadena y concatenar con la identificación para que solo tenga una columna. Sin embargo, esto sería un poco desagradable.

Answer 7

Pruebe esto (siempre que solo necesite el último precio , no el identificador o la fecha de ese precio)

SELECT ThingID, (SELECT TOP 1 Price FROM Thing WHERE ThingID = T.ThingID ORDER BY PriceDateTime DESC) Price
FROM Thing T
WHERE ThingID IN (1,2,3,4) AND DATEDIFF(D, PriceDateTime, GETDATE()) = 0
GROUP BY ThingID

Answer 8

Si la ruta de la subconsulta fuera demasiado lenta, consideraría tratar las actualizaciones de precios como un registro de auditoría y mantener una tabla ThingPrice, tal vez como un activador en la tabla de actualizaciones de precios:

ThingID int not null,
UpdateID int not null,
PriceDateTime datetime not null,
Price decimal(18,4) not null

La clave principal sería ThingID y "UpdateID" es la "ID" en su tabla original.

Answer 9

tal vez no entendí las tomas, pero ¿qué hay de a:

SELECT ID, ThingID, max(PriceDateTime), Price FROM Thing GROUP BY ThingID

Answer 10

Debe funcionar sin utilizar una columna PK global (para claves primarias complejas, por ejemplo):

SELECT t1.*, t2.PriceDateTime AS bigger FROM Prices t1 
LEFT JOIN Prices t2 ON t1.ThingID = t2.ThingID AND t1.PriceDateTime < t2.PriceDateTime 
HAVING t2.PriceDateTime IS NULL