我有一个可能每天运行几次的应用程序。每次运行都会生成写入表的数据,以报告发生的事件。主报告表如下所示:
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)
我知道,所有接近的运行都可能会归为一组,对此我很好。我只希望得到一个大致的分组。
谢谢!
如果您确定这些运行是连续的并且不重叠,则应该可以使用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.