按共享时间对事件进行分组

Question

这里有一个很好的方法:假设您有一个包含开始和停止列的调用日志表，作为日期时间--大致如下所示：

callID, start, end, caller, callee
1, 12/9/13 7:01 am, 12/9/13 7:15 am,  555-1212, 222-1616
2, 12/9/13 7:04 am, 12/9/13 7:06 am,  555-1213, 222-1516
3, 12/9/13 7:04 am, 12/9/13 7:10 am,  555-1222, 222-1646
4, 12/9/13 7:07 am, 12/9/13 7:31 am,  555-1312, 222-1674
5, 12/9/13 7:08 am, 12/9/13 7:10 am,  555-1417, 222-1655

如何找到一天中并发调用的最大数量(重叠开始时间和结束时间的最高调用数)以及该集合的调用I？

试图找出比较多个持续时间的最佳策略。谢谢!

Answer 1

这似乎起作用了。似乎有点复杂(我不喜欢做两次时间连接)，但应该比较容易理解：

declare @t table (callID int not null,start datetime not null,[end] datetime not null,[caller] char(8) not null,callee char(8) not null)
insert into @t(callID, start, [end], [caller], callee) values
(1, '2013-09-12T07:01:00', '2013-09-12T07:15:00',  '555-1212', '222-1616'),
(2, '2013-09-12T07:04:00', '2013-09-12T07:06:00',  '555-1213', '222-1516'),
(3, '2013-09-12T07:04:00', '2013-09-12T07:10:00',  '555-1222', '222-1646'),
(4, '2013-09-12T07:07:00', '2013-09-12T07:31:00',  '555-1312', '222-1674'),
(5, '2013-09-12T07:08:00', '2013-09-12T07:10:00',  '555-1417', '222-1655')

;With Starts as (
    select Distinct start from @t
), Groups as (
    select s.start,COUNT(*) Cnt
    from Starts s inner join @t t on t.start <= s.start and t.[end] > s.start
    group by s.start
), Ordered as (
    select start,Cnt,RANK() OVER (ORDER BY Cnt desc) as rnk
    from Groups
), CallGroups as (
    select start,Cnt,ROW_NUMBER() OVER (ORDER BY start) as CallGroup
    from Ordered
    where rnk = 1
)
select CallGroup,t.*,cg.start
from CallGroups cg
    inner join
        @t t
            on
                t.start <= cg.start and t.[end] > cg.start

@t只是你的样本数据。Starts CTE查找所有的调用启动时间--从逻辑上讲，当一个或多个调用刚刚开始时，并发调用的最大数量是在某个点达到的。

然后，Groups会找到每个启动时间重叠的所有调用。然后，Ordered和CallGroups找到达到最大值的时间(因为数据集中可能有多个最大值，我决定找到所有这样的最大值)，并为每个maxima分配一个单独的CallGroup值。

最后，对于maxima，我们重新选择每个最大值都是活动的所有调用：

CallGroup            callID      start                   end                     caller   callee   start
-------------------- ----------- ----------------------- ----------------------- -------- -------- -----------------------
1                    1           2013-09-12 07:01:00.000 2013-09-12 07:15:00.000 555-1212 222-1616 2013-09-12 07:08:00.000
1                    3           2013-09-12 07:04:00.000 2013-09-12 07:10:00.000 555-1222 222-1646 2013-09-12 07:08:00.000
1                    4           2013-09-12 07:07:00.000 2013-09-12 07:31:00.000 555-1312 222-1674 2013-09-12 07:08:00.000
1                    5           2013-09-12 07:08:00.000 2013-09-12 07:10:00.000 555-1417 222-1655 2013-09-12 07:08:00.000

因此，样本数据的最大值(只有一个)发生在07:08，用于四个调用(如果希望包含计数，只需将cg.Cnt添加到最终查询中即可)。如果有另一个时间到达四个调用，我们将有8个输出行，第二组为4个，其中CallGroup 2。

意识到我可以消除一个CTE。而不是：

, Ordered as (
    select start,Cnt,RANK() OVER (ORDER BY Cnt desc) as rnk
    from Groups
), CallGroups as (
    select start,Cnt,ROW_NUMBER() OVER (ORDER BY start) as CallGroup
    from Ordered
    where rnk = 1
)
select CallGroup,t.*,cg.start
from CallGroups cg
    inner join
        @t t
            on
                t.start <= cg.start and t.[end] > cg.start

我们可以有：

, CallGroups as (
    select start,Cnt,RANK() OVER (ORDER BY Cnt desc) as rnk,
        ROW_NUMBER() OVER (PARTITION BY Cnt ORDER BY start) as CallGroup
    from Groups
)
select CallGroup,t.*,cg.start
from CallGroups cg
    inner join
        @t t
            on
                t.start <= cg.start and t.[end] > cg.start
where cg.rnk = 1

我一直误以为我们需要RANK()的结果，然后才能分配连续的CallGroup值，但事实并非如此。