在TSQL中计算时间范围之间的会话 [英] Calculate Sessions Between TimeRange in TSQL

问题描述

这是数据库的一部分：

SessionID    SessionStartTime              SessionCloseTime
24       2012-10-16 01:00:06.000           2012-10-16 01:01:22.000
24       2012-10-16 01:00:08.000           2012-10-16 01:01:10.000
24       2012-10-16 01:00:16.000           2012-10-16 01:01:12.000
24       2012-10-16 01:00:30.000           2012-10-16 01:01:48.000
24       2012-10-16 01:00:41.000           2012-10-16 01:02:08.000
24       2012-10-16 01:00:48.000           2012-10-16 01:01:34.000
24       2012-10-16 01:00:56.000           2012-10-16 01:03:09.000
24       2012-10-16 01:01:02.000           2012-10-16 01:02:13.000
24       2012-10-16 01:01:05.000           2012-10-16 01:03:16.000
24       2012-10-16 01:01:09.000           2012-10-16 01:02:42.000
24       2012-10-16 01:01:15.000           2012-10-16 01:02:48.000
24       2012-10-16 01:01:18.000           2012-10-16 01:02:14.000
24       2012-10-16 01:01:18.000           2012-10-16 01:02:06.000
24       2012-10-16 01:01:42.000           2012-10-16 01:03:16.000
24       2012-10-16 01:01:45.000           2012-10-16 01:03:04.000

我想在此表中添加一个名为SessionCount的新列，然后我将能够看到呼叫高峰，我的意思是同时发生的会话的最大计数。

I want to add a new column to this table named as SessionCount and then I will be able to see the call peaks I mean max count of sessions which happened at the same time.

此表格应该像这样：

SessionID    SessionStartTime              SessionCloseTime            SessionCount
24       2012-10-16 01:00:06.000           2012-10-16 01:01:22.000     1
24       2012-10-16 01:00:08.000           2012-10-16 01:01:10.000     2
24       2012-10-16 01:00:16.000           2012-10-16 01:01:12.000     3
24       2012-10-16 01:00:30.000           2012-10-16 01:01:48.000     4
24       2012-10-16 01:00:41.000           2012-10-16 01:02:08.000     5
24       2012-10-16 01:00:48.000           2012-10-16 01:01:34.000     6
24       2012-10-16 01:00:56.000           2012-10-16 01:03:09.000     7
24       2012-10-16 01:01:02.000           2012-10-16 01:02:13.000     8
24       2012-10-16 01:01:05.000           2012-10-16 01:03:16.000     9
24       2012-10-16 01:01:09.000           2012-10-16 01:02:42.000     10
24       2012-10-16 01:01:15.000           2012-10-16 01:02:48.000     10
24       2012-10-16 01:01:18.000           2012-10-16 01:02:14.000     11
24       2012-10-16 01:01:18.000           2012-10-16 01:02:06.000     11
24       2012-10-16 01:01:42.000           2012-10-16 01:03:16.000     7
24       2012-10-16 01:01:45.000           2012-10-16 01:03:04.000     7

因此，在此表中，在01:01:18同时发生了最多11个通话高峰。在01:01:18有11个通话中。如何在会话处于活动状态时计算会话，然后在会话结束时为+1然后为-1？

so in this table there are maximum 11 call peaks happened at the same time at 01:01:18. There are 11 active calls at 01:01:18. How can I calculate the sessions when session is active then +1 when session is finished then -1?

推荐答案

在上一个扩展答案...

Expanding on the previous answer...

;with ids as 
(
    select *, ROW_NUMBER() over (order by sessionstarttime, sessionclosetime) rid from @t
),
cte as
(
    select SessionStartTime as ChangeTime, 1 as CC, rid from ids    
    union all
    select SessionCloseTime as ChangeTime, -1 as CC, rid from ids    

)
    select ids.id, ids.SessionStartTime,ids.SessionCloseTime, rt
    from
        ids
        inner join
    (
    select cc,rid, changetime,rt from cte
        cross apply 
        (select SUM(cc) as rt from cte c where c.changetime<=cte.changetime) rt             
    ) res
        on ids.rid=res.rid
    where CC=1
    order by SessionStartTime

这篇关于在TSQL中计算时间范围之间的会话的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！