sql - 使用前一行进行比较？

Question

我正在尝试找出一种比较 SQL Server 2008 中两行的有效方法。我需要编写一个查询来查找 Movement 中的所有行。表有 Speed < 10连续N次。

表的结构是:

事件时间
速度

如果数据是:

2012-02-05 13:56:36.980, 2
2012-02-05 13:57:36.980, 11
2012-02-05 13:57:46.980, 2
2012-02-05 13:59:36.980, 2
2012-02-05 14:06:36.980, 22
2012-02-05 15:56:36.980, 2

然后，如果我查找连续的 2 行，它将返回第 3/4 行(13:57:46.980/13:59:36.980)，如果我查找连续的三行，它将不返回任何内容。数据的顺序仅为 EventTime/DateTime。

你能给我的任何帮助都会很棒。我正在考虑使用游标，但它们通常效率很低。另外，这个表大约有 10m 行，所以效率越高越好! :)

谢谢!

Answer 1

DECLARE
  @n             INT,
  @speed_limit   INT
SELECT
  @n             = 5,
  @speed_limit   = 10

;WITH
  partitioned AS
(
  SELECT
    *,
    CASE WHEN speed < @speed_limit THEN 1 ELSE 0 END   AS PartitionID
  FROM
    Movement
)
,
  sequenced AS
(
  SELECT
    ROW_NUMBER() OVER (                         ORDER BY EventTime) AS MasterSeqID,
    ROW_NUMBER() OVER (PARTITION BY PartitionID ORDER BY EventTime) AS PartIDSeqID,
    *
  FROM
    partitioned
)
,
  filter AS
(
  SELECT
    MasterSeqID - PartIDSeqID    AS GroupID,
    MIN(MasterSeqID)             AS GroupFirstMastSeqID,
    MAX(MasterSeqID)             AS GroupFinalMastSeqID
  FROM
    sequenced
  WHERE
    PartitionID = 1
  GROUP BY
    MasterSeqID - PartIDSeqID
  HAVING
    COUNT(*) >= @n
)
SELECT
  sequenced.*
FROM
  filter
INNER JOIN
  sequenced
    ON  sequenced.MasterSeqID >= filter.GroupFirstMastSeqID
    AND sequenced.MasterSeqID <= filter.GroupFinalMastSeqID

替代的最终步骤(受@t-clausen-dk 启发)，以避免额外的 JOIN .我会测试两者，看看哪个性能更好。

,
  filter AS
(
  SELECT
    MasterSeqID - PartIDSeqID                              AS GroupID,
    COUNT(*) OVER (PARTITION BY MasterSeqID - PartIDSeqID) AS GroupSize,
    *
  FROM
    sequenced
  WHERE
    PartitionID = 1
)
SELECT
  *
FROM
  filter
WHERE
  GroupSize >= @n