Запрос SQL для получения последней цены

У меня есть таблица, содержащая цены на множество разных "вещей" в таблице MS SQL 2005. В день есть сотни записей за штуку, а разные вещи получают обновления цен в разное время.

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

Мне нужно получить последние цены за группу вещей. Следующий запрос работает, но я получаю сотни строк назад, и мне приходится их перебирать и извлекать только один из ThingID. Как я могу (например, через GROUP BY) сказать, что мне нужен последний из ThingID? Или мне придется использовать подзапросы?

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

ОБНОВЛЕНИЕ: В попытке скрыть сложность я поместил столбец идентификатора в int. В реальной жизни это GUID (а не последовательный вид). Я обновил таблицу def выше, чтобы использовать uniqueidentifier.

Ответ 1

Я думаю, что единственное решение с вашей структурой таблицы - работать с подзапросом:

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

(Учитывая, что самый высокий идентификатор также означает новейшую цену)

Однако я предлагаю вам добавить столбец "IsCurrent", который равен 0, если это не последняя цена или 1, если последняя. Это добавит возможный риск несогласованных данных, но это ускорит весь процесс, когда таблица станет больше (если она указана в индексе). Тогда все, что вам нужно сделать, это...

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

UPDATE

Хорошо, Маркус обновил вопрос, чтобы показать, что идентификатор является уникальным, а не int. Это делает запись запроса еще более сложной.

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)

Я бы предложил использовать столбец "IsCurrent" или перейти к другому предложению, найденному в ответах, и использовать таблицу "текущая цена" и отдельную таблицу "история цен" (которая в конечном итоге была бы самой быстрой, поскольку она держит таблицу цен самой маленькой).

(Я знаю, что ThingID внизу является избыточным. Просто попробуйте, если он быстрее с или без этого "WHERE". Не уверен, какая версия будет быстрее после того, как оптимизатор выполнит свою работу.)

Ответ 2

Я бы попробовал что-то вроде следующего подзапроса и забыл об изменении ваших структур данных.

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

Изменить выше, это ANSI SQL, и теперь я предполагаю, что для T SQL не работает более одного столбца в подзапросе. Мариус, я не могу проверить следующее, но попробую;

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

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

Ответ 3

Если маршрут подзапроса был слишком медленным, я бы посмотрел, как обрабатывать ваши обновления цен в виде журнала аудита и поддерживать таблицу ThingPrice - возможно, в качестве триггера в таблице обновлений цен:

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

Первичный ключ будет просто ThingID, а "UpdateID" - это "ID" в исходной таблице.

Ответ 4

Поскольку вы используете SQL Server 2005, вы можете использовать новое предложение (CROSS | OUTTER) APPLY. Предложение APPLY позволяет вам присоединиться к таблице с табличной функцией.

Чтобы решить проблему, сначала определите функцию с табличной оценкой для извлечения верхних n строк из Thing для определенного идентификатора, даты заказа:

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

а затем используйте функцию для извлечения первых 1 записей в запросе:

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

Магия здесь выполняется с помощью предложения APPLY, которое применяет функцию к каждой строке в левом результирующем наборе, затем присоединяется к результирующему набору, возвращаемому функцией, а затем перенастраивает окончательный набор результатов. (Примечание: чтобы выполнить левое соединение, например apply, используйте OUTTER APPLY, который возвращает все строки с левой стороны, а CROSS APPLY возвращает только строки, которые имеют совпадение в правой части)

Бламу: Потому что я не могу оставлять комментарии еще (из-за низких точек повтора) даже не до моих собственных ответов ^^, я отвечу в теле сообщения: - предложение APPLY даже, если оно использует функции с табличной оценкой, оно оптимизируется внутри SQL Server таким образом, что оно не вызывает функцию для каждой строки в левом наборе результатов, а вместо этого берет внутренний sql из функции и преобразует его в предложение соединения с остальной частью запроса, поэтому производительность эквивалентна или даже лучше (если план выбирается правильно сервером sql, и может быть выполнена дальнейшая оптимизация), чем производительность запроса с использованием подзапросов) и в мой личный опыт APPLY не имеет проблем с производительностью, когда база данных должным образом проиндексирована и статистика обновлена (точно так же, как обычный запрос с подзапросами ведет себя в таких условиях)

Ответ 5

Это зависит от характера использования ваших данных, но если старые данные о ценах будут использоваться не так часто, как текущие данные о ценах, здесь может быть аргумент для таблицы ценовой истории. Таким образом, неточные данные могут быть заархивированы в таблицу ценовой истории (возможно, с помощью триггеров) по мере поступления новых цен.

Как я уже сказал, в зависимости от вашей модели доступа это может быть вариант.

Ответ 6

Я конвертирую uniqueidentifier в двоичный файл, чтобы получить MAX. Это должно гарантировать, что вы не получите дубликатов из нескольких записей с идентичными ThingID и PriceDateTimes:

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)

Ответ 7

Поскольку идентификатор не является последовательным, я предполагаю, что у вас есть уникальный индекс на ThingID и PriceDateTime, поэтому только одна цена может быть самой последней для данного элемента.

Этот запрос получит все элементы в списке, если они были оценены сегодня. Если вы удалите предложение where для PriceDate, вы получите самую последнюю цену независимо от даты.

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)

Обратите внимание, что я изменил " > " на " > =", так как у вас может быть цена прямо в начале дня

Ответ 8

Попробуйте это (если вам нужна только последняя цена, а не идентификатор или дата и время этой цены)

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

Ответ 9

Он должен работать без использования глобального столбца PK (например, для сложных первичных ключей):

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

Ответ 10

Возможно, я пропустил тайкс, но как насчет:

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