Блог

У меня есть эта большая таблица под названием StateChanges (1,9 миллиона строк) из стороннего приложения, которое содержит эти данные:

 [ComponentID] : integer : Id of what changed
[NewStatus] : integer : How it changed (0: OK, 1: Error, 2:Warning)
[ConsoleTimeStamp] : timestamp, index : When it changed.
  

Мне нужно отобразить различные изменения состояния для группы компонентов между двумя датами. Это довольно тривиально. Однако я отображаю свои данные таким образом:

 [OldStatus] --> [New Status] | [ConsoleTimeStamp]
  

Проблема, с которой я сталкиваюсь, заключается в том, что для того, чтобы получить «начальный» статус любого данного компонента, мне нужно получить строки, которые непосредственно предшествуют начальной дате для всех компонентов. Мы не можем быть уверены в каком-либо диапазоне, с 2003 по настоящее время не могло быть никаких изменений.

Для одного компонента я мог бы заставить этот запрос работать достаточно быстро:

 SELECT TOP 1 [NewStatus], [ConsoleTimeStamp] FROM [StateChanges] 
WHERE [ComponentID] = ? AND [ConsoleTimeStamp] < ?
ORDER BY [ConsoleTimeStamp] DESC
  

Теперь, есть ли способ получить все «предыдущие состояния» для всех моих компонентов (идентификаторы находятся в массиве) эффективным способом? Я пытался:

 SELECT ComponentId, NewStatus, MAX(ConsoleTimeStamp) As LastDate FROM StateChanges
WHERE ComponentId IN ({0}) AND ConsoleTimeStamp <= ?
GROUP BY ComponentId,NewStatus
ORDER BY ComponentId ASC, LastDate ASC
  

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

Спасибо.

Я не совсем уверен, что полностью понимаю ваш вопрос, но одним из подходов было бы использование CTE (Common Table Expression), если вы используете SQL Server 2005 и новее (вы недостаточно конкретны в этом отношении).

С помощью этого CTE вы можете разделить свои данные по некоторым критериям — т.Е. вашим ComponentId — и пронумеровать SQL Server все ваши строки, начинающиеся с 1 для каждого из этих разделов, упорядоченные по некоторым другим критериям — т.Е. вероятно ConsoleTimeStamp .

Итак, попробуйте что-то вроде этого:

 ;WITH PartitionedComponents AS
(
   SELECT 
       ComponentId, NewStatus, ConsoleTimeStamp,
       ROW_NUMBER() OVER(PARTITION BY ComponentId ORDER BY ConsoleTimeStamp DESC) AS 'RowNum'
   FROM 
       dbo.StateChanges
   WHERE
       ComponentId IN (.....) 
       AND ConsoleTimeStamp <= (threshold)
)
SELECT 
   ComponentId, NewStatus, ConsoleTimeStamp, RowNum
FROM 
   PartitionedComponents
WHERE
   RowNum <= 2
  

Здесь я выбираю только последние две записи для каждого «раздела» (т.Е. для каждого ComponentId ), упорядоченные по убыванию с помощью ConsoleTimeStamp.

Подходит ли это к тому, что вы ищете??