查询记录并按时间段分组

Question

我有一个可能每天运行几次的应用程序。每次运行都会生成写入表的数据，以报告发生的事件。主报告表如下所示：

Id    SourceId    SourceType    DateCreated
5048  433         FILE          5/17/2011 9:14:12 AM
5049  346         FILE          5/17/2011 9:14:22 AM
5050  444         FILE          5/17/2011 9:14:51 AM
5051  279         FILE          5/17/2011 9:15:02 AM
5052  433         FILE          5/17/2011 12:34:12 AM
5053  346         FILE          5/17/2011 12:34:22 AM
5054  444         FILE          5/17/2011 12:34:51 AM
5055  279         FILE          5/17/2011 12:35:02 AM

我可以说有两次运行，但是我想要一种能够查询日期范围（运行过程的次数）的方法。我想查询一个查询，该查询导致进程开始的时间和组中的文件数。这种查询可以让我了解我想要的东西，因为我可以看到什么日期和时间以及运行了多少文件，但不完全是我想要的。例如，它不能适应从8:58到9:04的运行。例如，它还会对从9:02和9:15开始的运行进行分组。

Select dateadd(day,0,datediff(day,0,DateCreated)) as [Date], datepart(hour, DateCreated) as [Hour], Count(*) [File Count]
From   MyReportTable
Where DateCreated between '5/4/2011' and '5/18/2011'
    and SourceType = 'File'
Group By dateadd(day,0,datediff(day,0,DateCreated)), datepart(hour, DateCreated)
Order By dateadd(day,0,datediff(day,0,DateCreated)), datepart(hour, DateCreated)

我知道，所有接近的运行都可能会归为一组，对此我很好。我只希望得到一个大致的分组。

谢谢！

Answer 1

如果您确定这些运行是连续的并且不重叠，则应该可以使用Id字段来拆分组。查找仅相距1的ID字段以及大于相差某个阈值的日期创建的字段。从您的数据来看，一次运行中的记录看起来最多只能在1分钟内输入一次，因此安全阈值可能是1分钟或更长。

这将为您提供开始时间

SELECT mrtB.Id, mrtB.DateCreated
FROM MyReportTable AS mrtA
INNER JOIN MyReportTable AS mrtB
    ON (mrtA.Id + 1) = mrtB.Id
WHERE DateDiff(mi, mrtA.DateCreated, mrtB.DateCreated) >= 1

我称它为DataRunStarts

现在，您可以使用它来获取有关组的开始和结束位置的信息

SELECT drsA.Id AS StartID, drsA.DateCreated, Min(drsB.Id) AS ExcludedEndId
FROM DataRunStarts AS drsA, DataRunStarts AS drsB
WHERE (((drsB.Id)>[drsA].[id]))
GROUP BY drsA.Id, drsA.DateCreated

我将其称为DataRunGroups。我将最后一个字段称为“已排除”，因为它所持有的ID仅用于定义将被拉出的ID集的结束边界。

现在我们可以使用DataRunGroups和MyReportTable来获取计数

SELECT DataRunGroups.StartID, Count(MyReportTable.Id) AS CountOfRecords
FROM DataRunGroups, MyReportTable
WHERE (((MyReportTable.Id)>=[StartId] And (MyReportTable.Id)<[ExcludedEndId]))
GROUP BY DataRunGroups.StartID;

我称它为DataRunCounts

现在，我们可以将DataRunGroups和DataRunCounts放在一起以获取开始时间和计数。

SELECT DataRunGroups.DateCreated, DataRunCounts.CountOfRecords
FROM DataRunGroups
INNER JOIN DataRunCounts
    ON DataRunGroups.StartID = DataRunCounts.StartID;

Depending on your setup, you may need to do all of this on one query, but you
get the idea. Also, the very first and very last runs wouldn’t be included in
this, because there’d be no start id to go by for the very first run, and no
end id to go by for the very last run. To include those, you would make
queries for just those two ranges, and union them together along with the old
DataRunGroups query to create a new DataRunGroups. The other queries that use
DataRunGroups would work just as described above.