sql - 按时间 block 查询记录并分组

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 字段以及相距大于某个阈值的创建日期字段。从您的数据来看，一次运行中的记录之间的输入时间最多相隔一分钟，因此安全阈值可能是一分钟或更长时间。

这将为您提供开始时间

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;

根据您的设置，您可能需要在一个查询中完成所有这些操作，但您明白了。另外，第一次和最后一次运行不会包含在其中，因为第一次运行没有开始 id，最后一次运行也没有结束 id。要包含这些，您只需对这两个范围进行查询，并将它们与旧的 DataRunGroups 查询联合在一起以创建新的 DataRunGroups。使用 DataRunGroups 的其他查询将按上述方式工作。