如何使用重叠的启动和停止时间（SQL）（t-SQL）在15分钟间隔内总结发生的活动时间 [英] How to sum the activity time that occurred within 15-minute intervals using overlapping start and stop times(SQL)(t-SQL)

问题描述

我想编写一个查询，它将仅使用与活动开始和停止时间相对应的时间戳来计算每天15分钟间隔内发生的活动总量。

I'd like to write a query that will calculate the total amount of activity that occurred within each 15 minute interval of the day using only timestamps that correspond to activity start and stop times.

以下是一个示例数据集：

Here is a sample data set:

    DATE    StartDateTime   StopDateTime
2/2/2015    2/2/2015 7:00   2/2/2015 7:25
2/2/2015    2/2/2015 7:20   2/2/2015 7:29
2/2/2015    2/2/2015 7:35   2/2/2015 7:42
2/2/2015    2/2/2015 8:05   2/2/2015 8:14
2/2/2015    2/2/2015 8:16   2/2/2015 8:20
2/2/2015    2/2/2015 8:29   2/2/2015 8:40
2/2/2015    2/2/2015 8:55   2/2/2015 9:25

这是我想要得到的：

    DATE    Interval       activityTime(min)
2/2/2015    2/2/2015 7:00   15
2/2/2015    2/2/2015 7:15   19
2/2/2015    2/2/2015 7:30   7
2/2/2015    2/2/2015 7:45   0
2/2/2015    2/2/2015 8:00   9
2/2/2015    2/2/2015 8:15   5
2/2/2015    2/2/2015 8:30   10
2/2/2015    2/2/2015 8:45   5
2/2/2015    2/2/2015 9:00   15
2/2/2015    2/2/2015 9:15   10

我搜索过找到一种以我需要的方式组织数据的方法，这是我能够找到的最接近的，尽管我还没有得到它的工作：

I've searched to find a way to organize the data in the way that I need and this is the closest that I've been able to find so far though I haven't been able to get it to work:

将时间+持续时间分为t- sql

我对SQL很新，所以对解决方案的任何解释都将不胜感激。这也是我在stackoverflow上的第一篇文章，所以如果数据不是首选格式，或者有任何其他问题，请让我知道。谢谢！

I'm pretty new to SQL so any explanation of solutions would be much appreciated. This is also my first post on stackoverflow so please let me know if the data are not in the preferred format or if there any additional questions. Thanks!

推荐答案

假设有一些合理的最新版本的SQL Server，这应该是一个好的开始：

Assuming some reasonable recent version of SQL Server, this ought to be a good start:

-- Some sample data.
declare @Samples as Table ( SampleId Int Identity, Start DateTime, Stop DateTime );
insert into @Samples ( Start, Stop ) values
  ( '2/2/2015 7:00', '2/2/2015 7:25' ),
  ( '2/2/2015 7:20', '2/2/2015 7:29' ),
  ( '2/2/2015 7:35', '2/2/2015 7:42' ),
  ( '2/2/2015 8:05', '2/2/2015 8:14' ),
  ( '2/2/2015 8:16', '2/2/2015 8:20' ),
  ( '2/2/2015 8:29', '2/2/2015 8:40' ),
  ( '2/2/2015 8:55', '2/2/2015 9:25' );
select * from @Samples;

-- Find the limits and align them to quarter hours.
declare @Min as DateTime;
declare @Max as DateTime;
select @Min = min( Start ), @Max = max( Stop )
  from @Samples;
set @Min = DateAdd( minute, -DatePart( minute, @Min ) % 15, @Min );
set @Max = DateAdd( minute, 15 - DatePart( minute, @Max ) % 15, @Max );
select @Min as [Min], @Max as [Max];


-- Go for it.
with QuarterHours ( QuarterStart, QuarterStop )
  as (
    select @Min, DateAdd( minute, 15, @Min )
    union all
    select QuarterStop, DateAdd( minute, 15, QuarterStop )
      from QuarterHours
      where QuarterStop < @Max ),
  Overlaps
  as ( select QH.QuarterStart, QH.QuarterStop, S.Start, S.Stop,
  case
    when S.Start <= QH.QuarterStart and S.Stop >= QH.QuarterStop then 15
    when S.Start <= QH.QuarterStart and S.Stop < QH.QuarterStop then DateDiff( minute, QH.QuarterStart, S.Stop )
    when S.Start > QH.QuarterStart and S.Stop >= QH.QuarterStop then DateDiff( minute, S.Start, QH.QuarterStop )
    when S.Start > QH.QuarterStart and S.Stop < QH.QuarterStop then DateDiff( minute, S.Start, S.Stop )
    else 0 end as Overlap
  from QuarterHours as QH left outer join
    @Samples as S on S.Start <= QH.QuarterStop and S.Stop >= QH.QuarterStart )
  select QuarterStart, sum( Overlap ) as [ActivityTime]
    from Overlaps
    group by QuarterStart
    order by QuarterStart;

您可以将最后一个选择 select * from QuarterHours 或 select * from Overlaps 查看一些中间值。

You can change the last select to either select * from QuarterHours or select * from Overlaps to see some of the intermediate values.

解释性说明：

您可以使用任何范围（ @Min / @Max ）你想要的，我只是从示例数据中拿出来，以便运行这个例子。我用同样的原因使用了一个表变量，为了一个例子，不需要创建一个真实的表。

You can use any range (@Min/@Max) you want, I just took them from the sample data so that the example would run. I used a table variable for the same reason, no need to create a "real" table for the sake of an example.

通用表表达式（CTE）通过递归，覆盖所需范围的 QuarterHours 表。 （A 数字表或 tally表也可用于生成四分之一小时。）然后一个 LEFT OUTER JOIN 使用示例数据来查找每个季度的所有重叠，如果有任何。这将保留没有活动的四分之一小时。

The Common Table Expression (CTE) creates, via recursion, a table of QuarterHours that covers the desired range. (A numbers table or tally table could also be used to generate the quarter hours.) Then a LEFT OUTER JOIN with the sample data is used to locate all of the Overlaps, if any, with each quarter hour. That preserves the quarter hours for which there is no activity.

最终 SELECT 总结了结果。

这篇关于如何使用重叠的启动和停止时间（SQL）（t-SQL）在15分钟间隔内总结发生的活动时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！