SQL Server：获取特定日期之前发生的给定组件集的最后记录

Question

我有一个来自第三方应用程序的名为 StateChanges（190 万行）的大表，其中包含以下数据：

[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

现在，有没有一种方法可以有效地获取我所有组件的所有“先前状态”（id 在一个数组中）< /强>？我已经尝试过：

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

与获取两个日期之间的整个更改集的其他查询相比，此解决方案恰好非常慢（并且提供了我不需要的额外结果）。

谢谢。

Answer 1

我不完全确定我完全理解你的问题 - 但如果你使用的是 SQL Server 2005 及更高版本（你在这方面不够具体），一种方法是使用 CTE（通用表表达式）。

使用此 CTE，您可以按某些条件（即您的 ComponentId）对数据进行分区，并让 SQL Server 对每个分区的所有行进行编号，从 1 开始，按其他一些条件排序 - 即可能 <代码>控制台时间戳。

因此，尝试这样的操作：

;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 按降序排列。

这接近你正在寻找的东西吗？