选择日期大于前一行X分钟的多行

Question

我需要SELECT表中的所有行，其中所选行比先前所选行的日期时间大给定的恒定分钟数。举个例子也许最能说明问题。

以下是数据表，我们将称之为myTable。

guid     fkGuid   myDate
-------  -------  ---------------------
1        100      2013-01-10 11:00:00.0
2        100      2013-01-10 11:05:00.0
3        100      2013-01-10 11:10:00.0
4        100      2013-01-10 11:15:00.0
5        100      2013-01-10 11:20:00.0
6        100      2013-01-10 11:25:00.0
7        100      2013-01-10 11:30:00.0
8        100      2013-01-10 11:35:00.0
9        100      2013-01-10 11:40:00.0
10       100      2013-01-10 11:50:00.0
11       100      2013-01-10 11:55:00.0

我想做的是提供一个恒定的增量（比如10分钟），并从第一行返回所有距离前一行10分钟或更长时间的行。因此，10分钟后的结果集应该是这样的：

guid     myDate
-------  ---------------------
1        2013-01-10 11:00:00.0
3        2013-01-10 11:10:00.0
5        2013-01-10 11:20:00.0
7        2013-01-10 11:30:00.0
9        2013-01-10 11:40:00.0
11       2013-01-10 11:55:00.0

常量作为变量传入，因此它可以是任何值。假设是23分钟，那么结果集应该是这样的：

guid     myDate
-------  ---------------------
1        2013-01-10 11:00:00.0
6        2013-01-10 11:25:00.0
10       2013-01-10 11:50:00.0

最后一个例子显示，我从第0行的时间（11:00:00）开始加23分钟，得到下一个>>行，即11:25:00，在新行的时间上加23分钟然后得到下一行（11:50:00），依此类推

我试过用CTE来做这件事，但尽管我可以很容易地恢复所有的时间，或者一次也不恢复，但我似乎不知道如何获得我需要的行。我当前的测试代码使用23分钟硬编码到WHERE子句中：

WITH myCTE AS
(
    SELECT guid,
           myDate,
           ROW_NUMBER() OVER (PARTITION BY guid ORDER BY myDate ASC) AS rowNum
    FROM myTable
    WHERE fkGuid = 100
)
SELECT currentRow.guid, currentRow.myDate
FROM myCTE AS currentRow
LEFT OUTER JOIN
    myCTE AS previousRow
    ON currentRow.guid = previousRow.guid
    AND currentRow.rowNum = previousRow.rowNum + 1
WHERE
    currentRow.myDate > DATEADD(minute, 23, previousRow.myDate)
ORDER BY
    currentRow.myDate ASC

这不会返回任何结果。如果我省略了WHERE子句，我会返回所有行（显然是因为我没有进行筛选）。

我错过了什么？

我们将一如既往地感谢您的任何帮助！

Answer 1

@gilly3，几乎不是SQL voodoo

WITH CTE
AS
(
  SELECT TOP 1
         guid 
        ,fkGuid
        ,myDate
        ,ROW_NUMBER() OVER (ORDER BY myDate) RowNum
  FROM MyTable
  UNION ALL
  SELECT mt.guid
        ,mt.fkGuid
        ,mt.myDate
        ,ROW_NUMBER() OVER (ORDER BY mt.myDate)
  FROM  MyTable mt
        INNER JOIN
        CTE ON mt.myDate>=DATEADD(minute,23,CTE.myDate)
  WHERE RowNum=1
)
SELECT guid
       ,fkGuid
       ,myDate
FROM   CTE
WHERE  RowNum=1

SQL Fiddle在这里

Answer 2

首先，无论where子句如何，联接都不会返回任何行。Guid和rowNum都是每行唯一的键，所以如果Guid相同，rowNum也将相同。您可以看到，通过将previousRow中的字段添加到选择列表中并在不使用where子句的情况下运行查询，联接总是失败的。

接下来，在rowNum + 1上加入可以防止跳过行。您将只选择满足日期筛选条件的相邻行。

可能有一些带有递归查询的SQL voodoo可以使这一点发挥作用，但会对性能造成巨大影响。筛选应用程序代码中的数据。例如，在C#中：

List<DataRow> FilterByInterval(IEnumerable<DataRow> rows, string dateColumn, int minutes)
{
    List<DataRow> filteredRows = new List<DataRow>();
    DateTime lastDate = DateTime.MinValue;
    foreach (DataRow row in rows)
    {
        DateTime dt = row.Field<DateTime>(dateColumn);
        TimeSpan diff = dt - lastDate;
        if (diff.TotalMinutes >= minutes)
        {
            filteredRows.Add(row);
            lastDate = dt;
        }
    }
    return rows;
}